Colored text conditionally determined

[Jarvis]

I would like to have the text in one field change color as a result of a value in another field.

Field 1 is TASKS:

Field 2 is PRIORITY

If the priority of a task is urgent, the font would turn red.

If the priority of a task is not urgent, the font would remain black.

Is something like this doable in FM7?

Are there any plug-ins available that would do this?

Thanks,

Jarvis

[Ender]

FM7 can do this using the TextColor() function. The application help shows a couple examples.

[LaRetta]

Hi Tim,

Well, I'm not very good with 7 yet and I'm sure there are better calcs, but try this:

Add an Auto-Enter by calculation to your tasks field of:

Case(Priority = "Urgent"; TextColor ( Tasks ; RGB ( 255 ; 0 ; 0 ) ); TextColor ( Tasks ; RGB ( 0 ; 0 ; 0 ) ))

Be sure to unclick right under 'by calculation' the box that says "Do not replace existing value."

If your priority field says "Urgent", the text in Tasks will turn red but if you change the priority to something else, tasks will turn black again. Ah, the wonders of 7!! But that calc isn't very good.

In fact, you have Developer 7, and this might be an example of using a Custom Function. Then you could just use getRed or getBlack etc. whenever you want to. I'll have to play with that!

LaRetta

[The Shadow]

A smaller variant of LaRetta's method above could be done with:

TextColor( Tasks; RGB( If (Priority = "Urgent"; 255; 0 ); 0; 0 ) )

[LaRetta]

Very pretty!!

[CobaltSky]

The Shadow said:

A smaller variant of LaRetta's method above could be done with:

TextColor( Tasks; RGB( If (Priority = "Urgent"; 255; 0 ); 0; 0 ) )

Yes. Or try:

TextColor(Tasks; (Priority = "Urgent") * 16711680)

[LaRetta]

I understood The Shadow's rendition but not this! Could you explain it for us, please?

[Jarvis]

I'm just standing back applauding!

Jarvis

[LaRetta]

That number isn't even divisible by 255. All I can think of is that I've heard there are 16.7 million colors. I'm going to play with this too.

[-Queue-]

( x; y; z ) = 256^2 * x + 256 * y + z

( 255; 0; 0 ) = 256^2 * 255 + 256 * 0 + 0 = 16,711,680

In other words, the colors are defined in terms of base 256. So the maximum number of colors is ( 255; 255; 255 ) = 256^2 * 255 + 256 * 255 + 255 = 16,777,215, giving the 16.7 million colors.

[Lougee]

I've been playing around with 7 a little, trying to apply this kind of thnking to calculations... I'd like to be able to visibly compart 2 calculated values; if totalActualExpense (which is the sum of 'actual expense') ≤ totalBudgetExpense (the sum of 'budget expense'), then I would like totalActualExpense to be green or blue. But if totalActualExpense exceeds totalBudgetExpense, then I want totalActualExpense to show in red (and bold too).

The text formatting functions are just that -- they format text expressions. Can I 'text' format numbers and/or calculations?

Abbe

[LaRetta]

Hi Queue!

Thank you! You explained it very well and it makes perfect sense. It was right in FM Help on RGB function that red is 16711680. I didn't understand it at the time. So Fuschia is 16711935. I will never look at RGB numbers the same way again.

Abbe said...The text formatting functions are just that -- they format text expressions. Can I 'text' format numbers and/or calculations?

It looks like this forum didn't like the FM 'less than or equal to' and it messed up your post. It did the same thing to me so I changed to the old style to display it. But yes, you can color and style numbers and calculations, even based upon other calculations. For instance:

Amount (number)

SumAmount (Summary, total of Amount)

Paid (number)

SumPaid(Summary, total of Paid)

Waived (calculation, number, 1 for yes)

And you want to display a charge in red of $15 if they have an outstanding amount. But if the fee is waived, display 0 in black bold, otherwise display 0 black plain. Create a calculation (number)

Case(SumAmount <= SumPaid; 0; SumPaid < SumAmount and Waived = 1;

TextStyleAdd("0"; Bold); TextColor ("15"; RGB(255;0;0)))

And yes, I look forward to its dismemberment. I have a passion to learn lean code.

LaRetta

[Lougee]

Thanks, LaRetta.

Abbe 8-)

[Joseph31]

one simple question...how do you know what color 256, 256, 256 is or for that matter any numbering order?

[LaRetta]

Hi Joseph,

Quick way ... bring up your colors - for instance, right-click select fill color then select 'Other Color'. As you drag your mouse over the colors, watch the far right box (Red/Green/Blue). Also note that if you drag to the very top it turns white and the colors all say 255. And dragging to the bottom turns color black and shows 0.

LaRetta

[S Molly T]

Is there anyway to do this conditionally formatting of text in FM6?

It would sure would be nice to have this nifty ability.

[CobaltSky]

Hello S Molly T,

You'll find a number of FileMaker v6 demos which show different approaches to applying formatting (conditionally and via script etc) in the v6 demos archive at:

http://www.nightwing.com.au/FileMaker/demos.html

Look for them among those listed in the third group under the heading 'Finesse and Added Value'. It's not as straightforward as in v7, but there are some options nonetheless...

[The Professor]

I'm having trouble implementing conditional colored text in a date field. I have an auditions database that displays the date for each record in list view. I'd like Monday to be red, Tuesday blue, etc. Do I need a separate calculated date field for the list layout to return the day of the week?

I've tried something like this, but it's not working:

TextColor ( Date Color ; RGB( If (Date = "Saturday"; 255; 0 ); 0; 0 ) )

Thanks in advance for any help.

[The Shadow]

It doesn't seem to work for dates unless the calculation's return type is text.

From your post, its not clear what types and data your fields have, but I suspect the issue is there.

[CobaltSky]

Yes. It works for text. It also works for a field defined to return its result as number *provided* the field object, when placed on the layout, is formatted with the Leave data formatted as entered option selected (in the number formats dialog). But I'm afraid I have to agree that it doesn't seem to work for date results in any circumstance.

If you want your date calc returned and stored in date format, but you need the conditional formatting to appear on the layout, you might wish to consider using a second calc which references the first, applies the formatting and returns the result as text, then place the second calc on your layouts. If you need to perform finds on the data (eg to locate dates or ranges) you may wish to place both fields on the layout (stacked with the formatted version in front and with opaque fill) and set the Field Behaviour options for each so that the front one is accessible in Browse mode only and the back one in Find mode only. That way conventional date searches will still be possible, even though what the users see in browse mode will be a text rendering of the date.

With the release of FileMaker 7, a lot of the kind of work-arounds that use stacked fields have become unnecessary, but perhaps this is one case were you might consider resorting to some of the old 'tricks'.

[Neverland]

I think your calc would work a little better formatted like this...

TextColor ( Date Color ; RGB( If (DayName ( Date ) = "Saturday"; 255; 0 ); 0; 0 ) )

Give it a try.

SR

[S Molly T]

CobaltSky,

Thanks for the link to the demos, helpful stuff there. Yeah not as straightforward as it appears to be in fmp7 and a little kludgy. Maybe I will just wait until we upgrade to fmp7 before trying to get fancy with the conditional formatting.

Thanks again.

[The Professor]

Thanks for the responses, but I'm hopelessly confused. It's not the calculations themselves so much as getting them to apply to the fields I want. Is there no way to get a field to assign text color on itself, or must it always be based on a calculation of the data of another field?

Here's what I need in more detail. I have a database called Auditions with the fields Date (date) and Confirmation (text). Confirmation has a value list of "Confirmed" and "Unconfirmed".

In List view only, I'd like Confirmation to format with red text if the value is "Unconfirmed".

Also in List View only, I'd like Date to format with different colored text based on the day of the week: Monday red, Tuesday blue, Wednesday green, etc.

The first calculation I'm using is:

TextColor( Confirmation; RGB( If (Confirmation = "Unconfirmed"; 255; 0 ); 0; 0 ) )

This doesn't work "on itself". I have to create a second filed called Confirmation Color and import the value of Confirmation, but then it won't update if the status is changed.

The second calculation is:

TextColor ( Date Color ; RGB( If (DayName ( Date ) = "Saturday"; 255; 0 ); 0; 0 ) )

Since the TextColor function doesn't work on dates, does a separate text field have to be created to import the date as text so that it can then be formatted?

Thanks in advance for any help and sorry for the noob questions

[LaRetta]

Hi Professor,

Activity must happen within one of the record's fields to force auto-enter to change. Just switching to a report won't do it. And dates can be a problem because they don't take color. You will need a Date (text) field and you might consider setting the colors before you switch to the report and removing them after you are done, all from within the Report script. Attached is a simple demo which might help you.

If you strictly use auto-enter on a field (via calculation, do not replace), the text color would switch whenever the field data is changed but you want it only to colorize when you switch to a report. Another consideration when using auto-enter colorizing on text... when you print it (monochrome), it looks different (faded) because of the color. I'm finding more and more than I don't colorize the text through auto-enter (although I use auto-enter for many other things). Script makes it simpler and more flexible to colorize and style text depending upon layouts, users, etc.

I have attached auto-enter to the DateTxt field anyway - just so you can see it work when you create a new record. It is a great new feature. It is unnecessary with this script - the script alone will set the field but you may want a combination of these two options so I provided both for you.

If your Users need to change the date while viewing the report, simply turn off the DateTxt Field Behaviour > Allow field to be entered. Place your regular date field under DateTxt (Align > Center-to-center). Make DateTxt background colored so the Date field under it won't show. But if a User clicks into DateTxt, Date will open for modification.

And, yes, there are better calculations and I look forward to hearing them - particularly about fine-tuning the Choose() calculation; seems I couldn't combine any further because each color was a separate selection within Choose(). I used RGB() because everyone is familiar with that. For myself, I use the actual color number (ie, 16711680) as it is more efficient, I hear.

The portal is simply to view the data as if it were a Columnar/List report so you can see it in action.

LaRetta

CondText.zip

[The Professor]

LaRetta,

Thank you, thank you, thank you! I have a 9AM with my client and you are a life saver!

[Joseph31]

Hello all,

How do you combine this

TextColor( Tasks; RGB( If (Priority = "Urgent"; 255; 0 ); 0; 0 ) )

with

Case (

File_Login_SubSheet_Brokers::SimilarToFirstRow = File_Login_SubSheet_Brokers::AllSamePortal; "No"; "Yes")

this field is a calc field

Joseph

[CobaltSky]

Well Joseph,

How best to combine the calcs you have described depends on what result you want for what inputs. Your first calc will return text from the tasks field in red or black depending on the priority. Your second calc will return 'Yes' or 'No' depending on a match between related records.

If you are hoping to append the result of the two expressions (eg separated by a carriage return, you could simply enter them into a single calc dialog with ampersand and pilcrow characters between, viz:

Expression1 &

[Joseph31]

Thank you,

That worked well... It doews not work with a raido button but that ok

Thank you

Joseph