Inventory Management and transfers

[BillyPanaggio]

I am trying to create an inventory management system with location details for particular items. I am currently able to receive and consume products, but need help with the transfers both into and out of a location as well as transfers that don't add or consume inventory. 

I currently have the following tables

Products - List of items and pack type

Inventory - List of items and quantities with buttons for received and consumed

Received

Consumed

Locations

I am unsure how to integrate the locations table to mesh with the inventory.

[TheTimeSavingCo]

If your end goal is to see how much of each item you have at each location and you have a set number of locations that doesn't change much, you could potentially get away with creating a conditional rollup per condition like so:

Link to base

I realize this doesn't follow the schema of your base, but it should get you started I reckon.  Happy to help further if you hit any snags

[BillyPanaggio]

Would you mind taking a look at my base if I shared it with you? I have changed it a bit and can now see locations and items in each location, but I cant seem to get the product name to be cleared when inventory is consumed. 

I am also having a bit of trouble with my forms for "Consumed" and "Received" When I prompt the user for location name, and have a lookup field for Location ID it just shows up as the name, and wont input the Location ID. DO you think I should change my Location Name column to just be a formula based on Name, and ID?

Base

[TheTimeSavingCo]

> Would you mind taking a look at my base if I shared it with you?
Sure

> I cant seem to get the product name to be cleared when inventory is consumed.
Hm, I don't follow.  Could you give me an example of this please?

> DO you think I should change my Location Name column to just be a formula based on Name, and ID?
Yeah, a formula field with all the data the user would need to select the place would be great

[BillyPanaggio]

The base should be shared with you now, I linked it in the previous response. 

When I receive Product Via the button I can input a location and quantity, but when I go into my locations or Inventory Tables I cant see that the inventory is in there. Vice-Versa, If I then consume the product the product name doesn't seem to be cleared from the location table. See screen shots below, I will also leave the Base as is for now, so you can play around and take a look if you wouldn't mind.

I changed the location name to be a formula on the table, and that seemed to fix the receiving and consuming issue.

Thank you for all of your help.

[TheTimeSavingCo]

Ah hm, yeah, I saw your base initially but figured you were trying to explore an alternate method so didn't comment on it. 

May I know what issues you faced while implementing the suggestion I provided, or what issues you find with said suggestion?  While it's probably possible to get your desired functionality with the way you've got your base set up now, it would take some time to figure out how to do that, whereas if you used the example I provided your problem would be solved now, you know what I mean?

[BillyPanaggio]

Hey Adam,

I will give your method a shot over the next few days and report back! Nothing really holding me back other than the time spent to build it initially, but if your method works more gracefully I'm all for it.