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.
