Help with Text Formatting Function

[skunkworker]

Hello, I am pretty new to filemaker and I am trying to create a function that depending on the date a Text Field is formatted with different colors, if the date has not come yet, green, the date is todays date, yellow, or the date has allready passed, red.

Is there some way to do this easily?

[LaRetta]

Welcome!!

I'm assuming there is another date field here besides the current date. Otherwise, the text field would always display in yellow for a User viewing it. Create a calculation, substituting your date and text field names where applicable in green. It usually is easiest to copy the calc and then change to your field names from within the calc box. Be sure and grab all the calc including the last two parentheses:

Let ( days = Get ( CurrentDate ) - [color:green]date ;

Case (

days < 0 ; TextColor ( [color:green]text ; RGB ( 0 ; 255 ; 0 ) ) ;

days = 0 ; TextColor ( [color:green]text ; RGB ( 206 ; 197 ; 6 ) ) ;

TextColor ( [color:green]text ; RGB ( 255 ; 0 ; 0 ) )

) )

At the bottom, change calculation result to TEXT. Then select Storage Options and CHECK 'Do not store calculation results...' and say OK and then OK (to exit Definitions). Display this calculation field on your layouts. Because you rate yourself (at least at present) as Beginner, this calc will be easy to understand and change as your needs change.

UPDATE: The website wigged out and dropped much of my formatting and part of the calc! It has been repaired.

LaRetta :wink2:

Edited January 25, 2007 by Guest
Added update

[comment]

This could be optimized slightly by using Choose() and Sign(), e.g.

Choose (

Sign ( Get (CurrentDate) - Datefield ) + 1 ;

TextColor ( "not yet" ; RGB ( 0 ; 255 ; 0 ) ) ;

TextColor ( "today" ; RGB ( 255 ; 255 ; 0 ) ) ;

TextColor ( "passed" ; RGB ( 255 ; 0 ; 0 ) )

)

[skunkworker]

Thanks alot for this, it was very close to what I wanted. Although is there some way to actually edit the field as it still shows up black but when I create a field for the calc it shows up but it cant be edited.

[LaRetta]

No. Why can't it be standard field? Because the colorizing depends upon the current date. There is no way (short of *event-trigger plugin) that I'm aware of which would update a record which hasn't changed just because the current date changes. But all is not lost here ...

If you turn off fill pattern and line pattern on the calculation and also uncheck 'Allow field to be entered in Browse' in Field Control > Behavior and then place this calc exactly on top of your standard field, then it will display the colors. If a User needs to edit the field, they will drop into the real field underneath the calc. The text will be black while they are changing it but switch to color after User exits the field. To align the fields precisely, copy then paste your text field and re-specify the copy to the calc (Format > Specify Field). In this way, your font, field-size, alignment etc are exact. If you then use Arrange > Align and Center/Middle, you won't be able to tell they are actually stacked. If the calc wants to drop to the back (it will stay on top if you make the calc the copy instead of the original) then Arrange > Bring to Front while the calc is selected.

If you would be satisfied that it changes color only when modified, you can use a Modification Timestamp to trigger auto-enter calc on the standard field and it will change any time you change ANY value in any of the fields (on a per record basis). Or, you can use standard Auto-Enter (with 'Do not Replace Existing Value' unchecked) if you could accept it only changes color when a field inside the calc is referenced (the date or text field). But if the record isn't modified, it will get out of synch with your request (won't update) and couldn't be trusted to be right. And if it can't be trusted to be right, why use it at all? Using calc so it remains current is the best.

Plugin: overkill and should be reserved for important tasks which can't be achieved in ANY other way.

UPDATE: Comment, thank you for providing a leaner version! I appreciate it a great deal!

LaRetta

Edited January 26, 2007 by Guest