Skip to main content

Please bear with me since I'm new to Airtable and this is hard to explain.  I've inherited a base which contains details on job descriptions which is to be used to merge into a Formstack docs.

Main Tab lists the details of each job description in rows. (eg. title, reports to, requirements level, requirements...).  2nd Tab contains job requirement levels in rows.  (eg Level 1, Level 2...)  A column in this second tab contains bulleted lists of the requirements.  The main tab has a column for Requirement Level(s) and a job can have multiple levels linking to the records in the 2nd tab. So this column has links to multiple records/levels. The problem is with a lookup field/column in this main tab. It pulls Levels requirements (pulling from multiple records if more than one Level). So returning and combining bulleted lists from multiple records.

However when it does this, it will automatically adds a comma into the lookup field between the bulleted lists from the records. Looks like this below. Notice the comma before the bullet.   Is there any way to avoid this?

- Assist account teams with all required deliverables
- Contribute, with moderate direction, towards the development of a superior creative product
- Proactive take-charge mentality and the capability to own projects with moderate direction.
, - Independently manages projects within budgeted work scope and timelines, especially multifaceted and or multiple modes of delivery
- Contribute, with minimal direction, towards the development of a superior creative product 

Hey @tomn68! 

For this, you'll want to use a rollup field instead of a lookup field.
I created a small demo to help visualize this. 
The base you inherited seems like it might be structured in a weird way, so I've just replicated what I imagine the intent to be.

We have job records and requirement records.
Each requirement record contains data on actual role requirements. In addition, I added room for preferred skills.
A job record can be related to one or many requirements. On the job record, we want to create a cleanly formatted summary of all of the descriptions for each related requirement record.

I first created a lookup field which, as expected, displayed the comma behavior that I presume you're running into. 

I'll go ahead and create a rollup field that points to the field that we were previously referencing in the lookup field. Here's the rollup formula that I'll use for my desired outcome:

ARRAYJOIN(
values, "\n\n"
)

Here's what the final result looks like:

The ARRAYJOIN() function allows you to define a custom separator for array elements. In this case, I've defined that I want each element to be separated by two new-line operators ("\n").
You can switch the separator out to fit whatever you want your formatting to look like.
You can get really crafty with the final formatting if you play around with it for a bit.

For example, if I wanted to remove all the of the excess whitespace between the Preferred Skills and the Requirements, then I could use this formula: 

IF(
values,
REGEX_REPLACE(
"" & ARRAYJOIN(values, "\n"),
"\n\n", "\n"
)
)

I think that despite how quirky and weird arrays are in Airtable, clever rollup formulas are one of the most underappreciated areas of functionality.
That being said, it's still an extremely underdeveloped area and has been extremely neglected since the array functions were introduced.

Reply


Terms & Conditions