Here is a sample based on your calculation field. I created a second calculation field based on the relationship (table occurrence) that counts the number of warning calculation (dupeDetector) that match. Then it turn the first name and the count to red. You can make any field turn red I just did two for this sample. Couple of Notes : I used Upper() in the calculation field so this will catch upper and lower characters entered. Your condition format must use the table occurance so the count is correct. If you don't use the table occurrence it will count all warning calculation even if they don't match.
Unfortunately, I am using FMP 11 and cannot upgrade to 12 just yet so I couldn't open the file. Is it possible to save backwards and repost without too much trouble? If not, can you post your formula for the second calculation field you created? I might be able to figure it out from there.
Thank you for your time and quick reply!
I used a summary field "Count Of "(CountDupeField) to count the dupe calculation field . I only have fmp12 advance. You want to use the summary field from the relationship so it only counts the records with matching dupe field. Student 2::CountDupeField. Then your condition formatting would a be a formula Student 2::CountDupeField > 1
Here is a link to free trail of 12. Just incase you can't get it figured out from the above. You can post additional questions.
The v12 trial would be a good idea, but I wanted to wait to install that until I had an idea of which features I want to try out -- and when I have more time to do so.
I've emersed myself in another part of the system temporarily, but I'll work with the info you provided when I return to that section. Thank you again!
You might want to set up a layout/window with just 4 global fields for this information and this relationship:
GlobalsTable::firstName = Students::FirstName AND
GlobalsTable::lastName = Students::lastName AND
GlobalsTable::schoolID# = Students::schoolID# AND
GlobalsTable::DOB = Students::DOB
Then this expression: Not IsEmpty ( Students::FirstName )
will be true if a matching record already exists in students.
On the other hand, if you enable "allow creation of records via this relationship" for Students in the above relationship, performing this script from the GlobalsTable layout:
Set Field [Students::FirstName ; GlobalsTable::FirstName]
Go To Related Record [Show only related records; From table: Students; Using layout: "Students" (Students)]
Will create a new record in Students, only if a matching record does not already exist. Then the Go To Related Records step will pull up either that pre-existing record or the one just created by the set field step.
At first I was a very confused by this one, but after studying it, I believe this will work out best for me. I've added separate match indicators for each field and an ACCEPT and CANCEL button (rather than using commit) to create a new record in the Student table. This way I can display a warning dialog if the record already exists while still letting the user create a "seemingly" duplicate entry (in case there actually is another student with the same name & dob). Not only that, I can also allow the user the option of switching to a Student list for that school to manually verify if that student already exists, then come back to the entry screen to complete the entry or cancel.
Thank you to both of you for your help!