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!


You can do something like following code:

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

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

Hope I helped.

  • Thanks a lot Kamil, simple and concise! It worked great. Much appreciated. – Sesaco Admin May 28 '15 at 19:00

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.


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.