Expose Linked Record Positions

[dk82734]

Let’s say you have 2 tables within a single base: Department and People.

Next, pretend you create a single new record in the Department table and link 3 People records: Alice, Bob, and Chris.

So the “People” column in the Department table contains the record links and we “Allow linking to multiple records” option enabled.

This column also records the order of the People records linked, where the records are positioned in the order of: Alice (first), Bob (second), Chris (third).

Next, I’d like to create a VIEW in the People table that shows this corresponding position for these people for this Department.

That way, if someone re-orders the linked People to be (hypothetically): Bob (first), Alice (second), and Chris (third) – then this VIEW in the People table would automatically re-order those records, accordingly.

The problem is that I don’t think there’s a way to expose Linked Record positional information automatically through any sort of Lookup.

[kuovonne]

Depending on the structure of the data, it is possible to use rollup and formula fields to calculate record link position. Create a unique identifier for each linked record. In the parent record, have a rollup field of this unique identifier. Pass the list of all linked records back to the child table in a rollup, and have the child record calculate its position in the list. The exact formulas for the rollups depend on the unique identifier. The easiest method is when the unique identifier is always a set number of digits.

[dk82734]

I get your approach, but the biggest issue is one of usability. Using my original example, if I were to add a new Number column to the People table called “Rank”, I could define values like:

People

Name | Rank
Alice | 1
Bob | 2
Chris | 3

In this scenario, though, there’s no way for me to enforce this record ordering within the Linked Records “People” column in the Department table. A user could easily drag/reorder those linked items and that would not be kept in sync with the Rank values.

Therefore, I think the problem still stands. That said, let me know if I’m missing something else here.

[kuovonne]

I may have misinterpreted your situation.

I am suggesting using rollups to determine the position of the record in the linked field. When users re-arrange the linked records in the linked record field, the rollups would recalculate. Users could not reorder the linked record fields by arranging the records in a view.

If you want the linked records to be re-ordered according to a view, that can be accomplished with a button script. I have written scripts for clients that accomplish different variations of this. This cannot be done automatically with a automation because rearranging the order of records in a view does not actually change any data in the records. Trying to automate this is further complicated by the fact that records can appear in different orders in different views. Thus, this product suggestion is unlikely to be implemented.

[dk82734]

Thanks for the inspiration! I think I get it. I’ve attached an example base that I think captures what you’re describing, here.

Essentially, the steps were:

1. Create a “People Names” Rollup in the Department table, that’s effectively an ARRAYJOIN{values} formula.

2. Expose this rollup within the People table as “Department Names” column.

3. Create a “Rank” column in the People table that uses the following formula:
   SEARCH({Name},ARRAYJOIN({Department Members}))

The relative position of the person’s name indicates their relative rank.

Note: This assumes every person’s name is unique and no duplicates exist.

[dk82734]

Actually, one way to improve this would be to expose the RECORD_ID() for each People record, and then use those values in the SEARCH() formula… this is because we are guaranteed that every RECORD_ID() field is considered globally unique (with no chance of duplicates).

[dk82734]

I’ve updated my example base to use/reflect RECORD_ID() values, which will properly handle people that have duplicate names.

[Vikas_Vimal2]

Here’s the formula that works for me:
ROUNDUP(SEARCH(ChildRecID,ARRAYJOIN({ChildRecID (Lookup from Child Table) (Lookup from Parent Table)}))/18,0)

Table A is parent, Table B is Child

1. Create an Airtable Record ID field in Table B
2. Lookup the BRecordID into Table A (creates an array of linked Child/B records)
3. Lookup the array created in Step 2 back into Table B (Now Table B/Child has a column of its own recordID, and a column containing an array of all Child/B records linked to its parent)
4. Use the formula to search for the position of Child/B recordID in the array containing all child/B recordIDs from its parent. Output will be 1, 19, 37… (18n+1) {the recordid is 17 digits, plus a comma}
5. Divide the position by 18, then roundUP to the next integer to get the position of the Child/B record. 1st record will have a value of 1/18, which will roundup to 1, 2nd will be 19/18, which will roundup to 2 and so on.

[Hendrik_Yang]

Thanks for sharing! It’s very useful for me