topic Re: Lookup table formulla error I can't spot in Revit MEP Forum

Lookup table formulla error I can't spot

GTisRuleX — Thu, 27 Oct 2016 17:33:46 GMT

So I've been using a lookup table to provide part names on some of the content I generate for the company I work for. I've used this formula dozens of times now (Or similar method). Occasionally Revit will throw this error;

You can see in the screenshot above that the formula actually works. Alternate Description is being populated with the correct info. This error only gets thrown when "OK"ing out of the part, or changing the Type Name dropdown to a different size.

"Alternate Description" is a TEXT field. ND1,2,3 are all instanced based. The Lookup Table loads with no errors.

This happens about 25% of the time I use this lookup method to fill out a part name. I can't square out exactly what it doesn't like especially considering it's functioning until you click OK on the part. Usually the repair is so many random things I try until it works it finally just works.

This has been a major frustration I can't solve and push behind me, any help would be greatly appreciated!

Re: Lookup table formulla error I can't spot

michael_coviello — Thu, 27 Oct 2016 21:05:07 GMT

@GTisRuleX-

Which build number of Revit 2016 are you using? This information is in Help-->About Revit.

If this same worked in a previous version, Can you post the previous version too.

When Revit throws the error, do you have a journal file that you could share?

Please IM me if you prefer. Thank you.

Re: Lookup table formulla error I can't spot

robert.klempau — Thu, 27 Oct 2016 21:51:08 GMT

Hello,

Can you upload the Lookup table so I can see what’s in there?

What I see is that you read text values from a lookup table. The size lookup mend to read length values no text values. There is the Type Catalog file for.

Size_lookup(SizeLookup, “”,”000”,ND1,ND2,ND3)

What is the name of the column you want to get the value from.

I see “” instead of a name.

Solution that always works:

What you could do is read an info_code value from the lookup table (2). (last column with the unique number (1))

See picture below:

Than based on the number of the info_code parameter, use a formula for your Alternate Description parameter and use the if statements to produce the right values. (3)

See picture below:

Hope this method helps you to move on and get your family right.

Re: Lookup table formulla error I can't spot

fabiosato — Sat, 29 Oct 2016 00:49:05 GMT

Hello,

Actually the formula:

size_lookup(Table, "", "000", ND1, ND2) extracts the text value from the first column, I got this info at Revit OpEd blog.

The problem in your formula is that you are using the text separator " in the middle of the text, to avoid this problem I use in text instead.

Re: Lookup table formulla error I can't spot

GTisRuleX — Mon, 31 Oct 2016 18:30:16 GMT

Sorry, I've been out since Thursday. So as mentioned above, searching for "" pulls the first value from the lookup table (The unnamed column to the far left of a lookup table.)

Here is my lookup table.

The problem doesn't appear with my lookup table at all, I've also tried to use "FAIL" and both throw the same error in the first screenshot.

I also removed the quotation marks from the .CSV.

Re: Lookup table formulla error I can't spot

GTisRuleX — Mon, 31 Oct 2016 18:33:40 GMT

I have 27 different end conditions on 1 part that adjust in diameter and cut length that need to populate based on the connector sizes. This method would require one heck of an alias list but I like the concept of it. Something I need to remember in the future! Thanks for sharing!

Re: Lookup table formulla error I can't spot

GTisRuleX — Mon, 31 Oct 2016 18:34:38 GMT

I removed the quotation marks from the lookup table and reloaded it, it's still failing. What's frustrating, is I've done this about a dozen times, and seem to have the same struggle every time I try to pull the first column.

Re: Lookup table formulla error I can't spot

GTisRuleX — Mon, 31 Oct 2016 18:39:06 GMT

Update7 R2. It doesn't crash so there's no journal entry. It just tells me an invalid formula in the "" type. The part that drives me nuts is I can copy/paste this formula from families it works in just fine I've created before, but there always a struggle.

Re: Lookup table formulla error I can't spot

robert.klempau — Mon, 31 Oct 2016 19:15:46 GMT

Hello,

can you upload a picture of your .csv file after you opend it in a tect editor?

It can be that you have a lot of comma's at the end of your file.

Re: Lookup table formulla error I can't spot

robert.klempau — Mon, 31 Oct 2016 19:18:28 GMT

Do you see something like this in your csv file?

Re: Lookup table formulla error I can't spot

GTisRuleX — Mon, 31 Oct 2016 19:50:51 GMT

Nope, it looks as expected. When I first started using Lookup tables I fought the heck out of EXCEL until I could get it right on saving as .CSV. I'm pretty familiar with the formats now.

Re: Lookup table formulla error I can't spot

robert.klempau — Mon, 31 Oct 2016 20:01:45 GMT

Ok,

Take a look at my family and lookup table and compare yours with mine.

Re: Lookup table formulla error I can't spot

GTisRuleX — Mon, 31 Oct 2016 20:14:26 GMT

All of your returned values are in quotations? I tried quoting the entire comment (avoiding the inch marks to simplify testing.)
Wouldn't this have the same effect?
"1x7/8x7/8" for example?

What's driving me nuts, is the expression is actually working. The value updates, I just can't "OK" out of the part without it throwing the "invalid formula".

Re: Lookup table formulla error I can't spot

robert.klempau — Mon, 31 Oct 2016 20:48:51 GMT

Let me have a look at your family.

If you don't want to post it publicly you can send it to me by mail.

Re: Lookup table formulla error I can't spot

GTisRuleX — Mon, 31 Oct 2016 21:45:29 GMT

Holy cow, finally figured out what was going on with the help of my boss.

I created the lookup table parameter AFTER copying the family Type. So my statement worked on the instance in the part builder at the time, since the new parameter value was only added to the single instance. Since the lookup table name wasn't physically filled out on the duplicated types, it would cause it to fail on a nested part in the library, not the one on the screen I was editing at the time.

Copy/pasting the Lookup table name in every instance globally fixed the issue.

I can't seem to find a way to attach the family here, or I would post it just for others' information moving forward.

Re: Lookup table formulla error I can't spot

fabiosato — Mon, 31 Oct 2016 23:58:39 GMT

Hello,

To avoid this problem in the future, if all types use the same lookup table, input the table name between quotes in the formula area, so all types will use the same table!

Re: Lookup table formulla error I can't spot

GTisRuleX — Tue, 01 Nov 2016 00:36:01 GMT

That is genious!

Re: Lookup table formulla error I can't spot

thebimqueen — Fri, 02 Dec 2022 02:58:13 GMT

Tks for posting this, so helpful! I had the same issue spent hours trying to figure this out!