1 of 1 people found this helpful
Conditional formation works from the top of the list to the bottom
So condition 1 is Get ( CurrentDate ) > DateField - 90
2 is DateField is Get ( CurrentDate ) > DateField - 60 - this overrides the previous one if it is true (and presumably you not mean they are both amber either!!)
Then 3 is Get ( CurrentDate ) > DateField - 30
Because each Conditional Formatting condition can result in only one "result" (e.g., maybe fill color and text, maybe just fill color, whatever). If it's this, do this.
GetAsDate ( AAA ) - Get ( CurrentDate ) > 60 and GetAsDate ( AAA ) - Get ( CurrentDate ) <= 90
Pick an amber fill color
GetAsDate ( AAA ) - Get ( CurrentDate ) > 30 and GetAsDate ( AAA ) - Get ( CurrentDate ) <= 60
Pick a color for your fill color
GetAsDate ( AAA ) - Get ( CurrentDate ) <= 30
Pick a red fill color
Using the GetAsDate allows the calculations to result in the number of days between the two.
Thank you. I shall try that.
Thank you; I'll check this out. And nope, that was a typo. They will have diff colours.
Keep this in mind, from FMI:
"When you set multiple formatting conditions on an object, all conditions are evaluated starting from the top of the list. Each condition that evaluates “true” is appended to the objects previous format settings."
If the first one is true, it'll set your first fill color. If the second one is true, it'll set the fill color. If the third one is true, it'll set the fill color. Order is important.
For example: Let's say AAA is 88 days before today.
Then it is true that it is 61-90 days before today, so condition 1 would be true and the fill would be Amber.
The next condition would be evaluated. If you limit both sides (>30 and <=60), then it is false and wouldn't apply.
If you only say >60, then it is true and would apply the fill color, overriding condition 1.
Then the next condition would be evaluated.
If you only say >30, then the final result would be the fill color you want for condition 3 because it would override the other two. If you just say <=30, then condition 3 and only condition 3 would be true.
jrenfrew's methodology is more efficent than the one I suggested. You may need to re-order the conditions, or you may not. It appears to me that you're wanting today's date to compare to some future date (AAA), giving you some sort of "heat index". Red colors for close upcoming dates, then amber, then probably yellow (just guessing).
Use jrenfrew's method, get your order correct, and you'll be all set.
You totally understand what I am after. Thanks.
One other suggestion.
If you set a variable, $$CurrentDate, it will always be available to FM. If you say Get ( CurrentDate ), FM will calculate it. If people might be working round the clock, then setting a variable could cause a problem, and you'd want the constantly updated Get ( CurrentDate ).
But in jrenfrew's formulas, or in mine, using $$CurrentDate would be more efficient than using Get ( CurrentDate ).
Take my first post. I used Get ( CurrentDate ) five times. jrenfrew only made it get calculated 3 times. $$CurrentDate would have resulted in just retrieving from memory. Also, his method knows that all conditions will be evaluated anyway, so he allows each condition to over-ride the one before if needed. Therefore, he could leave out more calculations.
Just things to think about. If on login, you always set a variable $$CurrentDate = Get ( CurrentDate ), your most efficient set up might be:
$$CurrentDate > DateField - 90 = yellow
$$CurrentDate > DateField - 60 = amber
$$CurrentDate > DateField - 30 = red
Again...check the order to make sure it's highlighting as you desire, but that gets it down to a memory call for $$CurrentDate, a read on DateField, and three calculations.
Just tossing it all out there.
No worries at all. You can mark mine helpful if you want, but it doesn't really matter to me. Sometimes a lot of us just like to look hard, tweak, and do what can be done to increase efficiencies.
Happy to be part of the help you got.
Interesting; I never thought of setting a variable to current date.
Would it make that much of a dif?
I'll definitely be keeping you in mind! Thanks.
Let's say this works well, with AAA (maybe AAA means, "On Sale Date"). Your boss is going to say let's apply a similar treatment to BBB (Due to Printer Date), CCC (Ad Close Date), and so on.
$$CurrentDate is in the computer's memory.
Get ( CurrentDate ) is evaluated every single time, for every single record, that is visible on screen. On a form layout, applied to one field, there will be five evaluations. On a list layout with 25 records with Get ( CurrentDate ) on each record evaluated 5 times...that's a difference of one call to memory versus 125 evaluations of Get ( CurrentDate ).
So...it makes a difference. Sometimes small, and sometimes big. It's just something to think about.
I see your point. Thanks.