Your organization is collecting immunization data in a custom health information system (HIS) built on PostgreSQL.
Your government partner wants you to start sending weekly, aggregate reports to Salesforce on immunization key indicator results. To do this, you need to automatically calculate indicator values based on the individual data collected in the HIS.
You’ve been asked to design a workflow to:
- extract data from your HIS (you've been given direct DB access)
- automatically calculate indicator results & map the data elements to the Salesforce data model, and
- upsert
vera__Indicator_Result__c
records in Salesforce in order to update linked indicator reports
- Job 1: Get rows from the DB table
patient
- Job 2: Calculate the results for 2 different
indicators
. Reported on the calculated sum or countvalues
to Salesforce as a newvera__Indicator_Result__c
records.
Mapping Specs: See Day 2 mapping spec here and sample output linked below.
Job 1: postgresql
DB
- configuration: See slide 49 from training
- data: {}
Job 2: Salesforce
- configuration: See slide 49 from training
- data: Use final state from
Job 1
See the latest Adaptor docs here: https://docs.openfn.org/adaptors/
Upsert 1 record for each combination of the Indicator
+ Geographic Area
+ Reporting Year
in Salesforce.
So for the 1st indicator "Number of enrolled patients", you will need to COUNT the rows of enrolled patients, grouped by year and by geographic area. There should be 2 Indicator Result
records upserted based on the DB data:
- Result #1 for indicator
1001: Number of enrolled patients
in2022
forGuinea
- Result #2 for indicator
1001: Number of enrolled patients
in2022
forUganda
For the 2nd indicator "Number of OPV doses given", you will need to SUM the values in the opv_doses_given
column, grouped by year and by geographic area. There should be 2 Indicator Result
records upserted based on the DB data:
- Result #1 for indicator
1002: Number of OPV doses given
in2022
forGuinea
- Result #2 for indicator
1002: Number of OPV doses given
in2022
forUganda
See Salesforce Adaptor docs for more on available functions and example jobs.
For the mapping spec use the tab
Day 2: Workflow 2, Aggregate Reporting