How to: Set data validations

Excel has the ability to limit input values to a cell based on Data Validation.

Spark is able to read these Data Validations that are defined for Xinput in an Excel workbook (see Map a single/multiple cell range to an input/output).

Excel Data Validation present in the cells will be used to:

• Detect errors in the API request inputs in the API Tester and Execute API (v3) .

• Generate test cases in the Testing Center.

• Return validation parameters in the Validation API.

Add Data Validation in Excel

1. Select the cell(s) to which the Data Validation should apply.

2. In the Ribbon, select the Data tab and click Data Validation.

3. In the resulting screen, define the allowable inputs into the cell.

4. If the Validation criteria are linked to other cells, it is strongly suggested to use absolute cell references, e.g. $A$1.

   • Absolute references enable any dynamic dependent validation to be picked up by the Validation API and the Testing Center test case generation.

   • An example of dynamic dependent validation would be the choice of valid city and state combinations.

1. Spark can also return the customized messages that are entered in the Error message field under the Error alert tab. See a demonstration of this step here.

2. Click OK to set the Data Validation.

3. Data Validations applied to Xinput will be automatically picked up by Spark

4. If an API request is made where the parameters do not comply with the Data Validation, Spark will return an error in the API response response_data, which can be seen in the API Tester Error logs.

Note that Spark supports these validation criteria only:

• Whole number (min and max)

• Decimal (min and max)

• List

• Date (min and max)

• Text length (max)

Use complex formulas to define an error message

Syntax: Xvalidate_<parameter>

Spark allows users to define custom error messages based on the value of a mapped field.

1. Define the formula for Xvalidate.

   1. Typically Xvalidate is used with an =IF function, where the result of a formula is used to determine whether or not a message is returned.

   2. For example in this formula, =IF(A3 < 1000, "Insufficient value", "").

      1. Depending on the value of cell A3,

      2. If the value is less than 1000, return the error message Insufficient Value.

      3. Otherwise "" means not to return an error.

2. Follow the instructions to create a Name in Define a named range in Excel for the selected range.

3. When creating the Name, use the noted prefix defined in the syntax.

Example of Xvalidate

87KB

Xvalidate.xlsx

Open

Prerequisites for dynamic validation

The API Tester and Validation API can pick up validations where a validation depends on the formulas of other cells, e.g. city and state combinations. However there a number of requirements are needed for this to work:

• This applies to single cell Xinput.

• Data Validations that pertain to Dynamic Validation must be defined using absolute cell references, e.g. $A$1.

• For List data validation, Dynamic array functions such as FILTER() are not supported. It is recommended to use OFFSET() and MATCH() functions instead. An example is included in Sample file.

• Dynamic validations are only evaluated to 1 level of evaluation for performance considerations. In the Data Validation settings, when defining the formula for the dynamic validation rules (such as the List, Source), if the referenced ranges also contain formulas, then the dynamic validation will fail. For example, if the formula in Source included $B$1:$B$2 but the cell $B$1 also includes a formula instead of just text or a value, then the dynamic validation will fail.