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

    Avoid duplicate records

    miw

      Title

      Avoid duplicate records

      Post

      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
          comment_1
             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
            chaw_1
               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
              comment_1
                 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
                chaw_1
                   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
                  comment_1
                    

                  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
                    chaw_1
                       Brilliant. I converted the date to a number and it all now works perfectly. Thank you!!
                    • 7. Re: Avoid duplicate records
                      Keystone
                        

                      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
                        philmodjunk
                          

                        Keystone,

                         

                        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
                          Keystone
                            

                          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 ]

                             Else

                                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
                            Keystone
                              

                            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
                              philmodjunk
                                

                              I'm choosing to respond on the other thread:

                               

                              Avoiding Duplicate Records on Entering

                               

                              check there for a response.