Stump the Panel Topic: Calculated challenge of the day- any brave souls up for it?

Stump the Panel Topic: Calculated challenge of the day- any brave souls up for it? http://www.endusersharepoint.com/STP/ SharePoint QA en Sat, 01 Aug 2009 02:34:46 +0000 Nelson on "Calculated challenge of the day- any brave souls up for it?" http://www.endusersharepoint.com/STP/topic/calculated-challenge-of-the-day-any-brave-souls-up-for-it/page/2#post-8787 Wed, 29 Jul 2009 12:15:03 +0000 Nelson 8787@http://www.endusersharepoint.com/STP/ Thanks Nancy -- That's a great start for me. jbhoward on "Calculated challenge of the day- any brave souls up for it?" http://www.endusersharepoint.com/STP/topic/calculated-challenge-of-the-day-any-brave-souls-up-for-it/page/2#post-8775 Wed, 29 Jul 2009 10:09:03 +0000 jbhoward 8775@http://www.endusersharepoint.com/STP/ Nancy, Please, if you have time, download the 30-day trial version of Camtasia and create a screencast of all you've implemented based on this thread. It would be a fantastic addition to the EUSP archives. Thanks! Blessings, Jim Bob NancyCentury on "Calculated challenge of the day- any brave souls up for it?" http://www.endusersharepoint.com/STP/topic/calculated-challenge-of-the-day-any-brave-souls-up-for-it/page/2#post-8774 Wed, 29 Jul 2009 10:02:32 +0000 NancyCentury 8774@http://www.endusersharepoint.com/STP/ Wow, did you read through all this? amazing! I did get images, and not only images- images with a specific mouseover message when the user hovers over the image, which reflects the status of the project in relation to the due date. Dessie is truly remarkable for what he did here. that cannot be overstated. As far as all my columns, fields etc.... I would have to work on that when I have a chunk of time. :-) Beyond this specific effort, which only tacked the singular issue of "how to get 'TODAY' (date value) dynamically available and leveragable by other fields/formulas", a lot more was involved in my list to get the users what they wanted. Briefly- the project list was created off the 'issue tracking' list type. I created a content type for that list which presents the user with 8 required fields to complete when adding a new project: -Title -Description -Line of business (choice field, site column) -Assigned to (allowing multiple users) -Priority -% complete ( a choice column forcing selections 0% (default), 10%, 25%, 50%, 75%,90%, 100%) -Due date -Notes (append-only text field) The goal was to reduce as much as possible the ability of the users to change critical fields. Therefore, there are MANY hidden columns (calculated and non-calculated types) to drive other values while disallowing user interaction. For example, "status" is hidden from the content type, defaulting to "in progress". The user can only update status by changing the % complete value. When the user finally changes % complete to 100%: - Status field changes to Complete - A hidden "end date" field populates with the current date - A hidden "actual days" field calculates number of days it took to complete (= [start date]-[end date]) - a hidden "completion result" field displays (text) either EARLY or LATE by comparing 'actual days' to 'target days' ('Target days =[due date]-[start date], another hidden value) - A WF runs which copies the item to a separate Archive list. There is also an associated document library w/ a lookup field to the project title from the project list. When the user clicks the project title, they wanted to see not only the project data but any associated documents as well. To accomplish this, I created a custom display form that includes a web part with the project document library in it, and a connection between that and the display form to only pull in the documents that share the same project title. I modified this in SPD to change it to an XSLT data view so that I could have the ability to display a message when no matching items were available. Below the doc lib web part is a CEWP that allows the user to upload a doc if they want. This gives them all the functionality they wanted in one place. I also had to place a CEWP on that custom dispform that provides instructions to all my Office 2003 users (a lot of them) to make sure they choose the correct "edit" drop-down command to open the docs correctly. (If they don't, they default to a read-only view in Wordwhen opened.) I used the "collapse web parts toggle " script in another CEWP on this dispform to give a cleaner view by allowing the user to open/close the Office 2003 instructional web part as needed. Plus a lot more. whew. Documenting all this may take a while! Nelson on "Calculated challenge of the day- any brave souls up for it?" http://www.endusersharepoint.com/STP/topic/calculated-challenge-of-the-day-any-brave-souls-up-for-it/page/2#post-8772 Wed, 29 Jul 2009 09:35:16 +0000 Nelson 8772@http://www.endusersharepoint.com/STP/ Hello Nancy, This was a valuable effort. I'm wanting to present this to my project management team - can you please tell me all the fields/columns you have on your list and if you were able to get the images instead of the dots. Thank You. NancyCentury on "Calculated challenge of the day- any brave souls up for it?" http://www.endusersharepoint.com/STP/topic/calculated-challenge-of-the-day-any-brave-souls-up-for-it/page/2#post-8751 Tue, 28 Jul 2009 14:16:20 +0000 NancyCentury 8751@http://www.endusersharepoint.com/STP/ I hope someone else can use it too! Dessie Lunsford on "Calculated challenge of the day- any brave souls up for it?" http://www.endusersharepoint.com/STP/topic/calculated-challenge-of-the-day-any-brave-souls-up-for-it/page/2#post-8750 Tue, 28 Jul 2009 14:11:05 +0000 Dessie Lunsford 8750@http://www.endusersharepoint.com/STP/ Awesome!! Glad to hear it works (it was a tough/fun one to figure out). - Dessie NancyCentury on "Calculated challenge of the day- any brave souls up for it?" http://www.endusersharepoint.com/STP/topic/calculated-challenge-of-the-day-any-brave-souls-up-for-it/page/2#post-8747 Tue, 28 Jul 2009 13:48:52 +0000 NancyCentury 8747@http://www.endusersharepoint.com/STP/ Adding: I decided it would be cool to have a tooltip message when a user hovered over the indicator icon that corresponded to the relationship of the due date to the current date. I amended the script as follows: ********** 'if(dateCalc > 0) //Due date is in the past { document.getElementById(divs[z].id).parentNode.innerHTML = "<img style='float:left;' src='/_layouts/images/kpidefault-2.gif'title='Due date has passed' />"; } else if(dateCalc == 0) //Due date is today { document.getElementById(divs[z].id).parentNode.innerHTML = "<img style='float:left;' src='/_layouts/images/kpidefault-2.gif'title='Due date is TODAY' />"; } else if(dateCalc == -1) //Due date is tomorrow { document.getElementById(divs[z].id).parentNode.innerHTML = "<img style='float:left;' src='/_layouts/images/kpidefault-1.gif'title='Due date is TOMORROW' />"; } else if(dateCalc == -2) //Due date is in 2 days { document.getElementById(divs[z].id).parentNode.innerHTML = "<img style='float:left;' src='/_layouts/images/kpidefault-1.gif'title='Due date is in 2 days' />"; } else if(dateCalc == -3) //Due date is in 3 days { document.getElementById(divs[z].id).parentNode.innerHTML = "<img style='float:left;' src='/_layouts/images/kpidefault-1.gif'title='Due date is in 3 days' />"; } else //Due date is in the future and is not critical (more than 3 days out) { document.getElementById(divs[z].id).parentNode.innerHTML = "<img style='float:left;' src='/_layouts/images/kpidefault-0.gif'title='Due date is more than 4 days away' />" ' ********** Thanks again for all your help. NancyCentury on "Calculated challenge of the day- any brave souls up for it?" http://www.endusersharepoint.com/STP/topic/calculated-challenge-of-the-day-any-brave-souls-up-for-it/page/2#post-8733 Tue, 28 Jul 2009 08:32:35 +0000 NancyCentury 8733@http://www.endusersharepoint.com/STP/ Dessie, Those corrections look like a winner! I am on my feet clapping for you! What I was surprised to find- was that once the "today's date" field was in place and displaying the correct current date value, I then could remove that column from the view and the indicator still worked! This was an unexpected bonus since I might not always want that date value to be taking up list space. Thanks so much!! :-) Dessie Lunsford on "Calculated challenge of the day- any brave souls up for it?" http://www.endusersharepoint.com/STP/topic/calculated-challenge-of-the-day-any-brave-souls-up-for-it/page/2#post-8702 Mon, 27 Jul 2009 11:53:41 +0000 Dessie Lunsford 8702@http://www.endusersharepoint.com/STP/ Nancy, I too would like for a bullet-proof way to include "Today" within a calculated column without the need for hacks, scripts, workflows, or coded solutions (I've got my request in to be included in the SharePoint 2010 Beta...so we'll see if they decided to include this finally). For the usage of images rather than the bullet graphic in the resulting display, replace the "to be rendered" div html to an image instead: Current: <code> "<DIV style='font-weight:bold; font-size:24px; color:red;'>•</DIV>"; </code> Replace with: <code> "<IMG src='/_layouts/images/kpidefault-0.gif' />" </code> Replace each of the corresponding colors with their equivalent kpi graphic. Since we will already know the value to be displayed (calculated through the script), all we have to do is tell it to use images instead of a bullet. For the problem with the empty fields when using dates in the range of 1-9 (this will occur on any month), that was my bad. For some reason on the version of the script I posted last time, I didnt include the section to test for days less than "10" (internal dates will always have a 2-digit month and 2-digit day). Since this wasn't included, the displayed date will always evaluate to false when comparing it to the internal date. The fix for this is below: <code> <script type="text/javascript"> //--Christophe's Render HTML script--> var theTDs = document.getElementsByTagName("TD"); var i=0; var TDContent = " "; while (i < theTDs.length) { try { TDContent = theTDs[i].innerText || theTDs[i].textContent; if ((TDContent.indexOf("<DIV") == 0) && (TDContent.indexOf("</DIV>") >= 0)) { theTDs[i].innerHTML = TDContent; } } catch(err){} i=i+1; } //--> //--Working with Today's Date--> var d = new Date(); //Today's date (current date) var todaysDate = parseInt(d.getMonth()+1).toString()+"/"+d.getDate().toString()+"/"+d.getFullYear().toString(); var tbls = document.getElementsByTagName('table'); //All tables for(var i=0; i<tbls.length; i++) //Loop through all tables { if(tbls[i].id != "") //Only look at tables with ID's { if(tbls[i].getAttribute('summary') != "") //The table we want will have a "Summary" attribute { var nobrs = tbls[i].getElementsByTagName('nobr'); //All Date fields (Due Date column) in our table for(var x=0; x<nobrs.length; x++) //Loop through date fields { var divs = tbls[i].getElementsByTagName('div'); //All divs in our table for(var z=0; z<divs.length; z++) //Loop through all divs { /****Section that formats date as "mm/dd/yyyy"********/ var nobDate = new Date(nobrs[x].innerText || nobrs[x].textContent); var mm = nobDate.getMonth()+1; if(mm<10) { mm="0"+mm; } var dd = nobDate.getDate(); if(dd<10) { dd="0"+dd; } var yyyy=nobDate.getFullYear(); var dueDate = mm + "/" + dd + "/" + yyyy; /*****************************************************/ //Compare "Due Date" value with DIV "Class" name (looking at "Today's Date" column) if(dueDate+"Today" == divs[z].className) { //Set DIV value to current date divs[z].innerText = todaysDate; divs[z].textContent = todaysDate; } //Compare "DIV" id with "Due Date" column (this will be the "Indicator" column) else if(divs[z].id == dueDate) { var tDate = new Date(todaysDate); //Current Date var temp = nobrs[x].innerText || nobrs[x].textContent; var dDate = new Date(temp); //Due Date var one_day=1000*60*60*24 //Millisecond value for date comparison var dateCalc = Math.ceil((tDate.getTime() - dDate.getTime())/one_day); // date difference if(dateCalc > 0) //Due date is in the past { document.getElementById(divs[z].id).parentNode.innerHTML = "<DIV style='font-weight:bold; font-size:24px; color:red;'>•</DIV>"; } else if(dateCalc == 0) //Due date is today { document.getElementById(divs[z].id).parentNode.innerHTML = "<DIV style='font-weight:bold; font-size:24px; color:red;'>•</DIV>"; } else if(dateCalc == -1) //Due date is tomorrow { document.getElementById(divs[z].id).parentNode.innerHTML = "<DIV style='font-weight:bold; font-size:24px; color:yellow;'>•</DIV>"; } else if(dateCalc == -2) //Due date is in 2 days { document.getElementById(divs[z].id).parentNode.innerHTML = "<DIV style='font-weight:bold; font-size:24px; color:yellow;'>•</DIV>"; } else if(dateCalc == -3) //Due date is in 3 days { document.getElementById(divs[z].id).parentNode.innerHTML = "<DIV style='font-weight:bold; font-size:24px; color:yellow;'>•</DIV>"; } else //Due date is in the future and is not critical (more than 3 days out) { document.getElementById(divs[z].id).parentNode.innerHTML = "<DIV style='font-weight:bold; font-size:24px; color:green;'>•</DIV>"; } } } } } } } </script> </code> Try this version and see if it fixes the problem (it should). - Dessie (**Edited to include section for cross-browser support - forgot it again.) NancyCentury on "Calculated challenge of the day- any brave souls up for it?" http://www.endusersharepoint.com/STP/topic/calculated-challenge-of-the-day-any-brave-souls-up-for-it/page/2#post-8694 Sat, 25 Jul 2009 09:14:05 +0000 NancyCentury 8694@http://www.endusersharepoint.com/STP/ Dessie- I wanted to add that even on my test list, this error appeared. It seemed to be specific for certain due dates. The due dates that "failed' on my live Project List were the same due dates that 'failed' on the test list. Perhaps it's a script issue, or something else tield to the calculations? If the script looks at due date and sets the "Today" value off that, and someting is wrong with that relationship, would it therefore follow that the 'today's date' and 'indicator' columns would result in blanks? Due dates in the 8/1/09 - 8/9/09 range failed, FYI; also a due date in the 9/4/09 area IIRC. NancyCentury on "Calculated challenge of the day- any brave souls up for it?" http://www.endusersharepoint.com/STP/topic/calculated-challenge-of-the-day-any-brave-souls-up-for-it/page/2#post-8689 Fri, 24 Jul 2009 17:44:07 +0000 NancyCentury 8689@http://www.endusersharepoint.com/STP/ I could not resolve why certain projects did not display the Today's Date value or an indicator; I reverted back to my original format to avoid the wrath of my users. NancyCentury on "Calculated challenge of the day- any brave souls up for it?" http://www.endusersharepoint.com/STP/topic/calculated-challenge-of-the-day-any-brave-souls-up-for-it/page/2#post-8688 Fri, 24 Jul 2009 17:22:29 +0000 NancyCentury 8688@http://www.endusersharepoint.com/STP/ It's odd- this works on my actual project list, but for some reason, certain porjects have no values in the Today's date column and in the Indicator column. I have no idea why these would be blank!I deleted the projects an re-added them, and got the same result. :-( NancyCentury on "Calculated challenge of the day- any brave souls up for it?" http://www.endusersharepoint.com/STP/topic/calculated-challenge-of-the-day-any-brave-souls-up-for-it#post-8682 Fri, 24 Jul 2009 16:39:38 +0000 NancyCentury 8682@http://www.endusersharepoint.com/STP/ Your solution worked perfectly with my test list/test page. Some thoughts: In my original list, I needed calcuated columns to calculate the dates that constitute the "critical" period (1-3 days prior to the due date) as columns in the list itself, then have other calculated columns evaluate those values and reflect their values via formulas and workflows. Your solution only requires one date field- due date- and the script present in the page does all the work of evaluating the "critical period" and presenting the indicator accordingly, as long as the due date field, the indicator column and the script are present in any page or list view of my Project List. I don't need to worry about the 3 "critical date" calculated columns, the "timing" calculated column, or my "indicator" calculated column... or the need to ensure that "today" as an actual date value is present. I also will not need any workflows to set variables or pause til a particular date. While it doesn't solve the one issue- how to get TODAY as an actual date value into the list (which I still really want to be able to do)... it IS a solution that is a more than acceptable trade-off to the need to visit my list every date and manually change "today" to the current date, which is what I have been doing. I only have one further request.... Depending on the value present in the Timer column, my original Indicator formula returned the traditional KPI-style indicators (green circle, red diamond, yellow triangle): = "<img style='float:left;' src='/_layouts/images/KPIDefault-"&(3-RIGHT(LEFT(Timer,2),1))&".gif'/>; Is there any way to incorporate that indicator style into your script vs. the basic 24pt colored dot? THANK YOU!!! NancyCentury on "Calculated challenge of the day- any brave souls up for it?" http://www.endusersharepoint.com/STP/topic/calculated-challenge-of-the-day-any-brave-souls-up-for-it#post-8593 Tue, 21 Jul 2009 15:57:42 +0000 NancyCentury 8593@http://www.endusersharepoint.com/STP/ I plan to tackle this tomorrow- thank you so much for such a lot of help.!!! Dessie Lunsford on "Calculated challenge of the day- any brave souls up for it?" http://www.endusersharepoint.com/STP/topic/calculated-challenge-of-the-day-any-brave-souls-up-for-it#post-8545 Mon, 20 Jul 2009 13:09:46 +0000 Dessie Lunsford 8545@http://www.endusersharepoint.com/STP/ Nancy, Ok, I have a working model that seems to do what you're looking for. You'll have to adapt it for your list, but as a test to see how it works you can do the following: Create a new custom list (this is just to test the functionality). On the list, create the following three columns: 1. "Due Date" Date and Time column set for "Date only". 2. "Today's Date" Calculated column with a return type of "Single line of text". Enter in the formula: <code> ="<DIV class='"&TEXT([Due Date],"MM/DD/YYYY")&"Today'> </DIV>" </code> 3. "Indicator" Calculated column with a return type of "Single line of text". Enter in the formula: <code> ="<DIV id='"&TEXT([Due Date],"MM/DD/YYYY")&"'> </DIV>" </code> Next, add a CEWP to the default list page and add in the following script: <code> <script type="text/javascript"> //--Christophe's Render HTML script--> var theTDs = document.getElementsByTagName("TD"); var i=0; var TDContent = " "; while (i < theTDs.length) { try { TDContent = theTDs[i].innerText || theTDs[i].textContent; if ((TDContent.indexOf("<DIV") == 0) && (TDContent.indexOf("</DIV>") >= 0)) { theTDs[i].innerHTML = TDContent; } } catch(err){} i=i+1; } //--> //--Working with Today's Date--> var d = new Date(); //Today's date (current date) var todaysDate = parseInt(d.getMonth()+1).toString()+"/"+d.getDate().toString()+"/"+d.getFullYear().toString(); var tbls = document.getElementsByTagName('table'); //All tables for(var i=0; i<tbls.length; i++) //Loop through all tables { if(tbls[i].id != "") //Only look at tables with ID's { if(tbls[i].getAttribute('summary') != "") //The table we want will have a "Summary" attribute { var nobrs = tbls[i].getElementsByTagName('nobr'); //All Date fields (Due Date column) in our table for(var x=0; x<nobrs.length; x++) //Loop through date fields { var divs = tbls[i].getElementsByTagName('div'); //All divs in our table for(var z=0; z<divs.length; z++) //Loop through all divs { //Compare "Due Date" value with DIV "Class" name (looking at "Today's Date" column) if("0"+nobrs[x].innerText+"Today" == divs[z].className || "0"+nobrs[x].textContent+"Today" == divs[z].className) { //Set DIV value to current date divs[z].innerText = todaysDate; divs[z].textContent = todaysDate; } //Compare "DIV" id with "Due Date" column (this will be the "Indicator" column) else if(divs[z].id == "0"+nobrs[x].innerText || divs[z].id == "0"+nobrs[x].textContent) { var tDate = new Date(todaysDate); //Current Date var temp = nobrs[x].innerText || nobrs[x].textContent; var dDate = new Date(temp); //Due Date //Logic to set 2-digit month with leading zero if needed var mVal=""; if((dDate.getMonth()+1)<10) { mVal="0"; } var dVal=""; if(dDate.getDate()<10) { dVal="0"; } var one_day=1000*60*60*24 //Millisecond value for date comparison var dateCalc = Math.ceil((tDate.getTime() - dDate.getTime())/one_day); // date difference if(dateCalc > 0) //Due date is in the past { document.getElementById(divs[z].id).parentNode.innerHTML = "<DIV style='font-weight:bold; font-size:24px; color:red;'>•</DIV>"; } else if(dateCalc == 0) //Due date is today { document.getElementById(divs[z].id).parentNode.innerHTML = "<DIV style='font-weight:bold; font-size:24px; color:red;'>•</DIV>"; } else if(dateCalc == -1) //Due date is tomorrow { document.getElementById(divs[z].id).parentNode.innerHTML = "<DIV style='font-weight:bold; font-size:24px; color:yellow;'>•</DIV>"; } else if(dateCalc == -2) //Due date is in 2 days { document.getElementById(divs[z].id).parentNode.innerHTML = "<DIV style='font-weight:bold; font-size:24px; color:yellow;'>•</DIV>"; } else if(dateCalc == -3) //Due date is in 3 days { document.getElementById(divs[z].id).parentNode.innerHTML = "<DIV style='font-weight:bold; font-size:24px; color:yellow;'>•</DIV>"; } else //Due date is in the future and is not critical (more than 3 days out) { document.getElementById(divs[z].id).parentNode.innerHTML = "<DIV style='font-weight:bold; font-size:24px; color:green;'>•</DIV>"; } } } } } } } </script> </code> I've included some comments in the script to help explain things, but in a essentially it performs the following tasks: 1. Christophe's "Render HTML" script is added to render the values of the calculated columns to the page. 2. Create a date variable equal to the current date (this will update each day automatically). 3. Find the table on the page that we want to work with. 4. Find all of the "Due Date" column values. 5. For each one, loop through the div tags and find the ones that have a matching "Class Name" (the class name will equal the "Due Date" value plus the literal text "Today"). 6. For each one found, update its (the DIV) value to today's date. 7. For each of the other divs (the ones that dont match the "Class Name" check), compare the div's "ID" to the "Due Date" value (they should match). 8. If they match, perform the date comparison logic and display the colored indicator based on the result. It does seem to work fine, but there are a couple caveats to how it works. Right now, it does need to have the "Today's Date" column visible on the page, but could be modified to skip this since the value is persistent throughout the code. Also, (and this is one of the concerns when moving your logic and calculations to JavaScript) since we're performing all of the comparison logic in the script itself, the values we arrive at wont really be able to be used with other calculated columns since its all being performed at the client-side instead of at the server-side (as I mentioned in a revious comment). Play with it some and let me know if this will work for your needs (I may/should write this up into a blog article if it does). - Dessie *Edited post to include cross-browser version of the script - pasted in wrong one initially. NancyCentury on "Calculated challenge of the day- any brave souls up for it?" http://www.endusersharepoint.com/STP/topic/calculated-challenge-of-the-day-any-brave-souls-up-for-it#post-8511 Thu, 16 Jul 2009 17:31:08 +0000 NancyCentury 8511@http://www.endusersharepoint.com/STP/ Thank you!!! Dessie Lunsford on "Calculated challenge of the day- any brave souls up for it?" http://www.endusersharepoint.com/STP/topic/calculated-challenge-of-the-day-any-brave-souls-up-for-it#post-8510 Thu, 16 Jul 2009 17:28:01 +0000 Dessie Lunsford 8510@http://www.endusersharepoint.com/STP/ I'm playing with another script that will (hopefully) get you what you need...I'll post it up once I get the bugs worked out. - Dessie NancyCentury on "Calculated challenge of the day- any brave souls up for it?" http://www.endusersharepoint.com/STP/topic/calculated-challenge-of-the-day-any-brave-souls-up-for-it#post-8500 Thu, 16 Jul 2009 15:30:31 +0000 NancyCentury 8500@http://www.endusersharepoint.com/STP/ I think I understand what you are saying... I just don't know how that would be rendered. I also wondered if it would be possible to add another calc. column whose default value would be the value of the Today2 column... but if the true value recognized by the system is the div tag tex, not the date itself, that still will not work. It's frustrating to have a way capture of the current date (at least "visually"), yet ot able to do anything with it. :-( Dessie Lunsford on "Calculated challenge of the day- any brave souls up for it?" http://www.endusersharepoint.com/STP/topic/calculated-challenge-of-the-day-any-brave-souls-up-for-it#post-8498 Thu, 16 Jul 2009 15:18:25 +0000 Dessie Lunsford 8498@http://www.endusersharepoint.com/STP/ Hmm...I think the problem lies in that the value being displayed for the "Today" date is just in the browser (due to manipulation of the field with the script), but the actual value of the column is still just the empty "DIV" tag, which is being used in the calcluations instead of the "rendered" date (the calculations are being accomplished in the backend using the "actual" value, not the "displayed" value represented in the front-end). So...the only "real" (as far as I can tell) to make this work, would be to move the calculations to the script as well, then just render out the final html displaying the colors. Does this sound like a workable approach to you as well? - Dessie NancyCentury on "Calculated challenge of the day- any brave souls up for it?" http://www.endusersharepoint.com/STP/topic/calculated-challenge-of-the-day-any-brave-souls-up-for-it#post-8490 Thu, 16 Jul 2009 12:48:13 +0000 NancyCentury 8490@http://www.endusersharepoint.com/STP/ Originally, the Timer column referenced the Today column. So I created a new column called Timer2 whose formula references Today2. The result of that calculation must then be picked up by a third calculated colun, called "Indicator," whose formula produces the corresponding colored shape. I changed this formula to reference Timer2 instead of Timer. ="<img style='float:left;' src='/_layouts/images/KPIDefault-"&(3-RIGHT(LEFT(Timer2,2),1))&".gif'/>" My result was all green dots, meaning the formula in Timer2 was producing the default result in every case. It seems like the actual date value being produced in Today2 (via the script) is not being recognized as a date within the list, in these other formulas? Dessie Lunsford on "Calculated challenge of the day- any brave souls up for it?" http://www.endusersharepoint.com/STP/topic/calculated-challenge-of-the-day-any-brave-souls-up-for-it#post-8487 Thu, 16 Jul 2009 11:56:40 +0000 Dessie Lunsford 8487@http://www.endusersharepoint.com/STP/ Nancy, Your best bet is to just create a new "test" column and enter in the same formula, but reference "today2" instead of "today" - this way you can see if it will work without breaking anything existing. Realistically, you shouldn't have to do anything other than just update the reference (the system is pretty good at recognizing if a string value is a date and using it as such in formulas), but the test column should indicate this for you. If you need to convert it to an actual date value, there are functions to do this for you that shouldn't be to difficult to implement (just "string formatting" stuff really). Give it a shot and post back with the results (this sounds like a really interesting project you're working on). - Dessie NancyCentury on "Calculated challenge of the day- any brave souls up for it?" http://www.endusersharepoint.com/STP/topic/calculated-challenge-of-the-day-any-brave-souls-up-for-it#post-8486 Thu, 16 Jul 2009 11:35:29 +0000 NancyCentury 8486@http://www.endusersharepoint.com/STP/ Worked perfectly. My column using this method is called "Today2". Now- I need to make sure I undrestandhow this will work in relation to my other calculated columns. Right now, my "Timer" column formula (the one that displays the indicator color for hte item,you helped me on it yesterday) is: =IF([Due Date]=Today,"(1) Late",IF(OR(Critical1=Today,Critical2=Today,Critical3=Today),"(2) Critical","(3) On Time")) The "today" value used in that formula comes from another date/time column that I am entering the current date into manually. How do I now use the new column's value in that formula? Can I just change each reference to "today" to "today2"? Is is a problem if the "today" column is a date/time type and "today2" is a lne of text? Dessie Lunsford on "Calculated challenge of the day- any brave souls up for it?" http://www.endusersharepoint.com/STP/topic/calculated-challenge-of-the-day-any-brave-souls-up-for-it#post-8485 Thu, 16 Jul 2009 10:55:18 +0000 Dessie Lunsford 8485@http://www.endusersharepoint.com/STP/ Nancy, The calculated column containing the "div" html is just a "Single line of text" column (nothing special) since we want the script to render out its contents as plain html (think of it as a "placeholder" for a date value which we'll write into it later using script). Both of the scripts can go in the same CEWP (both in the same "script" tag block if you'd like, I had just separated them at the time I wrote them out). Does this help? - Dessie NancyCentury on "Calculated challenge of the day- any brave souls up for it?" http://www.endusersharepoint.com/STP/topic/calculated-challenge-of-the-day-any-brave-souls-up-for-it#post-8482 Thu, 16 Jul 2009 07:48:51 +0000 NancyCentury 8482@http://www.endusersharepoint.com/STP/ I tried this and ran into some questions: Are the 2 pieces of code you provide intended to be used in 2 different CEWPs? I am not clear on that. Also, should the calculated column that contains ="<DIV id='Today'></DIV>" be a date/time field or a single line of text? Dessie Lunsford on "Calculated challenge of the day- any brave souls up for it?" http://www.endusersharepoint.com/STP/topic/calculated-challenge-of-the-day-any-brave-souls-up-for-it#post-8433 Tue, 14 Jul 2009 16:49:42 +0000 Dessie Lunsford 8433@http://www.endusersharepoint.com/STP/ Yes - you should see your calculated column show up in an SPD workflow. - Dessie