Jump to content
  • 0
kgraham2121

Inventory/Materials List/Recording Physical Counts

Question

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

Share this post


Link to post
Share on other sites

3 answers to this question

Recommended Posts

  • 0

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

GridEditMode.PNG

ListView.PNG

DP_settings.PNG

Share this post


Link to post
Share on other sites
  • 0

@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.  

Share this post


Link to post
Share on other sites
  • 0

@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

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Guest
Answer this question...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...

×
×
  • Create New...