Hello SS gurus,
I am relatively new to SS "in the field", although I've watched over 20 hours of videos from the SS Learning Center so I have a good understanding of the mechanics. Here is what I'm trying to do...
We need to track inventory in our supply room: items on hand, items removed, items added, etc. We would prefer that the end user indicate what they are removing from the supply room using a web form, rather than directly updating a sheet. Also, we would like them to indicate items that have been added to the supply room by using a separate web form. This will enable us to very clearly see a record for each time an item is added to or removed from the supply room.
I would like a "master sheet" that lists all the items we stock in our supply room, along with details such as vendor name, item number, minimum quantity needed on hand, etc. This is what would drive the two web forms (one for "removing" items and one for "adding" items).
Then I would like a "detail sheet" where a row is added each time an item is removed or added to the supply room. Remember there would be two web forms: one for removing items (where the quantity would be a negative number), and another for adding items (where the quantity would be a positive number). Each form would pull the item details from the "master sheet", but upon submitting the form they would send the new row to the "detail sheet" so that we had a record of every time something was removed or added to the supply room.
Finally, I would like a rolled-up "summary sheet" that summarizes the "detail sheet" by aggregating the additions and subtractions of each item to and from the supply room so that we could see what is the current quantity on hand of every item. I would like this to be a sheet and not a report because I want to have alerts triggered when the quantity on hand of each item drops below a certain level as indicated on the "master sheet".
Example - The "Master Sheet" indicates we carry large gloves and always need 5 pairs on hand:
Now imagine the "Addition" web form is submitted indicating that a shipment of 10 pairs has been received into the supply room, thus adding a new row to the "Detail Sheet" as follows:
Then another employee submits the "Removal" web form indicating they removed 3 pairs of large gloves, and a new row is added to the "Detail Sheet" as follows:
The "Summary Sheet" should roll these up into a Qty. on Hand record of "7" and display as follows:
If another employee then submits the "Removal" web form to indicate 5 more pairs of gloves have been taken, another record is added to the "Detail Sheet" as follows:
And now the "Summary Sheet" should reflect the new Qty. on Hand of "2" as follows:
Since the "Qty on Hand" is less than 5 (which is the "Min. Qty" indicated for this item in the "Master Sheet"), an alert should be triggered to notify someone to order more of this item.
Is this possible? Am I even going about this in the right way? If there's a better way to accomplish this I'm all ears! Thanks!
