+ Start a Discussion
zoranszorans 

Subquery not showing all opportunities

Hi All,

I have a simple question about about opoortunities in account subquery. I am trying to fetch particular opportunities(based on StageName) in account subquery but the problem is that subquery is not returning All opportunities connected with particular account even without a WHERE condition.

I have:
 
SELECT ID,Name,Sales_Funnel__c,(Select Name,StageName,createdDate, id FROM Opportunities__r ShowAll order by CreatedDate desc) FROM Account WHERE id='j52gcw632x4bj43'
It is just returning 4 opportunities out of 9. I even tried with ShowAll in the query but it does not make any difference.

If I make a query for the particular opporutnity that subquery is not returning inside subquery it is normally returned and also with accounid and name etc.
 
Select CreatedDate, name, StageName, Account_Manager__c, Account.Name,accountid From Opportunity where id='g2dhjk50fjkB14g'

This particular opportunity (among other 5 that are missing) is also normally visible @ Account page layout also.

Knowing that these are standard objects I was wondering if there are any restrictions that are applied for these case scenarios.
Please Help :)

Thanks in advance.
Best Answer chosen by zorans
Anoop yadavAnoop yadav
Hi,
Try with the below Query.
SELECT Id, Name, Sales_Funnel__c,
(Select Name,StageName,createdDate, id FROM Opportunities order by CreatedDate desc) FROM Account WHERE id='j52gcw632x4bj43'

 

All Answers

Anoop yadavAnoop yadav
Hi,
Try with the below Query.
SELECT Id, Name, Sales_Funnel__c,
(Select Name,StageName,createdDate, id FROM Opportunities order by CreatedDate desc) FROM Account WHERE id='j52gcw632x4bj43'

 
This was selected as the best answer
zoranszorans

Hi Anoop,

I just tried that 2min ago and wanted to write that it is working like that and then I see you allready responded :)

Thanks anyway, but is there explanations why is this happening what is the difference between those two Opportunities and Opportunities__r.

Thanks

Anoop yadavAnoop yadav
Hi,

"Opportunities" is a standard  and  "Opportunities__r" is a custom Query field in Account Object.
For more information, Put Opportunity Lookup field on Opportunity Layout and you will get some blank field value.
That is why you are not getting all the opportunities in subquery.