I was asked to come up with a way to show how long a discrepancy went uncorrected once the department was notified.
This was on an inspection list (named Field Audit List).
These are the columns I used in the SITE workflow:
Status (Choice: corrected and needs correction as choices)
Audit Date (Date)
Submitted to Dept (Date)
Date Corrected (Date)
Days Outstanding (Number)
There are a few more columns, but these are the ones I used in the workflow. I scheduled the SITE workflow to run daily. Each day the Days Outstanding is updated with the number of days since the item was submitted to the Department with no update in status of Corrected. This is a filtered view of only those still needing correction.
In my scenario, I used Nintex to loop through the list and each item to calculate the number of days from the date it was submitted to the department to the current date without correction.
The workflow variables and types are:
The name of my list is Field Audit List. It made sense to me when I set it up.
Here are the steps included in the workflow and how I configured them.
Query List: FA_List is from the variables I set up (collection type)
For Each: FA_ListID is from the variables I set up (list item ID type)
Set Variable: Set the DateSubmitted (date variable) the same as the Submitted to Dept (date column from list) when the ID equals the Workflow Data FA_ListID. This way they correlate to each other.
Build String: Click in the box, then click Insert Reference.
Click on Inline Functions, double click DateDiffDays
Put your cursor between the (), then click on Workflow Variables - double click DateSubmitted to add it.
Put your cursor between the }), add a comma (,)
Click on Common - double click on Current Date
The formula should look like:
Insert Reference
Set Variable: Since my Days Outstanding is a number column I set the FA_Number (number) equal to the FA_DateDiff (text) so the number would display correctly. I believe you could save this step by changing your column to text and it may still work appropriately. I haven't tested it this way though.
Update Item: Now I update the Days outstanding with the FA_Number.
Now save and publish your workflow and schedule it to run daily. I recommend setting it to run during non business hours as to not tie up your server, although it isn't too complex of a workflow.
I've attached the workflow as an example.
