Inventory tracking various locations

I’m trying to create an app to track stock items through various “locations” in our workflow. I downloaded and tweaked some of the sample inventory apps but can’t get this desired functionality to work. Here’s what a typical inventory workflow would look like:

100 boxes of bristles received from Vendor and added to Connex Inventory.
50 boxes signed out from Connex Inventory and added to Garage Inventory.
10 boxes signed out from Garage Inventory and added to Truck 88 Inventory.
5 boxes signed out from Truck 88 Inventory and installed on Sweeper A.

I want to be able to look up current inventory at each “location” (Connex, Garage, Truck). Based on example above, the totals should be:

Connex Inventory: 50
Garage Inventory: 40
Truck 88 Inventory: 5

I’ve had no luck getting this to work and I’m not sure if it’s a matter of how my app is set up (need more/better tables) or if I haven’t found the right expressions.

My app currently has 2 tables - Items and Inventory. The Items Table is the master list of all inventory items, and the Inventory Table is capturing all the transactions. The tables have the following columns:

Items Table: [item id], [name], [description], [initial stock], [restock alert].

Inventory Table: [item id (ref column)], [name], [description], [from location], [to location], [amount].

Is this possible with just the 2 tables I have or do I need to rethink my structure? Or can this be accomplished with the right expression? Any guidance is appreciated!

1 20 2,074
20 REPLIES 20

Ethan_U
Participant V

Hello, I’m a newer user so happy to defer to others but since this seems reasonably simple, and there are no other replies I’ll give my thoughts!

It seems like 3 tables could do.

You’d have an ITEM table (bristles for example), add a virtual column here called current total inventory which would calculate the total values from the related child ITEM LOCATION records i describe later…
ITEM fields:
item id, name, description, current total inventory amount (VC), related items ITEM-LOCATION (appsheet will auto generate)

A LOCATION table (Connex for example)
LOCATION fields: location id, location name, related items ITEM-LOCATION (appsheet will auto generate)

An ITEM-LOCATION table with references to both ITEM and LOCATION. which would record an instance of an item occurring in a location with a positive or negative value. This value would adjust the virtual column on the parent ITEM record to keep a running tally for perpetual inventory.

ITEM-LOCATION fields
item location id, ITEM (ref), LOCATION (ref), sign in or sign out (enum), amount (positive number), adjustment amount (virtual column to calculate either a positive or negative number based on response to sign in or out, I believe it would reduce user error instead of allowing the user to input a negative sign before the regular amount column), current user, time stamp

Each new record to the ITEM-INVENTORY table would add to or subtract from the current total inventory of that parent item in the VC in the ITEM table (Bristles).

Also, since the LOCATION table would have a list of ITEM-INVENTORY records you could display this list grouped by item with a SUM on the group to show the current inventory of each item for that location.

That would be the broad strokes I hope it helps and let me know if you’d like me to elaborate more.

Ethan, thanks so much for the guidance! I’ll take a crack at this setup. I’m sure I’ll have follow-up questions. Thanks, again!

Great have fun and please let me know how it goes

How’s it going!

Hello Ethan, I learn about how easy your ways to do the inventory ! Thank you !

I have some questions about the inventory movement. Is it possible to add multiple items ? and concept about if we move item from location A to B, Do i need to create the action to add another row of deduct from location A ? like + for B and - for A

For this I will refer you to a much more experienced user…

@MultiTech_Visions

If I understand your question correctly I believe this is what you are referring to, and this video should help.

He has two videos on adding multiple rows, this is the shorter one. I am still honestly in the process of trying to understand and apply them for my apps.

Best of luck

I’d go with the following (TESTED):
Location table. Fields: ID, Name
Item table. Fields: ID, Name, other
Inventory table. Fields: ID, Item (Ref), Location (Ref), Stock (VC)
Transfer table. Fields: ID, timestamp, From Location (Ref), To Location (Ref)
Transfer_Item table. Fields: ID, Transfer (Ref), Item (Ref), Quantity
The Stock Virtual Column should take into account all the negative contributions of the From Location column, and all the positive contributions of the To Location column.
Moreover, in order to make it work in the real word, similarly, you’ll need to handle the orders in order to correctly record the locations the items are taken from in each order.

Stock Virtual Column definition:

SUM(SELECT(Transfer_Item[Quantity],AND(IN([Transfer], 
   SELECT(Transfer[ID],[To Location]=[_THIS].[Location])),[Item]=[_THIS].[Item])))
   - (SUM(SELECT(Transfer_Item[Quantity],AND(IN([Transfer], 
   SELECT(Transfer[ID],[From Location]=[_THIS].[Location])),[Item]=[_THIS].[Item]))))

Thank you for your suggest ! I will try with my inventory too. My warehouse have many location and would like to track movement.

For handle long term usage, Do i need to make scale ? I think i read somewhere that Google sheet have capacity 5M cells. If we do tracking movement as in and out that mean 1 item would have 2 row for action from Into some location and out from some location.

According to my model, you aggregate in a single shipment (Transfer) more items (Transfer_Item) being shipped at the same time, from location A to location B. Each item shipped therefore is a single line (not two) but has a parent shipment (that is able to collect many shipments at the same time from and to the same locations).
But it all depends on your preferences and business model.
For sure, if you forsee to have more than thousand rows in a single table, I’d consider to partition it or move to a real DB

Thank you for sharing your model !!!

This really help me so much.

I have one more question. If i would like to see list of items in Location A with the actual amount, How to do it ? I am thinking about make ref_rows with the formula like select To location - From Location but i think its a bit strange . Do you have any suggestion?

I got the answer now.
I create Current Amount (VC) with formula :

SUM(Select(Transfer_Product[Quantity],AND([Product]=[_THISROW].[Product],
[Transfer].[To Location]=[_THISROW].[Transfer].[To Location])))-SUM(Select(Transfer_Product[Quantity],AND([Product]=[_THISROW].[Product],
[Transfer].[From Location] =[_THISROW].[Transfer].[To Location])))

to get the current amount of that product when move into X Location and then create VC List in Location sheet with formula : SELECT(Transfer_Product[Transfer_Product Id],AND([Transfer].[To Location]=[Location Id],[Current Amount]>0))

Now i can get the exist and current amount of any product in X Location.

i am also raise an issue related to this could you please help me to creating

Hi, sorry ya, i tried using this VC column definition, unfortunately i got error code:

The expression wrong?

No idea what’s wrong with your formula, please try to simplify it until you find out the issue there.

Hey Perissf, thank you for sharing this, its a huge help. I was wondering if you could guide me through the tansfer process a bit. I would liek to move an item from location (X) to location (y), but am having a hard time doing so. I used your forumla and layout and it seems as though the app will transfer items, but ultimaetly after going through the process it doens move the location on the app, or my spreadsheet.

In my location tab should I also include Item and quantity fields as reference columns?

 

Any thoughts?

Hello Ethan. Been a busy month moving offices and working on other apps. Finally getting back here. Phew!

Thanks for the help with this. I set the app up as suggested and it’s close to what I need. I have a couple of questions that I’m hoping you can help with.

You wrote that:

Did you mean the ITEM-LOCATION table? I don’t see “ITEM-INVENTORY” as one of the tables you suggest to create. Or am I reading this wrong?

In the “ITEM-LOCATION” table I have the “Item Location ID” set up as my key. Is this just an auto generated UNIQUEID() or is it supposed to be something more? Can I add a “Date/Timestamp” column and make that my key, or would this mess with functionality?

Last thing, one of the ways we’re intending to use this app is to track inventory through various locations. For instance:

  • Bristles are received into garage inventory from vendor on Monday.
  • A weeks later I sign out a box of bristles and add it to my truck inventory.
  • Next day I sign it out of my truck inventory and I install it in my skidsteer.

If I were to do this as currently set up, I’d have to perform 5 transactions:

  1. receive/sign in to garage
  2. sign out of garage
  3. sign in to truck
  4. sign out of truck,
  5. sign in to skidsteer

If I added a “From” and “To” column for each transaction, how would I make this work? Thanks!

Did you mean the ITEM-LOCATION table? I don’t see “ITEM-INVENTORY” as one of the tables you suggest to create. Or am I reading this wrong?

Yes I’m so sorry I cant go back and edit apparently. Check out this post it seems @perissf may have a better solution

Nuttawutz
Participant III

Thank you ! Ethan

Hey all, I'm trying to create an inventory management system, and am running into a few road blocks. We would like to be able to accomplish the following within app sheets.

Bring in inventory to a specific location

Transfer inventory out of location to be sold

 

I am currently having trouble with getting app sheet to display the inventory in  the location, however the formulas to calculate total inventory, inventory in, and inventory out are working well. Currently I have the following sheets:

"Item Inventory Data" - Barcode, Image, Item Name, Item Category

"Warehouse Location Data" - Location ID, Location Name, Item Name, Item Quantity (this is where I'm pulling my "Initial Stock" from)

"Inventory In" - Timestamp, Barcode, Quantity, Location Name, Location ID

"Inventory Out" - Timestamp, Barcode, Quantity, Location Name, Location ID

I can add and remove inventory using a form in app sheet, and the current inventory will change, however when I look at the specific location I put it in the item name and quantity aren't displayed until I drill into the location. Currently being displayed under "Related inventory Ins"

 

Any help would be greatly appreciated! Thanks 

Please start a new topic for help with this.

Top Labels in this Space