Inventory App with multiple locations

AppSheet AppSheet Q&A
, ,
1

Hello,

Im sure this has been asked some way or another but I haven’t been able to find exactly the right solution.

I am building an inventory app with multiple locations (shop with several service vans), and currently I have it set up so that each locations has its own table. The general idea is that each location will have its own inventory but the part names across all of the locations need to be the same. (i.e. one item could have 3 in shop and 1 on each van).

I would like to set it up so that when an item is added to the shop, it is also added to all of the other vans with availability of 0. Also, when an item is taken from the shop, the user can easily add it to the van inventory.

I have been reading up on references and think this could be the way to do it, but I want the items to be able to be stored in more than one location, (just different amounts in different locations).

What is the best way to go about this?

2

Rightman_Ind:

with multiple locations (shop with several service vans)

This part is not quite clear. Is it a single shop with several service vans and each van counts as a location to arrive at “multiple locations”. OR…do you mean there are several shops (locations) and each has several service vans?

Rightman_Ind:

I would like to set it up so that when an item is added to the shop, it is also added to all of the other vans

I assume this means when a NEW item is added to the inventory list … not that an existing inventory has been replenished?

Regardless of your answers to the above, I would recommend starting your data design to mimic the real world objects. Create the following tables/sheets (some suggested columns but you may not need/want all of them):

  • Products - Columns = product ID’s, Part #, descriptions, specs, etc (NO inventory counts)
  • Locations - Columns = Location ID, Type i.e. (shop/van), Address, Van ID, etc
  • Inventory - Columns = Product, Location, Quantity on Hand, Quantity Allocated, Quantity Ordered, etc
  • any other supporting tables for you app following same idea

How you actually use the Inventory table depends on your app needs.

The inventory table described above is tracking counts by each Product and Location combination. If you deal with the inventory primarily by location, then this may be the way to go. You can filter by each location to see all its products or filter by a Product to see what each location has and using group aggregate functions to provide the Product totals.

On the other hand, if you deal with the inventory by Products mostly and occasionally look at how it is divided by location, then you may want to add a Product Inventory table that becomes the PARENT of the table described above. This provides inspection at a glance for each Product and then can drill into it see details at each location. The columns in this Parent table would be - Product, Total Quantity on Hand, Total Quantity Allocated, Total Quantity Ordered, etc - basically a sum across all the locations.

I hope this is not too vague to follow. Let us know it you have questions.

2 Likes
3

Hi, Thank you your response and advise.

To answer your questions:

WillowMobileSystems:

This part is not quite clear. Is it a single shop with several service vans and each van counts as a location to arrive at “multiple locations”. OR…do you mean there are several shops (locations) and each has several service vans?

We have one shop with 4 service vans, and each of them is treated as one location.

WillowMobileSystems:

I assume this means when a NEW item is added to the inventory list … not that an existing inventory has been replenished?

Yes, when a completely new item is added. I figured out how to do this one.

I have restructured the data since I posted this, I have formatted it into two tables/sheets as:
Inventory- columns=category, available, part #, location, (and a few other details)
location-columns= location

does having the third table, just the products listed themselves, help the with specific filtering? The idea for our app is that there is a tab at the bottom for each location, so the user can see what products are in that specific location. From what you have given I can see how that part is easy to set up using slices.

What I am still trying to figure out is how to reconcile one part being in several location, each with different amounts. Is that why you have a separate table for inventory, so that the parts are double listed there but the location column is different?

Then once that is set up, is there an action or workflow that can, with a press of a button, “move” a product form one location to another? Example: take an item from the shop and it goes onto a van.

I hope all of the questions made sense, thank you again for your help, it is very much appreciated.

4

Rightman_Ind:

does having the third table, just the products listed themselves, help the with specific filtering?

Actually, the main reason for having a separate Products table is three-fold:

  • It provides a single source from which you choose the Products to maintain consistency across your locations and avoid human error in the form of typos.

  • You only need to enter the Product and its related info once.

  • If you change some product attribute (e.g. Name or description) all places where it used will be automatically updated - that is if you access the Product via a Ref column which I highly recommend.

Rightman_Ind:

What I am still trying to figure out is how to reconcile one part being in several location, each with different amounts. Is that why you have a separate table for inventory, so that the parts are double listed there but the location column is different?

Yes, in some way you will need to pair the product and the locations.

You might be asking “why can’t the Inventory table have a column for product and then separate columns for each location”. You can do this and many do. You can easily add a new product and update the columns for each location.

The problem with that approach is that its NOT extensible for new locations without app changes - meaning if a new van was added to the fleet, then app changes are required to add a new location column. This takes time and introduces risk if an error is introduced that takes down the app.

In the approaches I described above, Inventory is extensible for Products AND Locations. If another van is added to the business, simply add new rows for each product/new location combo. No major app changes needed as the app is already designed to handle the new rows added. Also note…you can create an automated process that adds all of the product/new location rows for you!

Rightman_Ind:

Then once that is set up, is there an action or workflow that can, with a press of a button, “move” a product form one location to another?

In short, Yes. Regardless of which approach you choose, you will need to implement this button. It will be more difficult with the row-based Inventory approach I described but not so much that it should cause you to avoid the row-based method altogether. The future benefits outweigh those concerns.

2 Likes
5

WillowMobileSystems:

You might be asking “why can’t the Inventory table have a column for product and then separate columns for each location”. You can do this and many do. You can easily add a new product and update the columns for each location.> > The problem with that approach is that its NOT extensible for new locations without app changes -

Ahhh, this makes a lot of sense, I had this exact thought. Thank you again for your help, this has helped immensely and has gotten me on track to creating something that we will like.

Happy Holidays.