How to combine two tables in one

[gees]

Hi there!  Sorry - this is probably simple - but I'm having trouble finding a simple solution for the sample issue below (pretend scenario for ease of explaining):

I own a fruit store, and I sell individual fruits & bundles of fruits:

• Table 1: Multiple records of Fruits (apple, orange, banana, watermelon, cantaloupe)
• Table 2: Multiple records that are Bundles of Fruits (Red Bundle contains apple & watermelon, Melon bundle contains watermelon & cantaloupe) where I have the Bundles linked to the Individual Fruits records
• Table 3: I would like to create a third table that would be my Total Inventory - so each record in this third table is the individual Fruits as well as the Bundles.  I would like to have this third table autopopulated, so I don't have to remember to manually update the Total Inventory table every time I add in a new individual fruit or bundle.

Is this possible?  And the reason I want this in the first place is that I would like to create a separate Base for a Department Store, which could have a synced table for Total Inventory for fruit, and then have different tables for other merchandise like clothing, electronics etc.  

[TheTimeSavingCo]

Hmm, what if you had an automation tied to Table 1 that triggers when a new record gets created?  It's action would be to create a record in Table 3 and link it to the triggering record.  In Table 3, you'd then have a lookup field to grab whatever details you need

You'd then repeat the process for Table 2, and in Table 3 you'd use a formula field to combine the lookup field values

---
I'm not too sure about wanting different tables for each type of merchandise; what do you think of using a single select field to categorize them instead?

[gees · Accepted solution]

Thanks for your input!  I was trying an automation as you suggested but it just wasn't working correctly - likely operator error. 😉 I had an automation set up for table 3 that was triggered to add a copy whenever a new record was created in table 1 or 2 and it kept just creating blank records - I think I was missing the "look up" element.  I'll recheck...

But I also have been thinking more about your suggestion to consolidate tables.  I honestly like the idea, sometimes I have trouble deciding when I should break out data into tables or use select fields.  I decided to break out into tables in this case as I was using linked fields from the Table 1: Fruits to make up the Table 2: Bundles.

But I think you could be right, so I will play around a little and see if I can get it all on one table - 

[TheTimeSavingCo]

Ah I've set it up for you here to try out