Revit Lookup Table Hell

Revit Lookup Table Hell

result=size_lookup(LookupTableName, LookupColumn, DefaultIfNotFound, LookupValue1, LookupValue2, ..., LookupValueN)

In English?

It’s one thing abstractly to understand what lookup tables do, and to understand how they change how families behave in projects.  I recently wanted to use a lookup table in a family I developed where there wasn’t really a template I could steal from a library family, and found myself needing to develop the lookup table from scratch. 

I quickly found myself in lookup table hell.

Autodesk’s documentation is, frankly, awful.  That’s typical of software documentation today.  The gaps between developers and users of software have never been larger.  I found a number of blog posts that explained pieces of the puzzle, but I think an explanation useful to the average modeler faced with the problem of doing it all alone remains to be written.  Perhaps this is it, perhaps not, but here goes.

Why Use a Lookup Table At All?

Lookup tables aren’t unique to Revit, but are a common database feature.  Their original function was to save processing time.  The programmer of the lookup table calculates a whole bunch of data, then stores the data in a comma separated values (csv) file for later use, saving the software the problem of having to make these calculations every time it’s used.  In this case, what we’re storing is family parameters. 

When a family has a complex set of parameters, there are two ways the values of those parameters can be set when an instance is created: either they can be derived by parameter formulas, or they can be entered by the user.  To prevent the brain damage associated with either process, we can pre-calculate the parameters for all likely instances, and store them offsite in a table, where they are pre-fetched when we load the family and looked up when the modeler uses the family.

This is useful when you are trying to model actual objects made by real-world manufacturers, rather than generic objects.  Pipe fittings are the most common example of lookup table use.  The relationship between (for example) the inside and outside diameters of pipes is not consistent, so a formula can’t give you an accurate set of parameters.  Nominal pipe size often represents neither the actual inside nor outside diameter, so the use of nominal pipe size won’t produce an accurate instance.  Since formulas aren’t useful here because of the inconsistent size relationship, the only other option is to have the modeler look up all of the relevant sizes from a manufacturer’s data sheet.  With lookup tables, we do all of that in advance, and store it where Revit can recover those parameters when needed.

Architectural Elements of Lookup Tables

We need four things to make a lookup table useful.  We need:

·       

•   to know where the lookup table is

•  family parameters that can be filled with lookup table data

• a lookup table structure that allows Revit to find the data we stored

• parameter formulas in the family that tell Revit to use the lookup table

What follows is a semi-step-by-step method for getting a lookup table built.  It’s best to do as much as you can from within the family editor, which allows Revit to do the linguistic formatting needed so that it can use the lookup table later.  This will prevent many minor or typographic errors which can be very hard to decipher later.

Lookup Table Location

To find a lookup table, Revit needs to know its name and location. 

NAMING:  When naming a lookup table (this is really important), it must have exactly the same name as the family (.rfa) file, but with a .csv extension.  If you steal a library .rfa file and want to use the associated lookup table, you will have to make a copy of the lookup table and name it exactly the same as your copy of the .rfa file.  If you’re not familiar with .csv’s, you can open and use them in Excel.

LOCATION:  Lookup tables are stored in a common location.  The specific location may depend on the Revit deployment in your organization.  If you have a standard Revit installation, your lookup tables will be stored here:

C:\ProgramData\Autodesk\RVT 2016\Lookup Tables

Where “2016” will be whatever year version you are using.

There are two subdirectories, “Pipe” and “Conduit”.  I don’t think it matters which one you use – you should store the lookup table based on the family category you specify when you create your .rfa.

Unlike with type catalogs, it doesn’t matter how you load a family with a lookup table into a project.  Once a family is associated with a lookup table, Revit will find the table (if it’s in the right location) and use it, no matter if you load the family from inside your project or load it from the family editor. 

Developing Family Parameters

As you lay out the references for your family, you’ll be making parameters out of dimensions.  If you want to transfer these parameters to your lookup table, you need to make sure you specify them properly when you create them. 

“Type of Parameter” must be a size parameter. Lookup tables are all about sizes.  Use “Pipe Size” or “Conduit Size”.  You have to get this right when you create the parameter.  You can’t fix it later, short of deleting and re-creating the parameter.

Your parameters should be instance parameters.

You have some choices here about how you develop your parameters, and the choices are strategic.

The values and formulas you enter when you’re working in the family editor don’t matter, as far as the lookup table is concerned.  When a modeler creates an instance of your family,  parameter values will come from the lookup table, and lookup table values are static numbers.  There are no formulas or logical operators stored in lookup tables. 

The lookup table is intended to store the parameters for every possible configuration of the family – if a manufacturer makes 11 sizes of fitting, your lookup table is probably going to have 11 different sets of parameters from which Revit will choose, depending on the size fitting needed by modeler.

The idea here is to model what manufacturers actually create, rather than to allow the modeler to create an instance of a family which isn’t manufactured. 

The lookup table structure requires that you tell Revit what to do if the modeler wants to create an instance for which there are no lookup table data – say, if he wants to create a 5” elbow, when the manufacturer doesn’t make one.  This is the DefaultIfNotFound part of the lookup table call.

If you drive your parameters with formulas when you create the family, you can point the lookup table to those formulas, and tell Revit to use them if the modeler wants an instance configuration which doesn’t exist in the lookup table.  If you do that, Revit will create a credible-looking instance of your family.  That may be what you want, but it may not be.  It may be that you don’t want modelers to be able to model items which aren’t manufactured, so you will tell Revit to default to parameter values which will let the modeler know that this particular item isn’t manufactured.  That’s a strategic choice.

Once you have your parameters established, it’s time to create the lookup table, and tell Revit where the lookup table lives.

Creating the Lookup Table

The first thing you will need to do is to create a family type.  You only need one (remember, all of these parameters are instance-driven.  By creating a family type, you are just creating a type table which you can export as a lookup table).  Tradition is to call the type “Standard”, but you can call it anything you want.

If you look at the Family Types popup window (where you edit family parameters), you will see the bottom button in the right-hand button column, labeled “Lookup Tables….Manage”.

Click “Export”.  This will pop up a “Save As” browser window, asking you where to save the table.  You want to put the table wherever lookup tables are stored on your system, as described above.

Notice that the name and file type are already filled out for you.  This is the advantage of doing this from the family editor, rather than creating all this stuff yourself. 

Your lookup table is now created.  It will have only one line of values in it, which are those associated with the “Standard” type you created.  Before we delve into editing the lookup table, we should probably tell Revit where we put it, so that it can use it with your family.

Telling Revit the Lookup Table Location

We are going to change every parameter that uses lookup table data (later) so that it has a formula which tells it to use the lookup table.  That formula won’t contain the lookup table name, but will refer to a family TYPE parameter which contains the lookup table name.  This prevents you from having to tell every parameter the lookup table name.

You’re going to create a text parameter which contains the lookup table name.  What you name the parameter doesn’t matter – but remember, you’re going to be typing it a lot, so don’t make it too lengthy.  Usually, the parameter is named something like “Pipe Size Lookup”, or something like that, but the name doesn’t matter.  It is the text in the value column which matters, and must be the exact file name of the lookup table.  I’m going to shorten the name of my text parameter to make it easier to type, and to prevent a little confusion in explanation later.  I’m going to call it “Pipe CSV”.  I know that lookup tables are CSV files, so I’ll remember what this parameter is for later.  My parameter should look like this:

              Parameter                                                          Value

              Pipe CSV                                                              Manhole ABS

Where “Manhole ABS” is the exact name of my lookup table, which Revit assigned it because my .rfa file is called “Manhole ABS”.

Revit knows where to find the lookup table, because you stored it in the right place, so it doesn’t need a path.  It stored it as a .csv file, so it doesn’t need the file extension.  All it needs is the name.

Now Revit knows to associate the parameters you want with the lookup table.  You just need to tell Revit now which family parameters are going to use the lookup table, and what values they will pull from it.

Lookup Table Logic

To stay on the rails, we need to know how lookup tables are structured, and how Revit uses them.  If we understand that, the rest of this is pretty easy.  If we don’t get it, it’s a hair-pulling, teeth-gnashing experience.

Let’s start with how Revit uses a lookup table, then the structure of the table will seem a little more logical.

Let’s say that I have a pipe elbow family which uses a lookup table, and I want to create an instance of a 4” elbow.

A 4” elbow has a number of other parameters that always go along with it: an outside radius, a bend radius, and perhaps some other things that are specific to 4” bends of this type.  The values of these are stored in the lookup table, all in one row, something like this:

                (Key field)           (       Values we want to look up         )
                Nom Size             Outside Diameter             Bend Radius

                       4                            4.125                                  8.250

In this little table, the outside radius and bend radius are the values we want to look up, so that we can feed them into our parameters.

The nominal size is what we know, and we want the values associated with that particular size.  In this table, the first column is a key, or index, used to find the values we want, which are stored in the remaining columns to the right.

I want to make the point here that the table above contains everything that Revit needs to know in order to feed the desired values into your parameters.  This isn’t formatted in "lookup table-ese" yet, but the information is there.

Let’s parse the portion of the lookup command that is relevant here, and see what Revit is doing with that command:

                (LookupTableName, LookupColumn, DefaultIfNotFound, LookupValue1, LookupValue2, ...,                 LookupValueN)
 

The format of the lookup command is not very informative, but the logic works.   

We’ll build our lookup command, step by step, to see how Revit uses the information in it.  Our command is going to find the outside diameter of a 4" fitting from a lookup table, and return that value to the outside diameter parameter in our family.

To do this, Revit needs to know:

          - Where to find the lookup table

          - Which column to look in the lookup table

          - Which row to look in the lookup table

We start by telling it where the lookup table is.  LookupTableName has an obvious meaning, but there IS a subtlety here.  Revit isn’t actually asking for the file name of the lookup table itself.  It’s asking for the PARAMETER name of the text parameter which contains the value of the lookup table file name.  We named our text parameter “Pipe CSV”, and this is what Revit wants here.  This convention is very useful.  I can name my lookup location parameter “Pipe CSV” in every family I build, and Revit will never get confused.  

It’s finding the lookup table based on the VALUE of the text parameter, not based on its name.  So the value has to be unique – it has to match the .rfa file name, but the name of the parameter can be universal.  By naming the parameter the same thing in every family, I’m less likely to make mistakes.  We’ll see this when we start monkeying with our parameters.

So our lookup command, so far, looks like this:

        (Manhole CSV

Now we need to tell Revit in which column to look: Lookup Column is the NAME of the column from which you want to extract the parameter value.  You’re telling Revit which column to look in to find the parameter value you want.  When we actually view a lookup table, the column names contain some formatting information which we don’t need here.  What Revit wants is just the name, and it wants it in quotes.  The name of the column we want to search in the lookup table is "Outside Diameter", so that's the next term in our command.  Our lookup command, so far, looks like this:

        (Manhole CSV,”Outside Diameter”

We have told Revit to look at the Manhole CSV parameter and use the lookup table listed in the value.  We’ve told Revit we want to know a value from the lookup table that is somewhere in the “Ouside Diameter” column.

Now we need to tell it what row to look in.  To tell Revit what row to look in, we’re going to give it the value in the key column which is associated with data we want returned.  Revit doesn’t need to know which column to look in to find the key value – it’s always going to look in the first column of our table.  It's going to hunt through the first column in the lookup table for a value we tell it to look for (the nominal pipe diameter, in this case).  When it finds that value, it knows it's in the right row.

What we’re going to do is give Revit a value to look for in the first column of our table. It will look until it finds a match, and use the value in that row which is underneath the column header we specified.

We want Revit to return the value for an elbow which has a nominal size of “4”.  Revit doesn’t need to know the name of our column, or of our “Nominal Size” parameter, or anything else.  It just needs to know that it’s looking for a value of “4” in the first column of our table.

So our command now looks something like this (we’ll fill in the DefaultIfNotFound behavior in a minute):

        (Manhole CSV,"Outside Diameter",[stuff we'll do later],4)

Now Autodesk’s formula contains LookupValue1 through LookupValueN, so we are not tied to one key value.  We may have asymmetric bends, so that some are 4x4, some are 4x3, etc – to find the parameters for each type of bend, Revit is going to have to look for more than one value – it needs to know the diameter of each end of our bend.  So let’s change our table a little to accommodate that:

(              Key field                 )       (                             Values we want to look up                                 )
Nom Size 1          Nom Size 2     Outside Diameter1      Outside Diameter2                  Bend Radius

         4                           2                         4.125                               2.098                                    6.125

         4                           3                         4.125                               3.103                                    7.187

         4                           4                         4.125                               4.125                                    8.250

So let’s say that we have a parameter in our family “Outside Diameter 2”, which we need to build the geometry of our instance of a 4x3 bend.  Our lookup command will look like this:

            (Manhole CSV,”Outside Diameter2”,[stuff we’ll do later],4,3)
 

The underlined value will be returned.  First we told Revit what column to find the value we want in, then we told it how to find the row: look for “4” in the first column, “3” in the second column, and return the value in the “Outside Diameter2 Column” that’s in the row where the values in the first and second columns are “4” and “3”.

This works great if we are always searching for dimensions associated with a 4x3 bend.  In the real world, we are going to have our values “4” and “3” determined by parameter values entered by the modeler – that’s how we make these lookup values variable, and responsive to the modeler.

Let’s say that we have parameters “Nom Size1” and “Nom Size2” which need to be entered by the modeler in order to specify the fitting size.  Our lookup table command now contains the names of the parameters where our lookup values can be found, rather than the values themselves:

          (Manhole CSV,”Outside Diameter2”,[stuff we’ll do later],Nom Size1,NomSize2)

This formula does EXACTLY the same thing as the last one, within the lookup table.  The only difference is that we’ve turned our lookup values into variables (parameter names) so that the formula will enable Revit to look for any values the modeler wants.

We’ve now tied the lookup values to parameters which are controllable by the modeler.  Notice that the names NomSize and NomSize2 appear nowhere in our lookup table.  They are the names of the family parameters which we are telling Revit to use to give us our lookup values.   

Revit doesn’t care about the column name in which a lookup value occurs – it’s just looking for the first lookup value in the first column, the second lookup value in the second column, etc.  It already knows WHERE to look for those values, but it needs to know WHAT values you want it to look for in order to find the right row in the table - so it needs to know the names of the family parameters where it can find those values.

We can have as many lookup value columns as we need – if you have a fitting which has thirty pipe stubs sticking out of it, all of which can be different sizes, you just make sure that the first 30 columns of your lookup table contains the values associated with all configurations of whatever fitting you might want to use.

Revit will look for your array of LookUpValues 1….30, starting with the first column and working rightward to column 30.  When it finds an array of values that matches the array of values you gave it in the instance parameters for the fitting you want, it will return the value from that row from whatever column you told it to.

The command can only return one parameter value, but it can use as many lookup values as needed to find that one value.

What’s important is that the lookup values be fed into the command in the same order as the columns are organized in the table.

Now that we get the logic of the table, it’s time to look at the structure of an actual lookup table.

Lookup Table Structure

This is a lookup table – a simple one.  There is no header in the first column.  Revit will ignore this column.  You can put anything you want in this column, and Revit doesn’t care.  This column isn’t part of the table we constructed above when looking at the table’s logic.  This is a reference column, and is a place where you can put information which might help you to maintain the table later on.

The remaining columns contain data about the possible configurations of family instances, and also contain formatting information which Revit needs when plugging the data back into our parameters.

[This is another reason to export these tables out of the family editor, rather than to try to construct them from scratch.  The formatting is handled by Revit, and the chance for formatting a column with incorrect units is minimized.]

The column header names were exported to match your parameter names when you exported your lookup table.  As we can see from our discussion of the logic of the table, it is completely unnecessary for these column headers to match your parameter names.  When you tell Revit to look up data, you’re going to tell it the name of the column to look in, and your lookup formula is already associated with the parameter value you’re looking for.  The name of the column doesn’t have to match anything. 

If you have a habit of spelling out parameter names for clarity, this might be a good time to use brevity instead.  You might consider changing the column names to something very short in order to make them easier to type.  You’ll be entering them in parameter formulas, and shorter column names will reduce the risk of typos.

The “first” column (the first column Revit cares about) is highlighted in peach.  This is a key column which provides lookup values.  It is a column used to find the values which determine which row Revit uses to return the parameter value you want.

The remaining columns are *generally* the lookup columns from which Revit returns parameter values.

Remember, there may be more than one lookup value required for Revit to know which row is the right row (we already saw this with our 4x3 bend).  This table has four columns.  We can’t tell from looking how many of these columns are lookup value (key) columns and how many columns are lookup columns which return data.  Nothing about the table determines that.  Columns which are used as keys and columns which return data look exactly the same.

The question of which purpose each column serves is only answered by the lookup formula used in the family parameter.  If a formula contains two lookup values, then the first two columns of the lookup table will be used as indexes to find the right row, and all remaining columns will be used to return parameter values.

Not all family parameters may require the same number of lookup values, so individual family formulas may use lookup table columns in different ways.

Finish the Table!

When you exported the lookup table from the family editor, you only had one set of parameter values, so your lookup table will initially only have one row.

To fill out the rest of the table, you will use a manufacturer’s size chart (or something like it), and fill in the rest of the chart so that there are values for every item configuration the manufacturer makes (or which you choose to have in your family).

Just be conscious of units – look at the header, and make sure that you enter every table value in the units specified in the column header, doing any conversions you need prior to data entry.

Now we have a working lookup table; Revit knows what it’s called and where to find it, and we understand the logic of it.  All that remains is to create parameter formulas to feed data from the lookup table into our family parameters.

Pointing Family Parameters to the Lookup Table

It’s time to pull out our not-so-handy lookup table formula again.  This is the formula we will type into every family parameter that needs to use the lookup table:

       value = size_lookup(LookupTableName, LookupColumn, DefaultIfNotFound, LookupValue1,     
       LookupValue2, ..., LookupValueN)

We need to enter everything after the equals sign into each parameter in order to drive that parameter with the lookup table.

The command itself is size_lookup()

We will enter that verbatim.  We now know everything that we need to know to what we need to enter next:

LookupTableName = the PARAMETER name of the text parameter which contains our lookup table file name.

LookupColumn is the name of the lookup table column, in quotes, in which we want Revit to hunt for the value we want.

LookupValues are the value(s) we want Revit to look for in the first column (or first n columns) of our lookup table to decide which row from which to get the parameter value we want.

In the example table above, if we have a parameter for the outside diameter of our fitting, and the modeler specifies the fitting size in a parameter called Nominal Diameter, our formula in our family type editor would look like this.

      size_lookup(Pipe CSV,”SOD”,[stuff we’ll do later],Nominal Diameter)

The formula is the formula for the family parameter Outside Diameter, so it will return a value which your family will use as its outside diameter.

Pipe CSV tells it which family parameter contains the file name of the lookup table.

“SOD” tells Revit which column in the lookup table contains the Outside Diameter value we want returned (the quotes tell Revit that SOD is a text string, rather than a number).

Nominal Diameter tells Revit which family parameter contains the value we want it to look for in the first column of the lookup table, in order to determine in which row our Outside Diameter will be found.

We now need to tell Revit what to do if it cannot find the LookupValues fed to it by the modeler, and your approach goes back to your strategic desire either to cobble together an item as best you can, or to produce an item which will clearly be wrong, letting the modeler know there is no such item.

You can enter any value or formula you like in the DefaultIfNotFound field, as long as it is appropriate to the parameter type, which is “size”.  You cannot enter a text string or logical value.  If you enter a formula, it must return a number associated with a size (not an integer or dimensionless number).

Let’s say that we want to return a credible looking fitting if our lookup table doesn’t contain the lookup values the modeler wants to use, and let’s say that we’ll approximate our outside diameter to be 1.1 times the size of the nominal diameter.  We can now enter our default behavior, resulting in a formula like this:

      size_lookup(Pipe CSV,”SOD”,1.1*Nominal Diameter,Nominal Diameter)

If our manufacturer doesn’t make the size fitting the modeler wants, Revit will create one where the outside diameter is 1.1 times the size of the nominal diameter.

Once this is done, you’re finished, and your family is ready to use, at least as far as the parameters go.

With any luck, you’re no longer in lookup hell.

www.revirtualdesign.com