8 Replies Latest reply on Mar 25, 2016 8:13 PM by baytonemus

# Can a conditional produce multiple calculation formulas?

I've never attempted to write anything like this before. I hope that it's possible.

I have a field that pulls from a value list including the names of six different royalty agreements. The terms of each agreement are unique, requiring different calculations. Is there a way that I can use the first field in an "If" statement, but return six different calculation formulas based on the result?

I'm running FMP 14.0.5. Thanks!

• ###### 2. Re: Can a conditional produce multiple calculation formulas?

I agree with Beverly about using a Case statement. Incredibly powerful, vastly more easy to use and to read.

HOW different are the calculations? Can you let us know a bit of what the calculations might look like?

• ###### 3. Re: Can a conditional produce multiple calculation formulas?

I had considered using CASE(), but I was thrown off by...

Data type returned

text, number, date, time, timestamp, container

What I think the second link you provided is suggesting is that I create six different calculation fields, then use those field names in another calculation field...?

• ###### 4. Re: Can a conditional produce multiple calculation formulas?

(Price * .091) * Quantity

Product_Total * .15

...etc.

• ###### 5. Re: Can a conditional produce multiple calculation formulas?

Case (

SpecialFieldThatTheUserSelected = "Option 1" ; "Put your calculation formula here if this is true" ;

SpecialFieldThatTheUserSelected = "Option 2" ; "Put your calculation formula here if this is true" ;

SpecialFieldThatTheUserSelected = "Option 3" ; "Put your calculation formula here if this is true" ;

SpecialFieldThatTheUserSelected = "Option 4" ; "Put your calculation formula here if this is true" ;

SpecialFieldThatTheUserSelected = "Option 5" ; "Put your calculation formula here if this is true" ;

SpecialFieldThatTheUserSelected = "Option 6" ; "Put your calculation formula here if this is true" ;

"None of the above applied"

)

You actually pick the type of result. It CAN be date, or text, or number, etc.

• ###### 6. Re: Can a conditional produce multiple calculation formulas?

Case (

SpecialFieldThatTheUserSelected = "Option 1" ; Price * .91 * Quantity ;

SpecialFieldThatTheUserSelected = "Option 2" ; Price * .94 * Quantity ;

SpecialFieldThatTheUserSelected = "Option 3" ; Price * .78 * Quantity ;

SpecialFieldThatTheUserSelected = "Option 4" ; Product_Total * .15 ;

SpecialFieldThatTheUserSelected = "Option 5" ; Product_Total * .35 ;

SpecialFieldThatTheUserSelected = "Option 6" ; Product_Total * .05 ;

"None of the above applied"

)

Something like that would work well. If you always use Price and Quantity, and the VALUE for each option changes, then there is another option called a Let statement. It looks more daunting, but let statements are very powerful.

• ###### 7. Re: Can a conditional produce multiple calculation formulas?

I think this will do the trick. I'll give it a try shortly. Thanks!

• ###### 8. Re: Can a conditional produce multiple calculation formulas?

Thanks for all responses! I've got everything as it should. Much appreciated.