A lookup field allows you to pull record contents from one linked record into another linked record. As a reminder, linked relationships can be either within the same table or across two separate tables.
You could design a self-linking linked record field in an "Employees" table that could specifically look up an employee's immediate supervisor based upon the dependencies set in the linked record field. Alternatively, if you have an "Orders" table with a field linking to "Clients," you could create a lookup field that displays the linked client's address. In other words, the address from the "Clients" table is brought into the "Orders" table.
How to use the lookup field
Add a linked record field
The lookup field requires the use of a linked record to create a link between two tables. In order to use a lookup field, start by configuring a linked record field.
Add a lookup field
Then, add a new field and select the lookup field type.
Configure the lookup field
If you have multiple linked record fields, you'll have the option to select which of those linked record fields to use for the lookup. Once you've made that selection, pick the cells from those linked records that you want to display.
Add lookup conditions
As an optional step, you can select the option to only include linked records from your linked table that meet certain conditions. Using the example above, you may only want to display campaign leads for campaigns that are in a "Planning" or "Live" stage. You can use lookup conditions to specify that criteria, and the field values will only display for records that match the criteria.
Adding multiple lookup fields in bulk
You can easily add multiple lookup fields by right clicking on the name of your linked record field. Then, click the menu option to Add lookup fields.
This will bring up another menu where you can select specific fields from the linked table to add as lookup fields.
When to use a rollup instead of a lookup
There are certain situations where you'll want to modify the way information returned from a lookup field appears. A great example of this is a linked record field with multiple records.
If there are multiple linked records, the lookup will concatenate the cell values and separate them with a comma.
While it isn't possible to change the comma separator to another symbol, you can use a rollup field instead of a lookup to do so.
For this approach, add and configure a rollup field similar to the lookup field instructions above. Then, in the rollup aggregation formula, paste the following:
SUBSTITUTE(ARRAYJOIN(values), ",", " + ").This instructs the rollup field to substitute the comma separator with something like the
+ sign instead. You can switch out the
+ sign in this formula with any symbol you'd like to use as a separator.
So, if you find yourself wanting to change the information returned from a lookup field(like you'd do with a formula), we recommend looking into the Rollup field to accomplish your goal.