search

OpenFn / grassroot-soccer

Grassroot Soccer CommCare-Salesforce integrations
https://openfn.github.io/grassroot-soccer/
0 stars 0 forks source link

Update the Upsert Home Visit Log Form job to link Home Visits to Events using the participant case id #24

Closed daissatou2 closed 3 years ago

daissatou2 commented 3 years ago

Background, context, and business value

See #3 for project background.

Update the Upsert Home Visit Log Form job to link Home Visits to Events using the participant case id (form.case.@case_id) if the event name (form.basic_information.intervention_hidden) is not in the form.

The specific request, in as few words as possible

If the event name (form.basic_information.intervention_hidden) is defined in state, keep the mapping as is below. https://github.com/OpenFn/grassroot-soccer/blob/98f32957ab806e854a59e3226550bfa52746c216/jobs/upsertHomeVisitLog.js#L7

If the event name (form.basic_information.intervention_hidden) is NOT in the form, then use Person case_id (form.case.@case_id) to find eventid to return the mapping below...

field('Event__c', dataValue('${eventid}'))

In this scenario, to find eventid we should...

  1. Query all Attendance records related to the Participant 
    SELECT Id, Event__c, CreatedDate, Person_Attendance__c FROM Attendance__c WHERE Person_Attendance__r.CommCare_External_ID__c = {form.case.@case_id} ORDER BY CreatedDate DESC LIMIT 1
  2. This query should return only the most recent Attendance__c record for that Person. Then set eventid == response[0].Event__c and output the mapping... 
    field('Event__c', dataValue('${eventid}'))

state.json

https://www.openfn.org/projects/pd3yk4/messages/mz73ek3b

expression.js

https://github.com/OpenFn/grassroot-soccer/blob/master/jobs/upsertHomeVisitLog.js#L7

output.json

Rerun this transaction: https://www.openfn.org/projects/pd3yk4/runs/rn5j5np3 Field mapping should either be as follows, if intervention_hidden is defined...

relationship('Event__r', 'CommCare_Ext_ID__c', dataValue('form.basic_information.intervention_hidden')),

OR as follows if intervention_hidden is not defined and we have to go find the Event__c id. 

field('Event__c', dataValue('${eventid}'))
lakhassane commented 3 years ago

@daissatou2 I implemented the right logic.

Howver, the query provided does not seem to work. I'm having an issue saying No such column 'CommCare_External_ID__c' on entity 'Person__c'.

Can you advise on the right query and I could replace it on the job. Otherwise, everything is mapped as supposed to.

daissatou2 commented 3 years ago

@lakhassane Instead of CommCare_External_ID__c please use Participant_Identification_Number_PID__c

lakhassane commented 3 years ago

@daissatou2 corrected it and the mapping is correct now