6 Replies Latest reply on Oct 3, 2012 2:05 PM by veramilo_1

    Warning idicator when it appears a duplicate entry is being keyed

    veramilo_1

      Title

      Warning idicator when it appears a duplicate entry is being keyed

      Post

           I'm setting up a system to track student info for after school program enrollments at multiple schools.  We receive enrollment forms that need to be keyed in, and many students enroll in multiple programs over the course of a year.  I want to keep the user from creating a new student record for a previously enrolled student when they enroll in another program.

           I have fields for the student's first name, last name, schoolID#, and DOB as well as their unique studentID#.  When someone completes the first 4 fields on the "new student" screen, I want them to receive a warning if a record already exists with the same info in the first 4 of those fields.  I know it won't account for miskeyed entries, but it will do for now.  Conditional formatting (like red field text) would be sufficient for the warning.

           I added a "dupeDetector" field (calculation field that concatenates the first 4 fields) with a self-join thinking that would be the thing to do but I can't figure out where to go from there.

           Can someone help me out?

        • 1. Re: Warning idicator when it appears a duplicate entry is being keyed
          schamblee

               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.

                

               sample app:

          https://www.sugarsync.com/pf/D9559058_790_724029665

          • 2. Re: Warning idicator when it appears a duplicate entry is being keyed
            veramilo_1

                 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!

            • 3. Re: Warning idicator when it appears a duplicate entry is being keyed
              schamblee

                   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.

              http://info.filemaker.com/US-en-FMP-Trial-Form.html?try=fmp12-product-try

              • 4. Re: Warning idicator when it appears a duplicate entry is being keyed
                veramilo_1

                     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!

                • 5. Re: Warning idicator when it appears a duplicate entry is being keyed
                  philmodjunk

                       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:

                       Commit Records
                       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.

                  • 6. Re: Warning idicator when it appears a duplicate entry is being keyed
                    veramilo_1

                         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!