Skip to main content

View combining multiple tables

S
Forum|alt.badge.img+1

Dear AT community,
Doing inventory. I have 3 tables with a list of items, each having their quantities, re-order levels, price, etc. Reason for 3 separate tables below. Looking to create a combined “shopping view” - items from all 3 tables that are below re-order quantities. What is the best way to build this view?
Context - Each table has a re-order level column (1/0), that calculates based on a formula. Each table has a corresponding “shopping” view that is filtered on items where re-order level =1. The items are separated into tables by consumption types. Table 1 consists of items measured in numbers/units, 2 has items measured by weights and 3 by lifespan. I could do these as separate columns in a single table but I figured 3 separate tables will make for clean design. Also, curious to learn creating a single view from multiple tables.

As always, really appreciate the guidance and learning.

Regards,
Sunny

    2 replies

    C
    Forum|alt.badge.img+16

    You could create an inventory table with three link fields (one for each table of items) and three lookup fields that look up the reorder status on those three tables.

    You’ll need to create a record on the inventory table for each item in your other tables and link it up. So if you have 40 number products, 30 weight products and 20 lifespan products, your inventory table will have 90 records on it. It’s easy to set up those links – just copy the first column from each product table, paste it into the corresponding link field on the inventory table, and tell it to expand the table to create more records. One downside is that when you add new products you have to remember to add them to the inventory table as well, which is something to consider in your decision to have three separate product tables.

    Once your inventory table is populated, you can create a formula field that reads the three reorder lookup fields. Something like this:
    If(sum(reorder1, reorder2, reorder3) = 1, “Reorder needed”, “”)
    Only 1 of the reorder fields should ever have a number in it but you don’t know which one so you just add them all together to catch the one that does have a 0 or 1. You’ll have to test this but I think it will work.

    Then you can create a view that is filtered on that formula field so that it only shows the records where a reorder is needed and it will include records from all three tables.

    I would say that if your three product types mostly have the same fields with just a few differences, it would probably be easier and cleaner in the long run to put all three product types on the same table from the beginning. You can have different views that show and hide the fields that are different for each type of product, and you won’t have to remember to add links to an inventory table every time. You can have three different formula fields to calculate whether a reorder is needed (I’m assuming that depends on the product type) and sum them up in a fourth formula field on that same table.

    S
    Forum|alt.badge.img+1
    • sunny11
    • Author
    • Known Participant
    • 12 replies
    • August 17, 2020
    Celeste_Bancos wrote:

    You could create an inventory table with three link fields (one for each table of items) and three lookup fields that look up the reorder status on those three tables.

    You’ll need to create a record on the inventory table for each item in your other tables and link it up. So if you have 40 number products, 30 weight products and 20 lifespan products, your inventory table will have 90 records on it. It’s easy to set up those links – just copy the first column from each product table, paste it into the corresponding link field on the inventory table, and tell it to expand the table to create more records. One downside is that when you add new products you have to remember to add them to the inventory table as well, which is something to consider in your decision to have three separate product tables.

    Once your inventory table is populated, you can create a formula field that reads the three reorder lookup fields. Something like this:
    If(sum(reorder1, reorder2, reorder3) = 1, “Reorder needed”, “”)
    Only 1 of the reorder fields should ever have a number in it but you don’t know which one so you just add them all together to catch the one that does have a 0 or 1. You’ll have to test this but I think it will work.

    Then you can create a view that is filtered on that formula field so that it only shows the records where a reorder is needed and it will include records from all three tables.

    I would say that if your three product types mostly have the same fields with just a few differences, it would probably be easier and cleaner in the long run to put all three product types on the same table from the beginning. You can have different views that show and hide the fields that are different for each type of product, and you won’t have to remember to add links to an inventory table every time. You can have three different formula fields to calculate whether a reorder is needed (I’m assuming that depends on the product type) and sum them up in a fourth formula field on that same table.


    Thank you, @Celeste_Bancos. I will give this a try. The only roadblock I sense is the formulae. So far, I have listed 11 different ones across these 3 tables. I got this working in a Google Sheet last night, next stop is to bring it over back to AT

    C
    1 person likes this
    • C
      Celeste_Bancos

    Reply


    Most helpful members this week

    Terms & Conditions