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.
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
