Friday, April 17, 2009

Conditional formatting

So Steve stole the thunder this week and you already know how to do conditional formatting in Revit schedules. That Steve is one quick guy but after all, his readership eclipses my little corner here! Jokes apart, we were talking about how I stumbled across this hidden tool when trying to activate the Manage tab with shortcuts in Revit 2010. Now I just hope Autodesk doesn’t close the loop-hole. And guys, if you’re listening, please just add a button as we can use this tool in Architecture! EDIT: As of Revit 2010 SP3, this functionality is officially available. Thanks for listening!

The main shortfall I noticed is the fact that you cannot do conditional formats based on other parameters. That would be high on my list of enhancements for this tool. To get around this bump, one has to leverage the power of calculated parameters in schedules to perform the number crunching. A simple Yes/No parameter could be used to raise a flag and your condition would just look for the value and format the cell accordingly.

We can actually add multiple conditions to test for, but it is very limiting because it assumes an “AND” operator between them. What if I want an “OR” (which is what one needs when testing to see if a parameter falls between certain values and criteria….they can’t all apply at the same time! An “AND” operator expects all conditions to be satisfied and would thus force the overall condition to fail). It also took me a while to figure out how to add or remove conditions from the list. By the way, why does this dialog use the label “Field” when referring to a parameter? Seems to lack consistency with Revit’s terminology.

So I’ll stop rambling now and discuss how I plan on using this new find. The first two things that spring to mind are to check for human errors (egress calculations) and for room area comparisons between actual and target area.

Checking Occupancy

Unfortunately Revit cannot do calculated parameters in tags (my jaws are hurting repeating this one!) and so we have to resort to a “stone age” method: use a calculated parameter in the schedule and then manually type a copy of the result into a shared parameter that is added to both the schedule and the room tag so you can display the information in plan. PS: You cannot use the built-in Occupancy parameter because it is Text….DUH!

Yes, yes I know, the API can be used to copy values for you probably, but WHY do I need to resort to this for something that is a required task on every job by every firm? I’m getting side-tracked again….

So at least now we can use conditional formatting to help us identify values that don’t match the calculated values, which would happen when room sizes change. Here’s an example calculated parameter:

Flag1

In the schedule Formatting tab, select the parameter you want to add the condition to and type Alt+N to reveal the hidden gem:

CF1

Schedule1Here’s an example of what the schedule would look like when values don’t match up.

Note: You can check the option Hidden field in the Formatting tab to hide the calculated parameter used for the condition and it will still work just fine.

Some observations: Null values are ignored in calculated parameters (see the yellow cells above) and in conditional formatting. My condition “Flag = No” is not being met because the value is null, yet Revit is not coloring my cells. This needs fixed!

Room Area

For this scenario, I want to ensure that Actual Area:

  1. Is never below the Target Area
  2. Is never more than 5% of the Target Area
  3. Never varies by more than 100 SF from the Target Area

So the formula for parameter Flag would be something like this:

or(not(abs(Area - Program Area) < 100 SF), % Area Variance < 0, not(% Area Variance < 0.05))

where Area is the Actual Area, Program Area is the Target Area, & % Area Variance is (Area - Program Area) / Program Area.

Here’s what the resulting schedule would look like:

Schedule2

In the above example I added a conditional format (in orange) to highlight cases when the % Area Variance was below 5%, but the area difference was 100 SF or more.

Some observations: It would be really valuable if we could specify the boolean between multiple conditions instead of having Revit assume an “AND” operator. And we should be able to specify different colors for the various conditions instead of just one!

If that were the case, one would be able to highlight rooms with areas above the target with one color, and others below the target with another color. Finally, I also noticed that for Yes/No parameters, the Conditional Formatting dialog would let me change the value of my parameter Flag to “Yes”, but it would revert it back to “No” when I clicked out of the dialog or clicked on something else. Not sure why.

All in all this tool is of great value and I’m sure lots of you will find various ways to use it. Let me know how you intend to implement it!


Share/Save/Bookmark

14 comments:

Erik said...

An "OR" operator would be darn useful in the filter dialog as well.

Oh, BTW, you mentioned that conditional formatting won't plot (or I assume even show on a sheet) in Revit Arch. Just so you know, it's the same case with Revit MEP.

Dave Baldacchino said...

Absolutely. You'd think this would be standard in a database system!

Thanks for the heads up. I don't think I mentioned that, I know Steve pointed out that it won't print in any Revit version in his article. I personally see this tool mainly used for checking or analysis only. Printing might be nice to have, but is not at the top of my wishlist ;)

David Light said...

Thanks for this!!!! Extremely useful, although you are the master at formula!!! :-)

Dave Baldacchino said...

It's impossible to kill the nerd ;) In the future I'll start uploading some examples too.

Kevin said...

There are some nice things in Structural we as architects would like to use. You think there are some hidden dialogs there also? As someone on AUGI seemed to find out a few versions ago, all of the Revit Suite functions are there they just are not available through the UI in each package. You found a way to activate one and it seems like there should be others.

Kevin

Dave Baldacchino said...

Who knows? I'm sure there are. I hope Autodesk revisits their practices in this regard. It's plain silly to take away productive tools from one version and leave them in another. I'm optimistic that this practice will not last long. At least I hope they do something about it.

DaveP said...

Hi Dave;
Agree completely with the OR. I sumbitted that as a Service Request a long time ago.
About your Flag=No problem, though:
Some observations: Null values are ignored in calculated parameters (see the yellow cells above) and in conditional formatting. My condition “Flag = No” is not being met because the value is null, yet Revit is not coloring my cells. This needs fixed!I've gotten around a similar problem by using double negatives.
Instead of:
Flag=No
I use:
Flag=not(Yes)
That way, if the flag is either "No" or if the flag does not exist, it passes the test. This achieves the same result as Flag=No, but covers the non-existant case.
HTH

Dave Baldacchino said...

Hey Dave! Thanks for your comment.

Unfortunately that doesn't apply in this case but I understand what you're talking about. The calculated parameter for "Flag" (which is a Yes/No parameter) is shown in the first image. The problem is that Revit doesn't evaluate it to anything if some of the data is missing. In my opinion that's incorrect. If I'm asking a parameter to evaluate to True if A and B are equal and B is missing the value, then A and B are not equal! Yet Revit just decides to move on and leave the value empty.

Also notice at the end of my post where I mentioned that it would let me enter "Yes" in the conditional formatting dialog, yet when I clicked OK it would revert to No, so one cannot even try the double-negative approach.

Jonas said...

Hi,
Do you know if you can actually print the colours of a schedule using conditional formatting?
When i place the schedule on a drawing sheet the "colours" don't show up ie the cells are not showing any colour.

Thanks,
J

Dave Baldacchino said...

No, refer to the first comment by Erik.

Melissa Regan said...

My occupancy load calculation and occupancy load shared parameter match and I am still getting "no" in my flag! Why is this? If there is a large decimal point in the calculated value, does it need to be exactly the same in the shared parameter number?

Ryan Truxal said...

Is there a way to have multiple conditions?

Dave Baldacchino said...

Hi Melissa, sorry I forgot to respond. Yes the numbers have to match exactly, which is why you really need to round up the occupancy calculation consistently so when you type in your value manually, they match (I would also use the same parameter format to be on the safe side, ex: number, not integer).

Ryan, as far as I know there is not. I made that observation in the above post 5 years ago and nothing has changed on this front.

Adam Bear said...

If you use conditional formatting on a calculated value then that calculated value can contain if statements which do accept OR, NOT, AND etc conditions, so in this method it is possible to have multiple conditions, the downside is that the if statement can only operate on numbers and yes/no parameters.

Post a Comment