Help

Extracting the record ID from linked record and formatting

Topic Labels: Formulas
Solved
Jump to Solution
5657 12
cancel
Showing results for 
Search instead for 
Did you mean: 
Anxious
7 - App Architect
7 - App Architect

I have a situation where I need to display the linked records in a field like this (including the double “” on either end):

[““recFMVIujI5jqSiSD””,““recf8rGUjcM0li5f4"”,”“recztuGU7Tl4GSSYU”"]

Is this possible?

I have this code to format it, I just need the ID part.

'["' & 
SUBSTITUTE(
  {Relations},
  ', ',
  '","'
) & 
'"]'

Which displays:
[“record1”,“record2”,“record3”]

Instead of the record primary field name, I need it to display the record ID in a text field as formated above.

I have a linked record column, then I added a new text field column that grabs the name from that linked column but I need it to be the record ID. Does that make sense?

image
(replace the name with the record ID in the brackets)

Airtable Account: Free Plan
Records: 1,200/1,200 1000/1000 (I might have to find a different solution soon)
Automation Runs: ??/100
Extensions: 1/1 (TinyPNG Compression Script, looking for free alternatives)
1 Solution

Accepted Solutions

Hmm, I’m not too sure what your setup is, but I’ve managed to achieve something similar here, with both linked records to the same table and to another table

Screenshot 2022-07-05 at 10.18.31 AM

To view the formulas, you can duplicate the base by clicking the title of the base at the top of the screen, then the three horizontal dots on the right, and then the “Duplicate Base” button.

See Solution in Thread

12 Replies 12
Anxious
7 - App Architect
7 - App Architect

Ok, so found my own solution, but not all the way! Here is what I did.

  1. Create a formula field and use the formula RECORD_ID()
  2. Create a lookup field and select your ID field as the lookup. This will display the record IDs of the linked items, separated by commas. YAY!
  3. Created a third column, but upon trying to format with a formula field, I am getting an #ERROR? Airtable wants me to put a ( in the middle of my formula…?
    image
Airtable Account: Free Plan
Records: 1,200/1,200 1000/1000 (I might have to find a different solution soon)
Automation Runs: ??/100
Extensions: 1/1 (TinyPNG Compression Script, looking for free alternatives)
Carl
6 - Interface Innovator
6 - Interface Innovator

Formulas don’t read a lookup field as text. So you could create an automation that posts the lookup values into a text column and then apply the substitute formula against that text field. (Or maybe there’s a formatting formula that will force a field to be read as text? I don’t know that one.)

Another option is to create an additional table and apply an automation that creates a record with the name of the record ID from table 1, links it to table 2, and then use the substitute formula against that linked field. (This is more unwieldy.)

Screen Shot 2022-07-04 at 7.32.24 PM

Creating another table will not work in my situation. All data needs to be in the same table for export.

I was trying to use a rollup field for this too, but it won’t display the record id, but the title instead.

Airtable Account: Free Plan
Records: 1,200/1,200 1000/1000 (I might have to find a different solution soon)
Automation Runs: ??/100
Extensions: 1/1 (TinyPNG Compression Script, looking for free alternatives)

Hmm, I’m not too sure what your setup is, but I’ve managed to achieve something similar here, with both linked records to the same table and to another table

Screenshot 2022-07-05 at 10.18.31 AM

To view the formulas, you can duplicate the base by clicking the title of the base at the top of the screen, then the three horizontal dots on the right, and then the “Duplicate Base” button.

Ah ha! That’s perfect thanks.

Airtable Account: Free Plan
Records: 1,200/1,200 1000/1000 (I might have to find a different solution soon)
Automation Runs: ??/100
Extensions: 1/1 (TinyPNG Compression Script, looking for free alternatives)
Carl
6 - Interface Innovator
6 - Interface Innovator

Can you use an automation to take the lookup field into a text field? Trigger is when the lookup field is modified. Action is update record, lookup field value into a text field.

Nice, that skips the need to use an automation to convert the lookup field into a text field. I sometimes forget about using formulas in rollup fields.

Is there a way to keep the field blank if there are no linked fields? Instead of having the [“”]

Airtable Account: Free Plan
Records: 1,200/1,200 1000/1000 (I might have to find a different solution soon)
Automation Runs: ??/100
Extensions: 1/1 (TinyPNG Compression Script, looking for free alternatives)

I’m not sure what the original system is, but if it is a rollup of a formula field with RECORD_ID(), you can use this in the rollup formula field:

IF(
  values,
  CONCATENATE(
    '[""',
    ARRAYJOIN( values, '""][""'),
    '""]'
  )
)

Thanks, that worked! Just tweaked the code a bit. :grinning_face_with_smiling_eyes:

IF(
  values,
  CONCATENATE(
    '["',
    ARRAYJOIN( values, '","'),
    '"]'
  )
)
Airtable Account: Free Plan
Records: 1,200/1,200 1000/1000 (I might have to find a different solution soon)
Automation Runs: ??/100
Extensions: 1/1 (TinyPNG Compression Script, looking for free alternatives)

Glad you got it to work for you. Your original post had double, double quotes, so that was what I was trying to reproduce.

Yea, I thought they had to be formatted with double quotes in the beginning. I was wrong though, so I had to use single quotes in the end.

Thanks for catching that detail though!

Airtable Account: Free Plan
Records: 1,200/1,200 1000/1000 (I might have to find a different solution soon)
Automation Runs: ??/100
Extensions: 1/1 (TinyPNG Compression Script, looking for free alternatives)