I have two junction objects, say A and B, between User and Account. Now my scenario is that, when a record of A between User U1 and Account A1 - is deleted -> then I need to clear the user field having value U1 in object B records under the same account A1.

So in the bulkified implementation of this logic in a trigger, I have to consider the whole trigger.old( 200 records) and hence do the query in such a way

SELECT .. FROM B__c WHERE Account__c in Set1 AND User__c in Set2

Now this could possibly bring many unwanted records i.e. B records under A1 but under a different user say U2 that was a parent in another deleted A record. So my question is how to make this query more selective, such that I only want matching pairs of AccountID and UserID i.e. I only want B records in A1+U1 , A2+U2 etc occur. Hope I was clear.

An idea that came to my mind( haven't done feasibility check), create a formula field on object B, that contains the concatenated vlues of AccountID + UserID of that B record. Then use the WHERE clause as

SELECT ... FROM B__c WHERE IDConCat_Field__c IN SetWith IDConcatValues

I was hoping to actually implement this solution completely using apex and SOQL, Any ideas? Thanks in advance


Your concatenation scheme is certainly a viable solution. Your implementation will be much simpler if you use CASESAFEID, because a formula uses 15 digit values, but Apex uses 18.

Your formula would then look like:

CASESAFEID(Account__c) & CASESAFEID(User__c)

Then, in Apex, you could write helpers along the lines of:

public static Set<String> getConcatenatedLookup(List<A__c> records)
    Set<String> values = new Set<String>();
    for (A__c record : records) values.add(getConcatenatedLookup(record));
    return values;
public static String getConcatenatedLookup(A__c record)
{ // if neither reference is nillable this method is of little help
    String a = (record.Account__c == null) ? '' : record.Account__c;
    String u = (record.User__c == null) ? '' : record.User__c;
    return a + u;

Then getting the corresponding records is easy:

public static List<B__c> findCorrespondingJunctions(List<A__c> records)
    return [SELECT Id FROM B__c WHERE Concat__c IN :getConcatenatedLookup(records)];
| improve this answer | |
  • Thanks. I have implemented the same. Works great ( as of now ;) ) . Lets see what bugs the future holds for us. Cheers. – Prajith May 3 '16 at 0:21
  • @Stygon Good luck! – Adrian Larson May 3 '16 at 0:22

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.