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.
|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|
Solved! Go to Solution.
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.
@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.