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.
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,
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.
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?
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
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.
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.