Let's toss some salads aka a script for comparing linked record values

So let’s say I have a ‘Selection’ table that is linked to two other tables called ‘Fruits’ and ‘Veggies’. (Multiple linked records are enabled for both). I want to combine one fruit with one veggie to make new kinds of salads nobody has ever had before!

The ‘Fruits’ table has a single select field called {Colors}. The ‘Veggies’ table has a multiple select field that is also called {Colors}. Both fields have the same color options.

I am trying to produce a script that takes the linked records from the ‘Orders’ table and generates a new record in the ‘Salads’ table for every combination of a fruit and veggie that share a color property.

So in the example of [Apple, Tomato, Avocado] & [Onion, Spinach, Carrot], the script would create 4 records using the following accepted color combinations:

  • (Red) Apple & (Red) Onion
  • (Red) Tomato & (Red) Onion
  • Avocado & (Green) Onion
  • Avocado & Spinach

The way I think the script should work is:

  1. The script gets the linked records in both the ‘Fruits’ & ‘Veggies’ table for a given record.
  2. Cycles through the veggie records and compares each one with every fruit on the list to see if there’s a color match.

Remember, ‘Fruits’ table’s {Colors} field is single select and ‘Veggies’ table’s {Colors} field is multiple select.
3. If a fruit and veggie have a color in common, add it to a new array for later.
4. After all iterations are complete, create a record for each item in the new array for all fruit/veggie combos (linking the fruit and veggie in respective Linked Record fields)

Does this seem like the right approach? Is there a more efficient way to do this?
I’m struggling most with accessing the field values of the linked records and comparing them with a field from a record on another table.

Check out this script from Airtable and see if it does what you’re looking for!

If that doesn’t do it, @Kamille-Parks created this Junction Assistant extension:

1 Like

Thanks for the suggestions @ScottWorld!

I actually wrote a script for the same project that can create junction table records like in the Airtable script you shared.
EDIT: After actually installing the script I will add that while it doesn’t do what I need to, it’s really helpful for me to read the code and learn from that so while this doesn’t solve my problem I am still benefiting from it.

Kamille’s extension is more capable but it doesn’t look like it can compare two sets of linked records and only create records based on a matching criteria which is the main part I need.

I suppose that is one way of doing it. But that is a lot of nested looping. Another way to do it is to create a hash where you map colors to fruits and veggies. This would require fewer loops.

I put an initial write-up here. The code is not fully fleshed out, as I am typing on a tiny screen. But it should have enough info for you @AirBenderMarcus to understand the concept.

1 Like

Thank you so much for the thoughtfully explained suggestion. I’m still wrapping my head around it. Can the hash system dynamically account for new options down the line or does every possibility need to be hard coded into it?

You build the hash as the first of the script. You don’t hardcode any colors. Add a new key as you find a new color.

1 Like