search

OpenFn / grassroot-soccer

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

New jobs for GRS CommCare to Salesforce sync #1

Closed daissatou2 closed 3 years ago

daissatou2 commented 3 years ago

Background, context, and business value

GRS is working with Dimagi and OpenFn to design and deploy CommCare workflows across their organization to allow for Youth Mentors to leverage the advantages that come with mobile data collection. This CommCare implementation will also include automated data integration between CommCare and Salesforce.

Example: As a Youth Mentor hosting a Malaria Testing Event, I would like to take attendance on a mobile device and automatically sync participant information to Salesforce.

The specific request, in as few words as possible

We need to implement a one-way integration between CommCare and Salesforce, mapping CommCare form submissions to different Salesforce objects.

See the mapping sheet for field-level mappings and sample JSON response files for each form.
Salesforce system schema:
image

Create the following jobs that execute the following steps: Extract forms from CommCare

  1. GET the following form data from CommCare: Create Intervention My Team/Group Name Intervention Notes Register Participant Coach Support Visit Form Coach Support Visit Form Zambia See CommCare API docs and this example GET job that contains the xmnls ids: https://www.openfn.org/projects/pd3yk4/jobs/jvebnp We want to extend this job to send 1 GET request for every form. Also, if you add this job to Github, can you please remove the Inbox url from the expression?

Map CommCare forms to Salesforce

  1. Upsert Create Intervention in Salesforce. This job should upsert Event records using 'Name' as the unique identifier.
  2. Upsert My Team/Group Name in Salesforce. This job should upsert Event records using 'Name' as the unique identifier.
  3. Upsert Intervention Notes in Salesforce. This job should upsert Event records using 'Name' as the unique identifier.
  4. Upsert Register Participant in Salesforce. This job should upsert Person records with Participant_Identification_Number_PID__c as the UUID, and then create related Attendance records.
  5. Upsert Coach Support Visit Form and Coach Support Visit Form Zambia in Salesforce.

state.json

Salesforce creds: See LP GRS Salesforce Sandbox CommCare creds: See LP GRS CommCare App

Project Inbox: https://www.openfn.org/projects/pd3yk4/messages See links to sample CommCare JSON responses in google sheet - see Forms Objects sheet for the high level entity mappings, and links to state Messages.

adaptor

CommCare or HTTP adaptor to get? Salesforce adaptor to load

trigger

  1. Job 1 which fetches form data will be timer-based. Confirming with GRS team on the frequency of the sync...
  2. The upsert jobs will be message filter-based (e.g., {"form": { "@name": "Create Intervention"}}

expression.js

6 jobs...

1 to get all form data, remaining 5 to upsert to corresponding Salesforce object.

output.json

The output of job 1 will be state files that can be filtered by form. These messages will then be upserted in Salesforce.

aleksa-krolls commented 3 years ago

@daissatou2 Some quick feedback... if I'm new to this project reading this issue, it's not clear to me what each job is doing. Please...

  1. Be more descriptive about what each job is doing. Reference your Form Objects sheet as needed. For example... 
    Job 'Upsert Intervention' should upsert Event records using 'Name' as the unique identifier
Job 'Register Participant' should upsert Person records, and then create related Attendance records
  2. Share the schema (see notes doc) so Mamadou can better visualize the relationships
  3. For any relationship/ look up fields (e.g., Site__c), we will need to specify how to look-up the parent record using Name or some other external Id. For Salesforce, we can leverage the helper function relationship(...): http://openfn.github.io/language-salesforce/global.html#relationship This might look like... relationship('Site___r', 'Name', dataValue('form.site')) if looking up using Site Name relationship('Site___r', 'CommCare_External_Id__c', dataValue('form.site')) if looking up via a unique identifier that we'll export from CommCare Let's discuss!
  4. We need to add a Transformations column to the mapping template to specify any data cleaning and manipulation needed. See my comments on the Create Intervention sheet for initial feedback to apply across jobs: https://docs.google.com/spreadsheets/d/1CXrMYL0hELSeRkjJLUROTR0A3udJ0Yq8PQZhUtTQokk/edit#gid=0
aleksa-krolls commented 3 years ago

@daissatou2 please assign this issue to me if it's ready for final review & to put into the backlog.

aleksa-krolls commented 3 years ago

@lakhassane this is ready for devpt. Please walk through with @daissatou2 if you have any questions - she's the lead on this project.

lakhassane commented 3 years ago

@daissatou2 I made a draft for the diffrerent jobs according to the sheet. However, RegisterParticipant might need more checking. Im still not sure about how to understand that tab and the lookup value Attendance while upserting Person

aleksa-krolls commented 3 years ago

@lakhassane @daissatou2 and I have some questions about job 1 and how it works: https://www.openfn.org/projects/pd3yk4/jobs/jvebnp

Steps to replicate

  1. We ran the job like this where we commented out things to only fetch the forms for 2 xmnls ids: 

    'http://openrosa.org/formdesigner/4CFAC371-88F7-4349-BEB2-3DA2BDE445EA', //Malaria Testing Event Snapshot
'http://openrosa.org/formdesigner/FAF8F169-0FF1-4DE4-98B7-E4450BECBC84'  //Malaria Testing Event Statistics

    see job version: https://github.com/OpenFn/grassroot-soccer/blob/e4e4df77f75a2162df40fa588c1eca7ef4a954c4/jobs/1.getForms.js

  2. This GET request should have fetched submissions for both forms... but only 2 Statistics (not Snapshot submissions) were sent to the Inbox: https://www.openfn.org/projects/pd3yk4/messages?startDate=2021-05-05T17%3A36%3A17.628Z

However, we know that Snapshot submissions exist... so when we re-ran the job ONLY querying for this xmnls, we did have 1 Snapshot submission return.

Issue

It doesn't look like the job is correctly pulling submissions when we specify 2+ xmnls form ids. Are we using the job correctly? Or is there a bug here?

Feel free to run on prod to test: https://www.openfn.org/projects/pd3yk4/jobs/jvebnp

lakhassane commented 3 years ago

@aleksa-krolls @daissatou2 the issue was with the posting to openfninbox actually. I made the change and both forms are being sent to the inbox (see last messages: https://www.openfn.org/projects/pd3yk4/messages)

aleksa-krolls commented 3 years ago

Thanks @lakhassane seems to be working now :) cc @daissatou2 the job 1 should be good to go