I’m trying to combine multiple values from a multiple fields.
For example.
Column A: Type A, Type B, Type C (multiple values in a single cell)
Column B: Option1, Option2 (multiple values in a single cell)
Result: Type A - Option1, Type A - Option 2, Type B - Option1, Type B - Option 2, Type C - Option1, Type C - Option 2
How are these “multiple values in a single cell” delimited? Are field A and B (= column A and B) defined as Multiple Select fields? Or are the values in those fields in different lines (return delimited)? Or delimited in some other way (like commas)?
And how can you be sure that the first value in Column B properly corresponds to the first value in Column A, and that the second values properly correspond etc?
I’d find this easier to think about if you showed a screenshot of one record with values in it…
.
ADDED 5 MINUTES LATER: I see that @ScottWorld has an idea that you’re trying to implement a many to many relationship. That hadn’t occurred to me and if ScottWorld is right, then by all means read that link. But I’m thinking that it’s more likely the solution to your issue is a simple parent-child (one-many) relationship (as opposed to a many-many).
Your request strikes me as odd. In twenty-five years building databases I don’t think I have EVER wanted to do what you’re asking to do. As I say above, I don’t really understand what you want to do, but I’ve tried to imagine situations in which something kinda sorta like your example would make sense. In every situation I can think of, I would create a “child” (many) table and link it to the current table. So say there was a table (say, used by an elementary school) named Parents and a table named Children. In the Children table there might be fields like Name, DateOfBirth, etc. I could then concatenate those values from the Child record into the primary field for that table and in the column in the Parents table that shows linked values I could see things like “Susie (4/1/2010), Tommy (1/1/2014)”. And I’d know that the DOB values are linked to the right children.
.
SECOND ADDITION 15 MINUTES LATER: I already mentioned that this might be done with a related table and that still seems to me likely to be the right answer. But let me try to answer the question as actually asked. The answer to the question as asked is (I’m pretty sure) NO — this is not possible with Airtable’s native tools. It would be possible in FileMaker, because FileMaker’s function library includes a couple of tools Airtable lacks: a GetValue() function and also the possibility of recursion.
Say Column A of record 1 contains these values: Favorite Sport Favorite Novel Favorite Cuisine
And Column B of the same record contains Cricket Bleak House Indian
With those two lists, I could extract the values in an orderly way in FileMaker something like this:
Favorite Sport=Cricket Favorite Novel=Bleak House Favorite Cuisine=Indian
If you weren’t sure there would be three and only three values, in FileMaker you could revise the calculation above so that it uses an increasing variable (replacing the literal 1, 2 and 3) and loops until it exhausts the values in the list.
In Airtable, I’m sure you could do this with JavaScript and pretty sure you could do it with Regular Expressions. But neither of these is normal, easily accessible Airtable. It might be possible to bang together a complex formula in Airtable that uses Airtable functions like FIND(), MID() and others to parse out a string of known characteristics (known values, known delimiters, etc). But it makes my head hurt to think about it, given the limitations of Airtables function library.
@Ryan_Meacham It sounds to me like you might be trying to achieve a many-to-many relationship, which would require you to add a few extra tables to your base, and use linked record fields instead of multi-select fields in your current table.
You do not say where you want this result. Do you want each combination in its own cell? Do you want all the combinations in a single cell? Do you want new rows based on the combination? Should the result be plain text or do you want linked records?
Also, will there always be exactly three types and two options, or could the number of types and options vary?
Chances are that the best way to get what you want will involve a script of some sort.
Thank you all for your prompt follow up questions. I’d like to clarify a bit further with a more specific example:
I am trying to build simple element classifications from a much larger list. Some of those elements are applicable to multiple building categories so I’d like to dynamically create combinations of the EC1 and EC2 fields into the EC Combined Field via a formula and dump it into a 3rd cell. I don’t want to use a script because this is going to be continuously updated and ultimately used by a client who does not know how to use any of this.
This solution looks like it was getting close, but I couldn’t make it work:
