Sharing here because obviously the team I’m working for can’t appreciate it as much as you all will!! And the Airtable community is amazing!! Took me a hot minute and it’s a bit convoluted with lots of helper fields (if you have a better/cleaner way, please share!!), but I finally figured out how to get a linked field into a nice list with comma separators and an “and” just before the last item. I use this in a template email. I may do a Loom video if anyone wants it, just let me know in the comments.
Requirements: 2 tables linked :slightly_smiling_face:
In table 1:
First Name Field
First Name Length Field - This is essentially a character count of the length of the first names. If your scenario may have more than 99 characters you’ll need to add two zeros in front of the numbers less than 10, and other formulas later may need to change. Thanks to Airtable forum for this formula:
IF(
LEN(
{First Name}
)<10,
‘0’
)&LEN(
{First Name}
)
In Table 2:
Linked Records Field
Rollup of First Names Field (ARRAYJOIN(values, “, “))
Rollup of First Name Length Field ARRAYJOIN(values,””)
Rollup Count of Linked Records Field COUNTA(values)
Rollup of First Name Beginning Length Field - this formula adds 2 pieces of info together (since some linked fields will have 1 name and some will have ?# names)
part 1 - adds the character count together then takes away the right two characters (assuming theres 2 characters (a comma and a space)
part 2 - find 2 times the total number of names (since each name length will be two digits) less one character for the comma then takes away two characters for the last name
This formula could probably be simpler, but it works!
SUM(values)-RIGHT({First Name Length (from Primary Email)}, 2) + ((2*({Primary Names Count (from Primary Email)}-1)-2))
Merge Ready Name Formula Field - if there’s more than 1 name then concatenates the first names in the string ", and " then the final name, if not then just use the first name rollup field.
IF(
{Primary Names Count (from Primary Email)}>1,
CONCATENATE(
LEFT({First Name (from Primary Email)},
{First Name Beginning Length (from Primary Email)}
),
" and ",
RIGHT({First Name (from Primary Email)},
RIGHT({First Name Length (from Primary Email)}, 2)
)
),
{First Name (from Primary Email)}
)
