Inventory/Materials List/Recording Physical Counts

[kgraham2121]

Hi,

I am creating an inventory management workflow.  Components:

• Materials List/Table
  • Contains ~200 SKUs/items that we order.  Unique identifier is Material number.  Also includes Qty/pack, Unit Price, Material Description, Expiration, etc...
• Consumption/Ordering Tool
  • Tabular Datapage that lists each item, # consumed in last X Days, and some calculations, based on consumption rates, how many would need to be ordered to replace what was consumed, for X number of Days (User Defined)

Final piece I'm having trouble figuring out is a simple way for user to count and document how many are still in stock - and then be able to factor that into calculation for how much to order.  At a high level - I envisioned a template/form/datapage that starts with the materials list, where user can add a Qty for each item, and the count would be date stamped and its history not lost.  Can't get my head around how to get a form to work - because they are designed to update single records - and the workflow requires entering a QTY for ~ 200 unique items.  Maybe its a seperate table - just don't know how to make the data entry easy, i.e. user doesn't have to select/add each item to count (like a shopping cart) - the list of items is there at beginning and they just fill in the blanks and then how to reference it and perform calculations based on the counts.  I have attached a smample of Materials table and Tabular Datapage layout for reference.

 

Thx in advance for any assistance...

 

 

Materials_2020-May-26_1541.zip Material Consumption Datapage.xlsx

[CraigSZ]

Hi @kgraham2121

I'm no Caspio expert but have you tried using a tabular report with Grid Edit enabled? I've been looking at these myself and they will allow capture in a spreadsheet type format but I have experience a number of shortcomings with the grid edit view...

1. The datapage does not automatically open in grid edit mode and the user currently has to manually click the "grid edit" hyperlink in top left corner

2. Formatting the grid edit view can be tricky as column widths reset on reopening

3. You cannot use grouping and groups will change to columns as Caspio is expecting data to be entered

You can allow Grid Edit by setting up a Tabular Report datapage and then setting Grid Edit flag on the Edit settings per screenshot.

I have attached two examples..

• The List View is the default that opens even if grid edit is checked. The user must click the Grid Edit link
  • You cannot edit in this mode. Checkboxes, data pickers etc will not work
• The Grid Edit view where you can enter in a spreadsheet view
  • Date pickers, checkboxes operate as expected

Hope you get sorted

Cheers

Craig

[kgraham2121]

@CraigSZ Thanks for the response.  The Grid Edit view is exactly the look and feel I am going for.  Where I am still a little stuck is - These physical counts are a recurring event.  Trying to determine best way to present the Grid method uf data entry, without having to overwrite every field each time, i.e. Count on Jan 1, then on Jan 8, count again.  Ideally user comes to datapage and it's a "Clean slate" each time.  Also the Materials list is dynamic - can have additions and deletions.  Want the data entry page to have all existing items.  I feel like the answer is simple - but I'm just stuck.

 

Thx again for the feedback.  

[CraigSZ]

@kgraham2121

One possible solution is to do the following...

• Create a StockCountMaster table (holds a record for each time the physical count is performed) - simple table & only needs a date field as a minimum
• Create a StockCountDetail table (holds a record for each item in the Materials List) - holds link to StockCountMaster + Material List item + Physical Count (will be set to 0)
• Create a basic Submit Form datapage to add a new record to the StockCountMaster table e.g. for a Physical Stock Count for 14th of January
• Create a Triggered Action on the StockCountMaster table (to run on Insert) that does the following...

1. Loops through the latest list per the Materials List table and
2. Inserts a StockCountDetail record (with a zero physical count as default) for each active item on the Material List

The Grid Edit datapage would then use the data in the StockCountDetail table to present the Material List for the physical count. You would also then have a history of each Physical Count.

Hope the above makes sense

Cheers

Craig

[kgraham2121]

On 6/1/2020 at 4:44 PM, CraigSZ said:

@kgraham2121

One possible solution is to do the following...

• Create a StockCountMaster table (holds a record for each time the physical count is performed) - simple table & only needs a date field as a minimum
• Create a StockCountDetail table (holds a record for each item in the Materials List) - holds link to StockCountMaster + Material List item + Physical Count (will be set to 0)
• Create a basic Submit Form datapage to add a new record to the StockCountMaster table e.g. for a Physical Stock Count for 14th of January
• Create a Triggered Action on the StockCountMaster table (to run on Insert) that does the following...

1. Loops through the latest list per the Materials List table and
2. Inserts a StockCountDetail record (with a zero physical count as default) for each active item on the Material List

The Grid Edit datapage would then use the data in the StockCountDetail table to present the Material List for the physical count. You would also then have a history of each Physical Count.

Hope the above makes sense

Cheers

Craig

Hi @CraigSZ,

Thx for this response - apologies for delay acknowledging...

Hoping you can answer a couple questions about this solution...

1.  In the StockCountDetail table - when you say link to StockCountMaster table - do you mean like a join/creating a view? I think understanding this point is key to me understanding solution.  I'm struggling creating a new record for each material item for any given count date -in a bulk way- thus creating the filter/view I can present in the Grid view.

2. I think I may have some questions about syntax for the looping in the trigger once I better understand #1

 

Once again - thank you so much for being willing to take the time to to understand, vizualize, and solution my challenge.  Any additional feedback you could share would be really great...Ken