2 Replies Latest reply on Apr 21, 2015 5:29 AM by KylerPhilips

    Prevent duplicate Ids being created in join table portal



      Prevent duplicate Ids being created in join table portal



      I have a button which allows a portal with customers to be clicked on a relevant record and then the script (attached image) adds it into my ClientEquipmentJoin table and associates a client with an item. In short two portals - the client portal allowing the creation of records within the equipment join table. How can I prevent duplicate __pkClientIds being created in the join table that I have? 

      Tables: Clients, Equipment, ClientEquipmentJoinTable

      Maybe a dialog box that pops up saying you've already added this client to the item join table. 







        • 1. Re: Prevent duplicate Ids being created in join table portal

          I am guessing that you have this relationship:


          And I would imagine that you do want multiple records in the join table with the same ClientID, but only one for a given pairing of EquipmentID and ClientID.

          That sound correct. In other words, client can be linked to Equipment Records 123 and also linked to 456, but not linked multiple times to equipment 123.

          The simplest way to catch this (not necessarily the best) is to define a text field in the join table with this auto-enter calculation:

          EquipmentID & "|" & ClientID

          And then set a unique values validation on this added text field.

          You could also set up an added occurrence of the join table and link it to your existing table occurrence for the Join by both equipmentID and ClientID. You can then count the number of related records in this added occurrence of the join to determine if creating this new record will result in duplication.

          Or there are ways to make the client unavailable (they no longer appear in your portal) if they have already been linked to the current Equipment record. This prevents the user from making this mistake in the first place.

          • 2. Re: Prevent duplicate Ids being created in join table portal

            Thanks Phil, 

            Using your first method, it still seems to allow duplicate entries in a join portal.  I made a text field with calcuation of: "__fkClientId & " " & _fkEquipmentId" ( join table foreign keys) - do I need to use primary keys from each table? and then set a Unique Value validation. Still no luck when trying this still just goes through and creates another record. When I create the auto - enter creation field nothing is in there when added to the join table layout screen. It doesn't pull info, when I make it just a standalone calculation field the formulas pull straight into the field. 


            EDIT: I realised that the field wasn't caluclating records that had already been created, that was why! Sorted now