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:
- The script gets the linked records in both the ‘Fruits’ & ‘Veggies’ table for a given record.
- 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.