todd.debacker Posted October 3, 2007 Share Posted October 3, 2007 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 Link to comment Share on other sites More sharing options...
Delta Tango Posted October 3, 2007 Share Posted October 3, 2007 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!!!! Link to comment Share on other sites More sharing options...
todd.debacker Posted October 3, 2007 Author Share Posted October 3, 2007 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 Link to comment Share on other sites More sharing options...
todd.debacker Posted October 3, 2007 Author Share Posted October 3, 2007 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 Link to comment Share on other sites More sharing options...
Lee Smith Posted October 3, 2007 Share Posted October 3, 2007 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 Link to comment Share on other sites More sharing options...
comment Posted October 3, 2007 Share Posted October 3, 2007 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 Link to comment Share on other sites More sharing options...
todd.debacker Posted October 4, 2007 Author Share Posted October 4, 2007 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 Link to comment Share on other sites More sharing options...
comment Posted October 4, 2007 Share Posted October 4, 2007 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? Link to comment Share on other sites More sharing options...
todd.debacker Posted October 4, 2007 Author Share Posted October 4, 2007 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. Link to comment Share on other sites More sharing options...
comment Posted October 4, 2007 Share Posted October 4, 2007 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 Link to comment Share on other sites More sharing options...
todd.debacker Posted October 4, 2007 Author Share Posted October 4, 2007 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 Link to comment Share on other sites More sharing options...
Recommended Posts
This topic is 6183 days old. Please don't post here. Open a new topic instead.
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now