9 Replies Latest reply on Nov 15, 2014 5:03 PM by josetorre162

# how to make multiple if statements in a calculation field

Hello,

I would like to make multiple if statements in a field calculation that reference other fields in the table.

How can I make the calculation for example: if a field is equal to another field, the designated value is a certain formula, if a field is another diferent formula then its value is another formula. So on and so forth.

The following picture shows, what i have so far, but it's not working if I want to add more if statements.Can you please give me some advise on how to write my calculation.

Any help is appreciated. Thank you.

Jose

• ###### 1. Re: how to make multiple if statements in a calculation field

You can either use the Case() function or possibly even the Let() function.

EG:

Case (

condition1 ; result1;

condition2 ; result2;

elseresult

)

That let's you chain multiple "if" statements together. Case() evaluates until it reaches a "true" condition, then returns that result. If no condition is met, then the "else" result is returned.

So you could do something like:

Case (

Customers::Distr_Type = "SA-1" and Water_used_cf > 105000 ; (Water_used_cf - 105000) * .0151 ;

Customers::Distr_Type = "SA-1" and Water_used_cf > 100000 ; Water_used_cf - 100000 ;

etc...

)

1 of 1 people found this helpful
• ###### 2. Re: how to make multiple if statements in a calculation field

Hello Mike,

Wow, that makes alot of sense. Thank you very much for this suggestion, I will definately try it out right now.

jose

• ###### 3. Re: how to make multiple if statements in a calculation field

Mike Beargie wrote:

or possibly even the Let() function.

Especially when referencing the same field (or expression!) multiple times:

Let ( [

isSA1 = Customers::Distr_Type = "SA-1! ;

waterUsed = Water_used_cf

] ;

Case (

isSA1 and waterUsed > 105000 ; ( waterUsed - 105000) * .0151 ;

isSA1 and waterUsed > 100000 ; waterUsed - 100000

// ; etc...

) )

or even

Case ( Customers::Distr_Type = "SA-1" ;

Let (

waterUsed = Water_used_cf ;

Case (

waterUsed > 105000 ; ( waterUsed - 105000 ) * .0151 ;

waterUsed > 100000 ; waterUsed - 100000

// ; etc...

)

)

)

• ###### 4. Re: how to make multiple if statements in a calculation field

Hello,

Thank you for the last responce, it also helps. Another questions, what is the function is Filemaker pro 13 advance that is equivalent to the OR function in Excel?

Your help is always apprieaciated. Thank you.

jose

• ###### 5. Re: how to make multiple if statements in a calculation field

OR, it’s the same.

EG

Case(

color = “blue” OR color = “red” ; “this is red or blue” ;

“this is not red or blue”

1 of 1 people found this helpful
• ###### 6. Re: how to make multiple if statements in a calculation field

ok got it.

Thanks.

jose

• ###### 7. Re: how to make multiple if statements in a calculation field

Hello,

When writing my case fucntions and I select the as District type to be a certain value, how can I tell the function to exclue one of the values of the field that contains the District type.

For example, in the following function, I am saying that when the Customers::Distr_Type = "SA-1" (Customers table, field SA-1) perform the repective cases.

Case (

Customers::Distr_Type = "SA-1"; ( If ( Readings_SecondTable::Water_Used_cf >10000 ; Readings_SecondTable::Water_Used_cf - 10000 ; 0 )

I would like to exclude a particular record (row)  from the column field SA-1. How would I write this in the formula above?

jose

• ###### 8. Re: how to make multiple if statements in a calculation field

Please don't cross- or multi-post the same question!

https://fmdev.filemaker.com/message/163580#163580

josetorre162 wrote:

You could also show your appreciation by marking Mike's first answer as “correct” (and thus the question as being answered)

• ###### 9. Re: how to make multiple if statements in a calculation field

Hello,

I apologize for cross- or mult posting the same question.

I have just tried and confirmed Mikes first answer and ideed it just work perfectly!

Thank you Mike.

Jose