Skip to main content

Lookup

Introduction​

A lookup field is a field type related to link field.It displays the raw data, performs calculations, creates aggregates based on specific cells of records that are linked to another fields in other datasheet.

In the example below, you can know more about a lookup field. There are a datasheet below, which is named as "Task" recording project and task information.

There is a datasheet below, which is named as "Task" recording project and task information. To build relationships between project and task, a link field is created to link project information that is stored in the datasheet of "Project". Then to know status of the projects, a lookup field is added.

create lookup

When several records are linked on a field cell, lookup field will automatically separate multiple values with commas.

Filtering lookup records​

After using lookup to bring in information from other datasheet, you can use filter to only include the records that meet certain conditions in the datasheet. This is helpful for organizing data in many cases.

filter lookup

Sorting lookup records​

Sorting lookup records allows you to arrange the data based on specific criteria. This can provide a clearer view of the information and make it easier to analyze and prioritize the records.

To sort lookup records, you can follow these steps: sort roolup

Select the number of lookup records to output​

The lookup field also allows you to control the number of lookup records that are displayed. This gives you the flexibility to customize the level of detail in your lookup field output. With this feature, you can easily highlight the best performers in your data, provide a snapshot of the latest activities in a compact format.

To select the number of lookup records to output, follow these steps: select number of lookup records

Creating aggregates by using lookup functions​

You can insert a lookup function to aggregate values from linked records. Here are some lookup functions for suggestions:

  • VALUES
    • Returns raw values.
  • AVERAGE
    • Arithmetic mean of the values.
  • COUNT
    • Counts only non-empty numeric values. You can use COUNTALL to count all records.
  • COUNTA
    • Counts the number of non-empty values.This function computes both numeric and text values.
  • COUNTALL
    • Counts the number of linked records, including blank records.
  • SUM
    • Sum all numeric values together.
  • MIN
    • Returns the smallest values brought in from linked records.
  • MAX
    • Returns the biggest values brought in from linked records.
  • AND
    • If all the data are true (not null is true), returns true.
  • OR
    • If any one of the values is true, returns true.
  • XOR
    • If and only if odd number of values are true, returns true.
  • CONCATENATE
    • Joins two or more text values into one text string.
  • ARRAYJOIN
    • Concatenates all the values into a single comma-separated string.
  • ARRAYUNIQUE
    • Only return items that are unique.
  • ARRAYCOMPACT
    • Returns the array after removing all empty values and strings from the array."False" and strings containing blank character(s) will be kept.
Last updated on