Skip to main content

Hello Community. I have a form with multiple calculations performed inside the form with the results being stored in SharePoint list columns. Inputs to the calculations are both user inputs, form static values, and calculations dependent on other calculations within the form. User inputs are also stored in SharePoint list columns.

The power users are allowed to input or change values in the list directly using a datasheet view as part of the business process for making bulk changes. Of course the calculation results fields in the list do not update as the form is not running.

Opening and resaving the form does read the new inputs, recalculate the results, and store the new results back in the list. But we're talking about many hundreds to many thousands of records in the list, so it isn't feasible to manually open, save, and close all the items when a bulk change is made directly to the list.

I originally thought that simply running a workflow to make an update to a field in the list would trigger the recalculations as though the form had been opened and closed, but that is not the case. It simply changes the values in the list.

I thought about changing the calculations to occur in SharePoint rather than the Nintex form, but there are business reasons that this is not an acceptable solution.

Does anyone have any ideas on how to imitate, or actually perform, an open, save, and close operation to a record via a workflow (preferred) or JavaScript? Or maybe some other way to force calculations that exist only in the form to be executed without opening the form?

I keep thinking that a WF to sequentially open, save and close all the items in a list should be relatively easy to accomplish, but so far I haven't found a way to do it.

i dont think that its possible via workflow to trigger the "open form, save form".



 



u could recalculate the values with a nintex workflow when the item is "edited".



 



wouldnt that be an option?

Thanks Aleximo, yes that would be an option but it is one we are trying to avoid. The form already has many complex calculations and dependencies that would represent a massive effort to rebuild in a workflow. The form itself would do all the calculations and updates if it were opened, but having to open several hundred or several thousand forms when a bulk change is needed isn't feasible, and simply making the bulk changes in a datasheet view doesn't trigger the recalculations that would take place if the form were open. It's one of those "Catch22" issues where there are no good options due to the current design.

Ultimately, my solution was to build an Excel workbook to do the recalculations. Then a workflow is built that gathers the required input fields and using Query Excel Services ships those inputs into the workbook where Excel does the heavy lifting of the recalculations, then QES pulls the results back to the workflow in a data collection. The workflow then parses all the returned data and updates it into the correct SharePoint list columns.



It was still a lot of workflow steps to collect the data, ship it to Excel, retrieve it back, parse it, and upload back to the list; but, still much simpler than trying to write all the complex calculations within the workflow itself.



If you have complex calculations to do in a workflow, then I suggest you take a look at the Query Excel Services feature.

Yes! I have the same problem and I’ve been searching for days on how to do this. My form (like yours) has fields that automatically populate with employee information when we enter an employee’s name. I just need to open and save each record to make them populate but I have hundreds of records. How can I design a workflow to “open and save” each item again? 

Yes! I have the same problem and I’ve been searching for days on how to do this. My form (like yours) has fields that automatically populate with employee information when we enter an employee’s name. I just need to open and save each record to make them populate but I have hundreds of records. How can I design a workflow to “open and save” each item again? 

 

There a simply limited options when faced with this problem.

 

  1. You mandate that people have to go into the form to change things, everything updates like it should, and the universe is at peace.
     
  2. You follow the solution above, export everything out to an excel sheet, have it do the calculations, and then push those values back out to SharePoint.
     
  3. The only other solution I could think of would be to create a workflow that runs whenever an item is changed. It takes the values that need to be put into a calculation, does the calculation with them, and then updates the field or fields of the item. Since there is no particular way to tell, as far as I know, if an item has been changed via a form or via a direct editing from the the list level, this workflow would essentially be doing duplicate work if someone *did* edit the form. Additionally, you’d have to keep the formulas between the Form and the Workflow in sync, otherwise everyone will be sad.

    To update All of the list items you could write yet another workflow that simply executed the calculation workflow on all existing items in the list (likely in batches depending on the number of list items), after which, you’d likely never have to run it again unless you find yourself in a similar situation in a different list (or with a different column / field!)

 

 

No matter what you feel is best for your org, I do recommend starting a new thread specifically for your needs as this thread is considered “answered” for the original question. This way you can get the most help and things don’t get lost in the halls of time.

 

 

Reply


View our Community Site Map

Nintex Community Sitemap
Terms & ConditionsCookie settings