Filed color change by calculation

[Wingwalker]

I converted a file from 6 to 8. On one of my portals are two fields, (one on top of the other) one has red numbers and the other has green numbers. The red is the bottom field and shows the invoice date when the invoice is unpaid in red. Once the invoice is paid the top field shows the date it was paid in green. This worked fine in 6 but does not work in FMP8.

 

A recommendation was made that I use one field that has a color determined based on whether the invoice is paid or not. That sounds great but I don’t know how to do it.

 

As I mentioned above, I have two calc fields.

 

DateGreen

 

If(OrderStatus = "Paid"; InvoiceDate;Date("";"";""))

 

DateRed

 

If(OrderStatus = "Invoiced"; InvoiceDate;Date("";"";""))

 

They show unpaid invoices in Red and paid invoices in Green.

 

How do I combine this so it is done with one calculation? I do have a field called OrderStatus that reads "Paid" once the amount paid is posted if that field is needed for a trigger. I don't know how you would set it up but thought I'd let you know. The OrderStatus calculation is: Case(not IsEmpty(Payments | inv_id::amount); "Paid"; not IsEmpty(InvoiceDate); "Invoiced"; not IsEmpty(OrderDate); "Ordered"; "").

 

Thanks,

 

James

[mraz]

Make Date an auto-enter calculation as follows:

 

Case (

OrderStatus = "Paid" ; TextColor ( Date; RGB ( 0 ; 150 ; 0 ) ) ;

OrderStatus = "Invoiced" ; TextColor ( Date; RGB ( 150 ; 0 ; 0 ) ) ;

TextColor ( Date; RGB ( 0 ; 0 ; 0 ) )

)

 

This will turn the Date field green if paid, red if invoiced and black as default if neither of the two conditions evaluates to true. Make sure you uncheck the Do not evaluate if all fields empty... and Do not replace... checkboxes.

 

You might have to jig the color values a bit to get the shades you want.

 

Martin.

[Wingwalker]

Hi Martin,

 

I can not get your calculation to work. FMP would not accept the "Date" in your calculation so I tried.

 

Case (

OrderStatus = "Paid" ; TextColor ( InvoiceDate; RGB ( 0 ; 150 ; 0 ) ) ;

OrderStatus = "Invoiced" ; TextColor ( InvoiceDate; RGB ( 150 ; 0 ; 0 ) ) ;

TextColor ( InvoiceDate; RGB ( 0 ; 0 ; 0 ) )

)

 

Although FMP took the calculation it does not work. Another thing I do not understand is my DateGreen and DateRed calculations both have "InvoiceDate;Date" in the calculation, but for some reason it will not take "Date" in your calculation. Also, please tell me where I find the "Do Not Replace" checkboxes.

 

Thanks Martin,

 

James

[mraz]

Sorry, I meant InvoiceDate, not Date, that was my mistake.

 

I just realized that the reason it's probably not working is that TextColor is a fuction that works only on text fields, and not on a date field. I've attached a small sample file that shows how the calculation works if the InvoiceDate field is a text field, but I'm not sure what to do in the case of an actual date field...

 

good luck...

Martin.

[LaRetta]

It would be best to leave the original field a date field. Just create a calculation (text) with that calculation and display the calc field instead whereever you need it to change colors.

[Maarten Witberg]

nice to see you 'round again LaRetta Yeah

 

kjoe

[harryglos]

LaRetta,

 

Hey girl, it's so nice to see you lurking around the ol cafe. So what's your story? You out slummin or what?

 

I'm sorry kjoe got to you first, that can never be a good thing. And what's that dancing banana thing he's got goin on? Maybe he thinks he has great "a peel" Ar-Ar-Ar! But he was Quick, short and to the point and I could not agree with him more which is generally not a good thing... But he's right this time "Nice to see you 'round again LaRetta"!

 

BTW Kjoe is an Urban Planner so if you have any potholes on your street just give him a shout. He won’t fix it, but he'll enjoy the shout...

 

You go girl...

 

Harry

[Maarten Witberg]

I just tell people where they should put the potholes Harry.

 

OK here's one for you, too: Yeah

 

kjoe

[LaRetta]

Hey listen, guys ... that dancing banjo-banana guy is precisely why I'm back - he DOES have a peel. He makes me giggle every time I see him and I think he's totally irresistible - I'd marry him in a heartbeat if he wasn't so slippery.

 

Besides, I haven't REALLY been gone ... just quiet. And if you believe that then I have a bridge for sale also ... ;-)

[Maarten Witberg]

beware LaRetta

 

a peel is only skin deep

 

kjoe