Conditional Formatting on List View Breaks with Hidden Columns

Interesting behavior:

Conditional Formatting in SharePoint Designer requires that any fields used in the condition be shown in the view.

But what if you are using calculated values as placeholders for formulas in the condition?  You probably do not want to view these.  They are like temporary variables used to derive an end result, as developers we only display intermediate variables usually when we are debugging.  Once we have it working we only show inputs and outputs.

Example:
Let us suppose you have been tasked to create a colorful list view that shows a due date as different colors depending on how far away the due date is.  The formatting is: (background color, bold font, larger font, border solid, white, medium top/bottom 20px left/right, padding top/bottom 3px, text-align center)

Due Date = RED             if Due Date  is past or Today.
Due Date = ORANGE    if Due Date  is 0-30   Days in future.
Due Date = YELLOW    if Due Date  is 30-60 Days in future.
Due Date = GREEN       if Due Date  is 60+     Days in future.

Normally as developers we would go into the list and create new calculated columns to convert Due Date to another date that can be used to form a dynamic range.  The reason for this is that SharePoint will not let us do conditional formatting on the Current Date or Today as a formula.  Today-30 does not work.

So we create these columns: DueDate-30 and DueDate-60 for our 2 range thresholds.

Then in conditional formatting we use these conditions:

RED:
DueDate <= [Current Date]

ORANGE:
DueDate > [Current Date]
DueDate-30 <= [Current Date]

YELLOW:

DueDate-30 > [Current Date]
DueDate-60 <= [Current Date]

GREEN:
DueDate-60 > [Current Date]

Crazy huh?  It works.  You are comparing sliding Due Dates backwards in time so you can compare them all to Today or [Current Date], which is dynamic.

So now you set your conditional formatting, but in the standard list view, NOT using a Data View, the conditional formatting breaks unless you show the columns DueDate-30 and DueDate-60.  But we use these for calculations and do not want to display them!  They would confuse anyone.

The Fix:
Show the columns at the far right of the view.  Open the view in designer with design view showing, select one of the cells in the DueDate-30, use the Table menu in the ribbon at top, use the select drop-down at far left, choose select column.  This will highlight the entire column in design view.  CTRL-x or cut.  Amazingly enough the column will disappear from the view, but the fileds are still part of the CAML query and the XSLT, so the conditional formatting still has those fields to work with.

Caveat:
You may be able to spend some time figuring out how to use those hidden fields without using them as a column in the view.  I tried for a bit to build <xsl:param> so that I could use them even though I didn't have them in the <View Fields > section of the CAML query at top.

If you solve this more eloquently, please comment or send me a message, I would really like a better solution.

If you do not like this, because it may be confusing to some that you still have those columns displayed in the Fields: Add/Remove Columns, but they do not show in the view, then by all means build yourself a DataView webpart and do your own XSL:Choose formatting with CSS.  The downside to this is you will no longer have this view available in the drop-down views nor will you get the ribbon features for the list.  Essentially it is a static view that you have to place on a page.

Comments

  1. Just happened to stumble upon your post. One work-around would be to not use SP Designer at all but put the formatting HTML and Javascript code in Calculated Columns; see http://e2.nl/icc

    ReplyDelete
  2. Hello!

    I am trying to do the exact thing you are but without creating extra columns. Obviously the [Today]+30 will not work. Is there no other way to do this?
    Thank you!

    ReplyDelete

Post a Comment

Popular posts from this blog

SharePoint Designer 2013 Approval Workflow with Comments

Change SharePoint server hostname and Web Application Names

SharePoint Search - Content Processing Pipeline Failed to Process the Item