Conditional Formatting Based On A lookup Field
I will not explain conditional formatting here as you can easily find plenty of literature on how to do that. You can format a row or a column (or show/hide content) based on a certain rule or condition. Conditions can be simple or complicated and can comprise comparison and Boolean operators. There is an issue when you try to based your condition on a lookup field (field referenced from another list).
Let’s say we have a list and one of its columns (status) is a lookup to a different list. We want to highlight the status cells (column) when the status is either empty or ” Rejected”. You may say well, click inside one of the status cells in a listview (xsltlistview), select conditional formatting, format column (from the ribbon in SPD), and in the criteria dialog, you set status is null or status equal Rejected. You select your format (reddish background in this example) and test it. You will notice that empty cells were picked but not those with value equal to Rejected. What;s the problem?
Well, internally, the lookup column is not stored as we think it is. In fact, I created this example using a service as a data source so you can see what I am talking about (this is why it has those funny column headers) – the status is stored in the format: “number;#value” and this is why the equal operator will not work. This is not too bad, actually. We can use contains instead and it’ll work just fine. The number is the ID of the item corresponding to the selected value (they happen to be in order in this example just by accident!). Also, you won’t make a new list to store a few status values – this is just an illustration. Finally, make sure, when debugging your conditional formatting, to use easy to spot changes like backgrounds or fonts. Some styles like borders may be overridden by the SharePoint styles!
There are other cases where a similar format is used: multiple selections, user fields and managed metadata, so beware.
