1

I am looking for a way to prevent the addition of two records in a related list if one field's value in a second record meets a particular condition. We have a junction object ("Application Product") between a custom object ("Application") and the SF standard "Product" object. The junction object has a lookup to Product, and the "Application Product" record is created via a related list on "Application."

Is there a way to write a validation rule -- or a flow -- that will prevent two records in "Application Product" linked to the same Application from having both Product 1 and Product 2? Other Product combinations are acceptable (e.g., one Application Product is "Product 1" and another is "Product 3"). I've tried a validation rule on Application Product with a VLookup to no avail because there is no way to limit the validation to records linked only to one Application record. A flow would be great via a "Create Application Product" button, and I'm trying to avoid using Apex entirely.

The pseudo code would read: Multiple "Application Product" records can be created against an "Application," but if "Product 1" is selected and saved, another record cannot be saved against the linked Application.

Thanks.

2
  • It depends how extensible you want it. Is Product1 the only product you want to limit?
    – Nick C
    Mar 6, 2016 at 22:21
  • Did you make any progress on this question?
    – Adrian Larson
    Mar 9, 2016 at 20:58

2 Answers 2

1

If you have a Master-Detail relationship from Application Product to Application (or are willing to install declarative-lookup-rollup-summaries), you can create a rollup summary for Product_1_Count__c, Product_2_Count__c, and Product_3_Count__c. Then, you can create a validation rule on Application Product as follows:

OR(
    AND(
        Product__r.Name != 'Product 1',
        Appication__r.Product_1_Count__c > 0
    ),
    AND(
        Product__r.Name != 'Product 2',
        Application__r.Product_2_Count__c > 0
    ),
    AND(
        Product__r.Name != 'Product 3',
        Application__r.Product_3_Count__c > 0
    )
)
4
  • My description was somewhat simplified. Specifically, we have about 70 products, and three need to be exclusive. That is, Products 1, 2 and 3 must be stand-alone. If any of the three are selected, no other products are allowed. Mar 6, 2016 at 22:57
  • And I've already defined a master-detail on Application, so the relationship between Application and Application Product is only via Lookup (so maybe not by definition a junction object). Mar 6, 2016 at 22:59
  • @KevinBrown Should be pretty easy to scale to three products...I updated my answer to reflect that scenario as I understand it.
    – Adrian Larson
    Mar 6, 2016 at 23:05
  • hmm -- i think I'd put the VR on the parent Application to handle bulk insert/update use cases - assuming master-detail RSF or DLRS (on lookups). I also think you could generalize this to having a field on Application Product called isMutexProduct__c and simply RSF the count of these. If count > 1, then error
    – cropredy
    Mar 7, 2016 at 2:10
1

The declarative-lookup-rollup-summaries package didn't seem feasible, and without a master-detail relationship or roll-up fields, I decided to use a visual flow. It is launched via a button, collects the list of Products and displays them for selection. The flow then looks up any Products already added and compares them to those selected. If none are already present, validation within the flow -- using loops and decisions -- determines if any dis-allowed combination exists.

Thanks, everyone, for your suggestions.

You must log in to answer this question.

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