7 Replies Latest reply on Dec 12, 2013 9:21 AM by philmodjunk

    Duplicate data in same field

    AdamRandall

      Title

      Duplicate data in same field

      Post

           I use a calculation to extract the company name from an email address. Sometimes I end up with duplicates in the 'company' field (eg info@xyz.com and sales@xyz.com with give me xyz in two company fields). I want to set up a field that will flag up these duplicates as I want to manually choose with record(s) to delete.

           Anyone have a code I can use?

           Adam Randall

        • 1. Re: Duplicate data in same field
          philmodjunk

               Please describe your design in more detail. Would those two fields with the duplicate company name be the same field in two different records or two fields in the same record?

          • 2. Re: Duplicate data in same field
            AdamRandall

                 The two fields are in two different records. I would want them both flagged up so I could remove the one I want, and then the remaining record would become unflagged as there would be no duplication.

            • 3. Re: Duplicate data in same field
              philmodjunk

                   The simplest way to trap for duplicate values is to go to field options and specify unique values on the validation tab. This will not, however find and display the matching record.

                   You could, however, use the OnObjectValidate trigger to run a script that checks for duplicates and performs a find to show the record with the matching value.

                   A self join relationship where this field is the match field can be used to check for records with a matching value.

              • 4. Re: Duplicate data in same field
                AdamRandall

                     Hiya,

                     I understand that 'unique values' would just not permit the second record to be created.

                     I'd like to create a new field that would look at the information held the the company field in its record and compare it to all the other company fields in the other records. If it finds a duplicate or more it would respond with a message, otherwise stay blank - a kind of if/else equation.

                     Then, when the duplicate(s) have been deleted and there is only one left the message would disappear - I could search for duplicates visually or by 'not blank' command.

                     Adam

                • 5. Re: Duplicate data in same field
                  philmodjunk

                       You don't need a new field to do that. Just a self join that matches by the Company field.

                       YourTable::Company = YourTable 2::Company

                       then

                       you can use:

                       Count ( YourTable 2::Company ) to check for duplicate values

                  • 6. Re: Duplicate data in same field
                    AdamRandall

                         Hiya,

                          

                         attached copy of file with duplicate files.

                         how does the YourTable work?

                         i thinks that's a bit advanced for me!!

                          

                         adam

                    • 7. Re: Duplicate data in same field
                      philmodjunk

                           You cannot upload FileMaker files directly to this forum. But you can upload the file to a sharing site such as "drop box" and then post the download link here.

                           But "YourTable" is just my placeholder for the name of your table. You would open Manage | Database | relationships, find the box for your table of data, select it and then click the duplicate button (two green plus signs) to make a duplicate Tutorial: What are Table Occurrences?. Then you drag to connect fields to set up your relationship.