How to distribute inventory to multiple service vehicles

[Wallace_Service]

Hi, is it possible to add/pull inventory from more than one location? Say if you have a main warehouse which acts as a distribution center and multiple service trucks that pull inventory from the main warehouse and then remove it as used during service tickets? I’ve figured out how to add and pull from one location via referenced tables, just not sure if i can pull from multiple locations depending on user. Thanks for the help in advance!

[Steve]

Ideally, all inventory, regardless of location, would be recorded in a single table. A column of that table would identify the item’s location. Your question suggests to me you have a separate table for each location. Is that correct?

[Wallace_Service]

Hi Steve, thanks for the reply. Right now I have a single table with all inventory, except I can’t tell where the inventory is. We may have one row of that table in 6 or 7 different physical locations so I’m trying to find a good way of splitting the inventory into true locations for the purposing of refilling stock per location when needed. Thanks!

[Northside_Car_K]

Hi, I am also needing this feature please. We have 1 column for Workshop and 2 other columns to record stock in each of our vehicles. Needing a way to set up easy transfers between locations on the app please, so we can know which van has what stock at any time.

Looking forward to a reply soon.

[Lynn]

Hi @Northside_Car_Keys @Wallace_Service As @Steve Steve has said you need a column for location. You could also have a column that is a concatenation of location and product to differentiate between the same products at differing locations.

[Wallace_Service]

Hi @Lynn and @Steve, thanks for the advice. I think this makes sense to me, any suggestions as to a workflow that would move the parts from a central distribution location to each sub-location prior to being used? What if we were to concatenate location/part number, scan it out of central distribution and add it to the concatenation of sub-location/part number? Then when use scan it out of the sub-location to the work order? Thanks!

[Steve]

Could probably offer more suggestions, but need a clearer picture of what you’re trying to do.

[Wallace_Service]

Hi @Steve, nuts and bolts of it are we run a service business that has technicians across the country. Our head office/distribution center for parts is in one location but we often transfer parts all over the place to keep technicians stocked. The service ticket app was initially created as a work order where we could see what parts a tech used immediately to streamline the billing process. It appears though that the app could at the same time keep track of inventory at all of the different locations parts may be we can to ensure stock is proper everywhere.

[Steve]

Based on this previous comment:

Wallace_Service:

Right now I have a single table with all inventory, except I can’t tell where the inventory is. We may have one row of that table in 6 or 7 different physical locations so I’m trying to find a good way of splitting the inventory into true locations for the purposing of refilling stock per location when needed.

I take it to mean each row covers some quantity of a single part number with no indication of where a particular quantity of those parts are. Consequently, you have no visibility into the inventory at each location. Based on that interpretation, I would add a location column to the table and split the inventory records out according to their locations: one row per location per part number. The key could then be generated by concatenating the location and part number to ensure uniqueness, which is pretty much what you suggested:

Wallace_Service:

What if we were to concatenate location/part number, […]

Transferring inventory would probably require a separate table to capture the transfer order. A transfer order would include the part number, part origin, a quantity, and the destination. A workflow would process this transfer order table, remove the quantity of parts from the origin, add that same quantity to the destination, and delete (or keep and mark as complete) the transfer order record. Again, in-line with your thinking:

Wallace_Service:

[…] scan it out of central distribution and add it to the concatenation of sub-location/part number

Then we have the issue of moving parts to work orders. You could handle that by expanding the transfer table from above, replacing the destination column with two separate columns: To Location an To Work Order. Only one of those destinations could be filled per transfer order. Again, a workflow would handle the quantity manipulation. And again, this is what you suggested:

Wallace_Service:

Then when use scan it out of the sub-location to the work order?

What did you need our help for again?

[Wallace_Service]

Thanks @Steve, going to see if I can implement this. Appreciate all the suggestions. I’ll let you know how it goes!

[Elijah_Magrane]

I currently use prefilled linktoform actions to transfer inventory between tables. Can anyone suggest an alternative workflow? Linktoform works but I find it hard (and sometimes confusing)to maintain. Are there any sample apps that might be useful

[Steve]

@Eli, your question might be better posed in its own topic for greater visibility.

[Wallace_Service]

Hi @Steve, thanks for the direction thus far! I have most of this app working now, I have a few more bugs to work out. The main one being I don’t quite understand how to structure the expression for a transfer. I ended up simplifying as suggested. I have one table called Inventory that contains all parts and an individual column in that table for each location the part may be in. I have a second table called Transfer order that allows you to pick a part and move a given quantity from one location to the other or from a location to a work order. What I do not have working yet is how to connect the Transfer order table to reducing inventory/increasing inventory in the respective columns in the Inventory table. I know there are some expressions involved here but I’m uncertain of how to structure. Do you have any suggestions?

[Elijah_Magrane]

Hi, I’ve been following this thread because I have been running the exact same workflow for some time now. I am curious as to what solution you used for transferring items from the inventory table to the the transfer/work order table? I ended up using a prefilled LINKTOFORM paired with an action. I am curious to see what others have done.

For totalling the sums I just used spreadsheet SUMIFS expressions. I am sure you could do all this more elegantly in AppSheet so I am curious what @Steve has to say.

[Josh_Klassen]

Did you ever get a response to this? This is exactly what I am trying to accomplish with my app. To clarify, a response to transferring parts from one location to another and having the inventory change at each location accordingly.

[Wallace_Service]

Hi all been a while and thanks all for the helpful suggestions. I have managed to get work orders to tie to inventory locations now. I’ve also connected our purchase orders directly into inventory. Finally we are now able to get alerts when any stock location is low on a product. All flows nicely. I found I had to use both Appsheet expressions and Google Sheets expressions to accomplish. SUMIFS in Google Sheets was helpful in calculating inventory quantities at the backend. I’d be happy to help out anyone working on something similar.

[RN_Builders]

Hi! I’m creating an app that almost works like the one you discussed. How did you do it exactly? (the transferring of parts from one location to another and being able to simultaneously update the inventory) Any help would be great. Thanks!

[Mauricio_Corder]

is there rough you could post so that some of us can look at; I’d want to see how you organized the transfer workflow doc?

[Wallace_Service]

Hi Mauricio, I’ll share with you shortly for sure. Right now I’ve actually got the calculations being done in a Google Sheet (basically using a whack of SUMIF equations) to make this work and Appsheet is more or less the interface. We are in the process of moving this database to a Cloud SQL server so were are moving all of the equations to the Appsheet end. I think this will be a better design to share. Should have shortly!

[Luke_Vancleave]

I think I made something similar when I made an inventory tracker using Appsheet and a bunch of SUMIF’s. I had a whole matrix of like over 100 columns because I had to make a “Moved To” “Moved From” “Broken At” “Bought At” for every location and I was limited to how many locations I could do simultaneously because I’d have to create their SUMIF columns to calculate them. It was kind of a mess.

I’m working on something now that uses four tables that essentially lets me have unlimited number of items and locations and any number of those items at those locations.

I have one table of Items with no quantities.
I have one table of Locations.
I have one table of Movement records where I say what Item is coming From what Location, To another Location, and the Quantity.
Then I have a fourth table of Item Location Quantities which I have represent rows created to represent the Items that get moved to Locations and then SUM’s all the movement records to and from that location. It works pretty well to get whatever quantity of items amongst any number of locations with no spreadsheet calculations.

[Alex_Iv]

Hi Wallace_Service, I faced exactly the same task, do you have any draft that you are willing to sharet, I would really like to see the implementation of this task. the option with counting items in the table suits me quite well, we have a small business, 4 locations and about 50 items of spare parts.
I would appreciate any help!