6 Replies Latest reply on Aug 17, 2015 4:09 AM by erolst

# Conditional Formatting of a calculation field

Hi

This sounds a really dumba__ question for  really basic facility which I can’t seem to get to work

I have a ‘Time Sheet’ type application

The time sheet  has two fields with number (hours) and a third calculating the difference between the two (positive or negative).

The calculation field simply subtracts field B from field A to display an integer

I need the calculation field to simply display the calculation result (number) but also with some conditional formatting with the following logic

If A=B - then no action: display the number in default state

If A-B is positive then display the number in colour green and bold

If A-B is negative then display the result in colour red and bold

I cant get a way to make this simple requirement work and its driving me crazy: such a simple facility.

Can anyone please point me in the right direction

Thanks for your help. I appreciate you taking the time to read this and answer my question

Brian

• ###### 1. Re: Conditional Formatting of a calculation field

You'll need to set two conditions:

Formula is: Self < 0 [ bold and red ]

Formula is: Self > 0 [ bold and green ]

• ###### 2. Re: Conditional Formatting of a calculation field

I think the Case function is a much better function than the if, in that it does not need to be nested like the IF function. The Case statement on the other hands goes to on to evaluate conditions and apply results as needed.

ie, Case ( test1 ; defaultResult {; test2 ; result2 ; ... ; defaultResult} )

Case ( A=B ;result1 {;

A-B>0; Green and Bold ;

A-B<0; Colour red and bold} )

• ###### 3. Re: Conditional Formatting of a calculation field

ucharles wrote:

I think the Case function is a much better function than the if, in that it does not need to be nested like the IF function.

I only use Case(), but - to play Devil's Advocate – if you have a simple one-line predicate, then If() is two characters shorter …

That being said: neither Case() nor If() is appropriate here, because you cannot calculate the formatting* (like in your example) – you need two distinct Boolean results to trigger hardcoded format settings, as shown by Daniele.

*which would be very neat!

• ###### 4. Re: Conditional Formatting of a calculation field

If it's the field that has to be formatted then you should have access to the Value is greater than, equal to, contains etc... settings.

There you can specify one or more conditions with respective formatting.

I don't think you need to write a formula, just choose from drop downs.

One format per line, you can add as many formats as you need.

For default where conditions are not met, format the field in the inspector as you want.

• ###### 5. Re: Conditional Formatting of a calculation field

Hi Erolst

I do get the logic - pretty straightforward.

I guess my issue is just as you wrote: the fine detail in Filemaker about formatting the fields.

I have used Filemaker a lot but managed (incredulously!) without using Functions so I struggle with their use and application. My logic is OK but my technique is poor.

At the risk of being a total numpty could you please explain the solution in a wee bit more detail ie the formatting of the fields too

Brian

• ###### 6. Re: Conditional Formatting of a calculation field

Understand the difference between

1. using functions to calculate a field format – which then becomes part of your data

2. Use functions to calculate if a condition is true, and, if so, apply a set of hard-coded, pre-selected formatting options; that is what Conditional Formatting does, and is only applies to that field object.

Step by step you'd have to:

• Right-click the field and select Conditional Formatting

• Add one condition per … well, condition that you want to trigger a change in formatting

For simple comparisons concerning only that field itself, you can use “Value is”; for anything more complex, select “Calculation is”

• in your case, you could get along with two “Values is” conditions:

1. less than 0, 2. greater than 0

For each condition, select and specify the formatting options to be applied.

1 of 1 people found this helpful