The following are functions that you can use to change the value of a column. They can be added to the Map Via column in the Excel query file.
Function name | Description | Example usage | Since version |
count-selected | Count the number of options selected for a multi-select value. | Value: a b c d Map Via: count-selected Output: 4 | 0.12.4 |
selected-at | Get the option at the given position for a multi-select value. Note: the position index is zero-indexed. This means that to choose the first item, you need to enter 0, the second item, 1, etc. | Value: red green blue Map Via: selected-at(2) Output: blue | 0.12.4 |
selected | Checks to see if a value was selected from a multi-select value. Outputs 'True' or 'False'. | Value: rash fever Map Via: selected(fever) Output: True | 0.12.4 |
attachment_url | Converts a value into a URL that can be used to download attachments | Value: photo.jpg Map Via: attachment_url Output: https://www.commcarehq.org/a/my-domain/api/form/attachment/the-form-id/photo.jpg | 0.13.2 |
substr | Returns the substring indexed by [first arg, second arg), where indexes start from zero. Suitable for truncating long strings to fit a column's maximum length. | Value: abcdef Map Via: substr(2, 5) Output: cde | 1.3.2 |
form_url | Returns the URL to the form page on CommCare HQ (only relevant for form exports) | Value: (anything) Map Via: form_url Output: https://www.commcarehq.org/a/my-domain/reports/form_data/<FORM ID>/ | 1.4.0 |
case_url | Returns the URL to the case page on CommCare HQ (only relevant for case exports) | Value: (anything) Map Via: case_url Output: https://www.commcarehq.org/a/my-domain/reports/case_data/<CASE ID>/ | 1.4.0 |
format_uuid | Given a UUID this function returns a formatted HEX UUID. If the input value is not a UUID it will return a blank value. | Value: 00a3e0194ce1458794c50971dee2de22 Map Via: format_uuid Output: 00a3e019-4ce1-4587-94c5-0971dee2de22 | 1.4.0 |
Next Page: Exporting User and Location Data >