Form from one sheet populates another sheet
Answers
-
@Warren Labuschagne Hi Warren. I thought I'd share what I've come up with so far, in case it gives you some ideas for your own project. What I described in the early parts of this thread have been changed to accommodate my current solution. Hopefully you can follow along...
I still have a "Master" inventory list, but I've added a concatenated field for reasons that will become clear later. I needed to have multiple elements available to the user, so rather than have multiple drop-downs that are not linked and dependent upon one another, I concatenated those fields together into a single field.
For instance, my solution concatenates these fields together:
Inventory #
Item Description
Vendor Name
Vendor #
Unit of Measure
So rather than 5 separate drop-downs, I now have a new column with the above values concatenated together so that I can have a single drop-down that looks like this:
157 > Battery 9V > Duracell > 19283 > each
Then I have another sheet I call "Form Data", whose sole purpose is to store a copy of those concatenated values for the drop-down field in the form. The reason it's a separate sheet is because I'm using ddupdate.com to monitor my Master sheet for any changes in the concatenated field (such as when a new item is added to our inventory list), and ddupdate.com requires that the source data and destination data be on different sheets.
My forms are then driven off this Form Data sheet, and users can easily search the drop-down list by typing a few letters of the item, which filters the list of choices to only those items that contain the string being searched for.
When one of the forms is submitted (either to add or remove items from inventory), those items are added to the Form Data sheet (because this is where the form originates from, and Smartsheet doesn't let you send form data to a different sheet than the one the form is based on). But then I have a workflow rule that watches for any new items being added to the Form Data sheet, and immediately moves those rows over to yet another sheet I call "Transactions". This is where I track every item added or removed from inventory.
Finally, on my main Master sheet I have a formula that looks for a match on the concatenated value between the Master sheet and the Transactions sheet, and wherever it finds a match it sums up the total quantity added and subtracts the total quantity removed to give me an "Inventory on Hand" number.
To recap:
Master Inventory sheet - Contains all details on each inventory item, a concatenated field of all the values I want to appear in the form drop-down, and a formula that calculates the quantity on hand of each item based on the Transaction sheet.
Form Data sheet - Uses ddupdate.com to "watch" the Master Inventory sheet for any new concatenated values that don't already exist. When it finds one, it adds that concatenated value to the Form Data sheet so it can be used in the form drop-downs. When a form is submitted, the row is initially added to this sheet but then immediately moved to the Transactions sheet.
Transactions sheet - All items that are added/removed from inventory are logged here, capturing date, time, and employee name (based on their Smartsheet login).
Add Item form - A form for employees to add new items to the supply room. Contains only two fields: the concatenated item detail drop-down, and a Quantity field.
Remove Item form - A form for employees to remove items from the supply room. Contains only two fields: the concatenated item detail drop-down, and a Quantity field.
This all probably sounds more complicated than it is, and it did take me a while to work out how to make all the parts work together, but it seems to be doing exactly what I want so at this point I'm happy. 😃
Let me know if you have any questions!
Smartsheet Certified Product User
-
@Paul Grim This is great. I am working on building something very similar. Would you be willing to share an example of the sheets you built?
-
@Paul Newcome Hi Paul can you help me with something please
i have sheet which populated through forms function
i want to create another as a check list which i want to track who has been completing the forms
one of the fields in the forms is location, so in my check list im trying to do like a if + vlookup on the location column
if it appears in the sheet (populated by forms) i want to automate a yes result from the if function
any advice please
-
@Maz Uddin Are you able to provide some screenshots with manually entered data that shows exactly what you are wanting to accomplish?
