Create new upsert job for Attendance Skillz Plus Club

[daissatou2]

Background, context, and business value

The Attendance form for Skillz Plus Clubs does not send us the session name or number. The job should decide which session to map attendance to by checking which is the earliest session that is null.

The specific request, in as few words as possible

When uploading this form data, for each form.attendance_list.update_participant_cases.item[*]...

1. Query Event__c to search for the event_name to build the Attendance external Id. Use what we implemented in this other [Attendance job]... https://github.com/OpenFn/grassroot-soccer/blob/de74a730a6a3da1ca3a389b041ce27b601704235/jobs/upsertAttendance.js#L1-L5

2. Use the event_name returned to set the CommCare_Ext_ID__c in the next step...

3. Query for the existing Attendance record to check if the Session_X__c fields (Session 1-28) have been completed... then fill in whichever field is null and has not yet been completed.

SELECT Session_1__c, Session_2__c, Session_3__c, Session_4__c, Session_5__c, Session_6__c, Session_7__c, Session_8__c, Session_9__c, Session_10__c, Session_11__c, Session_12__c, Session_13__c, Session_14__c, Session_15__c, Session_16__c, Session_17__c, Session_18__c, Session_19__c, Session_20__c, Session_21__c, Session_22__c, Session_23__c, Session_23__c, Session_24__c, Session_25__c, Session_26__c, Session_27__c, Session_28__c FROM Attendance__c WHERE CommCare_Ext_ID__c = `${item[*].create_attendance_case.case.@case_id}-${event_name}`

4. Check if Session_1__c===null... if true, map item[*].create_attendance_case.case.update.skillz_plus_attendance to this field using this logic to set the SF Session field:

   function getSessionValue(skillz_plus_attendance) {    
   switch (skillz_plus_attendance.toString().toLowerCase()) {      
   case 'yes':        
     return 'X';      
   case 'no':        
     return 'A';      
   case '':        
     return 'N';      
   default:        
     return 'U';    }  }

   ...If false, check the next field Session_2__c===null. If true, map ...skillz_plus_attendance to this field. If false, check the next field Session_3__c...and so on.

Ex. The first time attendance is taken for a user we'll check if Session_1__c is null. It will be null so we map to Session_1__c. The second time, we'll check if Session_1__c is null. It will not be null so we'll check if Session_2__c is null. It will be null so we'll map attendance to Session_2__c. This will happen up to Session_28__c.

This job should also be able to handle multiple attendance entries like the general Attendance job. It should also be able to handle one attendance entry as well as many, see example state files below.

The mappings for the Skillz Plus Club Attendance form are here.

state.json

See LP for GRS Salesforce Sandbox https://www.openfn.org/projects/pd3yk4/messages/mpq5vbnd - Attendance for multiple participants https://www.openfn.org/projects/pd3yk4/messages/mrbp4azj - Attendance for one participant Please run against these states in prod to test.

trigger

Attendance Skillz Plus Club

expression.js

New job you can link to prod here: https://www.openfn.org/projects/pd3yk4/jobs/jv4enr

[aleksa-krolls]

@daissatou2 Pls note my feedback....

1. Fyi, I added steps 1-2 I added to reflect changes I made in the existing Attendance job, because I don't see that intervention_name is provided in this state... so we need to look-up this name using the case_id, which is available to us.
2. Then, for steps 3-4 I added details you left out, including... (1) defining what is the source path that tells us where the Attendance data value comes from, as well as (2) logic to transform CommCare attendance values to Salesforce ones. I'm guessing this is right based off of the other jobs, but given that you didn't define this... can you please confirm that's right or go ahead and edit the issue.
3. What does the state for this form look like if there is only Attendance for 1 Participant completed? Does the structure of state change significantly? (e.g., not item[*] array)? If yes, please provide another example of that state and specify on this issue that the job needs to work for both versions of state.

[daissatou2]

This is ready for implementation.

[aleksa-krolls]

@taylordowns2000 This grs issue is ready. Let me and @daissatou2 know if you have questions.

[taylordowns2000]

Sounds good. I'm shutting down for now and didn't get to this, but @chaiwa-berian is back online in the morning so I'm re-assigning to him.

[chaiwa-berian]

@aleksa-krolls @daissatou2 Is the Skills plus club attendance form a section of the Attendance form in CommCare?

[aleksa-krolls]

@daissatou2 What's going on with this one? Were you able to resolve Chaiwa's questions? This morning please test to provide feedback or close this issue if resolved - thanks!

[daissatou2]

@chaiwa-berian is this job in production? I can't find on Github.

[aleksa-krolls]

@chaiwa-berian What's the status of this one here? Still in progress? I'm moving to the top of the backlog in case there is still work to be done.

cc @taylordowns2000 @daissatou2

[chaiwa-berian]

@aleksa-krolls The job is linked and ready for testing. Getting this error but it should be quick to fix by you or @daissatou2 .

INVALID_FIELD: Foreign key external ID: dd621bb8-af17-4393-bcc2-ddba4d499750 not found for field Participant_Identification_Number_PID__c in entity Person__c