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

    Conditional Formatting

    mrosenhek

      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
          jrenfrew

          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
            dtcgnet

            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
              mrosenhek

              Thank you. I shall try that.

              • 4. Re: Conditional Formatting
                mrosenhek

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

                • 5. Re: Conditional Formatting
                  dtcgnet

                  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
                    mrosenhek

                    You totally understand what I am after. Thanks.

                    • 7. Re: Conditional Formatting
                      dtcgnet

                      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
                        mrosenhek

                        I wanted to mark BOTH your and

                        jrenfrew

                        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
                          dtcgnet

                          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
                            mrosenhek

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

                            Would it make that much of a dif?

                            • 11. Re: Conditional Formatting
                              mrosenhek

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

                              • 12. Re: Conditional Formatting
                                dtcgnet

                                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
                                  mrosenhek

                                  I see your point. Thanks.