A lookup table created by the Lookup Table builder can be used by other builders.
This table is used to translate between some computer-readable ID and a human-readable name, often the result of a database query. The table also caches the results across different users, including the opportunity to prevent data from becoming stale. The output of the Lookup Table builder can be used by the following builders.
Radio Button Group
Data Field Modifier
The lookup table can be used in the following ways.
In Select and Radio Button Group builders
The table can be used to provide the set of choices to be offered. In each of these builders, the user sees the human-readable form of the choices, but the actual value selected is the computer-readable form.In Text builders
The table can be used to translate a value for display. In this case, the internal value is the computer-readable form, and displayed value is the human-readable one.
In each of these builders, you do not see any Lookup Table-related inputs unless there is a Lookup Table builder in your WebApp. If there is, then a new builder input appears with the prompt Lookup Table Used
. Its default is None
. If you select some choice other than None
, in the case of the Select and Radio Button Group builders, the builder inputs options, Text Tag
and Value Tag
are hidden. These inputs are filled in automatically to correspond to the Lookup Table builder.
The Text builder operates in a similar manner. If a Lookup Table builder is in the WebApp, a new builder input appears with the prompt Lookup Table Used
. Its default is None
. If you choose something other than None
, no other changes occur in the UI of the builder. However, the result of the builder is that the value is translated through the lookup table.
A typical scenario where a lookup table is useful is a Sales Application, where there is a database Table of sales people, and another for customers. In the customer record is a SalesPersonID, which is a key into the SalesPerson Table.
When the user of the resulting WebApp is filling out a new Customer Record, he wants to assign a SalesPerson to that record. You do not want him to have to type in the ID of a SalesPerson record. You want him to be able to choose from a list of all the available Sales People, where he can see the names but the value actually entered is the ID. Similarly, when the user is viewing a Customer record (or a list of them), you do not want him to see IDs, even though that is what the value is in the record. You want the values translated through the SalesPerson Table so that the salesperson's name is displayed.
In a query, you could accomplish this with a database join. However, in a Customer record that has been created but not yet stored in the database, the confirmation page can use this technique.
You can accomplish this entire process with the following steps.
- Use the Lookup Table builder, targeted at the SalesPerson database, to create the Lookup Table.
Note: You can specify how often the Lookup Table information should be refreshed, enabling you to keep the data current without creating a performance problem.
- In the Customer Edit page, for the field SalesPersonID, use either a Select or a Radio Button Group builder, and choose the Lookup Table in the Lookup Table Used field. This has the effect of specifying the correct values for the Options, Value Tag, and Text Tag inputs.
- On the confirmation page (or anywhere else you display a Customer record) in the Text builder for the SalesPersonID field, you still choose, as the data, that field in the record. But also choose the name of the Lookup Table in the Lookup Table Used builder input. This causes the value, which is an ID, to be translated through the table and presented as the readable name of the salesperson.
If you are using a Data Page builder (or any of the builders that use it, such as the View & Form builder), you can also access the Lookup Table. Create a Data Field Modifier builder and point it to the field or fields in question. If there is a Lookup Table in the WebApp, the Lookup Table Used
prompt builder input is available for you to choose.
The Data Field Modifier builder uses the correct process depending on whether the field being modified is a Data Entry or a View Only field. When a Lookup Table is selected, if the field is Data Entry, either a Select or a Radio Button Group is used (depending on the other settings). If the field is a View Only field, the translated text is used.
You can change multiple fields with a single Data Field Modifier builder, even if some of the fields are Data Entry and some are View Only.
Parent topic: Lookup Table builder