Lookup Select Box Field in Custom App

[Erik Nelson]

Hello!

I've got a requirement to build out an app using a few different fields from the same table, setting it up like a Lookup Select variable would function (i.e. referencing the column name for the lookup). I'm coming up empty on setting this up since that's not an option as a field type outside of catalog items.

For clarity:

• The same table is being referenced for all three fields
• Each field needs to reference a different column name for its data
• The columns are independently populated because of how the data is used. That means if Column 1 is populated, Columns 2 and 3 are not; If Column 2 is populated, Columns 1 and 3 are not; etc.

Any help is greatly appreciated. Thanks!

Erik

[kmohammed]

Hi @Erik Nelson,

You are coming up empty because there is no Lookup Select Box field type for table fields - that type exists only for catalog variables. For table fields, use the dictionary Choice List Specification instead, which is the table-field equivalent:

1. Create each of the three fields on your table as type Choice (not Reference).

2. On each field's dictionary entry, go to the Choice List Specification section and set:

   - Choice table = the same source table, for all three fields

   - Choice field = Column 1 on the first field, Column 2 on the second field, Column 3 on the third field

   The platform then builds that field's dropdown from the distinct values found in the column you named - exactly the Lookup Select Box behaviour of 'lookup using field' on a variable.

3. Your independently populated columns are not a problem for this mechanism: choices are built from the non-empty distinct values of the specified column only. Rows where Column 1 is populated and Columns 2/3 are empty contribute to field 1's list and add nothing to fields 2 and 3, and vice versa.

Why the obvious alternatives do not meet the requirement:

- Reference fields: a reference field displays the referenced table's single display field (sys_display). One table has one display field, so three reference fields to the same table would all display the same column - you cannot make each one display a different column.

- Static choice lists (sys_choice entries): each field could have its own static choices, but they would not follow the data in your source columns - every new value in a column would need a manually created choice record.

Two caveats to design around:

- The stored value is the column value itself (a string), not a sys_id reference to the source row, so there is no referential integrity and duplicate values in a column collapse into a single choice.

- Because the list is derived from live data, keep the source columns clean - stray one-off values will appear as choices for every user of the form.

Hope this helps.