Having avoided workflow for many months, I finally had a project which required me to build a time sheet type application.
The idea was that the user would have a number of tick boxes and a time sheet record for each week.
I started off by creating the Time Sheet library with the appropriate columns, for each Yes/No column I created a mirror calculated column for storing a numerical value. The calculation basically said if checked then 1 else 0(actual statement is =IF(myField=TRUE,1,0).
To create a weekly total I created a workflow in designer that was fired on the update event.
This is where my major problems started, the first thing I noticed was that the work flow took ages to complete if it ever did, and when I clicked the item I either received a “Server Out of Memory” message or a “Some part of your SQL statement is nested too deeply” error message.
I traced all of my problems back to the Set Total to Variable:Total statement. Apparently, if within your workflow you update a field on the current item and you have the run workflow when item is updated ticked, the code will disappear into a never ending loop.
The only way I have found to stop this is to use a semaphore type approach. Basically I have a column on the form called update record. By default the column is set to No. If the user wishes to submit their record they have to tick the Submit Record box. Within the workflow I have coded a condition which says if Submit Record equals Yes then do the calculation, within this branch I then set the Submit Record back to No and then update the Total.
This does not stop the workflow from firing it will fire twice per update, but the 2nd time will do nothing as the condition will be false.
I will be looking at ways to do this via script on the edit form and will keep you posted.
