Rollup does not populate correctly - 0s or nulls for everything

[JakMGitHub]

hi! Further to my last Issue that I posted (https://github.com/jamessimone/apex-rollup/issues/526), Rollups are now not calculating correctly in any circumstances for me. The performance is actually worse because prior to the last fix, the rollup ran and calculated correctly when there were eligible records that fell into the WHERE clause. But now everytime I run it I get a null value (or 0 if I have populated the Full Recalculation Default Number Value), even when I'm expecting a positive values. I've tried using MAX and COUNT but both result in a null value and I also tried it with SUM and using a different Rollup Field On Child Object that is a standard number field rather than a formula field - everything results in null values being inserted.

This time I'm not getting any SOQL errors or anything to suggest that anything went wrong - I can just see that records are not populated with the correct rollup figures.

Let me know what detail you'd like from the debugging log if any.

[jamessimone]

@JakMGitHub how is your where clause structured? I won't be able to look at logs till next week, but I may be able to help by seeing that.

[JakMGitHub]

@jamessimone I have it like this for MAX rollups: 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_eOS__c = 1 or Contract_Line_is_eOS__c = 0)

and I have it like this when trying COUNT or SUM: 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_eOS__c = 1

When I run these in the query editor from the Developer Console (and adding a filter for the Lookup field for a parent I know should return '1') the results are returned correctly, i.e. 1 row is returned.

[jamessimone]

@JakMGitHub I won't be able to take a look at this until next week, unfortunately. It could be that there's some issue with the spacing within the parentheses, but it's hard to say at the moment.

[JakMGitHub]

OK thanks James! One thing worth noting - the first of the 2 SOQL queries I posted is the same syntax as what I used when I discovered https://github.com/jamessimone/apex-rollup/issues/526. At that time, the query successfully pulled records when there were >0 records to be retrieved.

[jamessimone]

@JakMGitHub I spent some time this morning attempting to reproduce this issue without success. The only way I was able to get inconsistent results was by swapping user locales, which created an edge case with the time of day not matching - however, that's consistent with how SOQL date literals like TODAY work. Based on the fact that you can't seem to get any records to match, it seems incredibly unlikely that this is a time zone mismatch between your user and the user running the rollups, but I'll throw that out there as something to consider.

I'm happy to assist in any way that I can - is it possible to upload one of the batch chunk log files generated when doing a full recalc?

[JakMGitHub]

Hi @jamessimone, sorry I've only just had the time to do this. I'm attaching the full log file from the Batch Apex operation as well as the SerialBatchApexRangeChunkHandler operation. Let me know if there's other log files you need: eOS Rollup log file 2023-11-08 - SerialBatchApexRangeChunkHandler - apex-07LAq000009potqMAA.log eOS Rollup log file 2023-11-08 - Batch Apex apex-07LAq000009q6VZMAY.log

[jamessimone]

@JakMGitHub I'm wondering if this is a similar issue to the one we are talking about in #523 - essentially, in the first log file (the SerialBatchApexRangeChunkHandler one), I can see the following in the logs (any org-specific data I've taken the liberty of mostly redacting):

Rollup v1.6.1: lookup record prior to rolling up:
{
  "attributes" : {
    "type" : "Your_Custom_Object__c",
    "url" : "/services/data/v59.0/sobjects/Your_Custom_Object__c/someId"
  },
  "Site_Location_ID__c" : "someId - S9",
  "CurrencyIsoCode" : "USD",
  "Id" : "someId"
}

And then:

Rollup v1.6.1: lookup record after rolling up:
{
  "attributes" : {
    "type" : "Your_Custom_Object__c",
    "url" : "/services/data/v59.0/sobjects/Your_Custom_Object__c/someId"
  },
  "Site_Location_ID__c" : "someId - S9",
  "CurrencyIsoCode" : "USD",
  "Id" : "someId",
  "your_rollup_field__c" : 2.0000
}

Which is probably right - however, there are two kinds of updates to records in Salesforce:

• AllOrNone true updates, which means an update operation only succeeds if all records are updated successfully
• AllOrNone false updates, which means partial saves are allowed and any records that fail to get updated you have to examine after the fact. This can be because the record is an an inconsistent state; that a validation rule is failing, a required field is missing, etc ...

In the next version, v1.6.2, I'll be exposing some additional logging which may prove helpful in examining possible issues like this with parent-level records. In the meantime, I'd check to see if you can update the parent record found in that serial batch apex chunk handler log (you can quickly find all of the relevant info in any of these log files by searching for the text USER_DEBUG, which will show you all of the info that I collate when logging is enabled in Apex Rollup). I suspect there will be some kind of VR error or something of the sort.

Lastly - the reason that you're getting 0s or nulls for everything is (probably) because you have the Should Skip Resetting Parent Fields checkbox on your Rollup Control record enabled. I would disable that setting; it will prevent the accidental clearing of data. It's really only useful if you want to fully wipe out the rollup field data on any parent records that haven't been updated by Apex Rollup. If you flip this flag to true, that will likely help quite a bit.

Please let me know if that all makes sense, and thanks for your patience!

[JakMGitHub]

Thanks @jamessimone!

I can confirm that I am able to update the parent record directly without any issues. There are no validation rules on this object (which is quite new) and no other automation that updates other records on other objects when edits to records on this one are made. It's really very simple as it currently exists.

Not sure if I'm misunderstanding but that checkbox is set to FALSE, as per this screenshot from the log:

I just updated it to TRUE and recalculated and the rollups are correct and accurate - as long as there >0 child records in the rollup. This goes back to my issue in https://github.com/jamessimone/apex-rollup/issues/523 in that when a record has 0 child records, it should be updated to 0, but instead the record is not updated. This is a really crucial element of the functionality for our use case.

When I moved it back to FALSE and ran again, I had the same situation as before - it cleared out the value in the target rollup field for a record that has no eligible child records, but also failed to update a record that DOES have >0 child records.

So to summarise, the performance seems to hinge on that Should Skip Resetting Parent Fields checkbox:

• When set to FALSE, the rollup correctly clears out values in parent records that have no child records, but it fails to update records that DO have child records.
• When set to TRUE, the rollup correctly calculates the values for those records that have >0 child records, but fails to update the records that should be set to 0 (or null).

Let me know if you need anything else from me!

[jamessimone]

@JakMGitHub for your use case, I would highly advise using the default value functionality for new records without children. Apex Rollup won't "know" about parent-level records until it's in a situation where there are children with which to work with. While I could add functionality to the full recalc code path to update parents from null to the full recalc default value you've set up, that would require additional work on your part to be running those full recalculations on a fixed schedule, which isn't ideal. It would be much better to have Apex Rollup be set up to work in harmony with the out of the box default value functionality so that the parent records are always in sync.

[JakMGitHub]

@jamessimone OK thank you for that. When using the Default Value as '0', I get 0s in every record, even those that should have >0. I should say that the plan is absolutely to run this from a scheduled Flow rather than any record trigger. This is because the start and end date of contracts don't change, but their relationship to the current date changes depending on the current date rather than anything on the record.

The use case is basically this:

• Each day, I want to run Apex Rollup to look at customer sites to see if they have an active contract that includes 'x' product.
• If a contract starts, we need to be able to call out that they are entitled to that product. If we're not seeing usage of the product they've paid for, we can commence some CS activities to increase adoption.
• If a contract ends, we need to be able to call out that they are no longer entitled to use that product. If we're still seeing usage of it, we know that we need to switch off that product for that site.

Is there a way to configure Apex Rollup that would achieve that?

[jamessimone]

It seems to me that the default value in conjunction with your scheduled run of the full recalc would suffice; only records with children would have their values updated to non-zero values. The one issue, presently, seems to be the behavior around Should Skip Resetting Parent Fields, which (when false) should be working to "clear out" your rollup field for parents that don't have any children (again, in conjunction with the default value for parent records already being 0).

In the eOS.Rollup.log.file.2023-11-08.-.Batch.Apex.apex-07LAq000009q6VZMAY.log file you attached earlier, the reset processor is only resetting a single parent's rollup field - what that should mean, realistically, is that particular parent was the only one without children (and, as a result, the only one not updated in the full recalc run you started).

[jamessimone]

@JakMGitHub are you still experiencing this issue?