The Power of Revit Lookup Tables

RALPH SCHOCH: Hello, everyone. I'm Ralph Schoch from Victaulic Company. I don't know if anybody knows Victaulic here, but we're a pipe fitting manufacturer. But the reason I'm here today is more to talk about what we do with our own BIM team, and how I'm using lookup tables to build content, and how we've used them for quite a long time. So I'm excited.

I'm sort of curious, how many people in here currently use lookup tables in projects, or in families? OK. How many people have never used them yet? That's OK. So it's pretty cool, right? I mean, so there's a lot you can do with them.

And I was hopeful that we'd get also a lot of people in here that have never used them before. I'm going to go over some really basic things that look basic, but you can do a lot with it. So I took a really complicated concept that we were using, and I simplified it into a really simple family. And then I'll show you how we leverage that same thought process into how we build all our content.

So how many people here build families? Everyone. OK, then you're in the right room. Because if you don't build families, you probably need to go in another class. I will say, I had a major meltdown on my laptop a few minutes ago. And it seems OK. And for some reason-- I know Amanda's close, and I'm trying to connect to her phone, which I'm not sure why. So we're going to do our best. But I think it'll be OK. I have faith. And then after this presentation, I'm going to get rid of this laptop.

So we're all here to talk about lookup tables and what we can do with our content, right? Try to get at more of the data. Obviously, Dynamo is hot. You can do a lot of things with Dynamo, but there's still a lot of things you can build just into your standard families that control data, and you don't have to worry about constantly running a Dynamo script to keep the data up to date. So that's what we're talking about.

So we're going to talk about some of the issues we've faced over the years with lookup tables. We're going to talk about part numbers. I say "part numbers", because that maybe means something on the piping side for us, but part numbers could mean anything. It could be text. It could be a link between one family to another. Do you ever have, like, 20 parameters in one family and you had to link those 20 parameters from that family into the other family? And you go through all this linking process? Well, you could technically do that with one parameter, if you set things up with lookup tables. So I'll show you that a little bit. I'll talk a little bit about simplifying complex families, sort of what I talked about there. And just add data and functionality.

This is a quick slide on us. Let's see if I can just get rid of that. Basically, the center of our business is content. Because all our detailers and all our modelers for projects-- if you want good content, you can't do any of this other stuff that we get paid to do.

So here's a quick one. So before you get in the lookup tables, you got to sort of think about some basic parameter stuff. Is everybody here are good at writing parameters? So-so? Yeah. I'm not-- you know, I've probably gotten a little better than I was. But I used to use the internet a lot. Because the internet's good. You steal parameters off families. One of the things is checkboxes. So everybody uses those. So I always go over this with my team when we're building families. When you have a 1 equals 1 condition in a formula for a checkbox, what does it do? Is it yes or no?

It's true. Yes. 1 equals 1. You can see what I did up there at the top here. 1 equals 1 is checked. 1 equals 2 is unchecked. Well, with lookup tables, there is a 0 value that you can report out if it finds nothing. So 1 equals 0 is unchecked. So I use that trick quite a lot. And I'm going to show you how I use that when I'm pulling data out.

Also, with yes no parameters, you don't have to put-- just for not, if you wanted the opposite. So I could type "NOT" in there and it would-- if I put not green in here, when I checked that unchecked green, it would-- or actually, let me rephrase that. On green, if I put not blue, when I check or uncheck blue, it would obviously be the opposite. And we'll play around with that then too.

Obviously, I go on the internet. I always like this guy. I never met him. So hopefully, someday I'll get a chance. But he's got a lot of cool formulas up there. So whenever I'm trying to do something, I always go up and check that out. So I don't know if he's in here. But if he is, it would be cool meet him.

So let's go to Revit and actually do some of that stuff.

I'm in a project, and this is the concept. Very simple. So inside this family, I have three different options here. So when I uncheck red, and I move my mouse over, you see how these two line up and they move? So you have green and blue, they move. So if I come in here and select this, and I uncheck green, now blue is on the top. If I uncheck everything, it's an invalid condition. Red. It just shows red.

So what I'm doing is, I'm using a lookup table to control this functionality. Something as simple as this. Let me show you a little more, and we'll talk about it. I just want you to fully understand what's going on.

So if I edit this family for a second, let's take a look at that. And I go into the plan view.

See what I did here? So I have some 3D text.

What I'm doing is just moving that text around. So this is 3D model text. What happens if that was 3D geometry? So when you build your families, what we like to do is, a lot of times, we'll build the components unconnected or unrelated to each other. So you build everything with an origin point, let's say, off to the side or wherever it makes sense for you. And then you put dimensions that physically take each piece and move it into the correct positions. So with lookup tables, what you can do is you can basically populate many parameters and many data points for x and y coordinates to get things to line up properly.

And then what I also have in here for this is, I have a visibility parameter in here, and that's set back to those checkboxes.

So when I go in, or if I go back out to the project again-- and then you can see here, on this piece of object, what I did is, the description on that is linked to a text parameter I have in here. A custom description parameter.

So let's take a look-- I'm going to show you what the lookup table side of this thing is, and how it's functioning, and show you how you can leverage that.

So what I have in my class files here is, I have a start and a completed one. But if I open up the started one, let's take a look. So I have red, green, blue. And I have the color up here. So these red, green, blue represents a bunch of different geometry. Let's take a look. So what I want to do is, in here, I'm going to utilize these checkboxes, and as I check these and uncheck these, the visibility of these objects turn on and off. Now we're in the family editor, it's not as obvious. But that's what's basically happening.

When we talked about it before, 1 equals 1, it's going to be on. Yeah, it's locked on. 1 equals 0, it's going to be off. What you have to do if you want to put in or? So if you put in or, or green, in this case, it would be, let's say, red comma blue.

So now, if I have any of these checked-- so checkboxes are a little different. Yes no parameters. So what we want to do is we want to take a look, and what I want to do is take a look at what I want to put in here. Remember when I was changing it, this description was going from-- it was actually changing the text as well in the project. Did you notice that? So has anybody ever had to build a text parameter that they wanted to drive with formulas? What's the biggest problem with a text parameter with a formula?

AUDIENCE: You can't use all the text parameters.

RALPH SCHOCH: What's that?

AUDIENCE: You can't use all the text parameters.

RALPH SCHOCH: Yeah, you can't group or concatenate. Yeah, you can't concatenate. It's a pain in the-- why isn't that available? Does anybody know?

AUDIENCE: [INAUDIBLE] is under review.

RALPH SCHOCH: Yeah, when it comes, it's going to be a really awesome day. Because I'd use it all the time. But this will get you around that super easy. So you can use a lookup table to get around that. And that's what I'm going to show next. Because basically, when I went out to that project, I don't know if you noticed-- I'll actually jump back out there.

So back out in that project, when I selected this and I unchecked, see how it's actually changing the text? Well for this, there's only three options. You're like, oh yeah, I could write a formula for that. Not really that cool. Well, I have families here. I have a butterfly valve. You know how many options that thing has? And some are available and some aren't. So you have to let the user know they're invalid. So what we have for that piece is we have a build material description parameter down here that we use. So as I check these things off, like with a memory stop, you see it change down there? If I check off chain wheel, it changed again. If I come down, maybe put a square nut on it. Oh, no you can't get that with a square nut, because that isn't available.

So depending on what the different seats are, you can change this out and make sure people put the right pieces in. So it's creating quick functionality, making sure the content is valid.

So let's jump back over to that family and work on that parameter a little bit-- or the formula for the parameter.

So what I want to do is, I'm going to take a look at-- I'll get my cheat sheet out so I don't mess up. But let's go back to this PowerPoint here for a second and what we're going to do. So in this case, has anybody used lookup tables? Do you know you can put text in a lookup table?

So type catalogs, text all day long. Not a problem. But lookup tables, I never knew I could put text in a lookup table, until a very, very smart person told me that. And once I saw that, then I started leveraging it in different ways. So my plan here is that I found that in this column right here, this is the column that you tell it what data field in the lookup table to go look for the data. Well you see what I did there? I put double quotes in. Well there's a secret hack in Revit. If you put double quotes in, it gives you this first column that you never could get to before. If you don't know that--

Yeah, so it's pretty awesome. It was pretty crazy. So how did I-- OK, now I get it. I can do a lot of things with that. Well now, I did one other thing. So we build a lot of metric and a lot of imperial content. Well, what unit is the same between metric and imperial? Text. Yeah, text might be the same. What about maybe angle? At least we actually use degrees, and they actually use degrees. I think it's the only unit out there.

So that's what I did. Look what I got here. I have an angle parameter, an angle parameter, and an angle parameter. You see what I have? I have 1s and 0s. What do you think they mean?

Yes and no, on and off. So these 1s and 0s are checking and unchecking my yes no parameters. Or, actually in this case, what they are doing-- they could check them, but in this case, if I have red checked-- OK, red is 1. If I have green unchecked, which is 0, and blue unchecked, in the bill of material, or in that text field, I want red. Pretty slick, huh?

AUDIENCE: [INAUDIBLE]

RALPH SCHOCH: That's the concept. So I tried to break this down in some stupid, simple way. And the families in the set, you can take it, and you should be able to get it. The trick will be is, if you're copying formulas from one parameter to another, you have to be very careful of your units. Because if there is degrees in here or inches in any of these values, you're going to have it break. So let's take a look.

Let's take a look at writing up that formula here. So I'm going to control tab.

I said I can use a text parameter. I can drive it. So I'm going to type in, size underscore lookup. That's good. But what am I forgetting? I need the lookup table. I haven't put that in yet. So where do you put the lookup table in? Does anybody notice that this button's been here for all the architectural components and things? Walls, doors, windows-- not walls. I shouldn't say walls. That's a system family. You can call me out on that.

So in here, what I need to do is, I need to import a lookup table. So I actually have one made up, and I'll talk a little bit about the tribulations and issues you can have with lookup table text files. So let me take a look here. Class files, and in this case, my lookup table name is called lookup table AU. OK, and I'm going to select that file. And now it's in here. So let's take a look at what's in that file.

So we're going to go back out. And I'm going to take a look at what that is. So does anybody know what a CSV file is? .CSV?

AUDIENCE: [INAUDIBLE]

RALPH SCHOCH: Yeah. You can open them up in Excel. One interesting thing if-- is anybody here from Europe? This won't work for you, or it's going to be a little more challenging, depending if you use commas as periods, or vise versa. I don't know if you realize that. It's a real challenge. Because our colleagues-- we have global colleagues, and we get in heated debates about commas and periods. But they use a period instead of a comma, which drives us nuts.

So basically, what you have to do is, you create-- this is a text file. You can open it up. You come in here and edit this thing. But what you can also do is, you can start with Excel and make this thing. And basically, what you have to do with a lookup table, you put in the different parameters or the different data fields. So like I have over here in the PowerPoint--

Like I have in this PowerPoint, what you have to do is, in this case for the text to get this first column, I can call really these first three columns anything I want. I can call them Fred, Larry, and Bob, or George and William, or whatever. Whatever these first couple columns that you're using to organize and separate your data, they technically can be called anything. Because what it's doing is, in this formula, it's looking at the first-- this value here, comma, this value here, comma, this value here, directly relates to these first three columns in this lookup table. And the fact that this double quotes is pulling from that first field, I don't have to worry about it. I don't have to actually worry about what's beyond here, What's beyond here are the lengths that's moving that thing down.

So let's go back to Revit. So I'm loading that in. Now that that's in-- now, here's another trick. What I'll also do is, I'll come in here and I'll click OK once that's loaded, and I'll get out of it. Because notoriously, you make a ton of changes to parameters, they don't work and now you get to redo them all again. And that's always disappointing. Another trick, what I'll also do-- we've had issues over the years-- I'll take this lookup table here, and I'll put TXT on it.

I don't have it open. That'll do it to me every time. Let's close it.

Let's see if I can rename it now. TXT. Yeah. So the other thing you want to do is, if you really want to be careful, you want to change out to a text file and you want to open it up. I've had people that are a little not maybe as careful as they need to be in that Excel file. And if you leave a bunch of trash off to the side or off to the bottom that you typed in there once, and you put some data in, you'll get erroneous commas and stuff in here. It won't look clean. And the trick is-- you'll get your blow your mind trying to figure out what's going on-- so just edit this text file and go in and see what it looks like.

And in this case, it looks pretty good. It's not too bad. And then you can come in here and just recall it as CSV again. And now obviously, your software can read it. So let's take a look. We're going to write this out. So it's going to be size lookup-- size underscore lookup. And that's natively inside Revit. That's what it's going to use to call up this lookup table.

Now, the next field is the lookup table name from what you see in your manage lookup table when you go in there. You see what I did is, I just put it in quotes. A lot of times, people will put the lookup table name in a parameter. You may have seen that. That's good, but it's also very dangerous. Because a lot of times, they won't set it with a formula. And then a user can accidentally change the type and change, or match properties-- match type properties-- and now break your family, because it's not pulling the right lookup table. So a lot of times, I'll do one of two things. I'll hardcode it like this, put it in a quote like this out to the actual lookup table name. Or, if I put it on a parameter, I'll always put a formula on that parameter-- two quotes, list out the lookup table name, problem solved. You won't have issues with your content.

So the next thing I have to do is put comma and the field that it's trying to find the data in. The name of the field that it's trying to find the data in is, in this case, quote quote. because we want the first column in the lookup table that we didn't know we could get, and then comma.

Now, what happens if it can't find anything? What do I want this text to say? What do I want this value to be? In this case, I'm going to in quotes, it's going to be invalid. Comma. And then, what data do I want to look in the lookup table to see if that value exists?

So remember, we had 1s and 0s. Now what happens-- I'm going to just do this. I'm going to put 1 comma 1 comma 1. So what is that going to give me? RGB. Now, that's all I need to do. If it's going to work, we're going to hit click. We're going to click and we're going to find out if I did it right.

So what else do we see what happened? You see the degree signs popped in here? So you see how it says RGB, or are red, green, blue? If I make this a 0, now it's red blue. If I make this one a 0, now it's red. What's that?

AUDIENCE: [INAUDIBLE]

RALPH SCHOCH: It puts degrees in. So Revit-- what happens if I put 0, 0, 0? Invalid.

So I never put the units on anything in Revit when I'm writing formulas. Let Revit figure it out. It'll put them in, you won't mess up. The question was is that, did you put the degrees in? I didn't see you put it. Revit put it in.

Obviously, when we're tying to other values or other parameters in the family, then we have to be a little more careful. Because spelling counts. Capitalization, all that stuff. So here's the question.

How do we link these three checkboxes into these three 0s, so that when we check this off and on-- so what I can do is, I can come into this first one. And what happens if-- I always put brackets or parentheses around when I create a formula. So that way, I can see-- and instead of saying in, I've got to say if. So it's going to be if red-- because it's RGB, red. I have to make sure I spell it correctly. It looks like I did. It's going to be comma. Now, if it's red, that means it's true. Otherwise, it's false. So if it's red, true is, for us, 1. False is 0. And end bracket, end parentheses. And I got to put one more at the end.

AUDIENCE: [INAUDIBLE]

RALPH SCHOCH: Yeah, and actually, I think I messed up. Hold on. I didn't put it in the right place. It's going to be right there.

So now watch what happens. It's invalid. And I check the first one. So it's seeing that. So I can just do that for the other ones. See, now it's easy. I just grab this formula from there to there. I copy. I never retype anything. Paste it in there. Paste it in there. And now we go from red to green to blue.

Now look at that. In any order. Pretty cool, huh? Anybody ever try that?

AUDIENCE: [INAUDIBLE]

RALPH SCHOCH: What do you mean by--

AUDIENCE: Instead of looking up the degrees.

RALPH SCHOCH: OK, yeah. So yeah, there's no yes no Booleans in lookup tables. That's why we have to use degrees to simulate that. There is such a thing as an integer. The problem with integer is that, if you use an integer and you bring it into Revit, I've had on the earlier versions, it actually turns it back into inches. I didn't like that. So I don't use integer in lookup tables. I use angle. That works.

AUDIENCE: A type catalog works.

RALPH SCHOCH: Type catalogs, yeah, they're perfect. So what's neat about this though, since this is an instance parameter, now you have one parameter you can tie between families and push data around. Because we do it all the time and pipe fittings. For size, nominal diameter. 6 inch, 3 inch. My fitting changes a ton of times here. And I just change the size, and now I have 20 families that changed. You just do that same thing and get-- because to me, I never use type. I hate a parameter that's like a type-- trying to reach for it allows you to select what type an object is inside a parameter.

Yeah, that's always a challenge. Because it's not as controlled. This is 100% controlled. It won't break.

So we got that going for us. What happens if we want to put lengths in here?

So we click OK. It took it. So what I can do here now is just select this first parameter. And what I'm going to do there is, I'm going to call it-- let's make something up. So it's going to be, for red, it's going to be red length.

That's going to be an instance parameter. It's very important that these are instance parameters. And this is going to be green length.

And then this one here is going to be blue length.

And I think I missed instance. Now, how do you know if you missed it? Everybody knows that. I thought I did miss it. But they're all instance parameters because they all say default. That's how you know. So let's think about what type of formula do I need in here?

So in this case, what I want to do is, as I check this stuff off, I want to pull the lengths from the lookup table as well.

So what I'm going to do-- I never retype anything, I always copy and paste. Actually, before, we're going to set this RGB-- we're going to put it in the correct orders, because we can. RGB, red, green, blue. And now that's red, green, blue. So we're all good there. And I'm going to paste this in here. And it's not going to work, because there's going to be a handful of reasons. So I don't want the first column. I want the location. So now we're going back into this.

So inside here, I have in my first columns, the three options, the three switches. Over here, I have all my different lengths. So whatever is important to you, this can be inches, or it could be angles, or whatever. And then what you can do is, you can come in and we'll just populate those values. And now we'll actually pull those lengths in there as well.

So to do that, all I have to do is look at my lookup table and make sure that I'm right. So if I go back into here, I always have that open. And I'll show this then too. Let's go here, AU lookup table. So in this case, we're going to take a look at this, and this is going to be the length red. I use this piece of text right here. Length green right here. And length blue right here.

So now, if I come back to the family, I just want to change it right here.

So it's going to be length green, and then, in this case, I need to make sure I'm very careful and pull out all the quotes. And I'm going to make it 1 foot. So when it fails, it's going to fail at 1 foot. It's going to actually move that down.

Now I can just copy that down through. And just change this from green to red to blue.

And you've got to spell it right, otherwise you get an error.

So that's pretty easy. So now, if I come in here, and I have them all checked, as I check and uncheck these things, see it moves them all down here. And it actually is turning the visibility off as well. Because the visibility parameters are linked to those pieces. And I'm changing up there as well.

So it's that idea that you're moving geometry around. And what I would probably do is, instead of just doing length, what I'm doing, you could do x and y on each part and change that around. For pipe fittings and things, what we've done is, we've made fittings that can make any fitting. I can make an elbow that makes every elbow that you can manufacture. Just one simple family. What we've also done-- and then I can just load this in the project. Let's just make sure it works. Because we know things don't always work. Yeah, we're good.

Invalid. Green. Red.

So let's take a look. Over here, some of the other things that we've done to get a little more advanced-- so I have a whole pump drop. And I can take this and change this from a 4. One of the products we have, instead of just selling one individual piece, we actually sell the entire pump drop. And you can just drop that in. It's a vibration isolation package. And then you can just-- you're done. It only takes a second to place. But to make it super easy on the user, you just select the piece. You come here and you change that, and it automatically resizes for one parameter. So one through the coupling, the suction diffuser, everything, and change that up. Same thing here. I can make this, let's say, a 4 inch. What happens if I make this a 3 inch? Same type of thing. It's an invalid size. So you flag the user. Hey, there's a problem. Similar to what I showed you.

But you just come in here and change this to a 6 or something. Now resizes.

Same thing here. This is our PRV. You think about some of this stuff, it really makes it really easy to change out. Does that help?

AUDIENCE: Does that CSV file then live in a family or does it need to stay in the file folder?

RALPH SCHOCH: Yeah, that's a great question. It used to be-- there was a lookup table folder, and you have to have that lookup table folder there. That stopped in 2014 for MEP families. And I think for all the architectural stuff, it was '15. You know what's funny? On the Victaulic website, I still put the lookup tables up there. Because we get calls like every week for lookup tables. The people get them, they put them in their folder, and they don't do anything. They just make people happy.

The guys at the office are like, why are you doing that? Don't worry, it'll fix the problem.

So sometimes, the solution to things isn't necessarily the obvious solution. Because we were training everybody at the time. But it was more IT people who knew they needed them than it was actual users. Because the people-- as most of the people in the room here probably know that stuff's like that.

So that's pretty neat. Let's jump back here to the PowerPoint here.

This is a couple tricks and things that have tripped us over the years. Like I said, the lookup table name right here, put it in quotes and put it in a formula here. If you don't do that, you're really taking chances.

The default value on these families for the lookup tables-- you don't ever want your family to fail. Everybody knows that. You've got to control how it fails. So you saw when I made that vibration isolation pump package fail, it did something and it made that graphic show. You want to deal with that here. And what we usually do is just make it the smallest size or throw things out in space or do whatever. You just have to make it obvious.

So that's another thing. This is a neat little trick too. Down here, inside families there's this is custom parameter. But this applies to any family. If you want to create a parameter, you can say that, if that value in the lookup table doesn't exist, it's 0. Then turn this checkbox on. So 0 equals 0 is what? Yes. So that's that same trick that I was talking about. So I have this in the data set as well. You can check that out.

Another thing that we've done is, I've actually-- the value here inside the quotes, this Pipe OD looking for a text field in the lookup table called Pipe OD. I'll actually put an if statement in here based on checkboxes. So depending on how complicated, I can reduce the amount of formulas I need, if I put even some if then statements inside this lookup table.

So let me show you what that would be like.

So this is that elbow that we made that makes every elbow.

If I come in here, you can see what I did. Let's see if I find it. Yeah, right here. Size lookup. So in this case, it was a flip condition that the component needed to flip. So with Revit, with elbows and fittings, I got to draw one way and then the other way. It's a real bummer if you're building pipe fittings. Just go home. No. But it can be a real drag. And in this case, we had a condition where it says, hey flip. And I actually did that in the lookup table. That saves a ridiculous amount of thinking to try to get that to work. So think about that. That's also a good little trick too.

And there's our pump drops and things that we have.

So I hope this helps out. We've talked a little bit about some of the common issues we've had. How to go through and really look at your content and try to put lookup tables inside. And then obviously, you can control that data and get the data from one family flowing into the other so things get connected.

Are there any questions?

AUDIENCE: Did you have a handout?

RALPH SCHOCH: Yeah, actually I did. I'm not sure why it didn't load up. So if it's not there, in probably like an hour or so, or two-- I don't know what's going on with that. But I'll go up and upload it again. But yeah, I did have a handout. I had the PowerPoint up there. I also have a bunch of families. So the family I showed today, I have that up there. So you can just copy and take that stuff out. The other thing is, up on our website, victaulic.com, we have all our families up there as well. So you can always open that stuff up and strip some of that out.

I know a lot on the architectural side-- and probably a lot of people are in architecture here. I'll also reach out and say, if you're trying to do something, and you're trying to get a certain thing to work out, let me know. I'd like to hear about it. And actually, I'd like to take this concept and really try to bring it out to more people and try to talk a little bit more about how you can really leverage lookup tables in our business.

AUDIENCE: [INAUDIBLE]

RALPH SCHOCH: You don't even have to. So the question is, between metric and imperial. What happens if you have metric content and imperial content? Do you need two different lookup tables?

AUDIENCE: [INAUDIBLE]

RALPH SCHOCH: Yeah, Revit has the units. But at the end of that lookup table, if I go back, I'll show you this. That's a really good question.

AUDIENCE: [INAUDIBLE]

RALPH SCHOCH: Yeah. Well-- we eliminated a whole set of content one day when I was thinking about something. You see how that says inches? What happens if you make that millimeters? Do you know what's the multiplier from millimeters to inches?

AUDIENCE: [INAUDIBLE]

RALPH SCHOCH: Yeah. Well, 25.4 multiplies to an even number every day. But when you divide by 25.4, you get change you can't get rid of. So I was forced to put millimeters in my lookup table. But what I do is, I have a 1 inch size. So my 1 inch size, I put it in as millimeters in here. I say millimeters. I put 25.4. Revit sees 25.4, converts it instantly as 1 inch.

AUDIENCE: [INAUDIBLE]

RALPH SCHOCH: Yeah, that will work too. Yeah. It works in the lookup table. So we had a metric database of our pipe fittings, and an imperial. In that one day, I deleted all the metric and got rid of it. Didn't need anymore. Just because I was-- we like to use inches all the time. But sometimes you've got to go to the dark side.

Any other questions? Really, that's a good one. Any stumping questions? I'd love to have some real stumpers.

AUDIENCE: [INAUDIBLE]

RALPH SCHOCH: Yeah, I got one for that too.

My everything-- a lot of things around pipe. So for pipe, 6 inch pipe is 6 inch pipe, globally. 10 inch pipe is 10 inch pipe, globally. It's a wonderful thing. In metric, it's not. There's 100 millimeter, some countries will call it 101.something. It's just crazy. It just doesn't make sense.

It'll be the same diameter pipe, but it'll be a different number. So what I do is, I take my data set, copy it down here, copy it down here, copy it down here, same exact data set, copying it down a bunch of different times. And then I just change the values out. But now, I have a trick. I don't manually type. So you know that you need to change text in these lookup tables a ton. So instead of manually typing data, I create a little macro. And I put that in the data set too. So do you ever use if then statements? Maybe?

So let's take a look. I have a little macro here. It's just text. You can load this into your spreadsheet. So what it does is, if it's this-- so if it sees 14, it puts in 350. If it sees 16 inches, it puts 400 in.

If you need a hard conversion--

I don't know if you noticed, this is the most awesome part. This will save you oodles of time. So we made this little script that I can run. And I can go down through the table here, control shift R changes-- yeah, I think it's control shift R. So if I hit control shift R-- hold on. That puts in something else. Cancel. I don't use this every day. So you gotta give me a little bit of a break. So nominal imperial to nominal metric, it's control Q. So if I control Q--

That's that same text macro. It's in the data set. You can just take that same thing, tweak it a little bit, use it to go down-- it was sort of funny. My programmers took this thing and went crazy with it, because I started it. And they made a tool that will actually bump down through the whole table at one time. So what I do is, I just sit a little thing on that one key, and just go away and then come back. Because I'm doing is on database files that have hundreds of them. And then they tell me that they made a new feature. It's like, yeah well, that's OK. I still got it done.

Does that help you at all? Yeah. It's not a perfect solution, but it gets a lot of boring, tedious work done.

AUDIENCE: [INAUDIBLE]

RALPH SCHOCH: No, you'd have to change that. Yeah, you'd have to change it. So if you want to use metric, you'd have to go back into here and change this to millimeters. And then you'd have to take that little macro I did. I have one that divides by 25.4 and multiplies by 25.4. And I would just go down through this column, it would multiply. Done.

Anything else?

AUDIENCE: [INAUDIBLE]

RALPH SCHOCH: Yeah, same exact thing. You have to have the hashtags in between. So this is the parameter name, hashtag, it's a length parameter, and the units are in inches. That's consistent. If you don't know what to put in there, how do you get that out?

AUDIENCE: Google it.

RALPH SCHOCH: You could Google it. Perfect. Google it. You can also export a type catalog from the family. And then that'll tell you.

AUDIENCE: [INAUDIBLE]

RALPH SCHOCH: Yeah. Anything else?

I'm happy-- Hopefully I taught you some things today. Totally reach out to me. I love a good challenge on some of this stuff. And enjoy AU. Thanks for your time.