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
Tom FrayTom Fray 

Retrieve information from child record and update parent record

I have 2 fields on the account: "Pricing_Closed_Lost_Reason__c" and "Pricing_Closed_Lost_Date__c".

I would like to write some code that goes through all accounts, and for each one looks for the opportunity with the most recent CloseDate where the StageName is 'Closed Lost' and the Action_Type__c is 'Competitiveness Opportunity'. From this opportunity, it should take the Lost_Reason__c and CloseDate values, and put them in the fields I mentioned above on the account.

How do I achieve this?

Should I start by querying all opportunities or accounts? Either way, how do i make sure i'm only looking at the opportunity with the largest CloseDate and the other parameters are true?
Vinit_KumarVinit_Kumar
The way I would approach is as follows :-

1.) Query all the account.
2.) Based on each Account,query all related opportunities and order by CreatedDate to get the latest one.The query should look somelike like below :-

select id,CreatedDate,Lost_Reason__c,CloseDate from Opporunity where AccountId=:<some account id> and StageName='Closed Lost' and Action_Type__c ='Competitiveness Opportunity' order by CreatedDate desc limit 1

The above query would return the record with latest Opportunity.

3.) Then use the values returned in the query to do your action.

Hope this helps !!
Tom FrayTom Fray
Thanks for your answer.

I need to run this code against all accounts, so this would quickly run against governor limits, as there would be one query per account, wouldn't there?

I was planning on using batchable apex to do the inital account query, but then I wasn't sure about how to avoid a query per opportunity to get the data I need.

Any more ideas on this?
Vinit_KumarVinit_Kumar
If you have large volume of data,batch apex is the way to go.

Based on the account fetched in,you should iterate over each Account and get the related opportunites and then the remaining work.

Try following the same and let us know if you are stuck somewhere,we can suggest something for you.
Tom FrayTom Fray
Thanks for this.

How do I get the related opportunities for each account, as you suggest, without using a SOQL query for every account in the scope?

i.e. how do i populate the "opportunitiesOnThisAccount" list in the below code?
for(account a:retrievedAccounts){
	for(opportunity o:opportunitiesOnThisAccount){
		/*
		process the opportunity so it updates variables if meets criteria
		*/
	}
}
Is there a way to use Opportunities__r or something like this?

If I'm approaching this wrong, I'm happy to hear other ideas.

Thanks
Saurabh DhobleSaurabh Dhoble
I'd suggest a different approach, from the opportunity side -

A. Use the below query to get a list of all accounts that have "Closed Lost" State and the correct action type, and the max date among all matching opportunity. This will give you a much smaller data set.

select AccountId, max(closedate) maxDate from opportunity
where StageName = 'Closed Lost' and Action_Type__c = 'Competitiveness Opportunity'
group by AccountId limit 2000

B. Extract all the AccountIDs and dates into individual sets
Set<Id> AllAccounts;
Set<Date> AllDates;
......
......
......
Code for load all the account IDs into the AllAccounts set and all the dates into the AllDates set

C. Again query opportunities, this time filtering on the AllAccounts and AllDates sets -

select Id, AccountId, Lost_Reason__c, CloseDate from Opportunity
where StageName = 'Closed Lost' and Action_Type__c = 'Competitiveness Opportunity'
and AccountId IN :AllAccounts
and CloseDate IN :AllDates

This will give you a master set of opportunities - there may be some incorrect entries here, for e.g., if two accounts both have opportunities with the same close date. For one account, the opportunity will be the one with the max closedate (the latest one), whereas for the other account, it may not be the one with the max closedate and that account may have other opportunities with a more recent close date.

Hence the next step -

D. Iterate over this list from step C., and pick the opportunities where BOTH AccountId and Date match with a record from Step A.

This will give you the final list of opportunities - each opportunity in this list will be the one with the MAX closedate for the account it is linked to.

E. Finally, loop through this list and update the corresponding account with the CloseDate and Lost_Reason__c fields.

Let me know if this approach works better. And make sure to mark the right answer, as the right answer :)
Vinit_KumarVinit_Kumar
You can query the related opportunities as inner query in the same query where you are fetching the Accounts something like below :-

retrivedAccounts = [select id,(select id from Opportunities),AccountId from Account];

then iterate over inner Query to check on each Opportunity for further getting the latest one.
Tom FrayTom Fray
Some great solutions here.

I haven't had a chance to try them yet, but when i do, i'll let you know which was my preferred.

Thanks for your help!