I am trying to alert our staff when a project change they are working on has been marked to show what budgets will be affected but still has $0 for the budget amount. I'm going to need to use this alert in other calculations so I can't use conditional formatting alone.

A user (any user) will create a record and check values in a field called disciplines_affected.

disciplines_affected has a checkbox value list with six values(all values have to remain - the values selected also denote sets of sheets in the drawing package)

ARCH

SPEC

STRUCT

MECH

ELEC

PLUMB

ARCH & SPEC affect the Architectural Budget

STRUCT affects the Structural Budget

MECH, ELEC & PLUMB affect the MEP Budget

So, for now, lets just look at the MEP parts.

I have a "flag" field called mep_total_flag set as a calculation (see below).

Case (

discipline_affected = "MECH" and discipline_affected = "ELEC"  and discipline_affected = "PLUMB" and IsEmpty (mep_total_fees); "FLAG";



discipline_affected = "MECH" and IsEmpty (mep_total_fees); "FLAG";

discipline_affected = "MECH" and discipline_affected = "ELEC" and IsEmpty (mep_total_fees); "FLAG";

discipline_affected = "MECH" and discipline_affected = "PLUMB" and IsEmpty (mep_total_fees); "FLAG";



discipline_affected = "ELEC" and IsEmpty (mep_total_fees); "FLAG";

discipline_affected = "ELEC" and discipline_affected = "PLUMB" and IsEmpty (mep_total_fees); "FLAG";



discipline_affected = "PLUMB" and IsEmpty (mep_total_fees); "FLAG";



"")

I know this is wrong because the "FLAG" text does not appear unless only one value is checked. I don't know how to tell the calculation, "If any combination of MECH, ELEC & PLUMB are checked and the mep_total_fees field is empty, put the word "FLAG" in the mep_total_flag field.

Any help is appreciated.

Since the field can have multiple values, you cannot use = to check for individual values.

Try instead testing for:

not IsEmpty ( FilterValues ( "MECH¶ELEC¶PLUMB" ; discipline_affected ) )

This will return true if one or more of the three values is checked.

I didn't get the "and the mep_total_fees field is empty" part: this is a calculation field, isn't it? What difference does the previous result make?

That worked - thank you.

Yes, the mep_total_fees is a calculation field. Instead of IsEmpty I will be using less than or equal to 0.

The reason I need that part is is because I have another layout that is going to be set up to not allow certain actions if there is no budget filled in.

Instead of IsEmpty I will be using less than or equal to 0.

Not a good idea, IMHO. The result of FilterValues() is text.

OK but isn't the IsEmpty changing the FilterValues result into a number?

The string below is the calculation (text result) of the mep_total_flag field. It is returning the expected result. Can you help me understand why it would not be a good idea to do it this way? Thanks again for your help.

If (not IsEmpty ( FilterValues ( "MECH¶ELEC¶PLUMB/FIRE" ; discipline_affected ) ) = 1

and mep_total_fees  ≤ 0; "FLAG")

isn't the IsEmpty changing the FilterValues result into a number?

Yes, it does: it changes it to either 1 (true) or 0 (false). This completes the test, so it's not necessary to test further. IOW, your formula can be simplified to:

If ( 

not IsEmpty ( FilterValues ( "MECH¶ELEC¶PLUMB/FIRE" ; discipline_affected ) ) 

and 

mep_total_fees ≤ 0 ; "FLAG" )

 





Moreover, it's usually best to use 1 and 0 (or empty) as flag values, so that the formula is further shortened to:

not IsEmpty ( FilterValues ( "MECH¶ELEC¶PLUMB/FIRE" ; discipline_affected ) ) 

and 

mep_total_fees ≤ 0

Make the result type a Number, and format the field to display as Boolean.