Lwala uses OpenFn to integrate its Salesforce database and CommCare CHW mobile application. The OpenFn jobs in this repository automate a bi-directional dataflow between the CommCare and Salesforce systems, ensuring regular data syncs and feedback loops for CHWs.
N.B. Any commits to the master
branch will be automatically deployed to
Lwala's OpenFn project
OpenFn jobs are used to automate the following workflows between CommCare and Salesforce. This integration is event-driven (triggered whenever a record is created/ updated). This diagram provides an overview of how data flows from CommCare forms to Salesforce objects through OpenFn jobs.
There are some reference data tables that need to be consistent across the CommCare and Salesforce applications to ensure successful integration:
Label
for these location records is consistent across Salesforce and CommCare. Id
for each CHW is captured in CommCare . Names
need to be aligned. CHWs register households, patients, and visits, and use CommCare as a tool for ongoing data collection and case management. As soon as the following CommCare forms are submitted, these CommCare-Salesforce-Jobs
execute to forward data to Salesforce.
In Q2 2023, new workflows were implemented to fetch cases from CommCare and sync to Salesforce in bulk. The aim was to reduce traffic and the number of OpenFn transactions, and to bulkify this process so that the updates run on a timer-basis, rather than in real-time after every small edit to a case record in CommCare.
Note that each workflow contains 2 jobs (the 1st to get cases from CommCare; the 2nd to map & bulk upsert records in Salesforce using the Bulk API). These jobs are configured as flow
jobs on OpenFn.org, and their source code is saved in the /bulk directory in this repository.
Bulk Workflow 1: Households & HH Visits Sync
Bulk Workflow 2: Person Sync
Bulk Workflow 3: Person Visit Sync
Bulk Workflow 4: Services Sync
See the 'bulkifying' diagrams via this link for the LucidChart workflow diagram documentation.
These forms were introduced to support MOH partnership requirements, but are only live in some areas... to be rolled out widely in July 2021
MOH513-Enroll-Person-in-SF.js
) (To be replaced by Upsert Person & Person Visit)MOH513-Enroll-Household-in-SF.js
) (To be replaced by Upsert Household & Household Visit)MOH514-Update-Person_V2.js
) (To be replaced by Upsert Person & Person Visit)MOH513-Update-Household-in-SF-Revised.js
) (To be replaced by Upsert Person & Person Visit)Referrals-Upsert-Person-Immunization_V2.js
) (To be replaced by Upsert Person & Person Visit)[Referrals-Upsert-Service_V2.js
](https://github.com/OpenFn/lwala/blob/master/commcare-salesforce-jobs/Referrals-Upsert-Service_V2.js))
7.[Q3 2022] Create Distribution & Referral in SF (Create-Distribution-Referral-in-SF.js
)Update-HH-Name.js
)
9.[Q3 2022] Upsert Household & Household Visit (upsert_household_and_household_visit.js
)Upsert-Person-Case-Update.js
)TEST_upsert_person_and_person_visit.js
)MOH514-Update-Person-in-SF-Production.js
)nutrition-survey-job.js
)Lwala Application Forms (These are the original CommCare forms that were replaced by the MOH forms.)
Create-Person-in-SF-Production.js
)Create-Household-in-SF-Production.js
)Update-Person-in-SF-Production.js
)Update-Household-in-SF-Production.js
)CHW-Reassignment-Production.js
)Enroll-in-Nutrition-Group-in-SF.jss
)nutrition-survey-job.js
)There are multiple Apex Triggers in Salesforce on the Household
and Patient
objects that send outbound messages to Lwala's OpenFn inbox when specific updates are made in the Salesforce system. This enables OpenFn to sync Salesforce info back to CommCare. This SF automation includes:
Households: Once HHs are creaed in Salesforce (via OpenFn/CommCare), a HH Code is assigned to Household__c.Name
via the Salesforce Apex trigger HouseholdCodeTrigger1
. Then another trigger HouseholdupdateWebhookTrigger
sends this HH code (e.g., 42920
) and other HH information back to CommCare (via OpenFn) to sync back this reference id back to CommCare.
These trigger run on Household insert/update and were developed by Lwala's Admin Will Edman in 2017.
Patients: Salesforce Apex trigger PersonTrigger
runs on Patient create/update to re-assign Persons to HHs and send notifications to OpenFn/CommCare when important information is changed or a case is de/activated, to sync back to the corresponding CommCare case.
This trigger was originally developed by Vera Solutions, and then extended by Lwala's Admin in 2017 to include the webhook notifications to OpenFn.
When these trigger-created Salesforce outbound notifications are received as new Messages in OpenFn, the following jobs are triggered to update data in CommCare.
CHW-Stats.js
)Close-Household-Case.js
and Close-Person-Case.js
)Create
jobs here)Update
jobs here) See here for the master troubleshooting user guide.
FIELD_CUSTOM_VALIDATION_EXCEPTION: Duplicate Visit
: Ignore. Salesforce-side error that occurs when OpenFn attempts to insert more than 1 Visit record within the same day. Forces failure to prevent duplicate data. This is NOT best practice - to revisit and consider re-design so that there are never expected failures in OpenFn.UNABLE_TO_LOCK_ROW
: Re-run to reprocess successfully. This is an error related to the Salesforce design that occurs when multiple automation flows attempt to update the same record at the same time. To try lowering OpenFn run concurrency to see if this reduces occurrence of this error.REQUIRED_FIELD_MISSING
: Occurs when a Salesforce field is made "required", but the corresponding data is not provided by and/ or not mapped by OpenFn. To troubleshoot, update the OpenFn mappings as needed and re-process Messages. Contact support@openfn.org for troubleshooting guidance.
openfn-devtools
to test and edit jobs offline. Propose a change by creating an issue, then feature branch named ###_issue
and
submitting a pull request against master
. When it has been reviewed by at
least one other person, it may be merged and deployed to OpenFn.
Create a /tmp
folder in this repo that is NOT part of version control. Put a
state.json
file in there, and after setting up openfn-devtools you can execute
job tests with:
~/openfn-devtools/core/lib/cli.js execute -l ~/openfn-devtools/language-salesforce/lib/FakeAdaptor -e ./commcare-salesforce-jobs/Update-Person-in-SF-Production.js -o ./tmp/output.json -s ./tmp/state.json
or run real jobs with:
~/openfn-devtools/core/lib/cli.js execute -l ~/openfn-devtools/language-salesforce/lib/Adaptor -e ./commcare-salesforce-jobs/Update-Person-in-SF-Production.js -o ./tmp/output.json -s ./tmp/state.json
A sample state.json file looks like this:
{
"data": {
"version": "1911",
"uiversion": "1",
"type": "data",
"server_modified_on": "2018-07-18T15:22:16.552478Z",
"resource_uri": "",
"received_on": "2018-07-18T15:22:15.184390Z",
"problem": null,
"metadata": {
"username": "Nu-uh",
"userID": "999368544f4fa1af1e1f42d0e208349c",
"timeStart": "2018-07-18T14:26:20.277000Z",
"timeEnd": "2018-07-18T14:26:23.420000Z",
"location": null,
"instanceID": "469351d5-2bb6-48e6-a4ad-39eba54961a9",
"geo_point": null,
"deviceID": "354385080046177",
"commcare_version": "2.35.3",
"app_build_version": 1913,
"appVersion": "CommCare Android, version \"2.35.3\"(431724). App v1913. CommCare Version 2.35. Build 431724, built on: 2017-04-19"
},
"is_phone_submission": true,
"initial_processing_complete": true,
"id": "469351d5-2bb6-48e6-a4ad-39eba54961a9",
"form": {
"owner_id": "de0c06552a6c471daef634553905036e",
"meta": {
"username": "Nope, Shhhhhhhhhhhhh!",
"userID": "999368544f4fa1af1e1f42d0e208349c",
"timeStart": "2018-07-18T14:26:20.277000Z",
"timeEnd": "2018-07-18T14:26:23.420000Z",
"location": {
"@xmlns": "http://commcarehq.org/xforms"
},
"instanceID": "469351d5-2bb6-48e6-a4ad-39eba54961a9",
"geo_point": null,
"deviceID": "354385080046177",
"commcare_version": "2.35.3",
"app_build_version": 1913,
"appVersion": "CommCare Android, version \"2.35.3\"(431724). App v1913. CommCare Version 2.35. Build 431724, built on: 2017-04-19",
"@xmlns": "http://openrosa.org/jr/xforms"
},
"group_id": "de0c06552a6c471daef634553905036e",
"case": {
"update": {
"owner_id": "de0c06552a6c471daef634553905036e",
"CHW_Name": "Shhhhhhhhhhhhh",
"CHW_ID": "a032400000GXUFDAA5"
},
"@xmlns": "http://commcarehq.org/case/transaction/v2",
"@user_id": "999368544f4fa1af1e1f42d0e208349c",
"@date_modified": "2018-07-18T14:26:23.420000Z",
"@case_id": "ad794303dee24a4189a80ee515bb2dfb"
},
"areaid": "tukjowichw",
"area": "91cdca73b8d423d03e5579d917bd1315",
"CHW_Name": "Shhhhhhhhhhhhh",
"CHW_ID": "a032400000GXUFDAA5",
"@xmlns": "http://openrosa.org/formdesigner/13C1B47D-82E6-4EA8-8E21-F286F1A3AE39",
"@version": "1911",
"@uiVersion": "1",
"@name": "Change Household CHW",
"#type": "data"
},
"edited_on": null,
"edited_by_user_id": null,
"domain": "lwala-community-alliance",
"build_id": "c08011fd07f347efb1a3be2d3b1fa06d",
"attachments": {
"form.xml": {
"url": "https://www.commcarehq.org/a/lwala-community-alliance/api/form/attachment/469351d5-2bb6-48e6-a4ad-39eba54961a9/form.xml",
"length": 1445,
"content_type": "text/xml"
}
},
"archived": false,
"app_id": "fc491785fa909e85fbdb63a9b346fa6c",
"__query_params": {
"app_id": "fc491785fa909e85fbdb63a9b346fa6c"
}
},
"configuration": {
"loginUrl": "https://test.salesforce.com/",
"password": "No way, dude!",
"username": "will@lwalacommunityalliance.org.veraist",
"securityToken": "shhhhhhhhh!!!!"
}
}