Combining (2) tables of inventory into a third combined table - dynamically?

Question

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.

gees · Accepted Answer

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?

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 · Answer

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?

Combining (2) tables of inventory into a third combined table - dynamically?

1 Attachments

3 replies

1 Attachments

Sign up

Login to the community

Scanning file for viruses.

This file cannot be downloaded