Manage Inventory w/ Multiple Locations

Topic Labels: Base design
2139 3
Showing results for 
Search instead for 
Did you mean: 
4 - Data Explorer
4 - Data Explorer

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?

3 Replies 3

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.

4 - Data Explorer
4 - Data Explorer

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)

Shared with CloudApp

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.

Shared with CloudApp

An extra formula field can then take care of tracking of the leftover quantity of each prescription available for new orders.

Shared with CloudApp

Here are the three columns next to each other on the Procurement table:
Shared with CloudApp

Please let me know if that helps-- or if there is anything else I can help with!