Help

Re: Loop a Concate Formula

Solved
Jump to Solution
1149 0
cancel
Showing results for 
Search instead for 
Did you mean: 
Daniel_R
5 - Automation Enthusiast
5 - Automation Enthusiast

I can't seem to figure out how to loop a concate formula. I have 2 columns: Show Name and Episode Number. Show is single select and Episode is multi. My desired output is a string that loops the show name before the episode number; instead I only get Show Name, then a list of episodes with a comma deliminator.

The desired outputs are the names of linked records. 

Example base below. Any help truly appreciated.

https://airtable.com/shr07v7lc15RGEaj5

 

Show (single)Episode (Multi)FormulaDesired Output
The Boys301, 302, 303The Boys 301, 302, 303The Boys 301, The Boys 302, The Boys 303
The West Wing401, 502, 302The West Wing 401, 502, 302The West Wing 401, The West Wing 502, The West Wing 302
Archer201, 303, 105Archer 201, 303, 105Archer 201, Archer 303, Archer 105
1 Solution

Accepted Solutions

Hm, what if you substituted the commas with the format you wanted like so?

 

Show & " " & 
SUBSTITUTE(
  Episode, 
  ",",
  ", " & Show
)

 

Screenshot 2023-01-22 at 2.12.52 PM.png

See Solution in Thread

4 Replies 4

Hey @Daniel_R

The big blocker in your way here is that Airtable's array formulas do not treat multi-select fields as arrays. They're treated as flat strings.

The only straightforward way I can think of for you to be able to get the desired data output you're looking for is to restructure your data to utilize linked records.
This will allow you to utilize rollups to build the desired output you're looking for.
Luckily, the data you're working with fits really cleanly into a new data structure.

I created a base for you to take a peek at the concept. It can be viewed from this shared base link.

I broke the data into three tables: shows, seasons, and episodes.
I populated it with a bunch of data for the sake of the example.

The final result is that we can build a rollup on the Shows table that returns a value like what is pictured below:

Ben_Young1_0-1674258577252.png

I would definitely advise that you copy the base I linked and play around with it yourself.
As a rule of thumb, I generally advise against the use of multi-select fields. They are evil and cause some massive headaches when working with scaled data.

Daniel_R
5 - Automation Enthusiast
5 - Automation Enthusiast

@Ben_Young1 Thank you for the thoughtful reply.

I had a feeling this would not be possible due to the array handling.  However, changing the data structure for my use case is something that cannot be done. The input is coming from a Jotform, so if there isn't a way to extract each instance in the array then loop I may be stuck.

Hm, what if you substituted the commas with the format you wanted like so?

 

Show & " " & 
SUBSTITUTE(
  Episode, 
  ",",
  ", " & Show
)

 

Screenshot 2023-01-22 at 2.12.52 PM.png

Exactly what I was looking for. Thanks @TheTimeSavingCo