Save the date! Join us on October 16 for our Product Ops launch event. Register here.
Jul 04, 2022 07:42 AM
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)
Solved! Go to Solution.
Jul 04, 2022 07:16 PM
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.
Jul 04, 2022 09:35 AM
Ok, so found my own solution, but not all the way! Here is what I did.
RECORD_ID()
Jul 04, 2022 04:33 PM
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.)
Jul 04, 2022 05:22 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.
Jul 04, 2022 07:16 PM
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.
Jul 04, 2022 07:30 PM
Ah ha! That’s perfect thanks.
Jul 05, 2022 03:53 AM
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.
Jul 05, 2022 06:05 AM
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.
Jul 05, 2022 10:18 AM
Is there a way to keep the field blank if there are no linked fields? Instead of having the [“”]
Jul 05, 2022 12:37 PM
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, '""][""'),
'""]'
)
)