Sign Up ›

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
David81David81 

SOQL Help needed

Hello folks,

 

I'm just about to pull my hair out on this one and I'm hoping someone out there can help me out. I have a few queries being used to populate a VF table that just aren't adding up, although I think they should be.

 

The two variables you will see are repIds (a List of User Ids) and firstOfMonth (a Date that corresponds to the 1st day of the current month.

 

This query gives me a grand total of 332 and is my total group in question.

 

List<AggregateResult> mtdAcctsARList = [SELECT Opened_by__c,count(Id)num FROM Account__c WHERE Opened_by__c IN :repIds AND Account_Established__c >= :firstOfMonth GROUP BY Opened_By__c];

 The next three, I think, should all total up to 332, but they do not. They return 7, 53 and 152 respectively.

 

//Count any Accounts linked to Cross Sell Opptys
List<AggregateResult> crossSellARList =[SELECT Opened_by__c,count(id)num FROM Account__c WHERE Opened_by__c IN :repIds AND Account_Established__c >= :firstOfMonth AND Opportunity__r.RecordType.Name = 'ETC Cross Sell Opportunity' GROUP BY Opened_by__c];


//Count any Account not linked to Cross Sell Opptys that have a Referrer		
List<AggregateResult> referralARList = [SELECT Opened_by__c,count(id)num FROM Account__c WHERE Opened_by__c IN :repIds AND Account_Established__c >= :firstOfMonth AND (Opportunity__r.RecordType.Name != 'ETC Cross Sell Opportunity' OR Opportunity__c = null) AND Account_Holder__r.Referred_by__c != null GROUP BY Opened_by__c];


//Count any Account not linked to Cross Sell Opptys that DON'T have a Referrer
List<AggregateResult> outboundARList = [SELECT Opened_by__c,count(id)num FROM Account__c WHERE Opened_by__c IN :repIds AND Account_Established__c >= :firstOfMonth AND (Opportunity__r.RecordType.Name != 'ETC Cross Sell Opportunity' OR Opportunity__c = null) AND Account_Holder__r.Referred_by__c = null GROUP BY Opened_by__c];

 I'm sure it has something to do with the Opportunity relationship, but for the life of me I can't figure out why it isn't working.

 

Thanks in advance.

 

David

bob_buzzardbob_buzzard

You seem to have covered the based for the opportunities as far as I can tell.  Can you have accounts without the Account_Holder__r populated?  
David81David81

Well, the Account_Holder__r relationship is a Master-Detail, so...
bob_buzzardbob_buzzard

So if you exclude the opportunity from it, do the numbers add up?

 

E.g.

 

//Count any Accounts that have a Referrer		
List<AggregateResult> referralARList = [SELECT Opened_by__c,count(id)num FROM Account__c WHERE Opened_by__c IN :repIds AND Account_Established__c >= :firstOfMonth AND Account_Holder__r.Referred_by__c != null GROUP BY Opened_by__c];


//Count any Accounts that DON'T have a Referrer
List<AggregateResult> outboundARList = [SELECT Opened_by__c,count(id)num FROM Account__c WHERE Opened_by__c IN :repIds AND Account_Established__c >= :firstOfMonth AND Account_Holder__r.Referred_by__c = null GROUP BY Opened_by__c];

 
David81David81

Yep. Those two add up to the first "master" count, but I need to remove the ones linked to Cross Sell Opptys...

 

It appears I can't pull back the ones where Opportunity__c = null AND the ones where Opportunity__r.RecordType.Name != 'ETC Cross Sell Opportunity.

 

WHERE Opened_by__c IN :repIds AND Account_Established__c >= :firstOfMonth AND Account_Holder__r.Referred_by__c = null AND Opportunity__r.RecordType.Name != 'ETC Cross Sell Opportunity'

 returns the same as

 

WHERE Opened_by__c IN :repIds AND Account_Established__c >= :firstOfMonth AND Account_Holder__r.Referred_by__c = null AND (Opportunity__c = null OR Opportunity__r.RecordType.Name != 'ETC Cross Sell Opportunity' )

 
David81David81

There seem to be some notes in the SOQL docs that might shed some light on this.

 

 

  • In a WHERE clause usingOR, if the foreign key value in a record is null, the record is returned in version 13.0 and later, but not returned in versions before 13.0. For example, if your organization has one contact with the value of its LastNamefield equal tofooand the value of its AccountIdfield equal tonull, and another contact with a different last name and a parent account namedbar, the following query returns only the contact with the last name equal tobar: 
    SELECT Id FROM Contact WHERE LastName = 'foo' or Account.Name = 'bar'
    The contact with no parent account has a last name that meets the criteria, so it is returned in version 13.0 and later.
  • In a WHERE clause that checks for a value in a parent field, if the parent does not exist, the record is returned in version 13.0 and later, but not returned in versions before 13.0.. For example: 
    SELECT Id FROM Case WHERE Contact.Lastname = null



But this seems to apply only to versions prior to 13.0. Could something have reverted?
bob_buzzardbob_buzzard

Hmm.  I keep reading those docs and it seems to me that your query should work.  The only difference with the example is that your query includes the id that creates the relationship, so I guess that may be treated slightly differently.

 

Does it help if you break that out into two queries?  

 

E.g. first query back all those where the Opportunity__c is null, then all those where the Opportunity__r.RecordType.Name doesn't equal 'ETC Cross Sell  Opportunity'.

 

 
David81David81

Bob,

 

Thanks for confirming that I'm not going crazy. I certainly could run two queries for each number, but obviously I'd prefer not too. Also, at this point, it's more of the principle of the thing :) I shouldn't have to run two queries to get this data set.

 

I'll go ahead and file a support ticket and see what dev support has to say.
bob_buzzardbob_buzzard

Please keep us updated with the progress - I'd be keen to understand what's actually happening.


Good luck!
David81David81

Still no progress on this.

 

Support has been painfully slow to grasp the issue here. I think I've finally gotten through to them on why this is a problem. Hopefully they can resolve this so that I don't have to continue with the 2 query work around that I've put in place to get the page into production.

 

For as much as I love the SFDC product, I've been VERY underwhelmed by the "Premier" support (especially when it comes to dev support).
David81David81

Back again a month later. Finally heard back from support that apparently this is a "known issue". It seems odd to me that it took them 2 months to determine that it is already known to them...

 

Oh well, no ETA on a fix at this time.