Mar 02, 2021 03:01 PM
I need to track a set of items that are received in one location and then moved to other locations. Each location receives from the main and/or each other. Each location decrements as well. I need to keep track of the quantity that is moved from the main location to all of the others, decremented from those locations and keep overall total quantity updated. Can anyone help?
Mar 11, 2021 04:12 PM
Hello and welcome @ssr125!
Could you possible share a copy (excluding any sensitive data) of the base you are working on? I think I’ll be able to better help once I get a sense of the base and tables you are working with.
Mar 12, 2021 12:25 PM
Hi Rose, thank you! And thank you for taking a look at this, its been driving me crazy! I feel like I am so close but can’t connect the last pieces.
Here is a link to the base.
Mar 12, 2021 04:20 PM
Thank you so much for sending me a link to your base!
After taking a look at I recommend restructuring your base to take advantage of the Airtable’s ability to represent a many-to-many relationship because I think it will help with your quantity tracking problem.
For your specific scenario I created a simplified version of your base that I think should be able to cover the functionality you are looking for. I created three tables:
Procurements - the same table you had before
Locations - for keeping track of each specific Pharmacy
Pharmacy Example Community:...
Orders - tracking the order of each prescription given to a particular pharmacy (including the quantity ordered)
With your tables setup like the above you could then create a rollup field on the Procurement table to sum up the total quantity of each prescription that each pharmacy had ordered.
An extra formula field can then take care of tracking of the leftover quantity of each prescription available for new orders.
Here are the three columns next to each other on the Procurement table:
Please let me know if that helps-- or if there is anything else I can help with!