topic Re: Text in formula if/then in Revit Architecture Forum

Text in formula if/then

laura.johanssonPN37Y — Wed, 23 Aug 2023 05:43:45 GMT

Hi,

I know that formulas in families are very limited to mathematical numbers and only adopts text when there's an If/Then condition. However is it possible to have both an If/Then condition and If/And condition in the same formula?

I have a family with 4 Yes/No tick boxes. Currently I have a If/Then formula that states:

if(Box1, "AA", if(Box2, "BB", if(Box3, "CC", if(Box4, "DD", ""))))

But I want to make it something like this:

if(Box1, "AA", if(Box2, "BB", if(Box3, "CC", if(Box4, "DD", if(And(Box4, Box 1, "DDAA", if(And(Box3, Box 2, "CCBB", ""))))))

so that if the user ticks 2 boxes it combines their value into 1 text field.

Is something like this possible in the formulas or is there a work around this?

Thanks!

Re: Text in formula if/then

L.Maas — Wed, 23 Aug 2023 06:02:30 GMT

You have to fix the structure of your formula.

The formula is And(Box1,Box2) and if(X, true,false)

So combined it becomes if(And(Box1,Box2), true, false)

See the position of the brackets

Re: Text in formula if/then

MetalFingerz — Wed, 23 Aug 2023 06:24:00 GMT

Hello @laura.johanssonPN37Y ,

It's possible but in the first part of your formula, you need to first specify that Box1 is clicked "alone" so it should be something like and(Box1, not(or(Box2, Box3, Box4))) so the text would be "AA" if only Box1 is clicked. However that would make the formula quite complicated ultimately.

What I would advise, is to use integer flags with a helper parameter named BOX_SCORE (name is for example). BOX_SCORE would get a value that would change according to which boxes are checked. All the boxes values would be incremental powers of 2 (very important as it prevents overlapping scores)?

- Box1's value would be 1 (2⁰)

- Box2's value would be 2 (2¹)

- Box3's value would be 4 (2²)

- Box4's value would be 8 (2³)

You would calculate BOX_SCORE with a formula like this (not sure it works properly as I don't have Revit opened right now but you would need to tweak this accordingly then).

= if(Box1, 1, 0) + if(Box2, 2, 0) + if(Box3, 4, 0) + if(Box4, 8, 0)

Then your formula would look like this :

if(BOX_SCORE = 1, "AA", if(BOX_SCORE = 2, "BB", if(BOX_SCORE = 4, "CC", if(BOX_SCORE = 8, "DD", if(BOX_SCORE = 9, "DDAA", if(BOX_SCORE = 6, "CCBB", ""))))))

Which is way cleaner.

If you want to map all cases, you would need to go up to a 15 score as it's the maximum possible value (1+2+4+8 meaning all the boxes are checked).

Re: Text in formula if/then

Simon_Weel — Wed, 23 Aug 2023 06:20:46 GMT

Seems to work, with some modifications on the parenthesis side:

if(Box1, "AA", if(Box2, "BB", if(Box3, "CC", if(Box4, "DD", if(And(Box4, Box1), "DDAA", if(And(Box3, Box2), "CCBB", "false"))))))

BUT. As soon as one of the evaluations turns out true, the rest of the line isn't evaluated.

I revert to this page for formulas.

Re: Text in formula if/then

Mike.FORM — Wed, 23 Aug 2023 12:59:42 GMT

The formula will work as intended if they check the AND conditionals before the singular conditionals.

if you format in this order - if(and(Box1, Box2), "AABB", if(Box1, "AA", "")) - you will check if multiple boxes are ticked first and then check for singular boxes.

Re: Text in formula if/then

ToanDN — Wed, 23 Aug 2023 15:16:36 GMT

@laura.johanssonPN37Y wrote:

if(Box1, "AA", if(Box2, "BB", if(Box3, "CC", if(Box4, "DD", if(And(Box4, Box 1), "DDAA", if(And(Box3, Box 2), "CCBB", ""))))))

You are missing a few brackets ).

Re: Text in formula if/then

laura.johanssonPN37Y — Thu, 24 Aug 2023 03:45:02 GMT

That is great, Thanks @Mike.FORM.

I rewrote the formula according to your suggestions and started replacing Box with codes that I was in there, and the formula works for the most part apart when I want to have Box 1 and 3 ticked. See formula below:

if(and(Box 1, Box 2), "FD-G", if(and(Box 1, Box 3), "FD-CG", if(Box 1, "FD", if(and(Box 2, Box 3), "-", if(Box 2, "SD-G"), if(and(Box 3, Box 4), "-", if(Box 3, "SD-CG"), if(and(Box 1, Box 2), "FD-G", if(Box 1, "FD"), if(Box 4, "SD", ""))))))))

It keeps bringing up error that its wrong boolean statement, or that end expression unexpected.

Basically I want the formula to say when FD and SD-G is ticked then the code becomes FD-G.

Now I am thinking, previously I tried the AA + BB = AABB which included all letters of each code, so instead of having FD-G does the Box 1 + box 2 ticked need to be "FD SD-G" for it to work?

Re: Text in formula if/then

Simon_Weel — Thu, 24 Aug 2023 07:53:25 GMT

as @ToanDN says and another thing to watch out for - spaces in parameter / variable names can bite you....

Re: Text in formula if/then

Mike.FORM — Thu, 24 Aug 2023 13:41:10 GMT

Looks like you have 14 open brackets and 16 closed brackets. You need to remove 2 brackets from the end.

Re: Text in formula if/then

laura.johanssonPN37Y — Fri, 25 Aug 2023 02:37:58 GMT

Thanks All, I went back to scratch, and started rewriting the formula and kept adding a bracket in every time I had an open bracket and it worked in the end. Man, those brackets are annoying. One little bracket too many or a "," misplaced and the whole formula fails. But we got there in the end. Thank you all for your help!