Using Advanced Expression Piping within your forms you can create mathematical formulas in order to calculate a value. This article will explain the process of using the COALESCE function to return the first input value in a list.
In this Article...
- What is the COALESCE Function?
- Proper Formatting for COALESCE Expressions
- Limitations of the COALESCE Function
The COALESCE function accepts a list of floating or integer values and displays the first value in the list of variables. This can either be used for a single row or column within a grid question, the table as a whole or a list of variables within the form.
The setup and formatting of the COALESCE expression is imperative in preventing an error message from appearing on your form where the result of your expression would normally appear. The first line of the COALESCE expression should be set up as follows:
| {{ COALESCE(variable1,variable2) }} |
- Each expression is always contained within a pair of double "curly" brackets {{ }}
- There should be a space between the first instance of COALESCE and the last bracket
- Variables you are using are case sensitive
- Always close the COALESCE operation with brackets ( )
- Can contain a (,) to separate values. Cannot contain any operators such (+),(-),(*), etc.
| COALESCE of Select Cells | {{ COALESCE(q1[0],q1[2],[q1[3]) }} |
Based on Grid Indexing, this will pull the first value from the 1st, 3rd, and 4th cells. Therefore if the 1st cell does not contain a response the value will be pulled from the 3rd cell. |
| COALESCE of a Column | {{ COALESCE(q1[:0]) }} |
Based on Grid Indexing, this will return the value in the first cell of column 1 to contain a response. |
| COALESCE of a Row | {{ COALESCE(q2[1:]) }} |
Based on Grid Indexing, this will return the value in the first cell of row 2 to contain a response. |
| COALESCE of a Range | {{ COALESCE(q1[0:4]) }} |
Based on Grid Indexing, this will pull the the value from the first cell in the list. |
| COALESCE of Reviewer Scores | {{ COALESCE(q1.score,q2.score,q3.score) }} |
Pull the value of the first reviewer question score in the form. If the first question is skipped, the second will be pulled. |
The COALESCE function would not be able to perform any operations using an expression such as addition (+) nor would it be possible to have other functions within a COALESCE expression as the expression does not allow for calculations to occur.
If you are planning on using fields that are created through expression piping, you will first need to perform the first function on one page and then pipe those fields into the next page using the COALESCE function.