I am building what should be a simple workflow to calculate the number days remaining before a due date.
Currently my setup is like this:
I have a calculated sharepoint column that gives a number of days remaining in before the due date.
I am using my List item workflow to update a number column with the same information that is in the calculated column.
I built a site workflow to run at midnight every day. Here is how the site workflow is setup:
I have a math operation that looks at the "days remaining" column
The math operation stores that result in a variable.
I have an update item following this math operation that updates the column with the stored variable.
I ran the workflow and it did work. BUT it only updated the first item in the list. It would not update any others.
Do I need to put in some sort of a loop to have it look at all of the items in the list? Any ideas?
Thank you,
Cody Allison
Solved! Go to Solution.
Yes you will need a loop to do the approach above - query list for all items and store ID in collection variable. Then for each (inside the loop) do what you've said above.
I think you could make this simpler though by having a site workflow that has one action in it - update multiple items. In the configuration update all items in list - set field required = list lookup - current item - calculated column value.
Does that make sense? If not I can mock something up for you either this morning or tomorrow morning (UK time).
Cassy,
The issue with SharePoint is this, the calculated columns do not update daily. They only update when a list item is edited. It is a known bug from what I have read on other forums.
If you could mock up a query/loop option, that would be greatly appreciated. Thank you!
I agree with the site workflow approach - I have several that are doing what Cody has described.
Cassy,
I tried the site workflow approach but I found something else that worked pretty well. I found someone else that wanted to achieve the same result that I am. They did so by writing a small amount of .js in the calculated column. Here is the code:
=IF(ISBLANK([Due Date]),"Missing Due date","<img src='/_layouts/images/blank.gif' onload=""{"&" var SPday=new Date();"&" SPday.setFullYear("&YEAR([Due Date])&","&MONTH([Due Date])-1&","&DAY([Due Date])&");"&" var Days=Math.round((SPday.getTime()-new Date().getTime())/86400000);"&" this.parentNode.innerHTML=Math.abs(Days)+' days '+((Days<0)?'past':'left');"&"}"">")
Note, the column "Due Date" was calculated by the workflow using the calculate date function. Then, the Column "Days Remaining" is the calculated column that this .js code is wrote in. I found this solution on Friday and tracked it over the weekend and found it did calculate the date correctly.
Thank you for your help!
Cody Allison