function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
Xav Theo 4Xav Theo 4 

List of parent IDs for which all related child records are closed

Hi all - We need to query a list of parent IDs for which all related record have a status = closed. In our case, the parent object is Contact and children are a Custom Object. Once we have the list we'll update a field on the Contact.

Can someone help with the SOQL query / list part?

Thanks!
Best Answer chosen by Xav Theo 4
Ishita sfdcIshita sfdc
Hi Xav,

If you ONLY want the list of parent IDs for which related record have status closed : 
Set<Id> parentIds = new Set<Id>();
List<Contact> contacts = [
		SELECT
			Id,
			<Field to be updated>,
			(
				SELECT
					Id,
                	Status__c
				FROM
					customObject__r
			)
		FROM
			Contact
	];
for(Contact contact : contacts) {
	if(!contact.customObject__r.isEmpty()) {
        Integer recordCount = contact.customObject__r.size();
        for(customObject__c obj : contact.customObject__r) {
            if(obj.Status__c != 'Closed') {
                break;
            }
            recordCount--;
            if(recordCount == 0) {
                parentIds.add(contact.id);
            }
        }		
	}
}
system.debug('==parentIds== '+parentIds);

Hope this helps you!

Thanks
Ishita

All Answers

v varaprasadv varaprasad
Hi Xav,

Please check once below sample code : 
  
set<id> parentIds = new set<id>();
list<contact> lstCons = [select id,name,(select id from customObject__r where status__c = 'closed') from contact];
for(contact con : lstCons){
  parentIds.add(con.id);
}

system.debug('==parentIds=='+parentIds);


Hope this helps you!
If my answer helps resolve your query, please mark it as the 'Best Answer' & upvote it to benefit others.

Thanks
Varaprasad
@For Support: varaprasad4sfdc@gmail.com


 
Ishita sfdcIshita sfdc
Hi Xav,

If you ONLY want the list of parent IDs for which related record have status closed : 
Set<Id> parentIds = new Set<Id>();
List<Contact> contacts = [
		SELECT
			Id,
			<Field to be updated>,
			(
				SELECT
					Id,
                	Status__c
				FROM
					customObject__r
			)
		FROM
			Contact
	];
for(Contact contact : contacts) {
	if(!contact.customObject__r.isEmpty()) {
        Integer recordCount = contact.customObject__r.size();
        for(customObject__c obj : contact.customObject__r) {
            if(obj.Status__c != 'Closed') {
                break;
            }
            recordCount--;
            if(recordCount == 0) {
                parentIds.add(contact.id);
            }
        }		
	}
}
system.debug('==parentIds== '+parentIds);

Hope this helps you!

Thanks
Ishita
This was selected as the best answer
Ajay K DubediAjay K Dubedi
Hii Xav,

Please refer the below code:-

public class UpdateContacts 
{
 public static void updatemethod()
 {
     List<Customobj__c    > objlist=[SELECT Status__c,Contact__c from Customobj__c where Status__c='Closed'];
     set<id> contactid=new set<id>();
     for(Customobj__c c:objlist)
     {
         contactid.add(c.Contact__c);
     }
     List<Contact> newconlist=new List<Contact>();
     List<Contact> contactlist=[SELECT LastName,Status__c from Contact where Id In:contactid];
     for(Contact con:contactlist)
     {
         con.Status__c='updated';
         newconlist.add(con);
     }
     update newconlist;
 }
}

I hope this code helps you.

Customobj__c is my custom object and status__c(picklist) its field.
Also, I create a field on contact Status__c(Text). when Customobj__c field Status is getting closed then contact status will be updated. 
You can add your field as you want.

Please mark it as best answer if it helps you.

Thank You,
Ajay Dubedi