?
Solved

IF OR statement in Sharepoint

Posted on 2014-01-13
8
Medium Priority
?
730 Views
Last Modified: 2016-03-02
Is there a Sharepoint Expert that can help me on this one.

I want to insert traffic lights if certain criterea is met. I have achieved this using single criterea where I set up a column called RAG and a column call Traffic Light, and depending what was in that cell RAG, i.e. R, A or G the Traffic Light will insert the appropriate 'picture' being Red Amber or Green.

What I am now trying to do is include a third dimention but cannot get this to work.

So. my query is ,IF(OR([Status]="WIP",[Status]="Yet to Start",IF([Target Date]<=TODAY(),"<IMG src='http://xxx/yyy/AS/Pictures/_t/kpiryg-2_gif.jpg' />"))

Much appreciate your assistance with this.
Thanks
0
Comment
Question by:Jagwarman
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 44

Expert Comment

by:Rainer Jeschor
ID: 39776040
Hi,
which SharePoint version and edition?
How do you "insert" the picture?
Can you please explain your logic in more details (and pseudo code)?
E.g. you have a column named Status. If the status equals ... and the datetime value in column XYZ > today then display X, if column XYZ < today then Y ...

Thanks.
Rainer
0
 
LVL 32

Expert Comment

by:Jamie McAllister MVP
ID: 39776174
If you're using SharePoint 2010, this can be done on lists using SharePoint Designer. I've detailed the steps here;

http://the-north.com/sharepoint/post/Replace-Task-List-Status-Column-with-Icons-in-SharePoint-2010
0
 

Author Comment

by:Jagwarman
ID: 39776190
Unfortunately my firm does not allow us to have access to Designer so is there another way of achieving this?

Thanks
0
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
LVL 44

Expert Comment

by:Rainer Jeschor
ID: 39776198
Hi,
which SharePoint version and edition?
So you do not already have that kind of displaying images implemented, correct?

Without SharePoint designer access, the quickest way would be to use a Content Editor web part and some Javascript/jQuery code to change the column content from text to image.

Thanks.
Rainer
0
 

Author Comment

by:Jagwarman
ID: 39776282
I am using 2010
0
 
LVL 44

Accepted Solution

by:
Rainer Jeschor earned 2000 total points
ID: 39776532
Hi,
OK, I still cannot fully understand your question (especially about the calculated column) but at least for dynamically displaying images here a solution.

Step 1: If you do not already use / have a jQuery library file, please upload one (downloadable from http://www.jquery.com - select a version < 2.0) and upload it to a document library of your site (e.g. assets).
Step 2: Create a text file, paste the following code and adjust the code to fit your needs (e.g. column position and image src urls)
Step 3: Open the page where you display the list, edit the page and add a content editor web part, referencing the under 2. uploaded text file
Step 4: Save and close the page and feel happy

Code:
<script type="text/javascript" src="http://yoursharepoint.local/sites/ee/SiteAssets/jquery-1.8.2.min.js"></script>
<script type="text/javascript">
        _spBodyOnLoadFunctionNames.push("ChangeColumnAddImages");

        function ChangeColumnAddImages() {
			
			var calculatedColumns = jQuery("table.ms-listviewtable tr td:nth-child(6)");
			calculatedColumns.each(function() {
				var imgUrl = '<img src="/_layouts/images/';
				switch(jQuery(this).text())
				{
					case "R":
						imgUrl += 'kpiryg-2.gif" />';
						break;
					case "G":
						imgUrl += 'kpiryg-0.gif" />';
						break;
					default:
						imgUrl += 'kpiryg-1.gif" />';
				}
				jQuery(this).html(imgUrl);
			});
        }
</script>       

Open in new window

The above code will replace the 6th column which contains the calculated value like "R" or "G" and sets the html of each column cell to display the related image.

If you need further help for you calculated formula, please add more info.
Thanks and HTH
Rainer
0
 

Expert Comment

by:Stewart Obis
ID: 41488879
=[M6 Date Expected]&[M5 Date Expected]&[M4 Date Expected]&[M3 Date Expected]&[M2 Date Expected]&[M1 Date Expected]=Today-7   and the complete box is not checked then show "Amber"
=[M6 Date Expected]&[M5 Date Expected]&[M4 Date Expected]&[M3 Date Expected]&[M2 Date Expected]&[M1 Date Expected]=Today+7   and the complete box is not checked  then show "Red"
0
 

Expert Comment

by:Stewart Obis
ID: 41488883
Hi Rainer,

=[M6 Date Expected]&[M5 Date Expected]&[M4 Date Expected]&[M3 Date Expected]&[M2 Date Expected]&[M1 Date Expected]=Today   and the complete box is not checked  then show "Green"

=[M6 Date Expected]&[M5 Date Expected]&[M4 Date Expected]&[M3 Date Expected]&[M2 Date Expected]&[M1 Date Expected]=Today-7   and the complete box is not checked then show "Amber"
=[M6 Date Expected]&[M5 Date Expected]&[M4 Date Expected]&[M3 Date Expected]&[M2 Date Expected]&[M1 Date Expected]=Today+7   and the complete box is not checked  then show "Red"

or

I am doing client side design and development. I don't have access to SharePoint Farm and SharePoint designer (Outsourced). I only have Site level permission (I don't  have Site collection Administrator permission).

KPI (Time) field will create a KPI indicator based on current entered information in the task line (specifically of the milestone expected date fields).


Date Field: KPI (Time)      

Detail:

      KPI (Time) will be triggered from the 6 Milestone Expected Date fields (M1 Date Expected -> M6 Date Expected):
a) If the date required of any of the milestone Expected Date field does is not greater exceed todays date and the complete box is not checked then Project KPI field will = Green

b) If the date required of any of the milestone Expected Date field is greater than todays date (by less than a week) and the complete box is not checked then Project KPI field will = Amber

c) If the date required of any of the milestone Expected Date field is greater than todays date (by more than a week) and the complete box is not checked then Project KPI field will  = Red

Each of our 6 milestones have a date expected and completed (as per the new fields required above). The overall KPI will look across all the milestone date expected fields and generate the KPI status based on any overdue and by how long.

Kindly help.

Stewart
0

Featured Post

Office 365 Training for Admins - 7 Day Trial

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Last week I faced a strange issue recently, i have deployed SharePoint 2003 servers for one project and one of the requirements was to open SharePoint site from same server. when i was trying to open site from the same server i was getting authentic…
The vision: A MegaMenu for a SharePoint portal home page The mission: Make it easy to maintain. Allow rich content and sub headers as well as standard links. Factor in frequent changes without involving developers or a lengthy Dev/Test/Prod rel…
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …

741 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question