2 Replies Latest reply on Oct 27, 2009 9:45 AM by mrvodka

    Conditional formatting inconsistency in FMP10

    Davis

      Title

      Conditional formatting inconsistency in FMP10

      Post

      My formatting on a date field says this...in this order

       

      Value is less than 2 days ahead, fill color = yellow (the date is coming up)

       

      Formula is GetAsDate(mydatefield)< Get (CurrentDate) & IsEmpty (my checkbox field), fill color = Red (You didn't check the box, and the date has passed, so color is red)

       

      Formula is GetAsDate(mydatefield)=Get (CurrentDate), fill color pink (you didn't check the box, and date is today)

       

      Value is empty, fill color white (there is not date, so no formatting)

       

      Formula is (my checkbox field) = "1", fill color white (the box is checked, so make the field white regarless)

       

      So, this seems to work most of the time, with a few exceptions. If there is a date for 2010, the field is red indicating that its late...possibly because it thinks that January comes before October...?

       

      Also, some dates that are Oct 1 are showing yellow, even though today's date is Oct 27. Very random colors. If anyone has a better way, please let me know.

       

      I had to put the last step in about checkbox="1" because it wasn't being recognized in previous steps so it has to be its own step...and that's possibly another bug..?

       

      Has anyone else had this happen? 

        • 1. Re: Conditional formatting inconsistency in FMP10
          Davis
            

          I figured it out...here's what I found with conditional formatting for anyone who's new to using it...

           

          Don't put multiple conditions on one formula line, such as "If X = 1 & if Y = 2" ...you will have to make 2 lines of conditions...1 line for "If X = 1" and another line for "if Y = 2" ...and you must put them in order from top to bottom.

           

          It doesn’t like the (Self), therefore we had to use the actual field name reference GetAsDate(MyDateField)

           

          As a reminder, the conditions are read from top to bottom.

           

          • 2. Re: Conditional formatting inconsistency in FMP10
            mrvodka
              

            Davis wrote:

            My formatting on a date field says this...in this order

             

            Value is less than 2 days ahead, fill color = yellow (the date is coming up)

             

            Formula is GetAsDate(mydatefield)< Get (CurrentDate) & IsEmpty (my checkbox field), fill color = Red (You didn't check the box, and the date has passed, so color is red)

             

            Formula is GetAsDate(mydatefield)=Get (CurrentDate), fill color pink (you didn't check the box, and date is today)

             

            Value is empty, fill color white (there is not date, so no formatting)

             

            Formula is (my checkbox field) = "1", fill color white (the box is checked, so make the field white regarless)



             

             GetAsDate(mydatefield)< Get (CurrentDate) & IsEmpty (my checkbox field)

             

             

             

            This wont work. You are using a concatenation instead of logical AND test.

             

            This will return 11 (1&1), if the date is less than today and the checkbox is empty. 11 is TRUE so it passes the test.

            However, if the date is less than today and the checkbox is not empty, the result would be 1, which is TRUE and passes the test.

             

             

            What you need is GetAsDate(mydatefield)< Get (CurrentDate) AND IsEmpty (my checkbox field)