7 Replies Latest reply on Jan 12, 2010 1:21 PM by philmodjunk

    Conditionaly format field based on dates being the same

    dmpjporter

      Title

      Conditionaly format field based on dates being the same

      Post

      I have a layout in list view that is sorted by dates in a date field. I would like to have the dates change to red text if there are two of the same dates on that field. I know that this cannot be accomplished by using Conditional Formatting and I am thinking it has to be an If statement but I cannot seem to find a starting point.

       

      Can this be done and if so, could someone help point me in the right direction?

       

      Thanks.

       

      Dave

        • 1. Re: Conditionaly format field based on dates being the same
          philmodjunk
            

          Don't see why you can't set up a conditional format for this.

           

          This phrase puzzles me: "if there are two of the same dates on that field"

           

          Perhaps that's a typo and you meant two dates in the same record? That would be a much better way of recording date information and will make your task here much easier.

           

          Using the two date field set up...

           

          Table::datefield1 = Table::datefield2 is an expression you can use to set a conditional format such as red text for a field.

           

          If you are storing two dates in a text field or a calculation field that returns text, please give the format and I can give you an expression for that as well.

          • 2. Re: Conditionaly format field based on dates being the same
            dmpjporter
              

            Phil,

             

            Thanks for the answer, but it looks as though my wording is just a bit off. The dates represent an appointment record. What I am trying to do is if there are two dates that are the same, meaning that two appointments were scheduled for the same day, I would like those dates to change color.

             

            I took what you mentioned and tried to modify it to what I was thinking (Table::Field = Table::Field) and as you would have guessed, made all the date fields red and not just the duplicates. I would like to upload the file but I do not have it with me at my work computer. If I am not making any sense, I will just wait to get the file and upload an example.

             

            Thanks again for the help,


            Dave

            • 3. Re: Conditionaly format field based on dates being the same
              philmodjunk
                

              Please note that my original expression compares two different fields, field1 and field2. This expression assumed that two date fields were part of the same record.

               

              Are these date fields in different records to which you wish to compare? That can be done also, but it takes some more work setting up.

               

              It would help to post the actual names of the table and fields that you are asking about.

              • 4. Re: Conditionaly format field based on dates being the same
                DLW-BPEX
                  

                Dave,

                Pardon my butting in...

                Are you wanting to highlight any duplicates after the fact for some reason? Or do you really want to prevent dupes from being entered in the first place?

                If the latter, would the Require Unique Value validation option do what you want?

                 

                David

                • 5. Re: Conditionaly format field based on dates being the same
                  dmpjporter
                    

                  Although I do not have Filemaker with me, I will try to just rough it out. This is a representation of what I am after.

                   

                  The table name is Sessions and the first table shows what it looks like in the Edit Layout mode to include the actual field names.


                  The next table shows what I would like it to look in Browse mode. Since the second and third dates are the same, I would like to format them as red.

                   

                  As to whether or not I would like to prevent this all together, not at this point. I just want to alert the user that these two dates may conflict with each other, but not prevent them from scheduling something on the same day.

                   

                  Does this help explain it? If not, I will post more once I get home.

                   

                  Thanks again for all the help.

                   

                  Table Name - Sessions

                   

                  Appointment Date          Time                Client

                      Session Date        Session Time    Client Name

                   

                  Appointment Date           Time              Client

                  1/10/2010                  1:00 PM        Jim

                  1/15/2010                  9:30 AM        Kevin

                  1/15/2010                  2:00 PM        Jill

                   

                   

                   

                   

                   

                  • 6. Re: Conditionaly format field based on dates being the same
                    comment_1
                      

                    dmpjporter wrote:

                    I would like to have the dates change to red text if there are two of the same dates on that field.


                    This would be very easy to do if the records were sorted by the date field - then you could use the GetNthRecord() function to compare the date with adjacent records. Otherwise you'd need either a self-join relationship (which would ignore the found set) or a custom function to collect the dates from the entire found set.

                     

                    BTW, if you search for duplicates in the field, you'll find all the records that should be red.


                    • 7. Re: Conditionaly format field based on dates being the same
                      philmodjunk
                         I'd probably use the self-join option as Comment mentions, but include a filtering pair of fields to limit the matches to the specific doctor/counselor/therapist? for whom these clients have appointments.