Is Empty for Multiple Fields?

[stann]

Hello

I am trying to make a field calculation to show if any of 4 fields are empty the amount of another field should be placed in a field called "Amount Owed"

I had

IsEmpty( AFC, CASH, CHECK, AU):"BUYERSCOST"; "0"

But that is not the way to write it.

Can someone help me with this

the fields that need to show if they are empty are

AFC, CASH, CHECK, AU

Thanks in Advance

STAN

[Lynn Johnson]

If ( IsEmpty ( AFC ) or IsEmpty ( CASH ) or IsEmpty ( CHECK ) or IsEmpty ( AU );BUYERSCOST;0)

You need to use the IsEmpty for each field separately.

Lynn

[Philland]

Needs to be

If (IsEmpty(AFC) or IsEmpty(CASH) or IsEmpty(CHECK) or IsEmpty(AU), BUYERSCOST, O)

Phil

[Lee Smith]

Amount Owed calculation should be something like.

Case(IsEmpty(AFC), BUYERSCOST, 0,

IsEmpty(CASH), BUYERSCOST, 0,

IsEmpty(CHECK), BUYERSCOST, 0,

IsEmpty(AU), BUYERSCOST, 0)

[bruceR]

Another way of handlng this is to recognize that isEmpty(someField) evaluates to 1 if the field is empty. So an alternate way to write this is:

Case( (IsEmpty(AFC) + IsEmpty(CASH) + IsEmpty(CHECK) +IsEmpty(AU) ) > 0, BUYERSCOST, 0)

[-Queue-]

Or

Case( Sum( (IsEmpty(AFC); IsEmpty(CASH); IsEmpty(CHECK); IsEmpty(AU) ) > 0; BUYERSCOST; 0 )

Any other variations we should offfer?

[cobra]

How about:

If(IsEmpty(AFC & CASH & CHECK & AU); BUYERSCOST; 0)

I think this should work without problems (I haven't tested it, tho).

BuyerCost is only used when all fields are empty.

Hope this helps,

Cobra

[-Queue-]

The request was for BUYERSCOST to be used if any of the fields are empty, not only if all of the fields are empty. So that calculation would solve a different problem.

[cobra]

Whoops! You're right. Sorry about that, I was distracted just before posting a reply.

Some alternatives*:

1. If all your fields are numerical, you can use the following:

If(Evaluate (field1 and field2 and field3 and field4); "All Are Full"; "One Is Empty")

This solution has the added bonus (depending on your POV) of working when one of the fields has 0 (zero) or a space in it.

2. If some of the fields are textual, you can use:

If(Evaluate (IsEmpty(field1) or IsEmpty(field2) or IsEmpty(field3) or IsEmpty(field4) ); "One Is Empty"; "All Are Full")

For solution 2, if a field's value is 0 or a space, the calculation above will consider it to be non-empty.

* you need to un-check "Do not evaluate if all reference fields are empty" for both solutions. IIRC, the Evaluate function is not part of FMP 6 or earlier.

I've tested both solutions and they seem to work without problems in 7.0v2.

Hope this helps!