7 Replies Latest reply on Dec 4, 2012 3:24 PM by ChrisSmythe

# Specified Calculation for Conditional Formatting

### Title

Specified Calculation for Conditional Formatting

### Post

Hello, I am using FileMaker Pro v12 on Windows XP.  I am trying to create a formula for conditional formatting via the Specified Calculation window (see attached screen-shot).

I need the formula to do the following:

Is the number of Personnel::Service = "Army" greater than or equal to one.

Any help in this would be greatly appreciated.

Kind regards,

Chris :)

• ###### 1. Re: Specified Calculation for Conditional Formatting

I don't understand what you want.  First, of all "Army" is not a number.

• ###### 2. Re: Specified Calculation for Conditional Formatting

Hello S Chamblee, thank you for your reply - much appreciated.

Sorry, that my post was not clear.

I am trying to get a True or False boolean value in my specified calculation.

I am trying to determine in my Personnel table, "is the number of Army personnel in the table greater than or equal to one?"

The field is: Personnel::Service

The value is: "Army"

"Is the number of "Army" personnel in my Personnel table greater than or equal to one?" (this part needs to be made in to a calculation that results in a boolean value).

Hope this is clearer.

Kind regards,

Chris.

• ###### 3. Re: Specified Calculation for Conditional Formatting

Assuming that your layout is based on Personnel, you'd need to use either the ExecuteSQL function (FileMaker 12 only) or a self join relationship before your calculation can be made to work.

The self join:
Define a calculaiton field constArmy as a calculation field that returns text and enter "Army" as its expression.

Create a new occurrence of Personnel and link it to your current table occurrence like this:

Personnel::constArmy = ArmyPersonnel::Service

Then this calculation:

Not IsEmpty ( ArmyPersonnel::Service )

will return true if there is at least one record in the personnel table where Service = "Army".

But are you sure that's really the correct logic for this? Seems like this expression will return True very early on in the use of the database and then stay true from that point on...

• ###### 4. Re: Specified Calculation for Conditional Formatting

Hello PhilModJunk, thank you for your kind assistance and help.

I am trying to use conditional formatting so that the user can visually see the following:

If they import Army records into the database and then accidently "double-dip" and try to do it again, my import Army records script will prevent them from "double-dipping".  So, if there is no Army records in my database, then the user is ready to conduct a Army records import.

If there are any Army records (i.e greater than or equal to one) then I need to visually show the import Army records link (button) to change color.

Could you please advise on how I may define a calculation field constArmy?

Do I need to duplicate my Personnel table (Manage > Database > Relationships)? If so, do I rename the duplicate Personnel table to: ArmyPersonnel?

Kind regards,

Chris :)

• ###### 5. Re: Specified Calculation for Conditional Formatting

Hello PhilModJunk,

I think I have created the calculation field constArmy as shown in the attached screen-shot?

Kind regards,

Chris :)

• ###### 6. Re: Specified Calculation for Conditional Formatting

Do I need to duplicate my Personnel table (Manage > Database > Relationships)? If so, do I rename the duplicate Personnel table to: ArmyPersonnel?

That sounds right, but you aren't really duplicating the table, just creating a new table occurrence that also refers to Personnel. The duplicate button on the relationships tab is an excellent tool to create this new occurrence.

constArmy is defined correctly.

• ###### 7. Re: Specified Calculation for Conditional Formatting

Thanks PhilModJunk - it works really well.  Very grateful for your excellent assistance and help.

Kind regards,

Chris :)