Reverse sync of case properties from MySQL Database

Users
1

Hi,

We are currently exporting case data from Commcare to our MySQL Database at a set frequency. Is there a possibility where we make changes on the case data in the Database and it syncs back to Commcare automatically? We currently have to do the same using the Case Import tool but it would be great if it can be automated.

Regards,
Nikhil

2

You can automate case changes using the APIs. Case changes happen via form submissions, which maintains a history of the case. You can find documentation and examples for that API here, as well as a link to an example implementation in python.

Unfortunately there isn't any way to sync directly from a local database, since all changes to cases happen through form submissions.

3

Hi Nikhil,

An alternative might be to use the Bulk Upload API, documented here.

The API allows you to upload an Excel spreadsheet of cases, where the column headers are the names of the case properties.

Python's "tablib" library makes it easy to create this Excel spreadsheet on the fly. You can find some sample code here. And you can see a function for uploading that file just below it, here.

If Python is not your thing, I hope that the code is readable enough to implement in your language of choice.

Useful things to note about that code:

  • 'search_field': 'external_id' tells CommCare to use the value in the "external_id" column to identify cases by. Considering the cases in MySQL come from CommCare, you may already have their case ID. If so, you can use a column called "case_id" instead. (If you use "external_id", values must be unique. Case IDs will always be unique.)
  • You'll want to change 'comment': 'Imported from DHIS2 tracked entities' to something more meaningful for you, like "Imported from MySQL" maybe.

I hope that helps.

Norman

4

Hi Ethan,

Thanks a lot for the suggestions.

Regards,
Nikhil

5

Hi Norman,

Thanks a lot for the suggestions.

Regards,
Nikhil