Hello all. A great newsgroup which has helped me a lot in the past. I need
some advice.

I have a DB which tracks when job are due to be done.
Each job is its own record with a date field.

I simply sort on it, the next job to be done is thus at the top of the list.

My users want me to show a different colour on a layout to draw attention to
how close the job is to being required to be done. They want some sort of
colour to be shown as follows:

1. If the date is more then a week away show GREEN
2. If the date is less than a week ago then show AMBER
3. If the date has passed then show RED.

Anyone know if there a way to do this using some sort of conditional
formatting? I can do this in Excel but cannot see a way to do this in FM.

As the list could have different dates then I cannot simply change to a
different layout.

Any ideas?

Ens

Yes, Ens. There are a couple ways to do this.

Approach A: Color the Text

The following calculation will color a date according to the criteria you specified:

ColoredDate (calculation, text result) =

Let ( T = TheDate-Get(CurrentDate);
Case
(
T<0; TextColor ( TheDate; RGB ( 255; 0; 0)); // Red
T<7; TextColor ( TheDate; RGB ( 240; 220; 0)); // Amber
TextColor ( TheDate; RGB ( 0; 255; 0)) // Green
)
)

Note, this isn't the same as your verbal description of the logic, because "less than a week ago" and "date has passed" conflict with one another. In this calc I assumed you meant "less than a week away." In any event here's how it works:

- "T" is a variable used within the calculation to indicate the number of days between today [Get(CurrentDate)] and TheDate. If TheDate is today, T=0. If TheDate is in the future, T will be positive. If TheDate is in the past, T will be negative.

- The Case() function lets you color the text differently according to the value of T. Case () steps through pairs of parameters for each "test." When it finds a match, it uses the matching result and then skips the rest. The last parameter is always the default value. A case statement is a little easier to understand and revise than nested If() statements (which could also have been used).

- The text is colored using the TextColor() function, which uses RGB values.

- The calc has to be a text result because the text formatting functions don't work on date results.

If you really wanted the result to be colored the way you said in your post, you could use

ColoredDate (calculation, text result) =

Let ( T = TheDate-Get(CurrentDate);
Case
(
T>7; TextColor ( TheDate; RGB ( 0; 255; 0)); // Green
(T<=0) and (T>-7); TextColor ( TheDate; RGB ( 240; 220; 0)); // Amber
(T<=-7); TextColor ( TheDate; RGB ( 255; 0; 0)); // Red
TextColor ( TheDate; RGB ( 255; 0; 0)) // Black
)
)

As you can see, the Case() statement combined with this sort of formatting makes it easy to see what is going on.

You would then use this calculation field instead of the source field on your color-coded reports.

Approach B: Color the Background

Suppose you want to color the background of the entire record, or the entire field.

An easy way to do this is with a relationship to a "ColorCode" table.

Suppose you create a table, ColorCode, with the following fields:

i (number, unique)
Color (container)

The actual records look like this:

i Color
1 Red
2 Amber
3 Green

That is, the container field is filled with a square graphic of the stated color. (You can just draw the square in Layout mode and paste it into the field in Browse mode.)

In your original table (MyTable)you add a calculation field just like the one in Approach A. But you use numbers as the result:

ColorID (calculation, number result) =

Let ( T = TheDate-Get(CurrentDate);
Case
(
T<0; 1; // Red
T<7; 2; // Amber
3 // Green
)
)

Then you set up a relationship between ColorCode and MyTable where MyTable::ColorID = ColorCode::i

Now you can drop in ColorCode::Color behind any field, or across the entire record, and it will show the color specified by ColorID for that record. (Be sure to set the Graphics format of the container field to Reduce or Enlarge, don't maintainoriginal aspect ratio).

Bill

"ens mith" <ns007b2655_4 (AT) blueyondernopespam (DOT) co.uk> wrote

Say you create three new fields job1, job2, job3 that you will make
transparent and that you superimpose one on each other.
Job1 is made by calculation equals to CloseDate only if - using the Case
function it meets criterium#1, and to nothing if not.
Job2 : the same for criterium#2 and Job3 for criterium#3.
Then you make Job1 Green, Job2 Amber and Job3 Red.
You manage the superimposition suche that they are perfectly aligned on
on each other.
Done.
The same technique could be adapted to make the background colored and
not the date itself.
I could send you examples in v6 if you give me privately your real
address.
Remi-Noel


"ens mith" <ns007b2655_4 (AT) blueyondernopespam (DOT) co.uk> a écrit ...

Here is yet another approach [FM8!]:
The task is changing colours in a list of records, depending on field
contents.
Following on from my earlier post I have created some container fields
that are superimposed (but are larger than) certain fields in the
layout. These are set to "Send to back" so that the fields show through.
When I create the record I run some script to choose the appropriate
colour for the container field (just a coloured rectangle).
----------
assign_colour_box
#Passed variable is score $$n1, calling script must be set to a
container field
Perform Script [ “calc_colour” ] -- this works out the desired colour,
depending on the field value.
If [ $$t1="G" ]
Insert Picture [ “imageics/box_g.gif” ] -- green box
[ Reference ]
Else If [ $$t1="Y" ]
Insert Picture [ “imageics/box_y.gif” ] -- yellow box
[ Reference ]
Else If [ $$t1="O" ]
Insert Picture [ “imageics/box_o.gif” ] -- orange box
[ Reference ]
Else If [ $$t1="B" ]
Insert Picture [ “imageics/box_b.gif” ] -- brown box
[ Reference ]
Else If [ $$t1="R" ]
Insert Picture [ “imageics/box_r.gif” ] -- red box
[ Reference ]
End If
--------
(I tried to get this to work by a formula rather than IF statements but
gave up.)

This method allows me to print a table of values with the fields
surrounded by backgound rectangles that depend on the field contents.
See http://idisk.mac.com/mpaineau-Public/ancap_list.gif for an example.

Michael Paine


Bill Marriott wrote:

Michael,

You could try a variation of the relationship technique (Approach B) -- this
way you don't have to run a script to ensure the borders are correct.

Bill

"Michael Paine" <mpaine (AT) tpgi (DOT) com.au> wrote

Perfect. Thanks for all your responses.

I've managed to make myself look really brilliant at work! Plus I've been
surprised with what Filemaker can do.
I'm encouraged to try more things now.

Thanks again

Ens

"Remi-Noel Menegaux" <rnmenegaux (AT) free (DOT) fr> wrote

Yes - thanks Bill. I will keep that clever technique in mind. However,
my relationship graph is already looking very complicated and, since I
have at least eight fields to "paint" I decided to use container fields
within each record, rather than multiple links to a reference table. The
container fields just store a reference to graphic files so there is no
storage overhead.
BTW - the printout example is based on a report I created in MS Access
97 that uses the On Format function to assign object colours as they are
printed. I needed to be able to simulate this in FM8 before I could make
the switch so I am quite pleased I managed to solve the problem (in one
of several possible ways).

Michael Paine

Bill Marriott wrote: