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

NeilKY

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!

Paul Newcome

Can they be limited to the number of different items that can be entered for a single order? There are 100 items, but (for example) they can only select up to 10 different things in a single order (or some other manageable number)?

NeilKY

https://community.smartsheet.com/discussion/comment/464200#Comment_464200

Sure, we could probably convince them to stick to 10 unique products per order, without restricting the quantities.

Are you suggesting I create 10 duplicate dropdown list fields, and 10 matching quantity fields? That's something I hadn't considered! lol. I like that idea.

How would I go about turning those into separate rows? Which would be more reliable? DataMesh or formulas?

Paul Newcome

Here is a solution that can be used as the base idea for you. Basically you would limit it to 10 items (20 fields - one for item and one for qty ten times).

Then you would take the base idea in the thread below to parse each item and its qty out into separate rows.

https://community.smartsheet.com/discussion/119380/split-multiple-selections-in-single-form-into-individual-rows-core-product

NeilKY

https://community.smartsheet.com/discussion/comment/464220#Comment_464220

I appreciate the idea but if you look closely, I'm one of the main confused individuals on that post :)

Do you have a working sample data sheet you would be willing to share?

Paul Newcome

My apologies. I haven't been getting notifications from that thread for some reason. The below screenshot should help. Yellow columns are form fields. Then you use the INDEX formulas in the purple section to pull in the selections and qtys.

NeilKY

Actually, your explanation worked well. Got it! Thank you.