Loop a Concate Formula

[Daniel_R]

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)	Formula	Desired Output
The Boys	301, 302, 303	The Boys 301, 302, 303	The Boys 301, The Boys 302, The Boys 303
The West Wing	401, 502, 302	The West Wing 401, 502, 302	The West Wing 401, The West Wing 502, The West Wing 302
Archer	201, 303, 105	Archer 201, 303, 105	Archer 201, Archer 303, Archer 105

[Ben_Young1]

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:

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]

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

[TheTimeSavingCo · Accepted solution]

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

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

[Daniel_R]

Exactly what I was looking for. Thanks @TheTimeSavingCo