Skip to end of metadata
Go to start of metadata

You are viewing an old version of this page. View the current version.

Compare with Current View Page History

« Previous Version 17 Next »

Have a very long survey that you’d rather put into an Excel file before uploading in the form designer? You can use XLSForm or FormHub.  Both are free, online form-building tools that allow you to design your form in an Excel file, and then copy the created XML code into the CommCareHQ form designer. You can work on the Excel file offline and then upload it later.

FormHub is not actively supported, so we recommend using XLSForm.

Both XLSForm and FormHub have excellent documentation on how to build a form, such as this instructional video. You can see detailed instructions on how to set up the Excel file here. 

Overview

Once you build your form in Excel and upload it to XLSForm (http://opendatakit.org/xiframe/), you can download the XForm and upload it into our form builder on HQ.

  1. To load into CommCare, download the xform, a file that will end in .xml (in Formhub: click the name of the form, i.e. For the sample form, you get the xform here. There is a download button for the xform.)
  2. Once in the form builder CommCare HQ, after creating a new blank form, Select "Tools" and "Edit Source XML" and just paste the XML generated from FormHub into HQ. You can also upload the xform directly on the form settings page under the "Advanced" Tab.

 

 

Notes while building your form

  • If you are familiar with XML, you may notice some differences in syntax between the XML generated and CommCare’s language – Don’t worry! Noticeable differences are validation writing and writing for display conditions (what FormHub and XLSForm calls “relevancy”). As all of these tools are based on the same standard, CommCare HQ will automatically fix the minor differences from FormHub syntax to meet our syntax requirements.

Example: In CommCare's Form Builder, a display condition might look like: /data/pizza_fan = 'no' while in Formhub it would look like: ${pizza_fan} = 'no' - these will be updated when the xml file is generated

  • To avoid issues with itext and how CommCare deals with languages make sure to specify the language in the header. For example, if your app is only in English then make sure to put "label::en" instead of just "label" - if you do CommCare will add two languages (default and English).
  • To keep using "data" as the ID of each form, please be sure to name your excel file "data". CommCare may not recognize the "settings" tab of the Excel file described XLSForm.
  • Labels (called "notes" in the other tools) are often treated as text questions when the form is uploaded to CommCare. You can change the question type from Label to Text in the CommCare form builder. This is a known issue and will eventually be fixed. However, if you use the question type "acknowledge" then CommCare will interpret that as a label with the checkbox confirmation turned on.
  • You can test your forms online using https://opendatakit.org/use/validate/


Known Limitations

There are some things that don't work well when uploading a Formhub form to CommCare; these are active efforts to make these more compatible

  • You may get an error message when uploading your form that CommCare xmlns will be added. You can ignore this message. If, once inside the Form Builder, you see a persistent error message about the Form Name make any minor change in the form, save, and refresh the page.
  • When first opening your form to edit you may see a message that says "id is not defined." Just click "ok" and then make any minor change in the form, save, and refresh the page.
  • If you have multiple languages in your Excel form but not in your CommCare app, any additional languages that are not already in your app will be deleted.  Before uploading anything go to the application -> languages and make sure all languages are added. On the languages page you will see that each language has a 2 or 3 letter code (English = en; Hindi = hin). In your Excel file you must use those codes in the headers. So for English you would use "label::en" as the header for the label column on both tabs of the Excel file. These are case sensitive.  Alternatively you can upload the xform with one language, add the additional language, and then use Bulk Translation to add additional languages.
  • Both tools described here uses the name of you Excel file as the root of all of the xpaths in your form. So if you call your file "survey" then your data will look like /survey/question4 instead of /data/question4. You can configure this in CommCare under Tools -> Form Properties. You can also just name your excel file "data" if you want to be consistent.  If you change the form name after uploading, however, it will break a large portion of your display logic.
  • The "add other option" for multiple choice questions will generate the "other" question with the correct question ID but will not generate the correct skip logic.
  • You may also notice some extra "hidden values" at the bottom of your form after using the Excel tools. You should delete these.  These hidden values are formhub, uuid, meta and instanceID.  
  • Advanced features link hint text, multimedia, read-only, and complex logic may not be well supported.


Shortcut for creating the XLSForm format from CommCare's Export Form Contents

You may end up with an Excel form that is formatted like Export Form Contents as generated in CommCare, where all of the choices and questions are on one page. Below is a process to simplify breaking it into two pages.

The two parts of the process are:

1) split the choices from the questions (e.g. if the question is "What block do you live in" then the choices would be "Manjhanpur", "Mooratganj", "Kaneli", etc); and

2) format the sheets to match what XLSForm wants.

Here are the steps

1. Create a new column in excel that is the name of the question for the choices. To do this, you can use an if statement. If you have the question names in column C and the type of question in column H, then it would bethe following: =if(H3="Select Item",C3,C2) . This way, if you have a question, it will copy the question name, and if you have a choice, it will copy the value above it, which will be the question name from its question.

2. Create a new column in excel that equals 1 if the row is a choice and 0 if the row is a question.

3. Copy the two new columns that you created and paste special values them on top of themselves.

4. Sort the newly created column of 0's and 1's for question to split the questions and choices. Then copy the choices to their own sheet.

5. On the new choices sheet, get rid of the columns that you don't need anymore. Then change the column headings to match the format required.

6. Go back to the sheet with the list of questions. Use find and replace to change the names we have used for question type with what xforms uses (e.g. select_one rather than single select, text rather than text question. So find and replace all "single select" with "select_one").

7. Create a new column in excel. In this column, if the type of question is select_one or select_multiple, then the value should be "type nameofquestion". If it is any other type, then it should just be the type of question.

For example, if the type of question is select_one and the name of question is consent, then the value in this column should be "select_one consent". If it is a text question, then this value should be text. To do this, if the question name is in column B and the type of question is in column C, starting from the 3rd row, you can use =if(OR(C3="select_one",C3="select_multiple"), C3&" "&B3, B3).

8. Change the names of the columns to match the format  for xforms and delete any that you don't want. You will need to delete the validations if you have not edited them for xforms format.

9. Upload the form following the instructions above

 

  • No labels