Lookup Fields
A Lookup field lets a user pick a record from another table. This page covers how lookup fields behave, how to bring additional values across with Lookup Field Transfers, and the limitations you should know before designing one.
What a Lookup Field Returns
A lookup field always retrieves the primary key of the record the user selects. More precisely, it resolves to the last field of the target table's primary key.
This has two direct consequences:
- The field type is dictated by that key field. A lookup to Warehouse Receipt Line resolves to
Line No.— an Integer — which is why the created field is an Integer. - You cannot make the lookup return a different field, such as a description or a name. To get any other value, transfer it separately (see Lookup Field Transfers).
The Field Type Is Fixed at Creation
When you create a Lookup field, SOD sets up the table relationship, but the field type comes from what you select at creation. SOD does not infer the type from the target field.
- Make sure the type matches the field you are relating to —
Code[20]for something like "Source No.", Integer for a "Line No.". - There is no way to change a Lookup field's type afterwards through the UI. Changing the type to Code means it is no longer a Lookup field.
- If a field seems stuck with the wrong type, delete it and recreate it. The type is fixed at creation time.
Renaming or recreating a field does not fix a design that was wrong to begin with. In particular, you cannot use the OnValidate trigger of an Integer Lookup field to assign a Code[20] value into it — that produces a compile error such as cannot convert from 'Code[20]' to the type of Argument 1 'Integer'.
Choosing the right design
When you want a value such as "Source No." from a related line to end up on a header record, pick one of these patterns:
| Option | How it works | Use when |
|---|---|---|
| A — Separate stored field | Keep the Integer Lookup for selecting the line, and add a separate Code[20] field for the Source No. Populate it in the selector field's OnValidate trigger with a direct assignment (Rec."YourSourceNoField" := TransferRec."Source No.";) |
You need both the line reference and the code stored |
| B — Code[20] Lookup | Make the lookup field itself a Code[20] relating directly to the "Source No." field |
Capturing the Source No. is the only goal |
| C — Lookup FlowField | Use a FlowField with calculation type Lookup | The value should be derived automatically rather than selected |
The deciding question is whether the user makes a manual selection (a true Lookup, which fires only when the user clicks the lookup) or the value should be derived automatically (a Lookup FlowField, which calculates on its own).
Lookup Field Transfers
The Lookup Field Transfers option appears in the Lookup section when you create a field of type Lookup. It is not new — it has been available for some time.
What it does: when the lookup points to another table (for example a Customer No. lookup to the Customer table), a Lookup Field Transfer populates one or more additional fields on the current record from the selected record.
Example: selecting a customer through the lookup also fills a separate "Customer Name" field with the Name from that customer. Selecting an item number can also bring across the item description.
The primary key comes back through the lookup itself; every additional value needs its own Lookup Field Transfer.
Lookup Field Transfer vs. Lookup FlowField
| Lookup Field Transfer | Lookup FlowField | |
|---|---|---|
| Storage | Copies into a real, stored field | Display only — nothing is stored |
| Editable afterwards | Yes | No |
| Filtering and sorting | Works well — it is a real field | Poor; FlowFields cannot be sorted |
| Updates when source changes | No — a one-time copy at selection time | Yes — always reflects current data |
The one-time-copy behaviour is deliberate and mirrors standard Business Central. On a sales order, Name and Address are transferred when the Sell-to Customer is selected; changing the customer card afterwards does not rewrite the existing order.
When to use which:
- Use a Lookup Field Transfer when you need a stored, editable value, when you need to filter on it, or when the value must stay fixed as of the moment of selection.
- Use a Lookup FlowField when you want a live display value that always reflects the current source, with no stored copy.
Filtering a Lookup
Filters on lookup fields can only reference fields that exist in the current table — the table where the lookup field is defined. A field that only exists in a related or anchor table cannot be used as a filter source.
For Data-Related Tables this matters: if the field you want to filter by (for example a "Job No." that relates records to their anchor) is not stored in the Data-Related Table itself, it cannot be used as a lookup filter there.
Workaround: replicate the needed field into the current table so it becomes available as a filter source. This is why fields sometimes appear duplicated where they look redundant — certain fields exist on the Sales Line, though they logically belong to the Sales Header, precisely so they can be used for filtering within that table.
Feature Lookup Filters
When configuring Feature Lookup Filters on a lookup field in a feature:
- The Filter Field dropdown lists fields from your feature table's own definition, not from the lookup target table.
- To filter the target table (for example table 1001 Job Task) by one of its fields (such as "Job No.") so it matches a value from your record, set Filter Type = Field and point it at the field on your feature table that holds the value to match.
- The target table's field being filtered is the filter target; the source value comes from your feature table.
Known issue (fixed): fields hidden by SOD did not appear in the Filter Field selection list, and typing the field number manually produced a "field does not exist" error. This was confirmed as a defect and the fix has been released to AppSource — update SOD via Apps in the Business Central Admin Center. Workarounds involving placeholder field numbers such as 99999 or 99998 do not work and should not be used.
Which Columns Appear in the Lookup
The columns a user sees when performing a lookup are controlled by the field group used for lookup defined on the target table.
- If the target table does not define that lookup field group, only the primary key field is visible and selectable.
- You cannot add extra columns (document type, name, vendor, …) to such a lookup with SOD alone. Supplying the missing field group definition requires AL coding.
This is a common surprise with tables that come from external or migration data sources — GP migration history tables, for example, have no lookup field group, so only the Document ID is selectable.
Making a Custom Field Searchable
Custom fields can be included in the search drop-down, so their values are matched when a user types into a lookup — for instance when choosing an account in a journal or searching an items list.
- Open Field Settings for the custom field and enable the setting that adds the field to the search drop-down.
- This requires SOD version 12.0.0.562 or later. In earlier versions it was not possible; if the setting is missing, update SOD.
Worked Example: A Clickable URL from a Lookup Table
Goal: let a user pick a code and have the related web address show up as a clickable link.
- Create a lookup table as a Feature, containing a Code field and a text field holding the URL.
- On the target table, add a Code field with a TableRelation (lookup) to that lookup table so users can pick a code.
- Use a Field Transfer to pull the URL from the lookup table into a field on the current record when a code is selected. It updates whenever a different code is chosen.
- Set the URL field's Extended Datatype to URL so it renders as a link.
- Set the URL field to not editable so it displays as a clickable hyperlink rather than editable text.
Limitations
No dynamic "type-then-number" filtering
SOD cannot create a field whose value changes which records another lookup field offers. The pattern to avoid designing around: an "Order Type" field (Sales Order / Purchase Order / Transfer Order) that filters a related "Order No." lookup to show only records of that type — the way the General Journal's Account Type determines whether Account No. lists G/L Accounts, Customers or Vendors.
This behaviour needs a substantial amount of custom code behind the field, which is outside the scope of "Simple".
No filtering of values in standard document lines
SOD cannot add a filter that restricts which values are available in lookup fields on sales and purchase order lines — for example limiting which items can be chosen in Sales Line (table 37) field 6 "No.". This use case is not supported by SOD at all.
Restricting the values available in such fields is provided by the Advanced Cloud Security App — see the Hougaard security product page at https://hougaard.com/security.
Extra lookup columns require AL
See Which Columns Appear in the Lookup. Adding columns to a lookup view on a table without a lookup field group cannot be done from SOD.
Troubleshooting
"Field <name> on <table> is incomplete" when publishing
Symptom: Publishing fails with an error that the field is incomplete, "most likely because information is missing on the Field Settings page".
Cause: The lookup field is not correctly connected to the looked-up field. The diagnostic indicator is that the field's Size is zero. This happens even when the field otherwise looks correctly configured as a lookup pointing at another table.
Resolution: Delete the field and recreate it. Recreating re-establishes the connection so the size and related settings populate properly.
Prevention: after creating any lookup field, check that its Size is not zero before publishing.
"…contains a value that cannot be found in the related table"
Symptom: An error of the form "The field <FieldName> of table <TableName> contains a value (<value>) that cannot be found in the related table (<RelatedTable>)." This can occur even when the field references a table it belongs to itself, such as a self-referencing lookup on the Dimension Value table.
First thing to check: whether a filter is applied to the related code field. A filter there can prevent the entered value from being found in the related table. Review and correct or remove that filter.
"Field type Text is not convertible to field type Option"
A TableRelation on a Text field that relates to a field of another type (Option, for example) produces this compilation error. Inspect every field carrying a TableRelation and confirm the referenced field's type is compatible with the field's own type.