Look for records related to another related records within specific criteria

Question

I am trying to created a set of records based on some criteria and i need to figure out the best way to do this.

I want to create a record for every object A and B that has an specific Account object. object A needs to have a status of "DONE", and B a status of "READY" in order to create the record (which will be an Object A with an "Active" status) the fields on the new Object A will copy from some of the Object A and B fields.

This is a process im not triggering from user action but a set of records i need to already dump in the database. I do have a sandbox to start working on and testing then rolling out.

Please let me know the easiest way to do this.

I appreciate the help!

eyescream eyescream · Accepted Answer · 2018-02-03T20:47:09

You didn't provide enough info, we don't know how the relations look like. Are A and B related lists under Account? Are they independent or is there some link from one to another?

I'll write what I would do as a script (if needed you could make a batch job out of it or perhaps you'll be more comfortable with Data Loader, reports with cross filters, MS Excel and orgy of VLOOKUPs...)

To identify all "candidate" accounts you can try with this skeleton of a query

SELECT Id, Name
FROM Account
WHERE Id IN (SELECT Account__c FROM A__c WHERE Status__c = 'Done')
AND Id IN (SELECT Account__c FROM B__c WHERE Status__c = 'Ready')
LIMIT 10000

Now, the question about amounts of data. Will it return 10K (which is limit of records you can insert/update/modify in single transaction), if more - you might have to chunk it somehow... Maybe ORDER BY Id, record Id of last processed Account and in next iteration add AND Id > '001....'

Anyway, we got a "candidate", well, maybe he already has an Active A record, we wouldn't want to make a duplicate. And besides we need to pull some fields from B so they'd be copied across. So let's modify the query a bit, to add "subqueries" (think of them as related lists or LEFT INNER JOINs if that helps)

SELECT Id, Name,
    (SELECT Id FROM As__r WHERE Status__c = 'Active' LIMIT 1),
    (SELECT SomeField__c, SomeOtherField__c FROM Bs__r WHERE Status__c = 'Ready' LIMIT 1)
FROM Account
WHERE Id IN (SELECT Account__c FROM A__c WHERE Status__c = 'Done')
AND Id IN (SELECT Account__c FROM B__c WHERE Status__c = 'Ready')
LIMIT 10000

Nice. So now you need to loop through accounts, see if they contain that at least 1 active record (and if they do - skip). If they don't - create new one.

List<A__c> toInsert = new List<A__c>();
for(Account a : [SELECT...]){
    if(a.As__r.isEmpty() && !a.Bs__r.isEmpty()){
        toInsert.add(new A__c(
            Account__c = a.Id,
            Status__c = 'Active',
            Foo__c = a.Bs__r[0].SomeField__c,
            Bar__c = a.Bs__r[0].SomeOtherField__c + 7
        ));
    }
}
insert toInsert;

Look for records related to another related records within specific criteria

1 Answers