Lookup fields can be used to link databases together by creating a hierarchical relationship between them. This allows you to select and view records from other databases within your current database.
For example, you can create a Projects database (parent database) that looks down to a Tasks database (child database) and vice versa. When on the Projects database you can view all of the Tasks which relate to your Project. When you're adding a new Task, you can select and view the related Project(s). Below you can see a visual indicator of this workflow:
Before setting up your Lookup fields
To use Lookup fields you need to first have or create two separate Databases. We will use the Projects / Tasks example outlined above to demonstrate this setup.
Selecting the right element on the Form Builder
After creating both databases, go to the Form Builder. The Lookup field option is available for selection on the Form Builder for all Database types and is highlighted in the screenshot below.
Once you add the Lookup field, you will be presented with a number of options specific to that field (ie beyond the standard field options, name, etc).
Lookup Field Types
The Lookup Type setting appears after you have selected the Lookup element and will determine how lookup records are displayed on the record and also how records will be appear within them.
Below you can see an explanation of each Lookup Type and how they work. The option you select here will also decide on the other options available.
This option will let users select specific Lookup value(s) within the field. Users will see a dropdown field (pictured below) that contains all the records of the other database. If you are also adding a Lookup Grid, then the Lookup select option will be the child database.
This option is also applicable if you always need to manually select the related lookup value. Using the scenario above, the Tasks Database would contain a Lookup select field, which allows the user to select the related Project. Below you can see how to setup a Lookup Select field.
The Lookup Grid field will automatically display any Record from the selected database which has selected the current record in the Lookup select field. These records will appear in a Grid view, as demonstrated below.
A Lookup Grid field should be added to the parent database (ie Projects) as it pulls records up from another database (Tasks) into the Grid. Records will only appear in the Grid if, for example, a Task has that current Project record selected in its Lookup Select field. Once that has been done, then that Task will automatically appear in the Lookup Grid field, as they are now linked.
Lookup Grid's will also display Draft Records (if you have permission to view that Draft Record).
The screenshot above is a Lookup Grid field within an existing Project record. All of those Tasks have this specific Project record selected in the Lookup Select field. As the Project is the parent database, it is populated with those Tasks.
Creating a Lookup Select field
If you select Lookup Select as the Lookup Type, there are a number of settings you can add in to enhance its functionality.
This enables users to select more than 1 lookup record in their dropdown. This is useful if you need to select multiple projects or tasks at a time.
Use Parent Thumbnail
This will only be available if Multiple is not enabled. If selected, then the thumbnail for each new record within the database will use the thumbnail from the lookup value selected.
To determine which records will appear within this lookup field, it is mandatory to select a Lookup Table. This provides the option to select Users, the Resources Module and a list of all other Databases on the platform.
For this scenario, we need to choose another Database, ie if I'm on the Task database I would select the Projects database.
Note that in order to create a Lookup Grid field in a Parent Database, this action needs to occur first.
Once a Database has been selected, the records within that field will all be sourced from that selected database.
If you need your users to see more information about each selected lookup value (other than the record name), then you can also select integrated fields. The options that appear in the dropdown are the fields that exist within that other Database selected in the Lookup Table. The selected options will appear on the Record once the user has made a selection.
Record Status for Selection
You can also restrict the type of Records that will be available for selection in a Lookup Select dropdown, based on their Status. Note that these setting options will depend on the selections made in the Database in the Lookup Table field. The options you can choose from include:
- Published: This is always selected by default and allows you to select Records which have been Published in the other Database.
- Pending Approval: This will only be available if a Publish workflow exists on the Lookup Database. If enabled, users will be able to select Records which are Pending Approval.
- Draft: This will only be available if Save as Draft is available on the Lookup Database. When enabled, Draft Records will be available for selection.
If these additional options are enabled; when the user is filling out the Records and Pending or Draft Records exist in the Lookup Database, they will see an indicator on the record to highlight the status of that Record (as pictured below).
Creating a Lookup Grid field
If you select Lookup Grid as the Lookup Type, another Database with your Current database selected as part of a Lookup Select field will need to exist.
For example, you will not be able to add a Lookup Grid to the Projects database, until the Tasks database (or another database) has a Lookup Select field with the Lookup Table field set to the Projects database.
Lookup Grid Source Fields
Similar to the Lookup Table option for Lookup Selects, this mandatory field will determine which database the records are pulled from into the grid. The only options you will see in the dropdown are existing Lookup select fields from other Databases that have your current database selected in the Lookup table field.
As a Database might have multiple lookup select fields, you can select the specific Lookup field your new Grid will pull selected records from.
Once you select a field, when Editing and Viewing a specific Record, you will be able to see all Tasks that have that Project selected in a Grid view.
Add New Button Options
As part of the Lookup functionality, users are always able to Add a new record to the database selected within that field. On the Form Builder, you can customise the name of the button and the size (ie its prominence to the user). The default button text is 'Add New'.
When the Add button is clicked on the record, the Add record page will load over your current record, allowing the user to quickly add a new record.
This option is always available for all Lookup types.
On the Record
With your Lookup fields successfully added in both Databases, you will be able to select and view records between both databases on the separate Databases.
Below you can see an example of the scenario mentioned above. This includes the following steps:
- User adds a new Task.
- They can't see a related Project so they Add a new one, which can then be selected in the New Task's Lookup Select field.
- Once the Task is added, the user views the Record and can see the selected Project and it's integrated fields. They then click the Project name to view more information about that record.
- Now on the Projects database, the user views the New Task in the Lookup Grid. They want to add a new Task to this field, so click add new.
- User adds a new Task whilst on the Project database. Once submitted, this now appears on the Lookup Grid along with the Task added in Step 1.
To find out more about the Databases module, you can browse other articles here. If you have any questions, please don't hesitate to contact your Customer Success Manager.
Please note that the Database module terminology referenced above may appear as another name in your platform as these are customisable. You can see comparable terms here.