4 Replies Latest reply on Apr 16, 2014 11:24 PM by JerryKallam

    Prevent duplicate entry in a related table

    JerryKallam

      Title

      Prevent duplicate entry in a related table

      Post

           I have two tables in a Horse Show Management program.  Base Table is "Entries" and the related table is "Horse".

           While in the entries table I want to add a duplicate entry trap script that prevents the creation of duplicate records in the "Horse" table.  I want to use a script rather than relying on Filemakers validation scheme.

           So far I have created a script that triggers when user exits the related field entry_HORSE::HorseName on the main entry screen.

           Most of the posts I have read on duplicate entry traps either use the Filemaker validation scheme or use the lookup function with a self join to the original base table.  My problem seems to do with the fact that the field is in a related table and I have yet to figure out how to script this when involves a related table.

            

            

            

      mainentryscreen.png

        • 1. Re: Prevent duplicate entry in a related table
          philmodjunk

               So you have a relationship of Entries-----<Horse

               Entries::__pkEntryID = Horse::_fkEntryID (But your field names may differ from my example...)

               So a duplicate entry in Horse would be two records for the same Horse that also have the same value in _FkEntryID correct?

               Do you have a field in Horse that uniquely identifies each horse?

               Any method that you use to trap for duplicate entries will require that you check both the _fkEntryID and the HorseID field values as a combined value to test for uniqueness. Validation field options can do this on an auto-enter field that combines the two values. A self join relationship to do the same thing might look like this:

               Horse::_fkEntryID = Horse|DupCheck::_fkEntryID AND
               Horse::HorseID = Horse|DupCheck::HorseID

               But I can't help but wonder if you shouldn't have a join table between Horse and Entry to facilitate a many to many relationship here so that a horse can enter multiple events and an event can list multiple horses entered.

               For an explanation of the notation that I am using, see the first post of: Common Forum Relationship and Field Notations Explained

          • 2. Re: Prevent duplicate entry in a related table
            JerryKallam

                 Phil,

                 First - Thanks for taking the time of a response --

                 The example above is a layout called "Entries" and the associated table is "entry".  The relationship between the "entry" and "horse" table is one horse can have many entries but one entry may only have one horse.  So the relationship would be more like this:

                 entry>- - -horse

                 _fkHorseID = entry_Horse::__pkHorseID (allow record creation is checked)

                 On the screen shot above a new horse record is added via the related field entry_Horse::HorseName upon exit of that field as per the above relationship (and the allow record creation being checked) .  The new record created will have unique ID via auto increment to the entry_Horse::__pkHorseID. So to answer your question, duplicate entries in the "horse" table only have same value in the name field, not the ID field since it is the unique identifier.

                 My goal is (via a script and exit trigger) when you enter a horse named "Chester" in the related field entry_Horse::HorseName and you exit that field that a script see if a duplicate record containing "Chester" exists in the related "horse" table, then via custom dialog box fire off a warning to 1)Try Again or 2) Allow Record to be created.

                 So far I have created a self join between the related table as pictured below and used the lookup function in the following script:

                 <!--StartFragment-->Freeze Window
                 Set Variable [ $HorseName; Value:Lookup (entry_HORSE_name|dup::HorseName; "Not Found") ]
                 If [ $Horsename="Not Found" ]
                 Go to Layout [ original layout ]
                 Exit Script [ ] End If
                 Show Custom Dialog [ Title: "Duplicate Exhibitor Number"; Message: "Duplicate Horse Name Exists"; Default Button: “Try Again”, Commit: “No”; Button 2: “Allow”, Commit: “Yes” ]
                 If [ Get ( LastMessageChoice )=1 ]
                 Go to Layout [ original layout ]
                 Go to Field [ entry_HORSE::HorseName ]
                 Halt Script
                 End If
                 If [ Get ( LastMessageChoice )=2 ]
                 Go to Layout [ original layout ]
                 Exit Script [ ]
                 End If
            <!--EndFragment-->

                  

                  

                  

                  

            • 3. Re: Prevent duplicate entry in a related table
              philmodjunk

                   I'm still not clear in what you need to have set up here.

                   What does one record in Entry represent?

                   I'm still thinking that your basic relationships need modification to support a many to many relationship.

                   I looks like you are recording which horses are entered in a horse show. Since many horses can enter the same show but a given horse can enter many shows, this is a many to many relationship best managed with different relationships than what you have described here.

              • 4. Re: Prevent duplicate entry in a related table
                JerryKallam

                     I made a test file that shows the problem simplified.

                      

                     The test database contains 2 tables - signup and racer

                     I have a layout where a user can enter data from a simple entry form called signup.  Racer name is added from a related field on that same layout.  The goal is prevent duplicates via a script and not using any Filemaker validation.

                     The signup layout is pictured below.

                     Signup has the four fields shown in the layout signup and racer has two fields __pkRacerID and name.

                     I am trying to script a duplicate record warning script that trips when the user exits the field signup::BibNumber or the related field signup_RACER::name

                     The script that triggers when the user exits signup::BibNumber is as follows: Note this scripts works fine.

                     <!--StartFragment-->Freeze Window
                     #Debugging Variables
                     Set Variable [ $BibNumber; Value:Lookup (signup_SIGNUP::BibNumber; "Not Found") ]
                     Set Variable [ $BibNumber1; Value:signup::BibNumber ]
                     Set Variable [ $BibNumber2; Value:signup_SIGNUP::BibNumber ]
                     Set Variable [ $SignupID1; Value:signup::__pkSignupID ]
                     Set Variable [ $SignupID2; Value:signup_SIGNUP::__pkSignupID ]
                     #
                     If [ $BibNumber="Not Found" ]
                     Go to Layout [ original layout ]
                     Exit Script [ ]
                     End If
                     Show Custom Dialog [ Title: "Duplicate Bib Number"; Message: "Duplicate Bib Number Exists"; Default Button: “Try Again”, Commit: “No”; Button 2: “Allow”, Commit: “Yes” ]
                     If [ Get ( LastMessageChoice )=1 ]
                     Go to Layout [ original layout ]
                     Go to Field [ signup::BibNumber ]
                     Halt Script
                     End If
                     If [ Get ( LastMessageChoice )=2 ]
                     Go to Layout [ original layout ]
                     Exit Script [ ]
                     End If
                <!--EndFragment-->

                      

                The script that triggers when the user exits the related field signup_RACER::name is as follows:

                     NOTE: Even though the debugging variables contain the correct information the line - If [ $RacerName1 = $RacerName2 ] tests false even if

                     $RacerName1 = "jerry" and $RacerName2 = "jerry".

                      

                <!--StartFragment-->Freeze Window
                     #Debugging Variables
                     Set Variable [ $RacerName; Value:Lookup (signup_RACER _racerName|dup::name; "Not Found") ]
                     Set Variable [ $RacerName_1; Value:signup_RACER::name ]
                     Set Variable [ $RacerName2; Value:signup_RACER _racerName|dup::name ]
                     Set Variable [ $RacerID1; Value:signup::__fkRacerID ]
                     Set Variable [ $RacerID2; Value:signup_RACER _racerName|dup::__pkRacerID ]
                     #
                     If [ $RacerName1 = $RacerName2 ]
                     // If [ $RacerName="Not Found" ]
                     Go to Layout [ original layout ]
                     Exit Script [ ]
                     End If
                     Show Custom Dialog [ Title: "Duplicate Racer Name"; Message: "Duplicate Racer Name Exists"; Default Button: “Try Again”, Commit: “No”; Button 2: “Allow”, Commit: “Yes” ]
                     If [ Get ( LastMessageChoice )=1 ]
                      Go to Layout [ original layout ]
                      Go to Field [ signup_RACER::name ]
                     Halt Script
                     End If
                     If [ Get ( LastMessageChoice )=2 ]
                     Go to Layout [ original layout ]
                     Exit Script [ ]
                     End If
                <!--EndFragment--><!--EndFragment-->

                      

                To summarize, the two scripts above are to prevent or warn agains the user entering duplicate data.  The only problem I have is with the related field.

                Again I appreciate your help and hope my attempt at simplifying things helps.