4 Replies Latest reply on Nov 28, 2013 2:46 AM by mansr

    Duplicates within subset of records

    mansr

      Hello,

       

      I am trying to find a way to flag for duplicates during data entry, and so far I have not been able to find a way of doing this successfully for a specific situation. Here's the situation:

       

      I have a table called Link, which contains a field for urls (as well as some other fields). I also have a table called LinkCollection, which functions, as the name suggests, as a way of grouping together records in Link. The relationship between Link and LinkCollection is many-to-many, with a join table in between. Now, what I want is a function whereby I can catch duplicate urls in the records in Link attached to the same record in LinkCollection. In other words, duplicate urls are fine, as long as the records containing them are not linked to the same record in LinkCollection. I want validation to happen during data entry. Currently, the way to create Link records is via a portal, where the url field is one of several fields edited after creation of the record. It is therefore not the case of linking already filled out records, where validation could be a lot more simple.

       

      Does anyone have a good idea of how to solve this? I have tried self-join relationships and a couple of different script-based solutions, but nothing has worked. Would appreciate any help!

       

      Thanks!

       

      (Working in Filemaker Pro Advanced 12)

        • 1. Re: Duplicates within subset of records
          LyndsayHowarth

          A self relationship with a count of the related records might work for you...

           

          When the count is >1 you can show a flag or something...

           

          - Lyndsay

          • 2. Re: Duplicates within subset of records
            mansr

            Hi Lyndsay,

             

            Thank you for your reply. I'm just not sure what this self relationship would look like - I take it I would use the url field as the match field? The problem is, whether or not a record in Link is to be considered a duplicate depends on which LinkCollection it is connected to. Since the relationship between Link and LinkCollection is many-to-many, there is no reference to the LinkCollection in the Link record. Do you see my problem here?

             

            Thanks again!

            • 3. Re: Duplicates within subset of records
              erolst

              You create a self-join by adding another TO of the (as yet unnamed) join table between Links and LinkCollection to the Relationship Graph, and connecting it to the existing join table TO by using URL = URL and _fk_linkCollectionID = _fk_linkCollectionID as predicates.

               

              As Lyndsay suggested, set a flag, or use conditional formatting to alert the user to the duplicate(s), when the calculation of, say, Count ( JoinTable_SelfJoin::_fk_linkCollectionID ) > 1.

               

              Another strategy might be to select either a LinkCollection or a URL from a picker portal to set a global and use this, plus the primary ID of the record you're on, to check via a relationship if the particular combination exists, then either give a warning or create the desired join table entry. How exactly you'do this depends on your workflow, i.e. whether you're creating records from the URL or the LinkCollection context (or both).

              • 4. Re: Duplicates within subset of records
                mansr

                Hi erolst,

                 

                Thanks for your reply. I have managed to solve this - perhaps not in the most elegant way, but nonetheless! In the end I opted for creating a field called checkunique, which gets populated by script trigger after saving and validating data entered into link::url. (data entry happens in a portal, where what gets displayed is ruled by a global field in a third table). The data entered into checkunique is a concatenation of url and the id of the currently displayed link collection. I've set validation on this field to "always" and "unique value", which means that if the user enters a url twice in the current link collection, validation fails.

                 

                Thanks again for your help!