1

I have a class in which I'm building a List for an object and am utilizing a nested query. My trigger/class works perfectly when there is a record within that nested query. Everything breaks down when there is no nested qualifying record. For the records in my List, I want to be able to determine which record does not have a corresponding child record and then create one for that parent record.

Here's my List/Query:

List<Locations__c> locsList = [SELECT Id, (SELECT Id, CreatedDate, Location__c, Closed__c, On_Hold__c FROM FG_Lots__r WHERE Closed__c != TRUE AND On_Hold__c != TRUE LIMIT 1) From Locations__c WHERE Id IN :RecIds.keySet()];

I want to be able to determine which location doesn't have an FG_Lots__r record and then create a FG_Lots__r record and associate it with that location record.

Something like this:

for(Locations__c l : locsList){
        for(Inventory_Lots__c il : l.FG_Lots__r){
        if(il == null){
        Inventory_Lots__c newIL = new Inventory_Lots__c();
        }
    }
}

Not sure if I should put my list results into a map with my Location ID's and the FG_Lots__r Id's and then run a "get" function to determine if the value is null for the Keysets in a for loop?

The object that I'm writing this for has a LOT of SOQL queries based around it so I need to be really efficient with how I build this out and am not sure of what the best approach would be.

Any help is greatly appreciated!

2 Answers 2

2

You can do something like following code:

List<FG_Lots__c> listToInsert = new List<FG_Lots__c>();
for(Locations__c l : locsList){
    if(l.FG_Lots__r.size()==0){
        Inventory_Lots__c newIL = new Inventory_Lots__c();
        /* Logic before insert like filling fields of child object*/
        listToInsert.add(newIL);
    }
}
insert listToInsert;

This should solve your problem, you need only one query(which you provided in question) and one insert.

Hope I helped.

1
  • Thanks a lot Kamil, simple and concise! It worked great. Much appreciated. Commented May 28, 2015 at 19:00
1

So, basically you want to check if the parent object has any child records. If yes, it's okay. If not, you want to create a child record for the same.

You may would like to leverage Aggregate functions in SOQL to achieve such functionality. You can do something like this:

//Summarize the data based on number of childs each parent has.
List<AggregateResult> results = [SELECT Count(Id) cons, CreatedDate, Location__c, Closed__c, On_Hold__c FROM FG_Lots__c WHERE Closed__c != TRUE AND On_Hold__c != TRUE and Location__c in :RecIds.keySet() GROUP BY Location__c];

//Loop over the result to check which parent has zero childs and insert child for them
for(AggregateResult ar : results){
if((Integer)ar.get('cons') == 0)
FG_Lots__c fg = new FG_Lots__c(Location__c = (Id)ar.get('Location__c')//Set field values here);
}

This way the code looks simple and it's easy to maintain. We can also do SUM, MAX, MIN etc using Aggregate SOQL. See yourself

Let me know if I understood your requirement properly and if this serves your purpose.

P.S - You may need to alter the field names above according to your requirement.

0

You must log in to answer this question.

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