The colors can be applied with conditional formats.
Case ( IsEmpty ( DateCompleted ) and ( DueDate > Get ( CurrentDate ) ) ; "InProcess" ;
IsEmpty ( DateCompleted ) ; "OverDue" ;
DateCompleted < DueDate ; "Complete" ;
DateCompleted > DueDate ; "CompletedLate" )
Calculation can also be written:
NOT DateCompleted ;
Case ( PartDueDate ≥ Get ( CurrentDate ) ; "In Process" ; "OverDue" ) ;
Case ( DateCompleted ≤ PartDueDate ; "Complete" ; "CompletedLate" )
As for conditional formatting, if you've never done it before, here's a jump-start.
Select your PartStatus field and enter 'Formula is' and Self= "In Process". Then select blue text below. Add each status exactly the same.
UPDATE: BTW, I couldn't figure out how the Lead Time entered into this and maybe it doesn't. So I just went by your example in Item #2. :smileyhappy:
sorry, the lead time is the first calculation I that am trying to do, and I have to say is not at easy as I thought. I want to take one date away from another, leaving me with either a positive or negative result in days.
so: a DueDate of 7/10/2010 minus the DateSubmitted 7/05/2010 would give a result of 5...
How do you do that as a formula??
I obviously need some practice to understand all the functions of the calculation formulas...
Thanks for the help so far...
PartDueDate minus the DateSubmitted
or ( in FileMakerese )
PartDueDate - DateSubmitted
( calculation with number result )
Thanks for the reply:
I tried this and got strings of numbers or decimals so long as to be of no use, I am missing something...lol
PartDue and DateDue should be fields of type date.
If you are trying to enter a date directly into a calculation expression, you have to use a format that fielmaker recognizes as a date and not as a division problem.
In a calculation, 6/21/2010 means 6 divided by 21 divided by 2010.
Date ( 6 ; 21 ; 2010 ) means June 21, 2010.
Thus 6/21/2010 - 5/31/2010 will not give you the difference between the dates in days, but Date2Field - Date1Field will.
PartDueDate and DateSubmitted must be real date field, NOT text fields
ahhh, so I will need to change the fields I have been putting the dates in...can I still use a drop down calendar to fill in the date??
I will try that in the morning...Thanks all, you guys are great!
That's the type of field you should use with a drop down calendar.
If you don't put dates in an actual date field you are asking for trouble.
Thanks all...I will try the big one tomorrow...
I got the formula working but the Conditional Formatting has me confused.
Do you mean I have to type in Self= or is that what the default symbol means?
Also there are two sets of "" "" does In Process go inside a set of these??
Sorry if this sounds confused, I am ...:-)
Self is a special identifier you can use here to refer to the field being formatted with the conditional format.
You create one such expression for each color and text combination, just changing the text between the quotes for each color.
I have put these in the Conditional Formatting for my Part Status field:
Formula is Self="OverDue"
Formula is Self="Complete"
Formula is Self="InProcess"
found out that I cannot have a second capital letter in a formula name...
anyway it works a treat, thanks all...
"found out that I cannot have a second capital letter in a formula name..."
Sure you can, Storywizard. You can have second capital letter in field names and in data as well. Your example would work perfectly. Did you happen to have a space between Over and Due? If it still doesn't work please tell us your OS and FM versions because I've never heard of that kind of bug.