Order form: Select Multiple Items and Different Quanties. Simple right? Nope.

I need HELP!

There is a department in our company that fulfills office supplies orders from other departments in our company.

They want a simple order form that people can use to order supplies, using Smartsheet. Simple, right? Not to me😊

They’re looking for the form to look something like this:

…that spits out something like this:

For this to work, the form would need to be able to:

1. Allow the user to select a product from a dropdown list
2. Select the quantity for that item.
3. After selecting, display the product list again along with another quantity for that amount.
4. Then after submitting the form it would duplicate the order number for the amount of unique items the user ordered, and place them all on individual rows, duplicating the Order Date, Order Number, Department, and Ordered By fields.

I can’t figure out how to create a form that will do that.

Instead, I tried another failed solution—creating a field for each item and that specific item’s quantity, then somehow use a process to duplicate the rows after the order.

The problem with this solution was that the actual list of items that can be ordered isn’t only 5 items, it’s almost 100 items. That means I would have to create 200 columns and form fields for the user would to scroll through on the form. And it would be VERY difficult for the Order Processing Department to update the product list if it ever changed.

The solution that sort of worked but they didn’t like:

We placed all the products in a dropdown list and offered ONE quantity field for that single item. The users were supposed to submit the form then hit the “Back” button before selecting another item and quantity and submitting again. This worked because it saved the user’s data when hitting “back”. But rightfully so, they did not like this solution because it’s not very intuitive for today’s average computer user. So, this solution is not an option.

Here are the Add-on Tools in our Enterprise Smartsheet Subscription we have at our disposal to make this work:

1. Data Shuttle
2. DataMesh
3. Dynamic View
4. Pivot
5. WorkApps

Google Products including Appsheet are not an option but Enterprise Microsoft products are.

The duplication of rows is something I do need help with eventually, but for now, the biggest question I’m facing now is, “How do I create a form that allows the user to select from a dropdown list and a quantity for multiple items, and select different quantities for each item?

Can anyone help me figure out a solution? I’m sure this would be a VERY frequently used post if we could lay it out step by step.

Help me all you Obi-wan Kenobis out there! You're my only hope!