Dynamic material costs

wokka · 13 Mar 2013, 09:22

Hi All,

Sorry if this has been covered, I have searched, but not been able to find anything.

I am building a dynamic component library of items I use regularly, for example, I use 100mm high flooring with various floor finishes.

Now I am having trouble with my IF formulas for multiple material selection

I have entered the following formula under a custom attribute called "Top_Floor_Cost":

=if(Material="Color_000", 60), if(Material="Carpet_Plush_Charcoal", 70), if(Material="Wood_Floor_Parquet", 75)

Now when I select the appropriate floor covering (eg. "Carpet_Plush_Charcoal" from the drop down menu in the Component Options widow, Nothing shows in the Top Floor Cost box, but, In the component Attributes Window, I see #=,70, So the Formula works, but wont translate to the Options window

Can anyone shed any light on the correct syntax for multiple options on the IF formula?

Thanks

DareDevil · 13 Mar 2013, 11:47

@wokka said:

Hi All,

Sorry if this has been covered, I have searched, but not been able to find anything.

I am building a dynamic component library of items I use regularly, for example, I use 100mm high flooring with various floor finishes.

Now I am having trouble with my IF formulas for multiple material selection

I have entered the following formula under a custom attribute called "Top_Floor_Cost":

=if(Material="Color_000", 60), if(Material="Carpet_Plush_Charcoal", 70), if(Material="Wood_Floor_Parquet", 75)

Now when I select the appropriate floor covering (eg. "Carpet_Plush_Charcoal" from the drop down menu in the Component Options widow, Nothing shows in the Top Floor Cost box, but, In the component Attributes Window, I see #=,70, So the Formula works, but wont translate to the Options window

Can anyone shed any light on the correct syntax for multiple options on the IF formula?

Thanks

The syntax of the IF command is if (test, true, false), so you must have a second value in case of the test is false.
In your case, you must put IF in IF in IF... for the false value like that :
= if (Material="Color_000", 60, if (Material="Carpet_Plush_Charcoal", 70, if (Material="Wood_Floor_Parquet", 75, 0)))
If the Material not equal "Color_000" it will test if is equal to "Carpet_Plush_Charcoal" and if it's false it will test with "Wood_Floor_Parquet" and if it's false the value will be 0.

The problem is if you have 20 different materials, you must have 20 IF in IF, with many brackets...

If the user choose the material in a list in the component option windows, you can use (must use ?) CHOOSE and OPTIONINDEX commands. OPTIONINDEX ("attribute") return the position number of the choise in the attribute list, and CHOOSE (number, 1st_value, 2nd_value, 3rd_value...) return the value in the numberposition in the list values.
So, if the materials are in the right order, your function can be :
= choose (optionindex ("Material"), 60, 70, 75)

I think the second way is shorter, cleaner and easyer to modify, but you must watch for the right order of your lists.

Hope it will help you

wokka · 13 Mar 2013, 22:48

Thanks, I do have more materials to choose from and was looking for a way to keep the values with the name of the materials

This is much cleaner, but like you said, I ned to keep an eye on material and value orders