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:
Nice Article it works fine !! also known jquery from that..
Thanks Jayesh
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?
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.
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.
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
I would be interested, how you used the same to get the traffic lights.
Thanks
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
Very nice and useful method. What code is needed in the script to work with grouped views?
Thanks,
ksul
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
ksul and sandy,
refer this link both your answers
http://www.sharepointkings.com/2008/12/highlight-sharepoint-list-rows.html
the formula of calculating Display field is nt taken it shows the eroor please solve this problem
What's the error.
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
Swaminathan,
rendering HTML should not related to User permission. Please check again.
if it is still there then we have to check.
Can you please modify the code for collapsed groups views?
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.
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.
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.
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.
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.
Anonymous,
it should worked in 2010 also,
we checked it and its working, just check with your view source and change your javascript accordingly.
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
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.
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?
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 <a 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.
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.
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.
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.
"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
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
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!
@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.
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
sabin,
you might be looking s=similar to this http://www.sharepointkings.com/2010/04/sharepoint-custom-so-called-kpi.html
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
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?
Post a Comment