Conditional formatting in Base?

[UncleRobin]

HSQLDB embedded
Version: 7.2.1.2 (x64) / LibreOffice Community
Build ID: 87b77fad49947c1441b67c559c339af8f3517e22

The question is straightforward, and I have a sinking feeling the answer will be too

My DB is taking shape now, and I’m currently working on the major form. This is how it looks at the moment

It’s very much a work-in-progress, but my question is about the “SCORE” field. At the moment, I have the font fractionally larger than the other non-label fields (12 vs 11), to make it stand out a bit.

Is there any way to have the content display differently depending on specified conditions? I’d love to have a “traffic light” colour system for example, with scores <5 either in a red font or with a red background, 5-7.5 orange/amber, and >7.5 green. Am I correct in thinking this is shooting for the moon?

[Hrbrgr]

I have tried this once on the basis of the small DB of @Villeroy .
Cyan is not displayed as soon as the background is white.
The smallest level gray (Standard pallet) as background and it works.



(I didn’t select a custom color now to see to what extent you can get close to white).

[RobertG]

All fields will set to a default font color “black”. So the conditional formatting won’t work in the displayed field. The font color must be changed to automatic color.

The original post tries to set the conditional formatting in a normal formatted field, not in a table control. The table control will have the same behavior: Change the row in the example database and the color ‘31.0 °C’ will change to black. Have tried to set it to automatically font color, but it doesn’t work in a table control with the first column. If I set as first column another field, for example “ID”, the temperature in the second column will be shown right when switching through the rows.

[Villeroy]

Have a look at Number Format Codes
The formatted field behaves more or less like a spreadsheet cell.

[UncleRobin]

Thank you for the link. I thought I had grokked this bit:

---

You can define a number format so that it only applies when the condition that you specify is met. Conditions are enclosed by square brackets [ ].

You can use any combination of numbers and the <, <=, >, >=, = and <> operators.

For example, if you want to apply different colors to different temperature data, enter:

[BLUE][<0]#.0 “°C”;[RED][>30]#.0 “°C”;[BLACK]#.0 “°C”

All temperatures below zero are blue, temperatures between 0 and 30 °C are black, and temperatures higher than 30 °C are red.

---

Using that information, I tried the following:

But this was the result after clicking OK and saving the form:

Have I missed a typo, or made some other syntax error?

[Villeroy]

Celsius.odb (11.7 KB)

[UncleRobin]

Sorry, I’m probably a bit dull-witted here, but what am I supposed to do with this odb? I can’t see any example of the conditional numeric formatting under discussion. Are you saying that I need to format the field in the table rather than on the form, as the form in the Celsius odb is blank?

[Villeroy]

Download the document (save to some non-temporary folder).
Open the document.
Open the form.

[UncleRobin]

I had downloaded the odb and saved it when you first posted it, thanks. I am still lost when trying to figure out how it helps me, because I can’t see the formatting, only the results.

It seems I must have failed to explain what I was looking for, my apologies.

I do not want the exact format specified in the example, I am trying to understand the syntax of the conditional formatting to figure out why the example works but mine (posted above) does not

So
[BLUE][<0]#.0 “°C”;[RED][>30]#.0 “°C”;[BLACK]#.0 “°C”
produces the results you kindly demonstrated in the sample table

but
[RED][<5]#.00;[GREEN][>7.5]#.00;[CYAN]#.00

has no effect at all on the data displayed in my form - as my screenshots show.

Since I am not displaying temperatures, I deleted “°C” and changed the decimal format from #.0 to #.00, but apart from that, the syntax appears the same. That’s why I want to know what I am doing wrong, so that I know how to do it properly not only in this one instance but in any future instance as well

[Villeroy]

The screenshot was made with LO 6.0. It looks the same with 7.2.1, both on Linux.
A work-around:

SELECT "TMP".*, CASE WHEN "TMP"."T" <= 0 THEN 'LOW' WHEN "TMP"."T" >30 THEN 'HIGH' ELSE 'MEDIUM' END AS "Level"
From "Temperature"AS"TMP"

[UncleRobin]

Thank you for your patient attempts to help, but the workaround does not fit my usage case. I tried entering the conditional format directly into the field on the source table, and it works there, as this screenshot shows

On the upside, this means I do have the syntax right, but on the downside, I have no idea why it works in the table, but not in the form. A bug, I guess. Thanks again.

[Villeroy]

The view of a table or query shows raw data only as a development help. Forms and reports are the user interface with some formatting ability and input helpers. The same trick works with reports too.

[UncleRobin]

@Villeroy

Forms and reports are the user interface with some formatting ability

Sadly, the forms clearly do NOT have the formatting ability I am seeking. As I said, that it displays in the table does show that I have not made any error in the syntax of my conditions.

My earlier screenshots show that when entering the conditional format in the Form properties, the dialog box shows the desired effect (1234.57 in green), but the condition is not actually applied to the form, no matter what I do.

It is not important, just a minor disappointment that a feature works in Calc and that displays correctly in a Base table does not work in a form. A cosmetic issue, but still disappointing.

[Villeroy]

Base is a tiny, tiny addition to this office suite. It is the drunken cousin among the other components. Not much development, only bug fixes.
The conditional formatting works for me. It does even work in the table view and in the data source window of the other components. Open the table for editing, select the numeric column in question and push the format button at the bottom.

[UncleRobin]

@Villeroy

Open the table for editing, select the numeric column in question and push the format button at the bottom

Yes, I did that, as I stated above. I also supplied a screenshot to demonstrate that it DID work in the Table. That is not the problem. The problem is that the formatting does not work in the form created which uses that table as its source.

[UncleRobin]

Following the suggestion here to file a bug report, I did. And one of the team found the fix. I’m sharing it here incase others are interested. The solution is to set the font colour to “Automatic”

and it works perfectly, as the linked screen recording shows