openfn-jobwriting-day2-hw-salesforce.md

Challenge

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:

1. extract data from your HIS (you've been given direct DB access)
2. automatically calculate indicator results & map the data elements to the Salesforce data model, and
3. upsert vera__Indicator_Result__c records in Salesforce in order to update linked indicator reports

Request

• Job 1: Get rows from the DB table patient
• Job 2: Calculate the results for 2 different indicators. Reported on the calculated sum or count values to Salesforce as a new vera__Indicator_Result__c records.

Mapping Specs: See Day 2 mapping spec here and sample output linked below.

Workflow diagram:

State

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/

Output

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 in 2022 for Guinea
• Result #2 for indicator 1001: Number of enrolled patients in 2022 for Uganda

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 in 2022 for Guinea
• Result #2 for indicator 1002: Number of OPV doses given in 2022 for Uganda

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