Family Analytics Job not Properly Calculating eRA in Specific Scenario

[mikedotmundy]

Description

In regards to eRA calculation, the Family Analytics job is not correctly handling the following scenario:

1. A student attends and gets checked in every week. The Group (Group Type) they are checked into has the "Weekend Service" option selected, so their attendance record will count towards eRA. This students RecordStatus is set to Active.
2. The students parent does not attend - they only drop the student off. Therefore, the parents RecordStatus is set to Inactive.

The problem occurs when the Family Analytics job runs. When it runs, it calculates that the student is not an eRA, when in fact they are. So, every time the job runs, it will create 2 rows in the Person's History, like this:

Not only is it creating these 2 rows, as you can tell it is also updating the persons eRAStartDate Attribute to the current date. It will update this attribute every day the job runs - so we don't actually know when the person became an eRA.

The spCrm_FamilyAnalyticsEraDataset.sql stored procedure is returning Is Era as False. Running a portion of that SQL returned the following results:

I was not able to dive super deep into that procedure, however I believe the issue is that the procedure is looking for an Adult at line 205 - AND gm.[GroupRoleId] = @AdultRoleId, and because the Adult is inactive and not an eRA, it is returning False. I could be wrong - again I did not dive deep enough into it.

If you get to it before the demo site refreshes, then you can see I replicated and did all my testing with Bill and Alisha Marble here (I made Alisha a child).

Actual Behavior

Children who are an eRA and active, with an Adult (Parent) who are not an eRA and are inactive are not being handled properly by the Family Analytics job.

Expected Behavior

Children who are an eRA and active with an Adult (Parent) who are not an eRA and are inactive should not have their eRA status and eRA Start Date updated every time the Family Analytics job runs.

Steps to Reproduce

• Start with a family with 1 Adult and 1 Child. Make the Adult inactive.
• Add some attendance records (at least 8) for the child. This must be done in a Group with a Group Type that has the Weekend Service option selected.
• Run the Family Analytics job.
• Go to the child's profile and ensure they were made an eRA. You can go to their history tab and see that 2 records were created that look similar to this:
• Verify that the Adult is not an eRA and that their eRA Start Date attribute is blank.
• Go to the child's Extended Attributes and change their eRA Start Date to be yesterday's date.
• Run the Family Analytics job again.
• Back on the child's history, see that there are 2 new rows in their history.

Issue Confirmation

• [X] Perform a search on the Github Issues to see if your bug or enhancement is already reported.
• [X] Try to reproduce the problem on a fresh install or on the demo site.

Rock Version

v16.4, v17.0

Client Culture Setting

en-US