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 Like

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.

8 Likes

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!

4 Likes

Great have fun and please let me know how it goes

3 Likes

How’s it going! :slight_smile:

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

2 Likes

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

5 Likes

Thank you ! Ethan :grin:

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]))))
3 Likes

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

3 Likes

Thank you for sharing your model !!!

This really help me so much. :+1:

1 Like

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 :thinking:. 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.

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? :grinning:

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? :grinning:

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

1 Like

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.

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