Days Remaining Before Due Date

[callison]

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 

[cassymfreeman]

Hi ‌

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).

[callison]

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!

[cgb]

I agree with the site workflow approach - I have several that are doing what Cody has described.

[cassymfreeman]

can you get rid of the calculated column all together and just use a site workflow to work out when the due date is reached on items?  ‌ might give you some ideas of what you can achieve - let me know if you need any help?

[callison · Accepted solution]

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