4

Trying to make the mental leap from SQL to SOQL is proving a bit challenging for me and some of the online documentation isn't making it clear where I am going wrong. Here is my current query, which is generating an unknown parsing error:

SELECT Account.Name, Account.Type, Account.VendorLocations__c, Account.Annual_New_Locations__c, Account.BusDev_Manager__r.Name, Account.Relationship_Manager__r.Name,
    (select sum(Total_MIDs__c) from Opportunity where Development_Partner__c = Account.Account_ID__c)
FROM Account
WHERE Account.Type IN ('Partner - Dealer','Partner - ISV','Partner - Reseller')
ORDER BY Account.Name ASC

I am trying to pull a list of all my Accounts that are partners and for each partner get a total of the size of opportunities that are related to them. However the opportunities are not under the partner Account, they are under the individual client Accounts, with a custom reference field to the Partner.

Where am I going wrong, or how better can I achieve this. I am using this with Conga Composer.

2

Unfortunately, You cannot compare field values across objects in SOQL relationship queries as you do with SQL joins.

You can use aggregate functions and run the SOQL directly on the Opportunity object and do a group by on the related account field.

your aggregate soql will be like this (you can include the other list fields you need)

Select Development_Partner__c, SUM(Total_MIDs__c) from Opportunity
Group By Development_Partner__c

But I'm not sure how this will go with Conga composer.

  • I like where you are going with this. It would pose a challenge in not all Partners have Opportunities, so trying to match them up in the results would be tricky. – Brett Williams Jan 30 '18 at 22:23
  • do you still need to list the partners with no opportunities ? if thats the case, can that be included in a separate section with default values as they don't hv related opps.. – Vamsi Krishna Jan 30 '18 at 22:35
  • It's possible, just not optimal. The users of the report would certain not be satisfied with it long term. – Brett Williams Jan 31 '18 at 12:52
  • 1
    I think I will be able to make this work within Conga as I can have multiple queries populate the report and I should be able to link them within Conga. – Brett Williams Jan 31 '18 at 14:46
0

(select sum(Total_MIDs__c) from Opportunity where Development_Partner__c= Account.Account_ID__c)

Firstly you cannot call aggregate functions in internal queries, this is could be one of the issues, but if you could post an exact error if you are getting any it would bring more clarity...

  • Welcome to SF.SE Kosha. You may want to visit the Help center to better understand how this forum works. You've provided a new answer to a 4 month old question with an accepted answer (note the green checkbox) indicating it's been solved. – crmprogdev Jun 27 '18 at 12:27

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.