Recalculating Rollup when the count is 0 leads to "First error: SObject row was retrieved via SOQL without querying the requested field"

[JakMGitHub]

Hi! I have installed v1.5.88 this week (first time using Apex Rollup) in our Sandbox environment. I am having an issue where 4 different rollups that are basically identical in functionality are all calculating incorrectly when there are no records that meet the WHERE clause. I would expect the records to be updated with a value of 0. Instead, the field is not updated and the Status Details in Apex Jobs gives me the dreaded "First error: SObject row was retrieved via SOQL without querying the requested field" error.

The following CMT seems to be working fine right now when there are records to count, but otherwise I'm not having any luck:

A further issue is that launching the rollup calc from the 'Recalculate Rollups' LWC on the parent record page always leads to errors, and the debug file shows that it is using the wrong value for the Parent Lookup field. It has the field name right, but uses the value from the record ID of the parent record instead of the value from the designated parent lookup field, which leads to no rows being returned, as would be expected. The following is a snippet of the debug log that shows this:

CalcItemWhereClausec" : "`Renew_from_parent_Intacct_Contractc = true and Start_Date_of_parent_Intacct_Contractc <= TODAY and End_Date_of_parent_Intacct_Contractc >= TODAY and (State_of_parent_Intacct_Contractc = 'In Progress' OR State_of_parent_Intacct_Contractc = 'Renewed' OR State_of_parent_Intacct_Contractc = 'Draft') and (Contract_Line_is_dOSc = 1 or Contract_Line_is_dOSc = 0) ||| XXXXX_Ship_Toc = 'a4pAq0000001DpnIAE' `

The XXXXX_Ship_To__c field value for the record in question is actually "001j000000n1aQ1 - S9".

What is incredibly frustrating is that I have another version of this rollup that works in-theory identically, but it doesn't throw the same error, even when there are also no records that meet the WHERE clause.

I'm a bit reluctant to upload any debug files as they will contain certain company-sensitive info (field names in particular) that I'll need to edit before uploading to a public site. But if it's needed, I'll edit and go ahead. Just wanted to confirm I'm not missing anything obvious first. Lastly, I'm a bit new to development side of things, though I'm a quite experienced admin, so apologies if anything I've written is non-sensical.

[jamessimone]

@JakMGitHub a few things here:

• I'd recommend updating to 1.6.0
• I'd recommend updating your where clause to: Renew_from_parent_Intacct_Contract__c = true and Start_Date_of_parent_Intacct_Contract__c <= TODAY and End_Date_of_parent_Intacct_Contract__c >= TODAY and State_of_parent_Intacct_Contract__c in ('In Progress', 'Renewed', 'Draft') and (Contract_Line_is_dOS__c = 1 or Contract_Line_is_dOS__c = 0)

The Recalculate Rollups button can only be used on pages where you're already at the parent record (in this case, that would mean embedding it on Account pages).

[JakMGitHub]

Rapid response - thank you! I will make the update and see if it affects things and report back. I've just tested it using your adjusted WHERE clause, and same error arises.

The Recalculate Rollups button can only be used on pages where you're already at the parent record (in this case, that would mean embedding it on Account pages).

In my Rollup, the parent record is my custom object named Portal Site Unit and that's where I am pressing the Recalculate Rollups button. Not sure if I'm misunderstanding, but the Account object doesn't come into it for this rollup. The rollup is between 2 custom objects.

[JakMGitHub]

Sadly I'm still seeing the same SOQL issue post-update and after implementing your adjusted WHERE clause.

• I have 2 test parent records with blank values in the target rollup fields - one should have been populated with 0, the other with 1. The 1 gets correctly populated, while the other one remains blank. If I then edit the child records so that a recalc should populate both test parent records with 0 then that's where the SOQL error occurs and neither record gets an update (the blank one remains blank, the one with a 1 keeps that 1 and isn't updated).
• If I then edit the child records so that both test parent records should be updated to 1, the rollup is completely successful, with no SOQL errors.

So the issue seems to present itself when the SOQL query returns nothing. Instead of updating the target rollup field with 0, at best the value is cleared (if there are other valid child records for OTHER parent records) or at worst is not updated at all (if there no other valid child records for ANY parent records).

I have tried adjusting the rollup to MAX and SUM as well (since the target child rollup field is a number field with values of either 0 or 1), but it makes no difference.

Let me know if you need any other info, I can edit the debug log file to remove sensitive info if needed. And I really appreciate you jumping in so quickly!

[jamessimone]

@JakMGitHub I may not need the full debug log, but if you were able to supply the stacktrace where the error is occurring, that would be supremely helpful. Here's an example:

Exception 
Class.RollupAsyncProcessor.execute: line 239, column 1
Class.RollupRepository.createQueryLog: line 77, column 1
Class.RollupRepository.get: line 49, column 1
Class.RollupAsyncProcessor.getExistingLookupItems: line 516, column 1
Class.RollupAsyncProcessor.getLookupItems: line 929, column 1
Class.RollupAsyncProcessor.process: line 690, column 1
Class.RollupFullBatchRecalculator.performWork: line 14, column 1
Class.RollupAsyncProcessor.execute: line 254, column 1

As for the Id being used with the Recalculate Rollups button, maybe I should re-state: at the moment, it only works if the Id of the record page you're on is the Lookup Field On Parent Object. Something that I could see updating here is the addition of an optional property when dragging the button onto the page layout to supply the API name for the lookup field when it isn't the current record's Id.

[JakMGitHub]

Thanks very much James - full disclosure, it may be Monday before I can provide that, but I will do so.

And thank you for the Recalc button explainer! That makes sense. The additional property option you describe sounds very useful.

[JakMGitHub]

Hi again James, here is the stack trace from the debug log when running a full recalc where I am expecting records to be updated from null values to 0. Let me know if there's anything else you need!

|FATAL_ERROR|System.SObjectException: SObject row was retrieved via SOQL without querying the requested field: Portal_Site_Unit__c.Site_Location_ID__c

Class.RollupAsyncProcessor.parentRollupFieldHasBeenReset: line 741, column 1
Class.RollupParentResetProcessor.execute: line 71, column 1
Class.RollupParentResetProcessor.runSync: line 103, column 1
Class.RollupParentResetProcessor.runCalc: line 56, column 1
Class.RollupFullRecalcProcessor.finish: line 80, column 1
Class.RollupAsyncProcessor.finish: line 261, column 1

[jamessimone]

@JakMGitHub I'll be including the fix for both of the issues you've brought forth here:

• the SObject row was retrieved via SOQL without querying the requested field ... error
• the (prior) lack of ability to stipulate an alternative to Id for the parent field API name when using the recalculate rollups button

Those will both be part of the next release. Thanks for bringing this to my attention!

[jamessimone]

One thing to note, additionally - Apex Rollup follows the SOQL defaults for aggregate functions. That means that the standard behavior when resetting records is to use null as the value. If you'd like to use 0 instead, you should update the Full Recalculation Default Number Value field on your rollup record(s)

[JakMGitHub]

Those will both be part of the next release. Thanks for bringing this to my attention!

Amazing, thank you so much! Very rapid response and fix too.

One thing to note, additionally - Apex Rollup follows the SOQL defaults for aggregate functions. That means that the standard behavior when resetting records is to use null as the value. If you'd like to use 0 instead, you should update the Full Recalculation Default Number Value field on your rollup record(s)

Very helpful thank you.