Sometimes you want to have a Revit parameter that returns a text string depending on a number of options. For example, you might want to have a parameter show a product SKU code based on other parameter values for dimensions, color and material.

If the options are all organized into family types, and you build a type catalog, then the text string can be returned by the type catalog, and job done. But there are times where we need the text to change based on instance parameters, or where the parameter is used with Revit families that don’t make use of type catalogs, e.g. to return model numbers or SKUs of pipe, duct and conduit fittings (1). What is the solution in such cases? To date, it’s been IF formulas that could run the length of books.

Below is an example of the kind of long-winded formulas (and this one can be considered small) that people will create to have parameters that report text:

if(V = 2015, “3/4×1/2”, if(V = 2515, “1×1/2”, if(V = 2520, “1×3/4”, if(V = 3225, “1 1/4×1”, if(V = 4020, “1 1/2×3/4”, if(V = 4025, “1 1/2×1”, if(V = 4032, “1 1/2×1 1/4”, if(V = 5025, “2×1”, if(V = 5032, “2×1 1/4”, if(V = 5040, “2×1 1/2”, if(V = 6550, “2 1/2×2”, if(V = 8040, “3×1 1/2”, if(V = 8050, “3×2”, if(V = 8065, “3×2 1/2”, if(V = 10050, “4×2”, if(V = 10080, “4×3”, if(V = 125100, “5×4”, if(V = 150100, “6×4”, if(V = 200150, “8×6”, if(V = 250200, “10×8”, if(V = 300250, “12×10”, “NA”)))))))))))))))))))))

I’m happy to report (pun intended) that there is a better way.

At a London Revit User Group (LRUG) back in March last year, Darren Snook and I got to talking about lookup tables. He mentioned some odd behaviour where the lookup table would return text. I knew there and then that he was onto something. We discussed the details, and he went on to write a blog post about it the next day; and all credit for this trick is his, so let’s call it the Snook Solution.

Let’s go back to the formula shown above and see how we can use the Snook Solution to achieve the same result with way less hassle and potential for error. In the image below we can see the above formula in context. For the different values of V, we have a lengthy chain of IF statements that return a different bit of text in the 0BV_Size parameter. The logic is straightforward enough, but checking such formulas and maintaining them over time gets to be a pain. And remember this is a short example among many.

Fortunately we can skip all of that with the Snook Solution. The family already has a lookup table that contains the exact same text strings we want Revit to return.

When we export the lookup table to a spreadsheet, we can see that the values for V are in the second column and that the text we want to return is in the first column.

So we only need to get the value of this first column based on the value of V. The formula we can use for this is:

**size_lookup(Look, “”, “NA”, V)**

Let’s break this down term by term. The “size_lookup” is the name of the function in Revit. “Look” is the name of a text parameter that contains the name of the lookup table. “NA” will be what we want the function to return if the value of the parameter “V”, which is the last element inside the function, is not found in the lookup table.

Now, the magic happens in the double quotes of the second element in the function. That tells size_lookup to return the value in the column with no name, from the row matching the V value that we have passed to it. We indicate the column with no name, i.e. the first column, by having nothing between the double quotes in the second element of the function. Below is an image showing the different parameters referenced in our formula, and the resulting value for 0BV_Size that gets reported from the lookup table.

There are many places where this trick can be put to good use, some of which I hope to share in future blog posts. Also, nothing stops you from loading more than one lookup table for different text parameters, which makes it convenient to update whenever the need arises.

Last year, at the first Building Content Summit in Washington D.C., I showed the Snook Solution at the hackathon, combining it with a couple more tricks to achieve nice reporting of alphanumeric product codes in pipe fittings. As long as you are using Revit 2014 or higher, you can get part numbers, product codes or SKUs in any type of fitting in Revit. No more monster formulas and hard to maintain product catalogs! And with some clever thinking, you can use this as an alternative to the still missing text concatenation feature in Revit parameters.

This year, at the second Building Content Summit in Scottsdale, AZ, Ralph Schoch from Victaulic reminded me that I had promised to write about this time-saving trick. Seeing that the question still pops up in forums, and that new manufacturer content is still coming out with performance-sapping formulas to deal with this, it seemed like as good a time as any to spread the word again about the Snook Solution.

(1) If you come across a fitting in Revit done with type catalogs rather than lookup tables, you most likely stumbled upon crappy content.

Hello

I remember this! I created the V formulas and found the solution to getting the first column text value http://forums.autodesk.com/t5/revit-mep/lookup-table-for-text/m-p/4941816/highlight/true#M22941

One text value isn’t enough in this age of BIM. In our firm we intend to look into an SQL database and using Project Parameters to solve this. Early days still…

Hi Hugh, I saw that you’d seen Darren’s post in the link you provided. Also, I realized that I forgot to link to the formula I used as an example, which was one you’d posted before Darren published his solution. I’ve now added the link to that post. Hope you’ll share something about the SQL approach when you have it up and running.

Just thinking about this there is no reason you even need multiple lookup tables to store text values of different types. Partcodes, Labor, Cost, Description. Use columns to the right to determine what text you want to pull from the first column of the lookup table. I use a angle parameters to determine what data I want to pull from the lookup table for many families. Works for metric and imperial content.

Hi there, thanks for the post.

Do you guys know of a way to go the other way? I’m looking to have a lookup table return a number value while checking against a text value. If param is “A” then return “1”, etc. This would be a real breakthrough since conditional formulas in Revit don’t take text input (if(param=”A”,1,0) etc is not supported). Thanks much!

Hi Jan, could you provide an actual example where this would be required, and what the workaround being used is at the moment?

Hi Jose,

it’s a long story. Here at the office we want to keep all our door parameters as instance parameters, since our project leaders and QA managers can access/edit/update the project’s doors from Excel through Ideate BIMLink. However, there are door types (instance parameter called “DoorType”) that trigger certain other parameters. For example, if the DoorType is “LL”, linework representing louvers needs to be switched on. So, text value “LL” needs to trigger a Yes/No visibility parameter. Which is impossible to achieve through Family Editor conditional formulas, which only take number/integer inputs for the test part of the conditional clause (if(DoorType=”LL”,1,0) is not accepted). So I thought Lookup Tables would be the way to go, but since they don’t seem to check against text parameters it looks like that won’t fly either. FYI: Key Schedules won’t work, and I don’t want run Dynamo scripts for this since it’s a central part of our documentation flow. Makes sense?

MOD=size_lookup(Look, “”, “NOT DEFINED”, COD)

IT MEANS THAT IN THE SCHEDULE CALLED LOOK, WHEN YOU FIND IN THE FIRST COLUMN CALLED COD##length##millimeters THE PARAMETER COD, RETURN THE LEFT CULUMN TEXT

:THE EVIDENCED DARK ARROW ABOVE IN YOUR PICTURE INDICATING THE

“” IN THE FORUMLA size_lookup(Look, “”, “NOT DEFINED”, COD)

WHEN APPLYED ARE GENERATING AN ERROR “WRONG UNIT DIMENSION”.

THE PARAMETER MOD, IS A TEX PARAMETER, IS IT RIGHT?

Hi Jose.

I have searched the web for a decent guide on lookup tables, and now i found yours.

I’m trying to make our drawing head fool proof, by auto-filling/pre-filling some of the parameters. And one way i was thinking we could do it, was by help of a lookup table.

But is it possible to make the parameter V (in your example) text, so Revit looks for the City name(entered by the user), and fills out address, zip code, phone, company name and so on.

I know you think, why don’t you try it for yourself. But I have, and it blows up every time.

Hope to hear from you.

Great information thank you very much……………..