topic Re: Some tricks with this lookup table.. in Revit MEP Forum

Some tricks with this lookup table..

Secttor — Tue, 14 Feb 2017 20:43:56 GMT

Hi all,

I wonder if my wish is possible by using lookup table instead of a generic if(and.. formula.

I'm trying to take in consideration 2 values (B & C) to get values from the lookup table.

Below is the screenshot. Thanks for any suggestions.

Re: Some tricks with this lookup table..

Secttor — Tue, 14 Feb 2017 21:28:22 GMT

I quess I'm on my way.. but I don't know how to take in consideration a second value from the lookup table for this..

I mean.. I need another column after "DN" with values that must be taken in consideration for my formula.

Re: Some tricks with this lookup table..

fabiosato — Wed, 15 Feb 2017 02:05:59 GMT

Hello,

If I understand it right, you want to do the search using many inputs, you can use as many as you wish, but the value must match, lookup tables does not work with ranges.

Re: Some tricks with this lookup table..

Secttor — Wed, 15 Feb 2017 18:52:49 GMT

A bit more clear : at this moment searches on the second column ("DN") in the lookup table for a value identical with the one from parameter B. I wish to be able to have another column in the lookup table and search for a value from DN (belongs to B) and in the same time for a value from the new column (belongs to C). This way..the value returned must be validated by those 2 parameters.. B & C..

Re: Some tricks with this lookup table..

robert.klempau — Wed, 15 Feb 2017 21:11:21 GMT

Hello @Secttor,

The way Size lookups work is:

let's take Value 1 as an example.

we are looking for a value in a column that has the name FOD.

we search for a value in column B (Parameter Search_column1) then for a value in column C (Parameter Search_column2) an then for a value in column D (Parameter Search_column3)

If the combination is found, then it will return the value for column E.

If not, he returns the value 10mm.

If you want another search column, just add a new column in your CSV file between column D and E and add a parameter at the end of your size lookup formula.

When I look at your formula for Height, it will return 500 if B or C is greater than 25 because the size lookup can't find the value 0 in column B (DN)

please see the attachment for the family I created for you.

Re: Some tricks with this lookup table..

Secttor — Thu, 16 Feb 2017 18:57:50 GMT

I know but can you tell what's wrong in my attached rfa? I can't see the issue..csv is in the family.

Thanks!

you saved your sample in a later version of Revit, 2015 here.

saw that your csv has a single column.. does this matter?

Re: Some tricks with this lookup table..

Secttor — Thu, 16 Feb 2017 20:30:22 GMT

Ok.. managed to get results finally. One single remark : I have a text parameter that must be taken in consideration in order to get good results..so the text parameter is only a search criteria.

Revit exports that parameter as : Sample##OTHER## . I want to know how should I fill it in the .csv in order to have in readable in Revit.

It's an important text parameter that will be linked also.

Re: Some tricks with this lookup table..

robert.klempau — Thu, 16 Feb 2017 21:33:37 GMT

Hello Sector,

you can't put text parameters ' Sample##OTHER## ' in a lookup table.

A lookup table can only have length and angle parameters in it.

you have to translate that text to a number or integer and search for that in of the columns in the Lookup table.

Try also this trick:

add a column with parameter 'info_code' to the and of the lookup table. (info_code##length##milimiters)

look for that info_code by using the Size Lookup. info_code = size_lookup(Lookup Table Name, " info_code ", Nominal Diameter, Nominal Diameter)

then use a formula to get the text back.

for example:

Text = if(info_code =1, "Text1",if(info_code =2, "Text2",if(info_code =3, "Text3","Unknown")))

You could also read column A from the CSV file. You are allowed to place text here.

Text = size_lookup(Lookup Table Name, "", "Unknown", Nominal Diameter)

Hope this helps.

Please let me know if you managed.

Re: Some tricks with this lookup table..

fabiosato — Fri, 17 Feb 2017 02:26:15 GMT

Hello,

You can extract text value from the first column, check the link below.

https://forums.autodesk.com/t5/revit-mep-forum/lookup-table-text-value/m-p/4888808/highlight/true#M22623

Re: Some tricks with this lookup table..

Secttor — Fri, 17 Feb 2017 19:50:31 GMT

Yes.. I've managed it..I've copied all the values that should go in the "A" text parameter on the first column of the .csv (without header) and extracted the values in A as text by using your formula : Text = size_lookup(Lookup Table Name, "", "Unknown", Nominal Diameter)

My second remark is related to your first conversion formulas, below. The ideea is that getting it back by using info_code=1, "Text 1" involve work and this formula must cover a large ammount of values, for this moment 150 but will grow; do you think it's reliable to have kilometers of if(info_code=x,Textx) ?

Try also this trick:
add a column with parameter 'info_code' to the and of the lookup table. (info_code##length##milimiters)

look for that info_code by using the Size Lookup. info_code = size_lookup(Lookup Table Name, " info_code ", Nominal Diameter, Nominal Diameter)

then use a formula to get the text back.
for example:

Text = if(info_code =1, "Text1",if(info_code =2, "Text2",if(info_code =3, "Text3","Unknown")))

Also..my main question is related to the future..do you think lookup_table (as a concept) will survive ? I want these implemented formulas and solutions to survive with minimum maintenance also in the future and coming Revit versions. Is lookup_table used in this moment or it has been replaced with if(and.. formulas?

Thanks!

Re: Some tricks with this lookup table..

fabiosato — Sun, 19 Feb 2017 01:01:45 GMT

Hello,

I suggest you to use a second lookup table instead of the if formula, another reason is that this way updating it would be easier!!!

I have plenty of content relying in this feature and never heard about Autodesk changing it.

Even if it happens, the benefit is so huge that it worth.

Re: Some tricks with this lookup table..

PaulFrancoisStorm — Thu, 05 Apr 2018 14:17:00 GMT

Is it possible to skip an input value and to have it returned instead? In the case below I want the angle returned base on some input values, but it is a recursive argument as I must input the angle before getting it back again? It defeats the purpose.

SELECT D FROM TABLE

WHERE

B = 1

AND C = 2

AND D = * wildcard meaning any value

AND E = 3