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!
