Lookup

A Lookup field brings field values from a related table into the current table. Use it to show more information about a record in a table. For example, you have an Orders table and a Customers table. Use a Lookup field to show the name and address of the customer in the Orders table.

Ask NocoAI

Copy a prompt, paste it into NocoAI in any base, and replace the words in [brackets] with your own.

Show each [customer]'s [Email] on the [Orders] table
Add a lookup of [Project Owner] to [Tasks]
Bring [Category] from the linked [Products] into this table

Create a Lookup Field

Click the + icon to the right of the Fields header.
In the dropdown modal, enter the field name (optional).
In the dropdown, select the field type Lookup.
In the dropdown, select the link field. This field links the current table to the related table.
In the dropdown, select the field to show. The current table shows this field.
Click the Save Field button.

Formatting

The Lookup field editor has two tabs:

Configuration: select the relation (link field) and the lookup field, as described above.
Formatting: set how the Lookup shows the looked-up value. This tab is disabled until you select a lookup field.

Use the Formatting tab to give the Lookup value a format that is independent of the source field. The format applies everywhere the Lookup shows, including the grid, the expanded record and exports.

The options depend on the type of the lookup result:

Result type	Format options
number	The Format selector shows Decimal, Currency or Percent.
Date	Only Date formatting.
DateTime	The Format selector shows Date, DateTime or Time.
Time	Only Time formatting.

When you select a format, NocoDB shows the usual sub-options of that format type (for example, precision for Decimal, or the date format for Date).

If the looked-up value is not a number, date, datetime or time value, formatting options are not available. You cannot format Duration results.

Lookup field Formatting tab

Conditional Lookup ☁

Conditional Lookup is available on NocoDB Cloud (Plus plan and above) and licensed self-hosted deployments (Business plan and above).

By default, a Lookup field shows all linked records from the related table. But you may want to show only some of those records. For example, only recent entries or records above a threshold.

To do this, turn on the option: Only include linked records that meet specific conditions

This option filters the linked records with conditions on fields in the related table. The Lookup field then shows only the data that is most relevant to your use case.

Applying filters

Turn on Only include linked records that meet specific conditions.
Click Add filter. Define one or more conditions with fields from the related table.
To combine filters with AND/OR logic, use Add filter group.
Click Save Field. NocoDB applies the filters.

Sort and limit lookup values ☁

Sort and limit for Lookup values is available on NocoDB Cloud (Plus plan and above) and licensed self-hosted deployments (Business plan and above). It is supported only on projects stored in PostgreSQL (including NocoDB Cloud). The options are not shown for lookups on other databases.

By default, a Lookup field shows every linked value in the link order. In the Lookup field editor, you can set the order of the looked-up values and the number of values that it shows.

Sort the looked-up values

Open the Lookup field editor.
Select the link field and the lookup field.
Under Sort records, click Add Sort Option.
Select the field to sort by and the direction.
To break ties, add more sort options.
Click Save Field.

Limit the number of values shown

Turn on Limit the number of items shown.
Under Limit to the, select First or Last in the dropdown.
Enter the number of values to keep (minimum 1).
Click Save Field.

Use a limit with a sort to show, for example, only the First three linked records by date. Existing Lookup fields do not change until you configure a sort or limit on them.

Good to know

Lookup fields are read-only. You cannot edit the values in a lookup field.