Lookup Tables and You – Part 1 – Understanding Lookup Tables

Life isn’t just about Dynamo, so here is something a little different to mix things up.

Earlier this year I spoke at BiLT ANZ 2017, I had the opportunity to present two separate talks of which one was lookup tables. It’s a bit of a dry topic, so stick with me.

Lookup tables for pipe and conduit fittings provide an invaluable enhancement to the MEP workflow that we take for granted. On every project we work on, lookup tables save us countless hours in the modelling process. Without them, modelling a pipe system would require each individual pipe fitting to be sized as you go. It may be only a few seconds lost per fitting, but multiply that by hundreds or even thousands of times across a project. Then consider the revisions across the piping network. Those few seconds wasted quickly adds up.

At their core, lookup tables are simply comma separated value files (.csv) that are used in conjunction with Revit family files. Prior to Revit 2014, these files were stored on your hard drive or in a common network location and that location was referenced by the revit.ini file.

You might have had experience with tiny pipework fittings that wouldn’t size correctly in Revit if the lookup table location was not correctly configured. Nowadays, lookup tables are stored within the family themselves, provided your family content has been properly updated to 2014 or newer the new system with embedded lookup tables should eliminate those tiny fittings altogether.

Most commonly lookup tables are used for defining pipe and conduit fittings based on the nominal diameter of the fitting. Revit is then able to automatically size and resize fittings based on the size for the pipe the fitting is connected to by using the data contained within the lookup table.

The Lookup Table Format

Lookup tables can be created and edited in either a simple text editor such as Notepad or Notepad++ or in Microsoft Excel and it’s alternatives.

The first column is a reference column, the values in this column could be ‘Tom’ or ‘Jerry’ but it’s obviously more useful to give enter data that is relevant to the family being created.

Usually when dealing with pipe and conduit fittings this is the nominal diameter, when dealing with pipework branches, I like to format my references as Upstream x Downstream x Branch so for example, 100x100x65. You could however include product names, say if it was a 5000 litres grease arrestor, the reference could be GreasePit5000

The second column defines the lookup value which is typically the nominal diameter of the fitting. This is the column that Revit will look up to determine the remaining parameters for the element.

The formatting of the headers within the csv file is

ParameterName##ParameterType##ParameterUnits

which you can see in the example above. The formatting of the headers is critical for the lookup tables to work.

In column B of the example, we have ND##length##millimeters (note US spelling of millimetres) which in this to the nominal diameter, it’s a length parameter and the units are in millimetres.

If further lookup values are required, say for example in the instance of a pipework branch, more lookup columns can be added as needed, shifting the remaining columns to the right.

As you can see in the above example, we have three nominal diameter columns to determine the size of our fitting.

The remaining columns after our lookup columns contains the data that drives the modelled dimensions of the Revit family and you can have as many lookup columns as you need to generate your family.

How Lookup Tables Are Used in Revit Families

Lookup tables are stored within the family file itself through the Manage Lookup Tables dialogue. The lookup table is then referenced using a text based parameter and lookup formulas are used to retrieve these values from the lookup table.

Importing and Referencing the Lookup Table

To import the lookup table, you first must open the family file itself. In the family types dialogue box, click on the Manage Lookup Tables button.

Once in the Manage Lookup Tables dialogue, you have the option to either import or export the lookup tables contained within the family.

If you’re starting with a family that already has an associated lookup table, it’s always a good idea to start by exporting the original lookup table so you can use that as a base and then modify.

Once you have a completed lookup table, you can import it in the Manage Lookup Tables dialogue. You can have multiple lookup tables within your family files and chose whichever is required.

Once the lookup table has been imported, you need to add a text parameter to the family that references the lookup table name. Historically this parameter has been named Lookup Table Name although you can name this parameter anything that you like, it’s best to remain consistent with the out of the box parameter naming.

When filling out the parameter data, it needs to match the name of the .csv file that you are going to use, minus the .csv extension.

Lookup Table Formulas

The formula used to reference the lookup tables is

size_lookup (Lookup Table Name, “Lookup Column”, Default If Not Found, Lookup Value) 

The format of the formula is similar to that of an if statement.

The first portion of the formula is telling Revit to find a value in the lookup table “Lookup Table Name” for a column named “Lookup Column” and then pull the value from the row that corresponds with the value from the “Lookup value”. If there is no match, Revit will provide the value from “Default If Not Found Value”.

In the example above, the formula is finding the value for the fitting outside diameter.

Stepping through the formula, Revit will lookup the table that has been defined in the parameter Lookup Table Name for a value in in the column named “FOD” that corresponds with the lookup value of the Nominal Diameter parameter (50mm). If no match is found for the nominal diameter in the lookup value column, a value equal to Nominal Diameter 1 + 9.1mm will be returned.

Note that the nominal diameter is currently set at 50mm. If we refer to our csv file we’ll see that our lookup value is ND1 which corresponds with Nominal Diameter 1 in our family.

When ND1 is 50mm we can see that the value of FOD is 56mm, this has set our family to have a fitting outside diameter of 56mm.

What About Multiple Lookup Values?

You may have noticed that the example family used is a DWV pipe branch fitting, so we need to deal with multiple lookup values. The fitting may be a reducing branch or it may have different variations in branch angles.

To achieve this, we simply need to add more lookup values to the end of the formula.

In this example we’re looking up the socket bottom to socket bottom run, we are again referencing the same lookup table however this time we’re looking for a match for Nominal Diameter 1, Nominal Diameter 2, Nominal Diameter 3 and Angle in a single row within the lookup table.

If a match is found, Revit will return the value from the column that corresponds with SBtSBR. If no match is found, Revit will return a value equal to that of Nominal Diameter 3 + 27.7mm.

This particular fitting we are working with is a 50x50x50 x 45° DWV branch

Referring to our csv file, we can see that when we look up the values 50, 50, 50 and 45 the corresponding value in the SBtSBR column is 81mm which is the value that has been set in our family.

Remember that the lookup columns need to be in order in columns from left to right to be able to lookup multiple values.

 

So that’s it for understanding how lookup tables work, stay tuned for Part 2 where we look at applying the use of lookup tables to improve our workflow.

 

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.