Skip to main content

CommCare to Azure Sql

This job was provided by an OpenFn.org user via the job library API.

Metadata

  • Name: CommCare to Azure Sql
  • Adaptor: @openfn/language-mssql
  • Adaptor Version: latest
  • Created about 4 years ago
  • Updated almost 4 years ago
  • Score: 3 (an indicator of how useful this job may be)

Key Functions

alterState, join, map, sql

Expression

// Insert household, fail on duplicate (consider upsert strategy, if you want!)
sql({
query:
'INSERT INTO Household (' +
[
'commcare_id',
'is_head',
'total_members',
'head_name',
'date_registered',
'village',
'new_thing',
].join(', ') +
`) VALUES ('` +
[
state.data.metadata.instanceID,
state.data.form.head_of_household === 'yes' ? 1 : 0,
state.data.form.hh_members,
state.data.form.individual_name,
state.data.form.registration_date,
state.data.form.village_name,
state.data.form.new_q,
].join(`', '`) +
`');`,
});

// Prepare a string to be used for VALUES (...) in our member insert.
alterState(state => {
const data = state.references[0];
const form = data.form;
const parentId = data.metadata.instanceID;
const memArr = form.register_hh_members.map((m, i) => {
return `('${parentId}-${i}', '${parentId}', '${m.hh_member_name}', '${m.age}')`;
});

state.members = memArr.join(', ');
return state;
});

// Insert members, fail on duplicate
sql({
query: state =>
`INSERT INTO HouseholdMember (commcare_id, parent, name, age) VALUES ${state.members}`,
});