Lookup and rollup fields

Show a value from a linked record — like a customer's city on every order — or count and total the records that link to this one, like a customer's number of orders and total sales.

A lookup field shows a value from the record a link points at: put the customer's city or GSTIN on every order without typing it twice. A rollup field works the other way round: on a customer, count their orders or total their sales. Both fill themselves in and always show the current value.

Before you start: you need permission to change an app's fields. The type needs a link field first. For a lookup, that's a link on this type (for example Customer on an order). For a rollup, it's a link on the other type that points here (for example Customer on an order, for a rollup on customers).

Add a lookup field

  1. Open the app, then its Settings, and pick the type's tab (for example Orders).
  2. Click Add field and enter a Label.
  3. Under Value, choose Lookup from a linked record.
  4. In Through reference, pick the link field (for example Customer).
  5. In Show field, pick what to show from the linked record (for example City).
  6. Click Add field.

The field takes its kind from the field it shows: a money field shows money, a date shows a date, a link shows a link.

Add a rollup field

  1. Open the app's Settings and pick the tab of the type that gets the total (for example Customers).
  2. Click Add field and enter a Label.
  3. Under Value, choose Rollup of linked records.
  4. In Records of, pick the type whose records point here (for example Orders).
  5. In Linked by, pick the link field on that type (for example Customer).
  6. In Calculate, pick Count of records, Sum, Average, Smallest / earliest or Largest / latest. For anything but a count, pick the field in Of field (for example Total).
  7. Click Add field.

The Add field window with Rollup of linked records chosen: Records of Orders, linked by Customer, calculate Sum of Total, labelled Total sales

Good to know

  • Lookup and rollup fields are read-only. They are left out of forms, imports, bulk edits and spreadsheet pushes.
  • Each person sees only what they're allowed to see. If someone can't see the linked customer, or can't see its City, the lookup stays empty for them. A rollup counts and totals only the records that person can see, so two people can see different totals.
  • Sum and Average work on number and money fields. Smallest and Largest also work on dates.
  • If the money being totalled is in more than one currency, the total stays empty rather than adding rupees to dollars. The count still shows.
  • You can't filter or sort a list by a lookup or rollup field, group a board by it, or place a calendar on it. They also don't appear in Analytics queries.
  • A lookup can't show another lookup or rollup, and a formula can't use one.
  • After you add one, only its label and description can change. To change what it shows, delete it and add a new one.
  • You can't delete a field that a lookup or rollup uses until you delete the lookup or rollup.
  • When a page of records has more than 5,000 linked records to total, the rollup stays empty instead of showing a part total.