How to build a forecasting model using cascading drop downs

  • 16 March 2016
  • 6 replies

Badge +3

Hello Community,

I have been tasked to create a form to submit product forecasts to SharePoint. I am not sure if this is going to be doable and when testing I am unable to make it work so I could use some help. I have a basic form and a cascading drop down menu where a sales rep can select a product manufacture and then a product type, and then product. They enter in the qty they think they will sell next year and move on to the next product on the same form. I am trying to get each line of product to save that info with qty into a separate list that will aggregate the totals for all our sales reps. Is this at all possible? Using the Connected to Field doesn't work as each product they enter will need to be a single entry on a separate list.

Thanks for any help one this!

6 replies

Badge +4


There could be many ways to achieve this, but Here is what I would do:

1) Create two lists, one where the Sales Reps enter the info (lets call it Source List), and one where the information is stored for easy reporting ((lets call it Destination List).

2) On the Source List, create a repeating section with these fields: Sales Rep Name, product manufacture, product type, product and quantity. If I were doing this, I would tie each of these fields to look up from another list, but they could be regular choice fields as well.

3) On submit, run a workflow to parse the repeating section, and create an item for each row in the destination list. How to do this is described here: Create an item on a different list for each repeating section item

Once each line in the repeating section is created as an item, across all forms, then it should be easy for you to group, sort and report on that data.

Hope this helps!

Badge +3

Would I have to create a repeating section field for sales rep? Each form submitted will be a single sales rep so couldn't I just pull the sales rep from a non-repeating section field or would that be more complicated to do? I ask because there will be other fields like region and location that will need to be pulled into the Destination list.

Badge +4

Sorry my bad. You do not need to include Sales Rep in the repeating section. I just meant to include it as a field on the form itself, since you cannot always depend on the "Created by" field (what if somebody enters on behalf of the Sales Rep).

The only things that need to be in the repeating section are the product Information. Your form could look like this.



Badge +3

Ah ok, just wanted to be sure I didn't have to make the whole form a repeating section for it to work. I'll give this a try and let you know how it goes. thank you for the quick replies!

Badge +3

So if I had to have another column for sales dollars forecast and then total that for each form, would that be possible? I was asked to be able to pull up the exact sales dollars forecast for each sales rep which ideally should be one form but I can't figure out how to calculate the sub total on a repeating section.

Badge +4


You can use the Calculated Value control to come up with a total on each form submitted by the Sales Rep, and copy that total to the second list, just like other values.

Performing calculations with Nintex Forms using the calculated value control