Extracting the record ID from linked record and formatting

[Anxious]

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?

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

[Anxious]

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…?
   

[Carl]

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.)

[Anxious]

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.

[TheTimeSavingCo · Accepted solution]

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

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.

[Anxious]

Ah ha! That’s perfect thanks.

[Carl]

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.

[Carl]

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.

[Anxious]

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

[kuovonne]

Anxious:

Is there a way to keep the field blank if there are no linked fields?

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, '""][""'),
    '""]'
  )
)