Question on using formula fields that return dates in Calc Item Where Clause

[jkenzer]

I'm trying to determine why I'm not getting the results I'm expecting and I wonder if it's because I'm using a formula field on the child record that returns a date. I know formulas aren't evaluated unless they are needed, like when displaying on a page or in a report. So I'm wondering if that is causing the issue?

Here's the scenario:

I'm trying to roll up fund grant amounts (child object) to funds (parent object) for a given time period. On the child object, I'm using Calc Item Where Clause = Grant_Date_From_Grant__c >= 2021-04-01. Grant_Date_From_Grant__c is a formula field that looks at a parent object, grants, to get the date. The fund grants object is a junction object between Grants and Funds.

The roll-up is working, but it's getting unexpected amounts. For example, I have one grant for $35,000 but I keep getting a value rolled up of $36,500. I've re-created the query in the developer console and in a report and get $35,000.

Here are my settings for this rollup:

And here is a quick ERD:

[jamessimone]

@jkenzer have you cleared out the rollup field and recalculated using the rollup app? We have several other NPSP rollup orgs using formula fields with dates in the Calc Item Where Clause, so that shouldn't be an issue. Typically, for updates to the child objects (Fund Grant Association here), only when the Grant Amount field has been updated will that value get incremented to the existing parent-level value (which is why the full recalc using the app is a pre-req).

Let me know if the full recalculation is still yielding some bizarre value, like $36,500! If that ends up being the case, modifying the Org_Defaults rollup control Custom Metadata Type record to enable Is Rollup Logging Enabled may help uncover some additional insight.

Thanks!

[jkenzer]

@jamessimone Okay, I have logs with debugs from SerialBatchApexRangeChunkHandler. Looks like a JSON object for each record that it's pulling. Should it have records that are outside my date range? I see many that fall before my date range. This is the rollup I'm running (it's just a simple count from last fiscal year and I know this field, Number of Grants Last Year, isn't being affected by any other process because I just created it):

And I'm seeing records like:

This rollup is undercounting the number of records. Is it possible that it's silently failing on the process? The Apex jobs says 69 of 69 batches ran. There should be a total of 18,162 lines counted for that time range but only 3,823 are being rolled up.

[jamessimone]

@jkenzer please don’t apologize! Clearly something is amiss and I will have to track down the source of the issue. Let me try a few tests tomorrow morning — based on your screenshots and feedback this should be easy to track down. I have one other issue for another NPSP user that I’m hoping to track down at the same time — both will be part of the next release.

Don’t worry. I’m dedicated to ensuring everything works flawlessly for you!

[jamessimone]

@jkenzer I've created #123 & #123 to address the issues you're experiencing. I have them both fixed, locally, and plan to have those fixes (as well as several others) delivered as part of v1.2.30 (to be released, latest, by this Friday).

[jamessimone]

@jkenzer both the issue with the recalc from parent button and the date filtering issue should be solved with the latest release. Please let me know!

[jkenzer]

Confirmed that both issues were fixed in V1.2.30.