Complex due days calculation and display

Sep 30, 2010 at 7:37 PM

I am sorry that this is going to be a somewhat long entry, but I wanted to state a few things that I have done and then go from there. I have been working on a site definition for a site that tracks commitments. One of the major requirements is that the due dates for the commitments are visible in the list views and color coded based on certain criteria. Also the list will have versioning turned on and as you will see in a moment this has been a major problem for this solution. Ordinarily a simple calculated column would work for this or at the very least some simple javascript. The problem came in when it was "decided" to make the due days calculation a "working days" calculation that will not count weekends or holidays that are added to a calendar. This really made it a lot more difficult to use client side calculations (at least for me!). My first and admittedly awesome (tooting my own horn here!) solution was that I created a timer job that calculated the working days left on open commitments and updated a working days field in the list. There were 2 calculated columns that used this information to along with some client side script to color code the list views as requested. Voila! It worked, but here is the problem. Because versioning had to be enabled, this means that every day the timer job runs each commitment will be edited and then have a new version! So now again, I will have to try to find another way to do this. So I have turned yet again to SPServices as I have many times before (tooting Marc's horn on this one!) to see what I can do to leave versioning turned on but also meet the requirements for displaying the working days and the proper colors. I believe that I can code this just fine, but there are 3 things that I need help or verification on.

1: Is it possible in javascript to check if dates fall on the weekend? (I think that there is, but it escapes me right now)

2: Can I do this through a loop that has to determine what commitments are being displayed and then show this next to that commitment in the correct color?

3: Will this be a slow process if there are 50 or more commitments on the screen? 

I know that I could write most of this and I see it working sort of in my head, but those are the main questions I have before I move on. I know that there may be another way, but I am trying to keep this inside the standard list views if possible.

Coordinator
Oct 1, 2010 at 12:58 AM

Thanks for the toot! ;+)

1: Is it possible in javascript to check if dates fall on the weekend? (I think that there is, but it escapes me right now)
As you know, date arithmetic is difficult. There's nothing available in JavaScript itself that I know about, but there may be plugins. The issue is that "working days" can mean many different things to many different people.

2: Can I do this through a loop that has to determine what commitments are being displayed and then show this next to that commitment in the correct color?
I'd plan to do the "coloring" with CSS rather than with calculated columns. (That's just the way I'd go at it. Christophe's calculated column formatting stuff is excellent, too.) You could set the CSS in a DVWP or with script. I'd avoid the timer job thing because every time you "touch" an item, you're going to create a new version, as you've noticed. Using the Web Services would be no exception.

3: Will this be a slow process if there are 50 or more commitments on the screen?
Not sure I understand what these commitments are, but client-side script is primarily throttled by the browser and the available resources. "50" in itself doesn't matter, but what you are doing with the "50". 

Hope this helps...

M.

Oct 1, 2010 at 11:15 AM

Yes, I was actually using Christophes calculated column method in my current solution! 

I thought more about it and then I thought that maybe I could combine the all the .Net date calculations into a separate page and then loop through the commitments on the screen and use the webservices to check the calendar to remove any holidays from the totals. Then use CSS color coding and it just might work. The number 50 was just a guess as there could be 1 to several hundred commitments on the screen inside a scrolling div. I really do not see this being "fast" per se, but it will not affect versioning. At this point it will really be up to the customer as to which method they will prefer. I will post back with whatever I come up with.

Oct 6, 2010 at 12:51 PM

I was able to actually do the required functionality to make this calculation work without affecting versioning. It does not use SPServices in this case, but it seemed cool enough to post my solution in case someone else might need a reference point. What I did first was to move most of what I had already accomplished in the timer job to a .Net aspx page that I keep in a document library. This page accepts an id on the querystring and then calculates the working days due or late and creates an html div tag that is also formatted for the correct color. On the list itself, I had added a computed field to the xml of the content type and schema that also created a div that has its id set to "dstatus_id" where id is the id of the list item. On the list view, in my document.ready event, I loop through all the divs on the page and check the id of the div. If it contains "dstatus" I then use an ajax call to the aspx page with the id and it returns that formatted div tag that I insert into the div on the page. This is working great so far and I am limiting the page size to 25 list items per page so there is no visible performance drop. I have already duplicated this functionality to do some other things as well.