24

I have a junction object C of A and B.

I found out that I can create multiple entries of A and B in Object C - this is wrong, as in my case, each combination of A and B should appear only once in Junction object C. The second time, someone will try to enter A and B as a new entry in C - it should fail.

How can I enforce this?

24

Create a text field (External Id, Unique) on the junction object.

Create a workflow on C to fire Everytime the record is created or edited (to account for cases where junction object records are reparented) to set this field as the concatenation of the Ids of A and B records that the C record is relating.

This workflow trying to set the composite primary key field will generate an error if a duplicate record is being inserted.

You can add to the composite key to include any other uniqueness criteria such as relationship type if you have that notion.

The workflow will fire only for new records created, you will need to retrospectively populate for already existing records possibly by extracting and concatenation via data loader, or simply do a phantom update on the junction records and that should cause the workflow to fire and set the key. However this could also throw some errors for any existing duplicates which you may need to resolve manually.

  • 5
    Also make sure your text field is unique case sensitive. You can even set the workflow to only run on OR( ISNEW(), ISCHANGED( Master_1__c ), ISCHANGED( Master_2__c ) ). – Thomas Crouse Jun 13 '13 at 3:43
  • 1
    Is there a specific reason why the field needs to be marked as an "ExternalId"? It seems like "Unique" attribute would suffice, and since Salesforce limits the number of "External Id" fields there can be on an object... – dana Aug 25 '14 at 20:59
3

I thought that junction objects were specifically for Many-to-Many relationships and as such the behaviour you describe is what I would expect.

You could enforce the uniqueness with a Trigger on Object C or as techtrekker says with workflow for a no-code solution.

0

Another solution would be to create a validation rule on object C which will do a VLOOKUP on A and B based on the populated lookups and also exclude the record itself. You can also handle this with a nicer error message.

  • 1
    The problem with this answer is that VLOOKUP only allows lookups by record name, which may not be unique. I could be attempting to set a parent named "Some Record" with an id of xxxyyyyyyyyyyyy, but if VLOOKUP first finds the record named "Some Record" with id xxxzzzzzzzzzzzz then my validation rule fails. – Thomas Crouse Jun 13 '13 at 3:39
  • You can lookup ID as well, as per documentation VLOOKUP(field_to_return, field_on_lookup_object, lookup_value) and replace field_to_return with the field that contains the value you want returned, field_on_lookup_object with the field on the related object that contains the value you want to match, and lookup_value with the value you want to match.. – Boris Bachovski Jun 14 '13 at 4:12
  • You can return the Id of the found record, but you cannot find the record based on its Id, as per documentation (look under "Tips") The field_on_lookup_object must be the Record Name field on a custom object. – Thomas Crouse Jun 14 '13 at 17:39
0

You could create a trigger that queries for other junction objects that have the same combination of A and B.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.