Help

The Community will be temporarily unavailable starting on Friday February 28. We’ll be back as soon as we can! To learn more, check out our Announcements blog post.

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

Topic Labels: Automations Base design Views
Solved
Jump to Solution
740 3
cancel
Showing results for 
Search instead for 
Did you mean: 
gees
5 - Automation Enthusiast
5 - Automation Enthusiast

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.  

1 Solution

Accepted Solutions
gees
5 - Automation Enthusiast
5 - Automation Enthusiast

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 - 

See Solution in Thread

3 Replies 3

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
5 - Automation Enthusiast
5 - Automation Enthusiast

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 - 

Ah I've set it up for you here to try out
Screenshot 2024-10-02 at 12.49.10 PM.png

Screenshot 2024-10-02 at 12.50.19 PM.png