1

please see my scenario. I have 2 custom objects A(child) and B(parent>. Date model is such that Account is the master detail parent of A, while B is the lookup parent of A. The objects Account and B are hence related to each other by this object A which acts as a junction.

The requirement was that no two records of the object A, under the same account and same B should have the same name. I implemented this using a before insert,before update trigger on the object A(child). The logic is as follows:

  1. Created a Set that will store a concatenated value of IDs and Name
  2. Queried all the records of object A from the database(for update-> didn't query the current records)
  3. Iterated through the query result, added entries in the Set of the form => Account ID + B Record ID + A Name
  4. Iterated through trigger.new and compared the [[ Account ID + B Record ID + A Name ]] of every record in trigger.new, using .contains to see if it exists in the set.
  5. If the value exists => addError

I feel like the algorithm is inefficient, because we query all the records of the object A for just this purpose. We are using from 50,000 max query rows in a transaction gov limit. There are other features that follow this on the same trigger too, so I am worried about hitting the 50,000 row governor limit if the number of records of the object A increase to say 10K.

Is there any way to implement this better, may be using duplication rules?

In general, what are the safeguards or design patterns one can put up against hitting the 50,000 row SOQL limit when the number of records in the database increases. Thank you for your patience.

  • 4
    why not a) create a formula field on A that is the synthesized lookup key and b) use WF to copy that value into a string field defined as Unique. Then SFDC does the dup checking for you – cropredy Feb 26 '16 at 6:36
  • @crop1645 The error message that appears when a "duplicate" record is found needed to be customised. Couldn't find a way to customize the error produced by the system when it finds a duplicate record. It was affected the user experience. – Prajith Feb 26 '16 at 6:41
  • is there a design where you catch the error and change the error message? – cropredy Feb 26 '16 at 6:45
  • @crop1645 The error we get when we go by the method you suggested earlier, is totally system generated right. That will happen as a part of system validation, which happens before the before trigger itself. So if my understanding is right, the execution stops even before any code comes into play. – Prajith Feb 26 '16 at 6:57
1

I'll build off of crop1645's suggestion.

If you have a simple formula field, one with no cross-object references, the formula field will have a value available in both Before Insert and Before Update triggers. (I haven't tested this with cross-object formula fields)

A formula field to generate the synthesized key, A.Account__c + A.B__c + A.Name, would meet this criteria. For sake of example, I'll call this formula field compKey__c.

Provided that the lookups to Account and B, as well as the Name of object A, are populated or updated before inserting/updating, you could modify your logic as follows:

  • Iterate over trigger.new, storing compKey__c in a Set<String> keys
  • [SELECT compKey__c FROM A WHERE compKey__c IN :keys], iterate over the result of the query, storing the compKey__c in a new set Set<String> conflicts
  • Iterate over trigger.new once more, checking if compKey__c exists in the conflicts set. If it does, addError()
    • Might be a good idea to check conflicts.isEmpty() to possibly avoid this second iteration over trigger.new

This should be able to create a single block of code that can handle both Before Insert and Before Update triggers.

Formula fields don't have indexes on them by default, which means my example query would result in a tableScan. It is possible to call in to support and have them put a custom index on the compKey__cformula field so the query would not require a tableScan.

If you don't want to go through that though, then a workflow rule to copy the formula field into a different text field (and using that text field in the query instead of the formula field) would be what you want to do.

It's important to be able to avoid the tableScan. Besides being less performant, you will run into a Non-selective query against large object type queryException using the example query if your A junction object ever grows over 100,000 records.

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.