RETURN A TEXT VALUE BASED ON A NUMERIC VALUE

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 organised into family types, and you build a type catalogue, then the text string can be returned by the type catalogue, 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 catalogues, 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.

The image below shows how it can be done with a formula. We can see that for different values of V, we have a lengthy chain of if statements that return a different bit of text in the 0BV_Size parameter.

Fortunately we can skip all of that, using 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 the values for V are in the second column and the text that 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.

• “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” is the last element inside the function, is not found in the lookup table.

The second element in the function 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. Nothing stops you from loading more than one lookup table for different text parameters, which makes it convenient to update whenever the need arises.