7 Replies Latest reply on Apr 22, 2014 7:51 PM by Shinobi

    Duplicate Records

    Shinobi

      Title

      Duplicate Records

      Post

           I am new to FileMaker and wanting to create a trust database. Please can someone explain simply how to stop duplicate records?

           For example a Trustee is a trustee for multiple trusts. I want to have just one record rather than the same person entered in multiple times.

           Any help would be greatly appreciated. Thank you.

        • 1. Re: Duplicate Records
          RickWhitelaw

               You need to tell us about your tables and relationships before we can give you advice. Not enough information in your question.

          • 2. Re: Duplicate Records
            Shinobi

                 Oops sorry, Ok so for my tables I have the main Trust table then I have – Roles (has trustee, has beneficiary etc) – Entity (natural persons, Company etc)

                 For my relationships I have the TrustID in my Trust table going to TrustID in my Role table then from my Role table I have EntityID going to EntityID on my Entity table.

                 Many thanks for your help

            • 3. Re: Duplicate Records
              dejosejimpaul

                   Hi Shinobi, good evening. Base on my understanding of your problem, you don't want to have a duplicate value on your Roles table which contains both TrustID and EntityID. If this is the case at least from my point of view, you would want the combination of your RolesID (from your Roles table), TrustID (from your Trust table), and EntityID (from your Entity table) to be unique and the way you could do that is to add a calculation field to your Roles table that concatenates these three fields and has a validation set to "unique".

                   Say you have a calculation field named ConcatenatedID and have a calculation like below:

                   RolesID & TrustID & EntityID

                   after defining the calculation above, you would then set this field's validation to unique by checking "unique" on the "Validation" tab.

                   this will give you the combined values of RolesID, TrustID, and EntityID like Role1Trust1Entity1 and will prevent you to have another occurence of Role1Trust1Entity1.

                   There you have it Shinobi, hope this helps or at least give you an idea.

                   Jim Paul

              • 4. Re: Duplicate Records
                philmodjunk
                     

                          and the way you could do that is to add a calculation field to your Roles table that concatenates these three fields and has a validation set to "unique".

                     Not quite. Make that a text field with an auto-entered calculation and this will work. You can't set a field validation option on a field of type calculation, but you can with auto-entered calculations.

                     There are also other options where the user interface doesn't allow you to create a duplicate record in the roles table in the first place.

                     See the method demonstrated in this demo file. One uses Jim Paul's validation method on a field with an auto-entered calculation, but then also demonstrates two layout designs where the user is not permitted to create the duplicate value instead of catching the error after it has occurred.

                https://www.dropbox.com/s/oyir7cs0yxmbn6i/ManyToManywDemoWExtras.fp7

                     This file is in the older format. If you are using FileMaker 12 or 13, use Open from FileMaker's File menu to open this file and FileMaker will produce a copy of this file converted to the newer file format.

                • 5. Re: Duplicate Records
                  dejosejimpaul

                       Oops, my mistake. Thanks PhiliModJunk for pointing that out. Btw, I'm interested in the other method you have mentioned. and maybe I could use the same technique.

                  • 6. Re: Duplicate Records
                    philmodjunk

                         You are welcome to download and examine the demo file. It demo's three methods:

                         A validation field option

                         A diminishing value list where each value selected for the join table drops that value from the value list so that it cannot be selected a second time.

                         A "check boxes" format (that aren't really check boxes) for selecting values from a portal. Since selecting the same value a second time clears the "check box", the user does not have an option that allows them to select the same value twice.

                    • 7. Re: Duplicate Records
                      Shinobi

                           Thank you so much for your help its really appreciated  - I will have a go!