Manage Inventory w/ Multiple Locations

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.

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.

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:

1. Procurements - the same table you had before

2. Locations - for keeping track of each specific Pharmacy
   Pharmacy Example Community:...

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