Kuovonne's Guide to Airtable

Explore

Kuovonne's Guide to Airtable

Kuovonne's Guide to Airtable

Specific Solutions

Identify the position of a record in a linked record field (top 4 of 6)

⁠

⁠ Tip Jar ⁠ Thank Kuovonne for creating this content! ⁠ ⁠ ⁠

⁠

Inspiration

If your linked record field is sorted, your individual linked records might want to know their position.

This information is useful in situations such as

the followup question in this thread⁠

on the Airtable community forums. The user wanted to sum the best 4 out of 6 scores, or the best 4 out of 7 scores.

Finding a child record’s position with back-and-forth rollups

Start with creating a formula field in the [Children] table that shows the Record ID of the child record.

⁠

In the [Parents] table, create a rollup of the {Record ID} of the linked children records.

⁠

Back in the {Children} table create a rollup of the rollup that calculates the position of the record based on the position of the record ID in the list.

⁠

How the final rollup works

The list of record IDs in the rollup reflects the order in which the records are linked. The first record ID starts with the first character in the text string. Because record IDs are always the three letters rec followed by 14 alphanumeric characters, and record IDs are separated by commas, each subsequent record ID starts 18 characters later than the previous one.

ARRAYJOIN(values) gets the list of record IDs as a text string. No text is actually joined since there is only one parent record.

The FIND() function finds the position of the current record ID in the list of record IDs: 1, 19, 37, 55, etc.

The inner SUM() subtracts one from the position of the record ID in the text string so that the result is a multiple of eighteen: 0, 18, 36, 54, etc.

Divide the previous number by eighteen to get sequential numbers: 0, 1, 2, 3, etc.

The outer SUM() adds one to the list of sequential numbers so that positions start at one: 1, 2, 3, 4, etc.

Notes on the system

You cannot use the {Position} in a formula for the primary field of the child record because that would cause a circular reference.

This system will not work if a single child record could be linked to multiple parent records.

My screen captures show the {Position} field formatted as a decimal to show that the positions are accurate. If you do not subtract and add one in the formula, your results might look okay if they are displayed as an integer, but they will be off.

I like a multi-line format for the final formula. You may find a single line format easier to read.

⁠

Summing only the top 4 scores

Summing only the top 4 scores now requires

an automation to ensure that all linked records are always sorted with the top scores first, such as my

Sort Linked Records Automatically script⁠

a conditional rollup that sums only the first four linked records, and optionally checks to ensure that there are at least four linked scores

⁠

⁠ ⁠ Thank Kuovonne for creating this content! ⁠ Give Kuovonne a sandwich ⁠ Signup for Coda w/ referral link ⁠ ⁠ ⁠Buy from my Gumroad Store ⁠ ⁠ ⁠ ⁠ ⁠

⁠

Gallery

Want to print your doc?
This is not the way.

Try clicking the ⋯ next to your doc name or using a keyboard shortcut (

CtrlP

) instead.