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.
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.
Solved! Go to Solution.
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.