Manage Inventory w/ Multiple Locations

[ssr125]

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?

[Rose_K]

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.

[ssr125]

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.

Airtable

Airtable | Everyone's app platform

Airtable is a low-code platform for building collaborative apps. Customize your workflow, collaborate, and achieve ambitious outcomes. Get started for free.

[Rose_K]

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!