12 Replies Latest reply on Oct 8, 2015 4:20 PM by keywords

Filemaker if then formula

Another newbie formula question.

Here is what I am trying to do.

If PRIMARY or LIFT not empty then OVERHEAD = 1

Thanks, for any and all help.

• 1. Re: Filemaker if then formula

Overhead, calc field, with formula  PRIMARY or not IsEmpty(LIFT) ?

• 2. Re: Filemaker if then formula

So the formula is not isempty(Primary) or not isempty(Lift)?

How does it know to put a 1 in Overhead?

Thanks.

• 3. Re: Filemaker if then formula

In a calculation, it is better to use "Case" instead of "If"

If I am not wrong, the "If" allows only a single condition.

I suggest this calculation to be inserted in the "OVERHEAD" field :

Case (

not IsEmpty ( PRIMARY ) ; 1 ;

not IsEmpty ( LIFT ) ; 1 ;

"" )

• 4. Re: Filemaker if then formula

if you want to feel more secure, know that

getAsBoolean(false or not 1)  gives a numerical value of 0, and from this you can derive a lot.

• 5. Re: Filemaker if then formula

Can I put this code in the auto-enter tab, calculated value, Specify?  Would I check or uncheck the "Do not replace existing value of field (if any)"?

Thanks.

• 6. Re: Filemaker if then formula

Nehme wrote:

In a calculation, it is better to use "Case" instead of "If". If I am not wrong, the "If" allows only a single condition.

If you only have a single test, then your choice of conditional doesn't make any difference; so there isn't a notion of “better” in the absolute – there's only “better suited to the job”.

Having said that, Case() can do everything that If() can, and more, so some people use it exclusively so as not have to think about it.

Also note that neither Case() nor If() require an empty default result.

ThomasFilemaker wrote:

How does it know to put a 1 in Overhead?

siplus's calculation works because

not IsEmpty ( PRIMARY ) or not IsEmpty ( LIFT )

returns a 1 if either field is filled, because 1 is the numerical value of True (which is the result or OR if either predicate is True). On the other hand, it returns 0 is neither is True, which may not be desired.

So I would try

Case ( not IsEmpty ( PRIMARY ) or not IsEmpty ( LIFT ) ; 1 )

• 7. Re: Filemaker if then formula

Yes you can put it as auto-enter calculation

Check or Uncheck the Do not replace ...: This depends on what you want to do

It you which to update your value, uncheck it

• 8. Re: Filemaker if then formula

Is there a way to check multiple answers as correct?  I think you all nailed it, in different ways.

• 9. Re: Filemaker if then formula

Nehme wrote:

If I am not wrong, the "If" allows only a single condition.

You can include multiple conditions in an IF statement, there is just one 'test' clause.  E.g.:

If ( not isempty(Primary) and not isempty(Lift) ; 1 ; 0 )

With Case(), you can have multiple test clauses, each one could also have multiple conditions in it.

• 10. Re: Filemaker if then formula

erolst wrote:

Also note that neither Case() nor If() require an empty default result.

They may not require that result, but they always INCLUDE a result.  If you don't provide one it will provide <null>, "", <empty>...  (whatever term you like to call it).  For example:

Let ( a = 0;

Case(

a = 1; 1;

a = 2; 2;

)

)

This returns empty/NULL/"".  I think it's always a good idea to supply an explicit 'Else' case for either function.

• 11. Re: Filemaker if then formula

justinc wrote:

For example:

Let ( a = 0;

Case(

a = 1; 1;

a = 2; 2;

)

)

This returns empty/NULL/"".  I think it's always a good idea to supply an explicit 'Else' case for either function.

That, of course, is your prerogative.

Personally, I find it verbose (in the tradition of the perennial argument between the schools of implicit and explicit coding), though it may have a calming effect on novices …

• 12. Re: Filemaker if then formula

Some interesting discussion here on differences between If() and Case(). Just note that:

If ( not isempty(Primary) and not isempty(Lift) ; 1 ; 0 )

is not the same thing as:

Case (

not IsEmpty ( PRIMARY ) ; 1 ;

not IsEmpty ( LIFT ) ; 1 ;

"" )

If BOTH fields contain a value, the first will return a 1. If EITHER field contains a value the second will return a 1, but you will not know whether (1) both contain a value, or, if only one of them does, which one is empty.

Remember that Case is hierarchical: it will only test until it gets a True result, then it will ignore the rest. If you want to be more analytical, you would need something like:

Case (

not IsEmpty ( PRIMARY ) and not IsEmpty ( LIFT ) ; 1 ;

not IsEmpty ( PRIMARY ) ; 2 ;

not IsEmpty ( LIFT ) ; 3 ;

"0" )