Hello, I have one table for inventory with several different items. By column, there are items, quantity, category, and program. Category and program are selectable from a drop-down list.
I also have a form for checking these items in and out. On the form, the category, program, and who checked out the item are selectable. However, the item and quantity are not. How do I get the quantity to update on the table when someone fills out the form?
Thank you!
If I'm understanding your set up correctly, you could do this with a script or with a view and an additional table. The simplest, but also easiest to break, would be a view and a new table. I couldn't give too many details on the script without understanding more.
The form would reside in the new table and the items filled out would be the category/program linked (not sure in this case), a selection for "check-in"/"check-out" , and a quantity. There would be another column for calculating; have the check-in equal 1 and the check-out equal -1. The calculating table then multiplies the check-in/check-out value by the quantity.
Now for the view...the view simply shows the unique category/programs and a rollup from the new table. The way it can "break" is if users select check-in when they should have done the opposite (or vice-versa).
Hopefully that helps. Apologies if I'm misunderstanding your needs.
You're going to need a new "Movements" table where your form will create new records, and from there you can create rollup fields to sum up your inventory quantity. Here's an example to get you started
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.