Lookup Tables: A Formula for Success
The use of lookup tables for pipe and conduit fittings provide an invaluable process that we take for granted. Imagine laying out a pipe system and going through to manually set the size of every single fitting in the system. Sure, each one might only take a few seconds, but with hundreds of fittings, it adds up fast. Then, of course, there are the multiple redesigns and resizes throughout the life of the project. We’re talking dozens of hours that lookup tables are saving us by automating fitting sizing. Let’s explore lookup tables and their relationship with Autodesk® Revit® families to better understand this process and its benefits.
Lookup tables are .csv (comma separated value) files that are used in conjunction with Revit MEP families of certain categories. Revit uses special parameters and formulas in families to reference the lookup table information. Lookup tables let you specify multiple parameter values relative to another value without creating a separate type for each set of values. The most common use of lookup tables is defining the various dimensions of pipe and conduit fittings based on the nominal diameter of the fitting. This relationship of the .csv file and the parameters/formulas in the family lets Revit immediately size and resize fittings based on the segments to which they are connected.
Formatting the Lookup Table
Lookup Tables can be edited using Microsoft Excel or Notepad. Row 1 contains the column headers for each column. Column header formatting is ParameterName##ParameterType##ParameterUnits.
Figure 1: Note the ## between each part of the column header.
Note: Lookup Table columns can be formatted for multiple parameter types, but the only types that can be used within the family are Size types.
The first column is a reference column used to designate a “name” or reference for what that row in the table defines. The column header for this column is always left blank. The second column defines the lookup value, typically the nominal diameter of the element. This column is a comparative column that Revit tries to match data from the family to the data in this column. All remaining columns are lookup columns, where Revit will retrieve values based on the lookup value data in the family. If more than one lookup value is necessary, then as many columns as needed are created, shifting the lookup columns over. So, if you’re creating a tee fitting that has three different nominal diameters, one for each connection, you should have three lookup value columns to coincide these parameters.
Figure 2: Reference Column, Lookup Value, and Lookup Columns in a Lookup Table.
How Revit Families Use Lookup Tables
Revit families must use a very specific process to access and retrieve information from lookup tables. The lookup table is stored within the family through an import/export dialog. This file is then referenced through a text parameter. These two components provide the access to the information in the lookup table. Then a special formula is used to retrieve values from the lookup table. We will take an in-depth look at each piece of the connection to understand the relationship between the file and how each piece of information is retrieved and utilized by the family.
Importing and Referencing the Lookup Table
To import the Lookup Table into your family, in the Family Types dialog, click on the Manage… button along the right side of the dialog box (Figure 3).
Figure 3
In the Manage Lookup Tables dialog, click on the Import… button. Browse to the appropriate lookup table file and click OK (Figure 4).
Figure 4
Back in the Family Types dialog, we add a text parameter named Lookup Table Name. (The text parameter can technically be named anything you want, but to keep consistency with out of the box families and to simplify formulas, I recommend keeping the standard name.) Then enter the name of the .csv file in the value of this parameter (see Figure 5).
Figure 5
Note: The Lookup Table Name value must match the .csv file name exactly without the .csv extenstion, but it is not case-sensitive.
Once the Lookup Table parameter has been filled in with the appropriate .csv file name, the lookup table can be used in formulas to reference the table for values.
Using Formulas with the Lookup Table
A specific formula is used to reference the lookup table to acquire values: size_lookup (Lookup Table Name, “Lookup Column”, Default If Not Found, Lookup Value)
To translate this into English, we are telling Revit to go look in (Lookup Table Name) for a column called (Lookup Column) and return the value from that column that corresponds with (Lookup Value) row. If there is no match, return the value specified for (Default If Not Found).
Let’s look at an example (see Figure 6):
Figure 6
In this case, the formula for finding the value of Fitting Outside Diameter reads something like this: In Lookup Table Name (refers to parameter under Text where you entered the .csv file name), look for a row in the Lookup Value column that corresponds to the family’s current value of Nominal Diameter and return the value from column “FOD” in that row. If no match is found for Nominal Diameter in the Lookup Value column, return a value equal to Nominal Diameter 1 + 1/8”.
Notice that Nominal Diameter currently has a value of 1”. If we look at our .csv file, we’ll see that ND, our Lookup Value, corresponds to Nominal Diameter in the Family Types dialog. When ND is 1”, FOD is 1.125”, or 1 1/8” (see Figure 7). So our family will have a Fitting Outside Diameter value of 1 1/8”.
Figure 7
Using Multiple Lookup Values
To verify the values of multiple parameters, additional Lookup Values are added to the end of the formula and the corresponding Lookup Value Columns are added to the .csv file between the first Lookup Value and Lookup Columns. Figure 8 is an example showing a family that uses multiple Lookup Values.
Figure 8
In this case, the formula for finding the value of Socket Bottom to Socket Bottom Run reads something like this: In Lookup Table Name (refers to parameter under Text where you entered the .csv file name), look for a match between Revit’s Nominal Diameter 1 and Nominal Diameter 2 parameter values and the Lookup Value columns in a single row within the lookup table. If a match is found, Revit will return the value found in that row for the column “SBtSBR.” If no match is found, Revit will return a value equal to .03*Nominal Diameter 1 + 1.96*Nominal Diameter 2 + 125/256”.
Notice that Nominal Diameter 1 currently has a value of 2” and Nominal Diameter 2 has a value of 1 1/4”. If we look at our .csv file, we’ll see our first Lookup Value, called ND1, corresponds to Nominal Diameter 1 in the Family Types dialog and our second Lookup Value, called ND2, corresponds to Nominal Diameter 2. When ND1 is 2” and ND2 is 1.25", SBtSBR is 3.0625, or 3 1/16" (see Figure 9).
Figure 9
We can define as many parameters as necessary using the lookup table, but remember that the values are driven by the first column(s). The number of lookup values in the formula should always be equal to the number of lookup value columns in the .csv file. If a parameter is unrelated to the Lookup Value (typically the nominal diameter), it shouldn’t be part of the lookup table.
Figure 10 presents another look at interpreting this complex relationship:
Figure 10
It’s important to understand that what Revit is actually comparing is the parameter name used in the formula (Nominal Diameter 2), and the corresponding column in the lookup table. So if your formula reads “Default If Not Found, LookupValue1, LookupValue2)” Revit will look at columns B and C, respectively, in the .csv file. If your formula is only referencing a single Lookup Value, Revit will never look past the first Lookup Value column in the Lookup Table. If you’re using “Default If Not Found, LookupValue1)” or “Default If Not Found, LookupValue2)” Revit will always compare the parameter in the formula to ONLY column B in the .csv file. The parameter name in the Lookup Value columns is in no way used by Revit.
Lookup Tables Improve Workflow
Now that we’ve covered how lookup tables are formatted and referenced within Revit families, let’s talk about how they can improve workflow. We already know how beneficial they are to the pipe and conduit modeling process, saving us a few seconds with each of hundreds of instances throughout our project. So why aren’t we using this concept with other categories?
Lookup tables may have originally been intended to be used only with Pipe Fittings and Conduit Fittings, but we can actually use them with any category we want! By using the process that we have discussed for creating lookup tables and associating them with Revit families, any family that can be flexed in Revit can be controlled by lookup tables.
Some are a little more logical than others. For instance, you could add lookup tables to pipe valve families so they are sized to fit the segment in which they are placed. However, when creating a VAV box family, it typically makes more sense to drive the parameters through formulas and other parameters since you don’t want the duct size to dictate the VAV connector sizes.
So how can lookup tables help your workflow? Maybe in Pipe Accessories, or Duct Accessories, or why not even Mechanical Equipment? Get creative! If you decide that lookup tables aren’t right for the situation, you can always convert the .csv file to a type catalog. Did I mention that lookup tables work with families that have type catalogs, too?
