Understanding the Query API

ActivityInfo provides a "default" query for each form that attempts to provide a form's data in a format most commonly needed by users. The "Default Query" can be accessed via the following URL:

https://www.activityinfo.org/resources/form/{formId}/query

Where {formId} is the alphanumeric id of the form, such as c1y5yyukfwb6x077. You can find the Form ID in the URL of your browser, or you can find it by selecting "Export", and then "Export via the API", and then "Query all fields as JSON"

The default query will include:

• The Record's ID in a column called "@id"
• Two columns for Geographic Point Fields in the form "{field name}.latitude" and "{field name}.longitude"
• One column for each text, quantity, single select, serial number, date and other simple fields
• One column for each multi-line text field, truncated to 128 characters
• One column for multi-select fields, with the selected items separated by commas.
• One column for subforms, showing the count of subrecords
• A column for each field in a referenced form, in the form of "{reference field name}.{field name}"
• A column for each field in the parent form, in the format "parent.{field name}"

If a field has been given a code in the Form Designer, then the code will appear in the column, otherwise the field's full label will appear.

When querying a "parent" form, the resulting table cannot include records from any sub forms, as this would not fit in the tabular format expected by Power BI. If you want to include data from sub forms, then you must query the subform itself from Power BI.

You can do this by navigating from the parent form to the sub form and copying the query URL from the subform:

This will include ALL of the sub records in this subform, even though those that belong to a different parent than the current selection:

ActivityInfo's Query API allows you to customize the columns you receive from the API. You can do this by using "query parameters" of the URL in the column_name=field.

For example, if you want to only query month and the number of kits from sub form above, you could compose the URL:

This will yield the following table in Power BI:

The first part of the parameter, before the equals sign, is the name that will appear in Power BI. The value after the equals sign can be any valid ActivityInfo formula, including a field ID, code, or a formula like "MEN+WOMEN". Note that query parameters must be URL-encoded, so a the formula would "MEN+WOMEN" would need to be written as:

The ActivityInfo Table View provides a convenient way to construct a custom query. Click the "Select Columns" button to open the column selection pane. You can then drag new columns onto the table, rename columns, and add a calculated fields to your table:

Once you are satisfied with your table, you can choose "Query selected fields as JSON" from the Export Menu:

That will give you a URL that includes precisely the fields you have selected:

https://www.activityinfo.org/resources/form/cn3wkxykmbwk6t59/query?Record+ID=cn3wkxykmbwk6t59._id&Province=c4q7r1xkmbwj7942.c40qj9zkmbwjrmy3.E00000012490000000001&TOTAL=MEN+%2B+WOMEN

ActivityInfo's server cannot accept URLs longer than 2,048 characters. If you include many fields, or very long column names, you may exceed this limit. If you do, you will receive the warning below. You can either use the default query instead, or reduce the number of columns or the length of their names.