I have a field, RP_Status, that is a radio button set with a custom value list. There are two options, "Open " and "Closed".

I am attempting to bring those into a calculation field, RP_Status_In_Color, with this calculation:

If ( "Open" ; TextColor ( RP_Status ; RGB ( 0 ; 255 ; 0 ) ) ; TextColor ( RP_Status ; RGB ( 255 ; 0 ; 0 ) ) )

This is not working.

Can someone tell me why this is not working?

And could someone point me in the right direction?

Thanks!

Hello rgnelson,

I believe the syntax you are looking for is something along the lines of:

TextColor(RP_Status; If(RP_Status = "Open"; RGB(0; 255; 0); RGB(255; 0; 0)))

...or, if you'd like to get a bit trickier, you could resolve the RGB( ) functions to their base values and enter those directly into the Textcolor( ) function. Eg:

TextColor(RP_Status; Choose(RP_Status = "Open"; 16711680; 65280))

Thank you for your help.

That works exactly how I needed it to!

If I have a date of an upcoming deadline;

for instance, if a job is due 3 weeks from now the date would be "07-21-05" with the color 0,0,255. If it were due this week the date would change to a color of 0,255,0. If it were due tomorrow the date would change to a color of 255,0,0.

How would I go about doing that?

Thanks

I suggest you try something along the lines of:

TextColor(DueDate;

Let(T = DueDate - Get(CurrentDate);

Case(T < 2; 16711680; T < 8; 65280; T < 22; 255; 0)

)

)

This works on the assumption that you are changing the color on the basis of 'weeks from today' rather than fixed calendar weeks.

Also, in order for this calc to update automatically to reflect the changing system dates, it will be necessary for it to be *unstored*.

I will give it a go!

Thank you for your insight!

RGNelson

Thanks, Again it worked great!

I'm having a little too much fun using TextColor.

I want to add another twist. This is what I have now that works great:

TextColor(JT_Due_Date;

Let(T = JT_Due_Date - Get(CurrentDate);

Case(T < 7; RGB ( 175 ; 65 ; 65 ); T < 14; RGB ( 180 ; 140 ; 53 ); T < 21; RGB ( 8 ; 99 ; 87 ))

)

)

I want to add some text to the date output.

If the job is due < 7 days, not only do I want it to show in this color - RGB ( 175 ; 65 ; 65 ), I want to add the word HOT next to the date. So, the output would look like this:

7-14-2005 - HOT

I have tried:

TextColor(JT_Due_Date;

Let(T = JT_Due_Date - Get(CurrentDate);

Case(T & "HOT" < 7; RGB ( 175 ; 65 ; 65 ); T < 14; RGB ( 180 ; 140 ; 53 ); T < 21; RGB ( 8 ; 99 ; 87 ))

)

)

And This:

TextColor(JT_Due_Date;

Let(T = JT_Due_Date - Get(CurrentDate);

Let(H = "HOT");

Case(T & H < 7; RGB ( 175 ; 65 ; 65 ); T < 14; RGB ( 180 ; 140 ; 53 ); T < 21; RGB ( 8 ; 99 ; 87 ))

)

)

But those are not working.

Could you point me in the right direction?

Thanks

Now you're getting tricky.

I suggest that you try:

Let(T = JT_Due_Date - Get(CurrentDate);

TextColor(JT_Due_Date & If(T < 7; " - HOT");

Case(T < 7; RGB ( 175 ; 65 ; 65 ); T < 14; RGB ( 180 ; 140 ; 53 ); T < 21; RGB ( 8 ; 99 ; 87 ))

)

)

You had to switch the let and the TextColor?

Just for kicks? : -)

Let(T = JT_Due_Date - Get(CurrentDate);

TextColor(JT_Due_Date & If(T < 7; " - HOT") & If(T > 7 < 14; " - GETTING HOT") & If(T > 14 < 21; " - NOT SO HOT");

Case(T < 7; RGB ( 175 ; 65 ; 65 ); T < 14; RGB ( 180 ; 140 ; 53 ); T < 21; RGB ( 8 ; 99 ; 87 ))

)

)

Now you're just getting fancy!

But if that's what you want, you might be better off to combine the three If( ) statements into a single Case( ) statement - or better still, put the whole logical fork into the existing Case( ) statement and then use the Evaluate( ) function to pass the result to the calc engine:

Evaluate("TextColor(JT_Due_Date & " & Let(T = JT_Due_Date - Get(CurrentDate);

Case( T < 7; "" - HOT"; 11485505)"; T < 14; "" - GETTING HOT"; 11832373)"; T < 21; "" - NOT SO HOT"; 549719)")

) )

So here is the final. :- )

If you can think of any thing else that would totally send it over the top, I'll add it!

Thanks for hanging with me on this one.

I learned a lot!

Evaluate("TextColor(JT_Due_Date & " & Let(T = JT_Due_Date - Get(CurrentDate);

Case(

T < 1; "" - Due Today"; RGB ( 175 ; 65 ; 65 ))";

T < 2; "" - Due Within 2 Days"; RGB ( 180 ; 140 ; 53 ))";

T < 14; "" - Due Within 2 Weeks"; RGB ( 8 ; 99 ; 87 ))";

T > 14; "" - You Have All The Time In The World"; 0)")

) )

Thanks,

RGNelson

[edit]

I added a spot for past due jobs.

Evaluate("TextColor(JT_Due_Date & " & Let(T = JT_Due_Date - Get(CurrentDate);

Case(

T < 1; "" - Your In Big Trouble - This Job Is Past Due"; RGB ( 175 ; 65 ; 65 ))";

T = 1; "" - Due Today"; RGB ( 175 ; 65 ; 65 ))";

T < 2; "" - Due Within 2 Days"; RGB ( 180 ; 140 ; 53 ))";

T < 14; "" - Due Within 2 Weeks"; RGB ( 8 ; 99 ; 87 ))";

T > 14; "" - You Have All The Time In The World"; 0)")

) )

So here is the final. :- )

If you can think of any thing else that would totally send it over the top, I'll add it!

Well I'd say with that last addition, it is just nicely over the top - in one direction at least. ;?

One minor editorial tweak: you might want to use the apostrophe-re format for 'You're in big trouble'.

However, that aside, the other direction to develop the concept is to track when the item is done/finished/cancelled etc and change the message (or remove it or whatever) to no longer show the urgency message once the task is completed.

Good call!

I think you've been doing this a while and I'll bet you've been down this road.

My original post was about and open and closed status.

I have built a layout that will open and close the job ticket. If the ticket is past due then it should be finished. Then the admin should close the ticket.

That is not allways the case. some jobs just hang on forever. They just don't ever die. lol

Any ideas off the top of you head that I could run with would be much appreciated. I am new to FileMaker. This is my first solution. I'm sure you noticed. ;- )

Thanks again for working with me!

Well, one way to handle it would be to provide a separate field into which a closure date is entered when the job is finished.

Then - as a simple example - you could enclose your calc in a conditional statement such as:

If(IsEmpty(ClosureDate);

--Your existing bells-n-whistles calc here-- ;

"JOB CLOSED - " & ClosureDate)

I guess there are a variety of other ways to work it, depending on what kind of information would be most useful (which in turn depends on the business rules and processes). But you get the gist...