The supplied XLS form could not be processed. Invalid ‘calculate’ [or ‘relevance’ or ‘constraint’] expression [expression] for the field: field_name
Have you received this error while using ODK? SurveyCTO is here to help!
What does this error mean?
For the field named field_name, you have a calculate, relevance, or constraint expression that has a syntax error that makes the expression incorrect. The expression – as you have typed it – will be listed in place of expression above and you can inspect it in the message and in your form definition to see where the syntax error might be.
How do I fix this error?
This is a broad error that can be caused by any kind of syntax error in your expression – from very small typos, to the use of functions that do not exist or are not supported by the platform you are using, to incorrectly specified terms (or “arguments”) within functions that do exist – so there is no one solution to fix this error.
You will want to carefully look at the expression and check every character – do you notice any typos? If not, are you sure the function you are using is a function that is supported by your tool? Are you using all functions in your expression correctly (e.g. if the function you are using requires three terms inside it, have you specified exactly three terms?).
Some common typos and syntax errors to look for:
- Expressions that use brackets that have a non-matching number of opening and closing brackets. There should be an equal number of opening and closing brackets.
- For expressions that use brackets, the wrong types of brackets have been used. For example, a square bracket, ‘]’, has been used instead of a round bracket, ‘)’, to close or open a function. Or a curly bracket has been used.
- When referencing fields in any part of the expression, the field has been referenced incorrectly. For example, instead of writing ‘${field_name}’, the user has missed one of the brackets, the dollar sign, or used the wrong brackets, like ‘{field_name}’ or ‘$(field_name}’.
- There are spaces between brackets and terms or field names that should not be there (e.g., ‘${ field_name }’).
- Any parts of expressions that require the use of single quotes are using the wrong type of single quote. For example, instead of ‘ the user has used ` – if(${field_name} = `abc’, ….). The quote at the start of ‘abc’ is not a single quote, but a different special character available on some keyboards. The difference is very subtle and requires a careful eye.
- Incorrect functions that are used for basic math. For example, using ‘/’ to divide two values or ‘^’ to do exponential calculations/square numbers. In ODK and ODK-based tools, the correct function to divide numbers is ‘div’, not the forward or backslash, and the correct function to perform exponential calculations is pow(), not ‘^’.
- When using the if() function or nested if() functions, users use the wrong number of terms inside the function.
Each if() function must take a total of three terms (or “arguments”): an expression that must evaluate to true or false, an expression that should be evaluated if the first term is true, and a second expression that should instead be evaluated if the first term is false. But users will often forget to include a third term in every if() expression. Consider the following expression:
if(index()=1,${field_name1},if(index()=2,${field_name2},if(index()=3,${field_name3},if(index()=4,${field_name4}))))
The final nested if() – if(index()=4, ${field_name4}) – only has two terms instead of three.
Pro tip:
Struggling to count all of those brackets in a long expression? Get software to do the counting for you! Copy your expression into a text editor application that reports the count of search terms. Search for “(“ and your text editor would tell you that there are X number of matches. There are many text editors available that can do this.
We hope you found this helpful. Feel free to browse through our other ODK error guides. To try SurveyCTO for yourself, and see all the improvements we made to ODK, you can sign up for a free 15-day trial. We offer 24×7 support from research experts, customizable form designs, smart data quality controls, and best-in-class data security.
Sign up for a free SurveyCTO trial
