13 Replies Latest reply on Jun 15, 2016 8:58 PM by mrosenhek

    Conditional Formatting


      I have a date field (FIELD AAA) that needs to have conditional formatting applied to it. I can't see the forest for the trees anymore, and if anybody can help me I will be most  appreciative.


      Three conditions need to be applied to FIELD AAA


      If today's date is up to 30 days prior to FIELD AAA or past FIELD AAA , then the colour red.


      If today's date is between 31 to 60 days prior to FIELD AAA , then the colour amber.


      If today's date is between 61 to 90 days prior to FIELD AAA , then the colour amber.


      Thank you.

        • 1. Re: Conditional Formatting

          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

          1 of 1 people found this helpful
          • 2. Re: Conditional Formatting

            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.


            Easiest way:

            Condition 1:

            GetAsDate ( AAA ) - Get ( CurrentDate ) > 60 and GetAsDate ( AAA ) - Get ( CurrentDate ) <= 90

            Pick an amber fill color


            Condition 2:

            GetAsDate ( AAA ) - Get ( CurrentDate ) > 30 and GetAsDate ( AAA ) - Get ( CurrentDate ) <= 60

            Pick a color for your fill color


            Condition 3:

            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.

            • 3. Re: Conditional Formatting

              Thank you. I shall try that.

              • 4. Re: Conditional Formatting

                Thank you; I'll check this out.  And nope, that was a typo. They will have diff colours.

                • 5. Re: Conditional Formatting

                  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.

                  • 6. Re: Conditional Formatting

                    You totally understand what I am after. Thanks.

                    • 7. Re: Conditional Formatting

                      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.

                      • 8. Re: Conditional Formatting

                        I wanted to mark BOTH your and


                        answers as correct. Tried both methods.


                        I marked jrenfrew first so I guess FM will not allow me to mark 2 replies as correct. I appreciate  your explanation though and wanted you to  know. Thanks again.

                        • 9. Re: Conditional Formatting

                          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.

                          • 10. Re: Conditional Formatting

                            Interesting; I never thought of setting a variable to current date.

                            Would it make that much of a dif?

                            • 11. Re: Conditional Formatting

                              I'll definitely be keeping you in mind! Thanks.

                              • 12. Re: Conditional Formatting

                                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.

                                • 13. Re: Conditional Formatting

                                  I see your point. Thanks.