8 Replies Latest reply on Apr 21, 2014 4:34 AM by radion1

# Pattern Count with nested if statements problem

Hi ,

I have a calculated list of options for a particular category of cafe item

E.g

Field 1 Category - coffee

Field 2 Checkbox list

- soy

- decaf

- double

- skim

- 1 sugar

If a coffee has soy it costs .50c extra, decaf .20c strong .20c this adds to the normal price.

So I used a nested if statement with pattern count and if it's one of those items then it works fine.

If there are 2 selections ( soy decaf) it always reverts to the price of just soy and doesn't add the decaf price

My if statements seem to cover all the possibilities but the calculation always reverts to the first option.

Can I use multiple items in the pattern count search string ?

Why does it revert to the first item ? Is pattern count the wrong approach ?

• ###### 1. Re: Pattern Count with nested if statements problem

You didn't show your calculation, but you said it was a nested if statement. I assume it looks something like this:

If ( patterncount ( options ; "soy" ) ; .50 ;

If ( patterncount ( options ; "decaf" ) ; .20 ; ...

)

)

If so, then it doesn't work because once one of the If statements evaluates as True, nothing else is evaluated. Something like this should work:

If ( patterncount ( options ; "soy" ) ; .50 ) +

If ( patterncount ( options ; "decaf" ) ; .20 ) +   etc.

1 of 1 people found this helpful
• ###### 2. Re: Pattern Count with nested if statements problem

Hi Julie, Here is my calculation

If ( PatternCount( prod extra note ; "soy" ) ; prod trade + gsttr  + .5; If ( PatternCount ( prod extra note ; "soy decaf" ); prod trade + gsttr  + .7; If ( PatternCount ( prod extra note ; "soy decaf strong" ); prod trade + gsttr  + .9; If ( PatternCount ( prod extra note ; "soy strong"  );prod trade + gsttr  + .7; If ( PatternCount ( prod extra note ; "strong decaf"  );prod trade + gsttr  + .4; If ( PatternCount ( prod extra note ; "strong"  );prod trade + gsttr  + .2; If ( PatternCount ( prod extra note ; "decaf"  );prod trade + gsttr  + .2 ; prod trade + gsttr)))))))

I tried your method but it didnt work or I got irt wrong...

thanks for helping..

• ###### 3. Re: Pattern Count with nested if statements problem

If() returns the first true condition, so any others that may be present aren't checked. Use

Let (

ing = ingredientsField ;

Case ( PatternCount ( ing ; "Soy" ) ; ,5 )

+ Case ( PatternCount ( ing ; "Decaf" ) ; ,2 )

+ Case ( PatternCount ( fing ; "Strong" ) ; ,2 )

{ + etc. }

)

Or look at the multi-key method used in the attached file – a sort of low-cost variant of a related records approach.

1 of 1 people found this helpful

• ###### 5. Re: Pattern Count with nested if statements problem

Sorry, I'm still having issues here ...hold the coffee for a minute

• ###### 6. Re: Pattern Count with nested if statements problem

oops, I was too quick on the trigger there. Sorry,

So my calc looks like this now

Let (

ing = prod extra note ;

( Case ( PatternCount ( ing ; "Soy" ) ;prod trade + gsttr + .5 )

+ Case ( PatternCount ( ing ; "Decaf" ) ; .2 )

+ Case ( PatternCount ( ing ; "Strong" ) ; .2 )

)

Which works perfectly if I start with soy. But if I start with . decaf I get no 'prod trade + gsttr'. Do I need to repeat the function 3 times to cover the other options using 'or' ?

• ###### 7. Re: Pattern Count with nested if statements problem

Do I need to repeat the function 3 times to cover the other options using 'or' ?

No, just factor out the parts that need to appear in (literally) 'any case':

Let (

ing = prod extra note ;

Case ( PatternCount ( ing ; "Soy" ) ; .5

+ Case ( PatternCount ( ing ; "Decaf" ) ; .2 )

+ Case ( PatternCount ( ing ; "Strong" ) ; .2 )

)

• ###### 8. Re: Pattern Count with nested if statements problem

Thanks Erol, you made my day....now I'll shout you any coffee you choose, damn the expense.