Skip to content
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.
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.
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.
an automation to ensure that all linked records are always sorted with the top scores first, such as my .a conditional rollup that sums only the first four linked records, and optionally checks to ensure that there are at least four linked scores
Kuovonne's Guide to Airtable
- Pages
Identify the position of a record in a linked record field (top 4 of 6)
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 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
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.
Notes on the system
Summing only the top 4 scores
Summing only the top 4 scores now requires
Thank Kuovonne for creating this content!
Want to print your doc?
This is not the way.
This is not the way.
Try clicking the ··· in the right corner or using a keyboard shortcut (
CtrlP
) instead.