SharePoint 2007 and Javascript/JSQuery/SPServices - Loop through list and update each item

Question

please can you assist with the below. Any advice is appreciated. Many thanks.

Solution Contents

1. SharePoint 2007 List
2. SharePoint List Columns: CreatedDate, DueDate, DaysRemaining
3. Coding Script Language: Javascript/JQuery/SPServices?

Solution A SharePoint 2007 list that contains tasks and the days remaining to complete those tasks. The days remaining MUST NOT include public holidays or weekends. The DaysRemaining column must update when the page is loaded so correct value is displayed each day.

Solution Issue The best solution would be to use SharePoint calculated columns, which work great from my experience, however they do not exclude weekends for the calculation. So, I am planning on using JS to do the calculation of DaysRemaining on page load.

I require a way of looping through a SharePoint 2007 list, and then for each item in that list update the DaysRemaining column.

Could you please point me in the right direction?

I assume a for loop, and some sort of update function? I have successfully used the SPServices.SPMultipleUpdateItems but that updates a list column with the same value, not a different value for each list item.

Answer 1

I dont have too much experience past migrations in 2007 but I think you are going at this the wrong way.

why not just add to the formula of the calculated column.

if its an integer value you could do something like:

= (number of days) / 7 * 5

If you cant do it all in one column, make a second column. First calc column finds integer value of days. Second calc column does math operations using that first column.

Answer 2

I am not allowed to comment on your post because my reputation is not high enough.

So forgive me using the answer option for maybe a non-answer.

What happens in 2007 if you create a Calculated Column with formula:

="<B>Hello World!</B>"

AND set the datatype to Number

In 2010,2013 and Online it will output the correct Bold text, that means you can use JavaScript code in there as well

If 2007 displays the string as <B>Hello World!</B> you are out of luck and can't use this approach in 2007

FYI a DaysRemaing calculation relies on the current date; but TODAY is not available in Calculated Columns. Since Calculated Columns are only updated on Item update any use of TODAY will be 1 day off tomorrow, 2 days off the day after etc. That's why using JavaScript to do the calculation in a VIEW is a viable workaround.