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?

Improve this question
1

5 Answers 5

Reset to default
26

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.

Improve this answer
2
  • 6
    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, 2013 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, 2014 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.

Improve this answer
1

With spring20, you can use a before-save flow and native duplicate management to block duplicate records on junction objects

Native duplicate rules only allow you to reference a single relationship field. However before-save flows fire before duplicate rules, so they can concatenate both IDs into a compound key, which can be referenced in the matching rule.

Here are the steps

  1. create a text field named Compound Key on your junction object
  2. create a flow
  3. click on the start button and set the flow to launch on ‘new or updated records’ (you can decide if it fires on insert, update, or both)
  4. add an assignment element that sets Compound Key to a formula field which concatenates the two ID fields on the record
  5. create and activate a matching rule that matches on Compound Key (exact)
  6. create and activate a dupe rule that alerts / blocks - whatever makes sense in your case

More details and screenshots on this blog post

It may be a better UX to have the duplicate rule match on one lookup, and to name the field set by the flow something more human readable, like 'duplicate account Id', instead of compoundKey. See the screenshot below for the UX of the Proof of concept

enter image description here

Improve this answer
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.

Improve this answer
3
  • 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, 2013 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, 2013 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, 2013 at 17:39
0

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

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .