Inventory Multiple Locations

[sudos]

I'm learning Caspio, lots ot learn.  

I'm trying this out with an attempt at building an inventory system to track inventory across multiple locations.

My challenge is here, how do I table this correctly.  I have data tables separate for locations and part numbers.  Now I need to figure out how to in/out adjust appropraite inventories.  Do I need to do a specific data table by part # or by location#, or is there a way to run a 'current' inventory data table?  What I'm struggling with is how to is a 'admin' going to add/remove locations without needing the dev (me) to add or remove data tables.  I understand the 'admin' could add locations into the location data table but how is the location going to be updated by locaiton of each item.

I think I have the display figured out, I just can't wrap my head around the operations behind this.  In my current (excel) system, I use a index-sum where I vlookup the part number in a massive table that has data of where the part# came from and where it goes to, therefore having a calculation to add all the 'to's and subtract them from all the 'from's to get a current inventory at each location.

Thank you in advance!

[kpcollier]

OK. I apologize for the long post. Inventory apps are kinda complex. 

 

I'm thinking you need to add a third table. You say you have your Parts table and your Locations table, right? Now you need one to combine the two. 

In your new table, lets call it Stock_Inventory for now, you would need to add a few different fields. First, you need at least two integer fields for Parts and Locations table foreign keys. The IDs of the records from the Parts Table and Locations Table will go here. Make sure to go to Relationships and give a relationship from your Parts and Locations table ID field to the Stock_Inventory PartsID/LocationID fields. 

Then you need to add a number field to use as a Stock Level field, to denote how many of the part you have in stock. Self explanatory.

One part that is tricky is adding and removing from the stock level, and there is probably a better way than what I am going to try to explain, but this way definitely works. You will add 'Add_To_Stock' and 'Remove_From_Stock' fields to your new Stock_Inventory table as number fields. For this workflow, I created a Single Record Update form that is embedded into a link on my Stock Inventory Report via an HTML block. When you click on the link next to the desired Part, the link will send the ID parameter of the part from Stock_Inventory table to that form, that way we are editing the correct Part. Then we can use the 'Add_To_Stock' and 'Subtract_From_Stock' number fields in this form to edit the Stock Level. Then you need to use a trigger like the one below to either add/subtract from the Stock Level and to reset the Add_To_Stock and Subtract_From_Stock fields. 

 

Obviously, to populate this new table, you will need a submission form for it. In this form you will add the PartID and LocationID fields and have them as a dropdown or something with the datasource being a lookup to their respective tables. That way the dropdown for Parts shows only values from your Parts Table, likewise for Locations. Add the Stock Level field to the form to set the initial stock level. Now the users can use this form when part/location combo is not yet listed in the Stock Inventory table.

Now you'll run your stock inventory report on your new Stock_Inventory table, that will display the Part Number, the Location it is stored, and how many of it you have. Each line will have a link to the Single Record Update form, where you can either put in a value for Subtracting or for Adding to the stock. You can make the records in the report editable so that you can change the Location field via a dropdown from your Locations_Table. 

Then, for your other worries, you can create submission forms for your Parts Table and for your Locations Table, add these forms to the report as link somewhere (I use the header). Now the user can use those forms to add a Location to the Location Table or a Part to the Part Table. 

 

For some more helpful tools, you can add a 'Desired Stock Level' field for each part, and a 'Restock Level' formula field. This way you can take your current Stock Level and your Desired Stock Level and show as a percent how many you have in stock vs how many you want to be in stock. When this percentage gets low, say to 30%, you know it is about time to order some more.'

 

Some helpful links of things I talked about here:

Caspio Link Generator with Parameter Help

Triggered Actions Help

Database Relationships