Lookup
A lookup field is an action for creating a field based on an existing link field. It allows you to reference specific data fields from another table through a link field. This not only enables cross-table data sharing but also ensures data consistency and integrity.
Unlike other products, our “Lookup” is not a field type. Lookup is considered a special action here. When you create a lookup, you’re actually creating a field of the same type as the field you’re referencing, with data obtained from the linked records of that field.
Relationship Between Lookup Fields and Link Fields
To use lookup fields, you first need to create a link field to connect two tables. Once the link is established, you can use lookup fields in one table to reference any field from the other table.
How to Create a Lookup Field
Ensure you have created a link field between the two tables you want to connect. In the table where you want to add the lookup field, select “Add Field,” then choose “Lookup.” Based on the existing link field, select the source table and specific field you want to reference.
Lookup Field Use Cases and Examples
Consider the following bookstore scenario:
First, we have two tables: “Author Details” and “Books.”
Author Details Table
| Author Name | Birth Year |
|---|---|
| Steve McConnell | 1960 |
| Martin Fowler | 1963 |
| Erich Gamma | 1961 |
In this table, we list each author’s birth year.
Books Table
| Book Title | Author Link | Author Birth Year (Lookup Field) |
|---|---|---|
| “Code Complete” | Steve McConnell | 1960 |
| ”Refactoring” | Martin Fowler | 1963 |
| ”Design Patterns” | Erich Gamma | 1961 |
In the Books table, when we select an author, the author birth year lookup field automatically displays the selected author’s birth year:
-
For “Code Complete”, we selected author Steve McConnell, whose birth year is 1960, so the author birth year lookup field shows 1960.
-
For “Refactoring”, we selected author Martin Fowler, whose birth year is 1963, so the author birth year lookup field shows 1963.
-
For “Design Patterns”, we selected author Erich Gamma, whose birth year is 1961, so the author birth year lookup field shows 1961.
Through using lookup fields, we can easily display each author’s birth year in the “Books” table without having to repeatedly enter or manually look up the data for each book. This is a very useful feature that allows us to avoid duplicate data entry, ensure data consistency and accuracy, and simplify the data management process.
Formatting and Interactive Display
The data type, formatting, and interactive display of lookup fields are determined by the referenced field. This means if you reference a date type field, the lookup field will also be treated as a date type and can be formatted accordingly.
Formulas and Rollup Calculations
When lookup fields are used in formulas or rollup calculations, their data type is also determined by the referenced field. For example, if you use a lookup field in a formula that references a number type field, that lookup field will behave like a number in the formula.
Important Notes
Data Integrity: Ensure that your source table and fields exist and are valid. Deleting source tables or fields may prevent lookup fields from displaying data correctly. Display Format: Lookup fields can have the same display format as the referenced field, meaning if you modify the data display format in the source table, it will be reflected in the lookup field.
Single Value vs. Multiple Values: Referenced fields may change from single-value to multiple-value types due to many-to-one relationships. For example, a single select field might appear as a “multiple select” after being referenced due to multiple linked records. This is normal logic. Please see the Single Value vs. Multiple Value chapter for further understanding.
Conclusion
Lookup fields provide an efficient, streamlined method for cross-table data referencing and display. This flexibility allows users to easily share and calculate data between different tables without complex data synchronization or conversion. Understanding this concept will help users use the product more flexibly and efficiently, maximizing their data management and automation capabilities.