Tuesday, April 21, 2009

SharePoint calculated column and jQuery Highlight row

Hi All,
Here I am writing new blog after long time I highly apologies to our all regular SharePointKings readers. I have explore what you can do by SharePoint calculated column and jQuery here is small practical stuff follow the instructions and do it yourself you will surely enjoy this work I am sure.
Sometimes you need to represent SharePoint List in terms of legend like in map. Say for example you have result list and you want to show those Student who got less than 35% marks Should Have “Red” Legend, those who got 100% marks with “Blue” Legend , those who got >65% with “Pink” Legend , those who got >70% with Yellow, marks > 60 with Green.

Follow the steps
1. Create Result SharePoint custom list.


2. Create column StudentName single line text.

3. Create Column Color with Calculated Column.


Copy and Paste following code in formula.
=IF((Marks*100)>=100,"#0000FF",IF((Marks*100)>=70,"#FFFF00",IF((Marks*100)>60,"#FF00FF",IF((Marks*100)>=35,"#00FF00","#FF0000"))))

4. Create another column called Display.


Copy and paste following formula
="<DIV style='border: 1px "&Color&" solid;background-color:"&Color&";color:#FFFFFF;'>"&Marks*100&"<DIV>"

5. Now your fields looks like this


6. Open new form and Add Student Name and Marks obtained by him.
7. Once you enter marks for student your SharePoint list looks like this


8. Edit Page

9. Add ContentEditor webpart.

10. Add Following Code in Source Editor

<script type="text/javascript">
if(typeof jQuery=="undefined"){
var jQPath="http://ajax.googleapis.com/ajax/libs/jquery/1.3.1/";
document.write("<script src='",jQPath,"jquery.min.js' type='text/javascript'><\/script>");
}
</script>

<script type="text/javascript">


$(document).ready(function(){
$(".ms-vb2:contains('<DIV')").each(function(){
var tempDIV = document.createElement ("DIV");
tempDIV.style.cursor = "pointer";
tempDIV.innerHTML = $(this).text();
$(this).text("");
$(this).append(tempDIV);

});
});
</script>

11. Click ok button
12. Now your result SharePoint list look like


Cool……………
It works now you can do many more things using jQuery and Calculated column.
For calculated column refer


do let us know your experience with this post.
Cheers,

36 comments:

Chandresh Pansuriya said...

Nice Article it works fine !! also known jquery from that..

Thanks Jayesh

happycats said...

hi - not directly related, but this involves customizing colors using calculated columns (I believe)

I'm trying to customize list/calendar to set font color based on "created by" field
hi - I want to customize a sharepoint calendar so the font color of a calendar item is triggered off of the "created by" name value of that item. So person x creates an entry and I have it set so that entry appears in red font, and person y creates an entry and it is set to appear in blue font, etc...

Ideas?

SharePoint Kings said...

need to check weather it will be possible or not.

JQuery works with all the object available from client side. so if in your calender if created by is available then it might be possible.

need to work on that.

Anonymous said...

This is excellent - thank you. We used it to disply a "traffic light" type item based upon another column. Used Wingdings, "l" which is a circle character, and changed the color based upon the value in the other field. Works perfectly.

crisraj said...

I would like to understand how you used it for traffic lights. ( i have been browsing lot of sites to do just that!)
Thanks in advance

crisraj said...

I would be interested, how you used the same to get the traffic lights.

Thanks

SharePoint Kings said...

crisraj,

for traffic light kind of scenario (first red then yellow then green and repeat it again) you have to use following method from java script.

window.setTimeout
and
window.setinterval

please check this link for difference

ksul said...

Very nice and useful method. What code is needed in the script to work with grouped views?

Thanks,

ksul

Sandy said...

I'm trying to apply background color to a column based on today's date,for ex. if the date displayed matches with todays date it should be green. Else no color, I'm not an expert on jscript but tried some changes, still no clues

SharePoint Kings said...

ksul and sandy,

refer this link both your answers
http://www.sharepointkings.com/2008/12/highlight-sharepoint-list-rows.html

Anonymous said...

the formula of calculating Display field is nt taken it shows the eroor please solve this problem

SharePoint Kings said...

What's the error.

Unknown said...

Hi,

the solution was great and helped get rid of dataview solutions (which take lot of time to load) to just have the link on another field other tan the title column.

one issue i face, as a read permissions user group, the user sees the HTMLcode instead of the desired formated view. please clarify.

regards,
Swami

SharePoint Kings said...

Swaminathan,

rendering HTML should not related to User permission. Please check again.

if it is still there then we have to check.

Anonymous said...

Can you please modify the code for collapsed groups views?

Nir said...

I have the same problem with collapsed groups. It only work when I make the default to be in open mode. I think it's a problem of loading the JQuery.

SharePoint Kings said...

Nir and all,

in group view, if you check the view source, there are only header is rendering.

on "+" click of the group sharepoint make async call (ajax call) and fetch detail so the items inside the group is not available in html ( view source) and hence we are not getting object in javascript/jquery and we can not change the color.

Reggie said...

Excellent work my friend. I have scores that displays 0-5 decimals behind the number. How do I format to make it only have 2 decimals. I have tried to create another column and with fixed decimals, but nothing is working. I have sharepoint 3.0 if that helps.

SharePoint Kings said...

Reggie,

we think once you assigning the innerHTML to tempdiv
"tempDIV.innerHTML = $(this).text();"

you will get the value in tempDiv object then you can convert it to which ever mean you want i.e. you can convert in numeric/decimal/text in JavaScript remove the decimal or remove last few char if string so it will show you what you want.

try it out this may work.

Anonymous said...

Is there any way of doing this in SharePoint 2010? I followed the directions and received a popup stating "Warning: The HTML source you entered might have been modified" and the div tag still appears in the column.
Thanks.

SharePoint Kings said...

Anonymous,

it should worked in 2010 also,
we checked it and its working, just check with your view source and change your javascript accordingly.

Anonymous said...

This is great information and works perfectly with normal lists. However, i still can't get this to work with grouped listings, and I already tried using the code from here with still no luck (it doesn't color in grouped listing either) :

http://www.sharepointkings.com/2008/12/highlight-sharepoint-list-rows.html

Any suggestions to get this to work with grouped lists?

thanks,
- Isengero

SharePoint Kings said...

Isengero,

ya this is because jquery works if data renders on browsers.

but if you check view source of the grouped one its not rendered.

Thats the only reason its not working.

hope you can understand the problem.

Nilios said...

Great article - helped out a lot about something I have been searching for.

Wanted to know - I am trying to do the same thing but not using a DIV rather I need a calculated value to appear in a hyperlink.

So my calculated value would be:
="Click Here"

This of course only displays exactly what is written above and not just the Click here hyperlink.

How do I use your jquery code in this case?

SharePoint Kings said...

Nilios,

we used div to find our specific div inside each cell, and use below way

.ms-vb2:contains('<DIV')

but you can use something else like &lta or click here text etc

we suggest first add what you want then check view source html how it renders and then make a way to modify.

Nilios said...

Thanks. I tried it out and it is not doing exactly what I needed it to do. Basically it might be easier to explain the whole thing:

I am using the [Created] value as a pointer to a specific excel file to be included on the hyperlink from SharePoint to that Excel file.

I have one column called Key which is simply the =[Created] value

What I want to do is have a field with some HTML in it. So:

New field name
="<a href='www.sharepointsite.com/excelFile.xls?pointer=[Key]><img src="image.jpg"></a>"

I guess I really didn't need the Key and just use the created value but what I was trying to do display the info, read the value in Key and then use that value for the calculated field in the hyperlink above in another field.

Not sure if that makes sense.

Nilios said...

One more thing to add to my last post. My thought was just to have a value in a field and replace it using javascript with the <a... in the code. I might be making it more complicated than it really needs to be.

SharePoint Kings said...

Nilios,
your requirement is similar to what the article has explained, but you have to change script and placeholder.

you can follow below steps.
a. first apply some identifier/placeholder to you column

b. check how it renders

c. find pattern to identify you cell

d. replace your things--do your stuff

hope this helps.

scot said...

"SharePoint calculated column and jQuery Highlight row"
- >> is this exaple works for only sharepoint 2007? i tried this example in sharepoint 2010, did not see the results the way how i got it from sharepoint 2007( i tried same thing in shapoint 2007, it did display exactly the way you showed.) Thanks for Your reply

SharePoint Kings said...

scot, it works in SP2010 as well

just check javascript/jquery we used we used class ".ms-vb2" which default applied in SharePoint 2007 just check is it same in SP2010 or not if not just use the class that SP2010 had used [you can use developer tool or view source to find]

hope it worked for you

Anonymous said...

I am trying to modify the default view to add a Color column to my SharePoint list, works great, except when I modify the AllItems.aspx and add a CEWP to call the jQuery script, SharePoint is removing all webparts from the page (listview, CEWP). How do I get it to stop removing my web parts?

Thanks!

SharePoint Kings said...

@Anonymous, we tried on content editor webpart on allitem.aspx but was not facing this problem.

just check your allitem.aspx have proper column available.

it should work then.

Sabin said...

Is it possible to add icons as a background or traffic lights instead of html color. If yes can you please explain how to modify

Thank You

SharePoint Kings said...

sabin,
you might be looking s=similar to this http://www.sharepointkings.com/2010/04/sharepoint-custom-so-called-kpi.html

Anonymous said...

Great post I finally got it to work! My colour boxes are only outlined how do I fill the box with this colour???

Thanks again

Bharath Kancharla said...

Hi

I wish to get the data about weather from yahoo or some other site and store in a sql server database. This has to be done using webpart. If I click on the webpart by selecting ZIP Code, it should display weather report for that Location and has to store in sql server database in backgroud.
I am new to sharepoint. Dont know from where I have to start. Can anyone guide me?




Share your SharePoint Experiences with us...
As good as the SharePointKings is, we want to make it even better. One of our most valuable sources of input for our Blog Posts comes from ever enthusiastic Visitors/Readers. We welcome every Visitor/Reader to contribute their experiences with SharePoint. It may be in the form of a code stub, snippet, any tips and trick or any crazy thing you have tried with SharePoint.
Send your Articles to sharepointkings@gmail.com with your Profile Summary. We will Post them. The idea is to act as a bridge between you Readers!!!

If anyone would like to have their advertisement posted on this blog, please send us the requirement details to sharepointkings@gmail.com