Using relationship fields in WHERE clauses

[marapetro]

Hi @jamessimone, I am running into issues when trying to use relationship fields in child object where clauses because the fields on related record that I need to set up the condition on have not been quarried so I get SOQL error ('RollupDeferredFullRecalcProcessor for job ID 707RT000003hiQg. : SObject row was retrieved via SOQL without querying the requested field: Customer_Insights__c.Opportunity__r'),

Use case: I have a junction object called Customer_Insights__c that has lookups to Opportunity and another custom object called Feature_Request__c. I am trying to count all Customer Insights where related Opportunity is Closed Lost and roll them up to a Feature Request. This is how I have it set up:

I understand why the error happens, but I am wondering if you have any guidance on how to best work around this. I also encountered the same error when trying to use record type in WHERE clause, so I had to hard code the RecordTypeId since SOQL did not like RecordType.Name.

Thank you,

Marija

[jamessimone]

I would try IsDeleted = false AND Opportunity__r.StageName = 'Closed Lost'. You should be able to filter by RecordType.Name as well, but there are some case-sensitivity areas of the codebase that expect proper API names (including capitalization) to be used

[marapetro]

Thank you, that did it! I usually capitalize properly, but I was copying over the queries someone else wrote in Rollup Helper. I will make sure I fix them moving forward!

[amp42]

I ran into a similar issue

OpportunityLineItem -> Opportunity rollup with WHERE clause on OpportunityLineItem:

1. Works OK
   Rollup Control > Should Run As > Synchronous
   Child object WHERE clause: Opportunity.RecordType.DeveloperName = 'blah'

2. Does not work
   Error: SObject row was retrieved via SOQL without querying the requested field: OpportunityLineItem.Opportunity: Class.RollupEvaluator.WhereFieldCondition.getRelationshipFieldValue: line 720
   Rollup Control > Should Run As > Synchronous
   Child object WHERE clause: opportunity.recordtype.developername = 'blah'

3. Works OK
   Rollup Control > Should Run As > Queueable
   Child object WHERE clause: opportunity.recordtype.developername = 'blah'

[jamessimone]

Ok I will take a look!

[jamessimone]

@amp42 I wasn't able to reproduce the error you're having. What version are you running? Additionally, I would try properly casing your where clause:

Opportunity.RecordType.DeveloperName = 'blah'

[amp42]

I am on 1.6..30, the latest as of this writing. Mixed (correct) case works, yes. Lowercase also works when the run method is set to Queueable.

[jamessimone]

Still looking into this, for the record. I expect to be able to fix this, though

[jamessimone]

@amp42 just fixed this issue, it will work in 1.6.31 - hopefully I'll be able to release that update later today