Skip to main content
Solved

Loop a Concate Formula

  • January 20, 2023
  • 4 replies
  • 117 views
D
Forum|alt.badge.img+3

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

Best answer by TheTimeSavingCo

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

 

4 replies

Ben_Young1
Forum|alt.badge.img+22
  • Ben_Young1
  • Brainy
  • January 20, 2023

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.

D
Forum|alt.badge.img+3
  • Daniel_R
  • Author
  • New Participant
  • January 21, 2023

@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
Forum|alt.badge.img+31

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

 

Adam, Airtable Consultant - thetimesaving.company
D
D
Forum|alt.badge.img+3
  • Daniel_R
  • Author
  • New Participant
  • January 22, 2023

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

 

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

 


Exactly what I was looking for. Thanks @TheTimeSavingCo 

TheTimeSavingCo
Terms & ConditionsAccessibility statement