vLookup

A vLookup queries another SharePoint list and shows the matching rows in a table on your form. Users can open those rows, edit them in place, and add new ones — without leaving the form they are filling in.

The classic use is a parent item with children in a separate list: a purchase request and its order lines, a project and its milestones, a customer and their support cases. But the list can be any list, in any site in the site collection — or in another site collection entirely — and the filter is yours to write.

1. vLookup, repeating section, or related items?

Three elements show a table of other items, and they are easy to confuse. The difference is what each one is for.

A vLookup and a repeating section can point at the same list, and sometimes both are right: the repeating section for entering the rows, the vLookup for showing them somewhere else — on the display form, on a different form, or filtered differently. What a repeating section cannot do is query a list it does not own.

2. Connect to list

The first tab of the vLookup configuration answers which rows.

The filter is where a parent–child vLookup is made. Give the child list a column that holds the parent’s id — ParentID in the example above — and filter on it with a token: ParentID eq '[[fieldValue:_DFFSID]]'. Prefill that same column when adding new rows (section 4) and the two halves line up. _DFFSID is one of the columns DFFS can maintain for you; see System fields. Every other setting on this tab is presentation.

3. Build the table

The second tab answers which columns, and how they look. Each column offers a Custom field label, a Style, a Conditional style, a Totals setting (Sum, Average or Count, with a Prefix and Suffix either side of the result), and the name of a customizer function.

The tab also carries the table’s own settings: a Header over table, a No items found label, a maximum height before the table scrolls, a maximum cell height, and whether the view, edit and delete icons appear on each row. The header, the no-items label and the add button’s label each offer a Multilanguage option, which uses the languages picked on the Miscellaneous tab.

Reorder the columns by dragging the icon at the left of each row under Select fields to display — a gap opens where the column will land. The prefill fields under Add new item reorder the same way. Move up and Move down are still in each row’s ··· menu if you prefer the keyboard.

Show another field from a lookup

A table listing order lines can show which product each line refers to, because that is a column on the list — but nothing about that product. A single-value Lookup column now carries a + Show another field button, listing the fields of the list the lookup points at. Pick one and it becomes a read-only column in the table: a product’s unit price beside the product, a customer’s phone number, a supplier’s status.

• Nothing is added to either list. The column exists in the table only, and never appears in SharePoint’s own views.
• It costs no extra request. The value is fetched by the query the table was already making.
• Formatting comes from the source field’s type, not its full definition. The column knows a date from a number, but not a currency’s locale — so a price shows as a plain number. The totals Prefix is how you put a currency symbol in front of a total; a customizer function is the way to format the cells themselves.
• The value updates when the row is saved, not while it is being edited. Change the lookup on a row inline and the derived column still shows the previously saved value until you save the row.

Calculate a column from the other columns

Add calculated column… opens the formula builder: a title, a formula, live validation, the full function reference, and the list of columns the formula can name — click one and it is inserted at the cursor. Formulas use the same [InternalName] shorthand as repeating sections:

[Quantity] * [UnitPrice]

• A formula can reference ordinary columns, columns derived from a lookup, and other calculated columns — so a second formula can build on the first. Two formulas that name each other resolve to an empty value rather than failing.
• Calculated columns can carry a Sum, Average or Count in the totals row, and can be colour-coded with conditional style, exactly like a real column.
• The value keeps up with a row being edited inline, rather than jumping when the row is saved.
• An empty value counts as 0 in arithmetic — the same behaviour repeating sections have.
• Nothing is written back to the list. The value is worked out as the table is drawn.

Neither kind of column can be sorted, grouped or filtered on. Ordering, grouping and filtering are done by the query, and neither column exists in it. They are display columns. Repeating sections have the same limitation, for the same reason — so if you need to group by a value, make it a real column on the list.

4. Add new item

The third tab lets users create rows in the target list from the form. Switch Add new item on, give the button a label, and choose whether it sits above or below the table.

Prefill the field your filter uses. This is the one setting people get wrong. Prefill field values writes values into the new item’s form before the user sees it — and if the field your filter tests is not among them, the row will be created and then vanish, because it no longer matches the query that draws the table. Prefill it, and the new row appears where it should.

For a document library, Add documents to a specific folder targets a subfolder (empty means the root), and Open EditForm after adding a document takes the user straight to the metadata. Override form panel type controls how the child form opens, taking precedence over the Miscellaneous setting on the child form itself.

5. Inline editing

Switch on Allow inline editing and each row gains an edit control that turns the row itself into inputs, with Save row and Cancel. Two things stay as they are:

• Read-only fields cannot be edited inline — the cell says so.
• Complex field types open the Edit form instead, in the usual panel.

What updates while a row is open for editing. A calculated column follows what you type. A column stored on the list — including one another element works out, such as a repeating section’s line total — keeps showing its saved value, and the totals row with it, until you save the row with the tick.

6. Customizer and prepare functions

A column can be rendered by a function you write in Custom JS. Name it in Name of customizer function and it is called for each cell, receiving the row, the column and the vLookup configuration, and returning the HTML to show.

function vLookupShowStatus(item, field, config) {
    var colour = item.Status === "Received" ? "green" : "grey";
    return '<span style="color:' + colour + '">' + item.Status + '</span>';
}

A column does not need a field at all — leave the field unset and the customizer decides the whole cell.

A customizer function must return its HTML immediately. It cannot query SharePoint or any other service, because it has to hand its result back while the table is being drawn. Writing it as async does not work: the cell would show [object Promise]. If the value has to be fetched from somewhere else, that is what the prepare function is for.

The prepare function, named on the same tab, is called once with every row the query returned, before the table is drawn. If it returns a promise, the table stays on its loading spinner until that promise resolves — so the customizer still runs the instant the table draws, with the data already in place.

async function vLookupResolveOwners(items, config) {
    var ids = items.map(function (item) { return item.OwnerId; }).filter(Boolean);
    if (ids.length === 0) {
        return;
    }
    // One request for the whole table — not one per row
    var filter = ids.map(function (id) { return "Id eq " + id; }).join(" or ");
    var data = await dffs_spGet("Owners", { select: ["Id", "Title"], filter: filter });
    var titleById = {};
    data.forEach(function (o) { titleById[o.Id] = o.Title; });
    items.forEach(function (item) {
        item.__ownerTitle = titleById[item.OwnerId] || "";
    });
}

Because it receives the whole table at once, twenty rows needing a name from another list cost one request rather than twenty. It runs again on every reload, so what it resolved cannot drift out of step with the rows. A prepare function that fails, or never finishes, is reported in the browser console and the table is drawn anyway — a broken one can slow a table down, never hide it.

Full signatures and behaviour: Custom JS — Window API reference.

7. Controlling a vLookup with rules

Both take the vLookup’s id, shown at the bottom of the Connect to list tab as ID for use in a rule or in Custom JS / CSS.

8. Other things worth knowing

• vLookup (add new only) is a second element in the toolbox: the same configuration with the table and query hidden, leaving just the add button. Use it where a form should let users create a related item without showing what already exists.
• Copy a configuration between forms with Copy vLookup config, then add a vLookup on the other form and choose Paste copied vLookup config.
• Coming from Classic DFFS? Import classic vLookup config takes the JSON from a Classic configuration and rebuilds it here.
• It prints. Columns, derived and calculated values and the totals row all render into Save as PDF. Print uses what the live table has already worked out, so a vLookup on a tab nobody opened prints without its prepare function having run.
• Custom JS can reload it with vLookup_loadItems(id), open a row with vlookup_open(vLookupId, form, itemId), and react to a load with dffs_vLookup_callback(vLookupId, items).
• React to a single item changing with dffs_vLookup_itemChanged_callback(vLookupId, action, itemId) — it fires on add, update, remove and cancel, covering the child form panel, inline editing, the delete button and the add-document path. Add, update and remove fire after the write succeeded, so dffs_vLookup_callback follows immediately with the reloaded rows.
• An “add new only” vLookup against a document library uploads into the folder named in its configuration, or the list root. If you read folderPath from dffs_formcontext in such a form, note that it was previously undefined there and is now resolved.

Practical notes

• Write the filter first, then build the table. A table that shows the wrong rows is a filter problem, and no amount of column configuration will reveal it.
• Filter and prefill must name the same column. Nearly every “the row disappeared after I added it” report is these two disagreeing.
• Prefer a derived column over a customizer when you only need to show a field from a lookup. It is one click, costs no extra request, and needs no code.
• Reach for a customizer when formatting matters — a currency, a badge, a link — and for a prepare function only when the value genuinely lives somewhere the query does not reach.
• If you need to sort or group by a computed value, compute it into a real column on the list instead. Derived and calculated columns are drawn after the query, so the query cannot order by them.
• Cross-site-collection vLookups need the fully qualified URL, and a lookup’s target list resolves in the vLookup’s own site collection — not the site the form is on.