11 Replies Latest reply on Nov 2, 2009 11:18 AM by philmodjunk

    Avoid duplicate records



      Avoid duplicate records


      I know how to avoid duplicate records on a single field input through validation.  However, during input, I want to avoid creating a duplicate record based on two field input parameters.  For example, I have two fields to complete, Field A and Field B.  I do not want to create a record which duplicates the same inputs in both fields.  Say I already have a record that notes "USA" in Field A and "Blue" in Field B - I do not want to replicate that same set of values in a subsequent record ("USA" and "Red" would be fine and so would "France" and "Blue" but not another "USA" and "Blue").  Any suggestions for a script after making an input in Field B which will warn me that I am replicating a prior record that has the same inputs in both A and B?

        • 1. Re: Avoid duplicate records
             Define a third field as Text, with auto-entered calculation (replacing existing data) concatenating the two fields. Validate this field as unique, validate always.
          • 2. Re: Avoid duplicate records
               I've just started using Filemaker after becoming fed up with MS Access and I'm also struggling with the duplicat record problem. Is there any other way to ensure a unique record other than creating a new field that cocatenates two or more other fields? In Access, all you do is create an index field based on the two (or more) fields and ensure it is unique. Hence, no additional field needs to be created.
            • 3. Re: Avoid duplicate records
                 There is another way: you can define a self-join relationship matching on two (or more) fields, and validate by counting related records. But I'd rather have one more field than one more relationship. Also, using a field avoids issues with new, yet uncommittted records, which may not be perceived by the relationship.
              • 4. Re: Avoid duplicate records
                   Thank you. I did as you suggested, and tried to produce the new field but hit another snag. One of the fields is a text field and the other is a date field, so I couldn't work out how to create the calculated field. Does the Date field first have to be converted to another data type? Or is there another way?
                • 5. Re: Avoid duplicate records

                  If you make the calculation, for example =


                  Textfield & " | " & Datefield


                  the date will be automatically converted to text. However, it would be better to use =


                  Textfield & " | " & GetAsNumber ( Datefield )


                  so it remains independent of the date format in use.
                  • 6. Re: Avoid duplicate records
                       Brilliant. I converted the date to a number and it all now works perfectly. Thank you!!
                    • 7. Re: Avoid duplicate records

                      Here's my issue...


                      - I have a third field that combines First_Name and Last_Name, called Full_Name


                      - Full_Name is displayed on my Header (for display purposes only, the user doesn't tab to it)


                      - What I'd like to see is after I enter a value in First_Name AND Last_Name (after I tab out of Last_Name), a pop up dialog box comes up warning the user of a duplicate (without having to click on the Full_Name field).


                      - The dialog box should give the user the following options:


                      "This name exists, what would you like to do?"


                      Options: "Continue with this record" ; "Go to Duplicate" 


                      Any suggestions on a script that would allow me to do this?


                      NOTE: I'm a complete Newbie when it comes to scripts, and I'm using FMP 10.


                      Thanks in advance! 

                      • 8. Re: Avoid duplicate records



                        You were shown a scripted option for this in the thread you started:


                        Avoiding Duplicate Records on Entering


                        It would only take some minor modifications to do exactly what you want.

                        • 9. Re: Avoid duplicate records

                          Yes, thanks. I'm trying to modify that script:


                          Set Error Capture [ On ]

                          Commit Records/Requests

                          If [ Get ( LastError ) ≠ 0 /* validation error */ ]

                             Show Custom Dialog [ Message: "Combined first and last names must be unique."; Buttons: “OK”, “Cancel” ]

                             If [ Get ( LastMessageChoice ) = 1 /* OK was clicked */ ]

                                Go to Field [ NameValidation::Last ]

                                Exit Script [ Result: False ]


                                Revert Record/Request [ No dialog ]

                             End If

                          End If 


                          But it doesn't seem to do what I need it to (I'm probably modifying it incorrectly). i.e. I need it to validate the "Full_Name" field (which the user doesn't edit, it's just displayed), after the "First_name" and "Last_Name" fields are entered. 


                          So the user enters "First_Name", tabs to "Last Name", and as they tab to the next field the dialog pops up (if Full_Name is a duplicate).


                          Sorry, like I say, I'm new to scripts. 


                          • 10. Re: Avoid duplicate records

                            I'm pulling my hair out.


                            I don't see how I add these lines to the script: 


                            Go to Field [ NameValidation::Last ]        (do I click on "Select Perform" or "Go to target Field"?) Why can't I get the script line to look like this??

                                  Exit Script [ Result: False ]            (when I enter this into the calculation box, this just gives me a "The specified table cannot be found" error.)


                            Sorry, I've been trying to figure it out myself, re-reading Missing Manual, etc. but this has me stumped. 



                            • 11. Re: Avoid duplicate records

                              I'm choosing to respond on the other thread:


                              Avoiding Duplicate Records on Entering


                              check there for a response.