Job List Conditional Formatting

[todd.debacker]

I have created a Job list with about 250 jobs in it so far.

I want to have an entire row become red when a job that has been invoiced is over 30 days old and nothing has been entered into a Date Paid column.

The fields I am working with are called:

- JobList_InvoiceDate

- JobList_DatePaid

I know it needs to be something like this, just not sure the proper way to say it:

Formula is: If JobList_InvoiceDate - Today's Date > 30 AND

JobList_DatePaid = "" THEN APPLY FORMATTING

How do I do this in Filemaker? (Filemaker Pro 9 Advanced)

Thanks,

Todd DeBacker

[Delta Tango]

Hi.

I have a database that does something similar.

I know that in 9.0 they added conditional formatting but I believe this only works with fields, not rows.

Here's what I do -

I create a global container field and insert a solid red image and actually store it in the field, don't reference it. You have to add it in single user mode for it to be saved and available every single time.

Then, you create a calculation field set to container that goes something like this:

If(

Your condition;

globalField;

""

)

And place it under the other fields of the record/row.

Also, make the field the entire size of the row, and set the graphic properties so that it stretches to fit both horizontally and vertically.

Hope this helps!!!!

[todd.debacker]

Thanks Delta, but not exactly what I was looking for.

Filemaker has the condtional formatting so the red color is not a problem. The problems is I do not know how to format my Formula correctly.

I had put the basic syntax of what I thought it would be, but not sure how I need to do this in Filemaker and if I need to add some fields to accomplish this.

Let me know if you come up with anything else, thanks for the quick reply.

-Todd DeBacker

[todd.debacker]

I got as far as this:

Formula Is: MechanicalJobbers::JobList_InvoiceDate - MechanicalJobbers::Today's Date> 30 and MechanicalJobbers::JobList_DatePaid=""

However, this is just formatting when "DatePaid" is blank and not when both of them are true. What am I doing wrong?

Thanks

-Todd DeBacker

[Lee Smith]

I made you a demo of how to do this with a graphic (old way), and the conditional Formatting.

You will have to change the Fields to fit your file.

HTH

Lee

highlightRow.fp7.zip

[comment]

Unless you are invoicing in the future, I believe today's date should be greater than invoice's date. Try:

Get ( CurrentDate ) - MechanicalJobbers::JobList_InvoiceDate > 30 

and 

not MechanicalJobbers::JobList_DatePaid

[todd.debacker]

Thanks for the help, but it is still not working correctly.

I need it to light up red if the invoice is over 30 days and nothing has been entered in the Paid column. However, if no invoice date is entered, then it needs to remain the normal colors.

Also, any value may be entered into the Paid column, so I can just say if it is blank and invoice is over 30 days then light up.

Hopefully I can get this working sometime here, thanks for the help so far

-Todd DeBacker

[comment]

If invoice date can be empty, you should adjust the formula to:

MechanicalJobbers::JobList_InvoiceDate

and

Get ( CurrentDate ) - MechanicalJobbers::JobList_InvoiceDate > 30 

and 

not MechanicalJobbers::JobList_DatePaid 

I don't understand what you mean by "any value may be entered into the Paid column". I don't see any Paid field, only DatePaid field. I think this should either have a date, or be empty, no?

[todd.debacker]

Comment,

Yes the Paid column is DatePaid, sorry. The problem is I just compiled this job list and invoice list and whoever was in charge of the books before did not always mark a date, and just put 'Paid' or 'PD' so I need to make it so any value can be entered, not specifically a date.

This formula is not working either, now nothing is lighting up under the formatting for me.

[comment]

If you allow text data in the DatePaid field, you need to change the last condition to:

...

and

IsEmpty ( MechanicalJobbers::JobList_DatePaid )

This formula is not working

I never know what to say to that. Is the attached working for you?

UnpaidHL.fp7.zip

[todd.debacker]

Comment,

Your file definitely worked for me. I just need to figure out what th hell is different with mine. I'm sure it is something is just set up weird.

Thanks for all the help, hopefully I figure it out from here.

-Todd DeBacker