Combining rows from various tables into unique strings

Ask the Community
#1

Hey folks,

I have 4 different tables that each contain a single “name” column. You could think of these as “types of ingredients”

  • Audience name
  • Terms of art
  • Community keywords
  • Products

I want to use the unique ingredients from those tables in “recipes” uncover every possible combination of ingredients (within the constraints of each recipe, where order does matter):

  1. Audience name + terms of art + community keywords
  2. Audience name + products + community keywords
  3. Products + terms of art + community keywords

Ideally each unique string of ingredients produced by a recipe would populate as a row in another table. I’d also love to be able to easily come up with new recipes and produce strings from them as needed in the future.

Has anyone built something like this, or could you give me some pointers with where to start? I’m already questioning if having all my ingredients in separate tables is over-complicating this; perhaps they ought to all be in one table with another column that handles “type” (which is currently their table name).

#2

Do you want to generate a table of all possible combinations? Random ones? Manually select from dropdowns?

1 Like
#3

Hi @Alice_Packard ,
please, help me to better understand.

You have four tables:

  • A (Audience name)
  • T (Terms of art)
  • C (Community keywords)
  • P (Products)

And you need to combine their rows (with the single column name) in all the possible permutations and subsets (i.e. all 4 tables, 3 tables only and 2 tables only)

This would produce:

  • 24 permutations of all 4 tables (4!) (e.g ATCP, ATPC, CTAP, …)
  • 24 permutations of the subsets of 3 tables: (4!/(4-3)!) (e.g. TAP, TPA, CTA, TPC; …)
  • 12 permutations of the subsets of 2 tables: (4!/(4-2)!) (etc…)

Multiplied by the rows of tables that, for the sake of simplicity, let’s assume they all have the same number of rows, e.g. 20
So we would have (24 * 20) + (24 * 20) + (12 * 20) = 1200 possible combinations.

Is this correct…?

#4

Hey Nick! My answer to your first two questions is the same: No, only certain combinations. I could see using a series of dropdowns to make those selections. I’ll explore that idea!

#5

Hi Federico, thanks for asking for clarification!

I don’t need all possible permutations, I just need some. I’m specifically looking for:

ATC, APC, and PTC

I may need other combinations in the future, but for the time being those are the only combinations I care about. Order does matter. So with those three combinations, the grand total would be more like a couple hundred strings.

Ideally these permutations would all end up in a single table, perhaps called “permutations.”

Hope this helped; if not let me know, happy to try explaining what I’m trying to achieve another way!

#6

Hi @Alice_Packard ,
in that case, if you already know in advance the subset of tables, you can write an action that fill up a table with the possible row permutations.

I provided you an example with the ATC combination, so that you can easily go with the other ones.
Have a look:

Let me know if this goes to your desired direction.

1 Like
#7

Yes! I haven’t looked closely at the formula (and I suspected FormulaMap might be needed to achieve this—it’s not something I’m confident in using just yet), but this seems to do exactly what I need it too. Really appreciate your help Federico!

1 Like