Merge with other data

A view is a view of one data object. That used to mean any question spanning two objects left the app: a workflow writing a third, flattened object, or two exports joined in a spreadsheet.

Merge answers those questions in the view itself. It borrows columns from a second object and shows them beside your own, without copying any data and without changing a single record.

Two promises, before anything else

These are worth reading before you use it, because they are what make it safe to try:

• Your row count never changes. Merging only ever adds columns. If a row would find several matches, SPARK stops and asks you which one to show rather than quietly turning one row into three.
• Borrowed columns are read-only. They belong to another object, so nothing in the view will write to them. The dialog says it plainly next to the button: This only shapes the view. Your data is not changed.

Bring in an object

1. Open the data object and the view you want to extend.
2. Click the Merge icon in the grid's header, among the view controls. Merge is not available on a shared space's guest page, and it stays greyed out while the workspace has no other published object: Nothing else to bring in yet. Publish another object first.
3. In Add columns from, search for the object you want. The list separates Linked in your data model from Everything else, and holds only the published objects you can read.
4. Answer how the rows go together, if you are asked (see below).
5. Under Columns to add, tick the columns you want, or Select all. The list offers the columns your workspace added to that object, not Created At and the other bookkeeping columns. To rename one, point at it and click the pencil that appears.
6. Give the result a name under Name this view, or keep the one suggested from the two objects, such as Funds Delegates.
7. Click Add the columns. SPARK saves a new view and opens it; the view you started from stays as it was. On a view other than the default one, Update this view adds the columns to that view instead.

How the rows go together

This is the one question the dialog asks, and the answer depends on what your data model already knows.

When the objects are already linked

Pick an object under Linked in your data model and the pairing is filled in for you, shown in the same two pickers you would use to write one yourself, under the note From your data model. Change it if you need something else. The second picker names the other object's row itself: each fund's Fund Administrator points at one delegate, so the pairing reads Fund Administrator is the delegate itself.

The model's answer is the likeliest one, not the only one. If it is not the pairing you want, change it. Remove it and the dialog offers it back on one line, Your data model connects them through the link it knows, with Use it.

When they are not

You get one question, phrased in your own objects: When does a trade go with a price? Answer it with one or more conditions, each pairing a column on your object with a column on theirs. Add as many as you need with Add another condition; all of them must hold for a row to match. The second picker greys out the columns that hold another kind of value, a date against text for example, so you cannot pair two columns that would never match.

SPARK also looks for likely pairs and offers them on one line, for example ISIN looks like Instrument ID, 94% of values overlap. Use it fills the condition in. When a condition has been filled in for you without being asked, the dialog says so, Filled in for you. Change it if that is not how they go together., so you always know which answers are yours.

Watch the match rate

As soon as the conditions make sense, the dialog measures them against your real rows, up to 10,000 of them, and reports something like 96% matched with the number of rows it read: on the last 48 rows.

The rate never rounds up to 100%: a single unmatched row reads as 99%, because that one row is exactly what you are looking for. Treat anything short of 100% as information rather than a failure. It is telling you how many of your rows will find a value in the borrowed columns, and the ones that do not will simply show empty. A number far lower than you expected usually means the condition is wrong, not that the data is missing.

When a match cannot be measured

If the dialog cannot check a condition, it says why where the rate would be, in the words SPARK refused it with, for example 'Trade Date' holds temporal values and 'Name' holds text values, so they never match. Change what it names and it measures again. When the check itself failed, a lost connection for instance, it reads That match could not be checked. Adjust it and we will try again.

On two very large objects the dialog may not count the matches at all, and says Too big to count the matches here. Saving still checks them. That is not an error: the match is checked exactly when you save.

When a row finds several

If one of your rows matches several rows on the other object, the dialog says so plainly, for example A trade finds up to 2 prices., and will not let you continue until you resolve it. You have two ways out:

• Show just one, choosing first or last, and after by the column that decides which. It starts on Updated At, when the record was last updated.
• Add another condition, narrowing the match until a row finds exactly one.

Which is right depends on the question. A price per trade date wants another condition; the latest known rating wants last. On objects too big to check for duplicates, the dialog says Too big to check for duplicates here. and offers the same choice: If there are any, show just one:.

Living with a merged view

The borrowed columns behave like any others for reading: filter on them, sort on them, total them, and a canvas table loaded from the view shows them too. They cannot be edited, and they do not appear in the record form. On a view that borrows columns, the Merge control stays pressed, with a dot, so you can tell a merged view at a glance.

A merge can also stop working later, through no fault of yours: the other object is unpublished, a column you borrowed is archived, or your access to the other object is withdrawn. SPARK checks a merged view's links every time it loads, and while one is broken the view's rows do not load. Nothing is lost: the data is untouched, and the object's other views still open as before.

Where to go next

• Manage data views
• Attribute types
• Build a canvas