CommCare-export tools

Hi everyone

Is there anyone experiencing this issue as described on image ?

It looks like you have authenfication problems on your local database. Or password is not correct or the priviliges for the account used are not sufficient.
update: rereading the error message I would start with confirming the access details to your database

Finally by digging my commcare-export script work now, but as I thougth with the schema (columns) speficied into my_query_file that I will have the same schema to my MySQL DataBase side, do I miss something? Can you take look to my query file? is that correct ?

I will appreciate your support




The export tool creates a few tables in general (alembic and commcare_export_runs) but if it runs properly also a table for each tab in your excel file with the table name the same the name of the tab. (registrations in your case) The tool can make the columns itself if it has the right priviliges. So in principle no need to determine the columns names yourself in the database.
Looking at the error send now, there might be something wrong with the adres of the form. since you get HTTP 404 error.

btw be aware that you are not exposing your (local) pasword for the database, so you might want to hide if it is not only for testing purposes

Thanks @Jos_van but it works now even it was challenging :slightly_smiling_face: , noted I will change it now


It would appear that the numero de telephone du contact column has been created with an Integer column type but the value (2345039434) is too big for that column type. Normally we could use a function in the query file to change the type but we don’t have an int to string function so what you will need to do is change they column type manually:

-- assuming 32 characters is long enough
ALTER TABLE registration MODIFY `numero de telephone du contact` VARCHAR(32);

Thanks @Simon_Kelly already corrected but the other problem that I’ve, My application contains 3 forms (registration, follow-up and close) as speficied to my query file attached and I want to have that my Database contains those 3 tables (registrations, follow-up and close), the problem is after running my commcare-export just one table (registrations) is created the two other one not created do I miss something? Please can you take a look to my query file?

Hi

The cloture contact sheet has an incorrect XMLNS filter. There is a space after the XMLNS: "http://openrosa.org/formdesigner/AC164B28-AECA-45C9-B7F6-E0668D5AF84B ".

For the other sheet (suivi contact) the ‘Data Source’ column is incorrectly titled ">" which is why it is being ignored. The tool should be printing out a warning message on every run like this:

2019-04-29 12:45:12,177 commcare_export.minilinq WARNING  Ignoring sheet "suivi contact": Sheet has no "Data Source" column.

Hi @Simon_Kelly

It looks like commcare-export tool doesn’t export Mobile Worker Username, I’ve used this link : https://webapps.stackexchange.com/questions/103199/can-the-commcare-export-tool-export-mobile-worker-or-location-information to export user_id, but instead user_id how do I export Mobile worker user username ?
Many thanks for your supports

The tool can only export what is in the API output. If you are exporting forms you can use the form.meta.username to get the username.

If you want to convert a user_id to a username you can do that with a mapping field. This allows you to map input values like 1234 to an output value like user1. In order to do this of users you will need to export all your users from the CommCare HQ Users page and then put them into your config file for the commcare-export tool with the ID and the username columns.

There is a section on the help docs about this: https://confluence.dimagi.com/display/commcarepublic/Data+Export+Tool%3A+Tips+and+Tricks#DataExportTool:TipsandTricks-Convertoutputvaluesbasedonamapping

Hi @Simon_Kelly
I hope you are doing well.
how to manage the duplication of forms, let’s suppose I do submit one follow -up form by day by patient, how do I create display, validation condition or lock form follow-up to run once by day? is there feature with CommCare can help to handle this issue?

I’m not sure I understand the question - are you asking about duplicate forms in the export?

Hi Aboubacar,

Forms can be configured with display conditions as detailed here. You could add a property to each case stating the date of the last visit, then configure your follow-up form to appear only if that date is not today. This would mean that once a user fills out this form, it would no longer appear for that case until the next day.

Similarly, you could filter the list of patient cases to hide those who have already been visited on a particular day.

1 Like

Hi @Simon_Kelly

How do I can reference username of mobile worker into excel query file for case data like form.meta.username for Data form

Thanks

You can’t reference the username but you can reference properties.owner_id. This could be a the ID of a user, group or location depending on how your app is configured.

To get the username you would have to do a mapping from the ID as I mentioned previously: CommCare-export tools

1 Like

Thanks @Simon_Kelly for your response, but instead mapping properties.owner_id to location which will be a little difficult for referencing as filter to my tableau dashboard as my case type has a hierarchical locations (Province ->Territoire->Zone de Santé->Aire de Santé). I would like to find a way to have the username (in single table) of mobile worker into my query file for exporting the mobile worker username who will created and added during the project. I have attached screenshot the columns ( in red color) that I want reference to my case form query file.

in order to filter data by username of mobile worker in the Dashboard as further we want build mobile worker performance dashboard ( just let’s know how number case opened, closed, modified by mobile worker during the periode of patient monitoring)

Thank you

Hi

The images didn’t come through. You could try user_id which should be the ID of the user who last updated the case.

Instead of mapping the IDs in the export tool have you considered having a separate user table in your DB and joining on it to get the usernames?

Thanks @Simon_Kelly I think finally i will choose the table join option

Hi Simon,

I need your help in adding row number in Commcare data using Commcare Export Tool.One more thing how to link repeat group data with non repeating data .

A row number is automatically added for Excel exports. It’s not possible to add a row number for SQL exports however.

In terms of linking repeat group data with other form data, you can use the form ID. When exporting repeat group data you can use the $.id field which will output the form ID. That should allow you to join between the repeat group table and the main form table.