Dear All,
I am downloading a form which has 3 levels for repeat i.e I get 4 tabs: one master and 3 repeats in excel with row numbers in all of them for linking. But when I download the same form data in MySQL using DET, I do not get the numbers. Then how to link the data in mysql. Please note that I am keeping data in separate tables and I have formid in all the tables i.e main and 3 repeat tables. Since this is a survey form so I do not have CaseID in this download
Hi Amit,
Do check your DET file again, the number field has to be there, even in the repeat group sheets. However, as per my knowledge, the formid is only in the main form sheet and not in the repeat group sheets in the DET.
As for linking the tables in MySQL with the number columns, I really do not think that is a good idea, but you might be knowing better as we have not tried much linking using the number field. We always link using some concatenated unique id or the case id.
We have been using CommCare at our organization for more than a decade and we have always advised against using repeat groups as it creates different sheets in Excel and we need to download the same in different tables in SQL and it becomes almost impossible to link them until and unless we manually add some unique ID in all the repeat group.
As per our experience, the only way to link them is to manually add the concatenated unique ids in all repeat groups, and only then can you join the same in SQL. However, we have never tried linking the same using the number field so do not know much about that.
Also, we always advise against using the survey type module and we always use case list modules even for one-time data collection because the survey module does not create a case id which makes things difficult. However, this is not the issue in the case list type module.
Dear Hussain
The number field is in the DET file downloaded. But the value returned is NULL. And what do you suggest if we have to say ask diseases history of all the family members. Now those are repeat questions. So what is the best way to set up such question or create forms for them
I do not know why the number field is null. Did you check in the commcare raw data export? Is the value present there? Try contacting the support team if things do not work out.
As for the disease history of all the family members, yes these are repeated questions but another way to do this is to ask this question multiple times by adding them multiple times in your form. You can add these to a question list by selecting the question list question type in your form. This will display the questions one below the other on the phones. For this, you will have to decide for how many family members you want to capture the details for, for eg 5 or 7, and then add those number of questions. Before that ask a question stating how many members are there in your family, the next set of questions should show based on the number entered in the previous question. For eg, if I enter 2 in total family members, then 2 questions will show about disease history, if I add only 1 then 1, and so on.
I know that the above-stated suggestion will increase the total number of questions from the developer's side but it will make the data analysis much more simple as you will not have to deal with multiple sheets in an Excel file and all your data will come in one table in SQL.
Dear Hussain
By raw data you mean the export from the Data menu of CommCare? Yes the number field is populated there but not in DET. As per the documentation, DET provides form data. And this number field is actually a row number. This starts from 0 everytime you download the data
I am not really sure about that because we have never used the number field as there has not been any need of doing so. You will need to check with the commcare support team I guess.