ajCheckDataItem function

Description

The ajCheckDataItem function verifies the data items against their properties defined in a data dictionary and custom validation/transformation.

Below are some of the data item properties. Please refer to the Data Dictionary for the full list.

• The field name.
• The data types. For example, integer, double, string or date.
• The size. For example, minimum and maximum value or length for a number or string respectively.
• The default values that exist for all fields in the organization.
• Required field or not.

ajCheckDataItem does not return error if the data value does not match the defined type but it can be automatically converted to the defined type. For example, "true" (string) for a Boolean and "-1" (string) for an integer.

Syntax

ajCheckDataItem(header,data, data_dictionary, [error_definition], [transform],[ignore_empty_record],[custom_validation],[custom_transformation])

Argument Name	Argument Type	Description
header (required)	Range / Array	The data item names (field names) to be checked. The range of cells can be selected vertically or horizontally. Data item names are case-sensitive. The orientation of the Header and Data parameter must be the same. For example, if the headers are selected as a row, the data must also be listed as rows. Vertical orientation only supports one set of data items. To check multiple rows of data items, you must select the Header and Data horizontally. The orientation of the function result would be the same as the orientation of Header and Data
data (required)	Range / Array	The data item values to be checked. The range of cells can be selected vertically or horizontally and the order must be the same as the Header.
data_dictionary (required)	Range / Array	The range of cells or name that specifies the data item list to be used. Please include the header in the range of cells in the first row. When a data dictionary component is added to a workbook, it automatically creates a name followed by the phrase 'AlchemyJ_DataDic' that specifies the data item list area. Therefore, you can simply use [DataDictionarySheetName]!AlchemyJ_DataDic here. For example, if the data dictionary worksheet name is CustomerDataDic, the name here would be CustomerDataDic!AlchemyJ_DataDic.
error_definition (optional)	Range / Array	The range of cells that specifies the error message override definition. Please include the header in the range of cells in the first row. If you do not specify anything, the default error message will be used. You can use the table name DataDictionarySheetName!AlchemyJ_DataDicErrorDef to refer to that area.
transform (optional)	Boolean	To indicate whether transformation should be performed. TRUE means to transform while FALSE means do not. Transformation properties are properties that change a data item value such as case conversion and rounding number of digits.
ignore_empty_record (optional)	Boolean	To indicate whether need to check the empty record. TRUE means to check while FALSE means do not. The default value is TRUE.
custom_validation (optional)	Variant	The range of cells that specifies the custom validation result. If corresponding cell is not empty, take the custom validation result. Otherwise, continue the validation specified in data dictionaryThe range of cells could be selected vertically or horizontally and the order must be same as the Header.
custom_transformation (optional)	Variant	The range of cells that specifies the custom transformation result.if corresponding cell is not empty, take the custom transformation value and continue transformation specified in data dictionary. The range of cells could be selected vertically or horizontally and the order must be same as the Header.

The function will return:

1) Return Value: Transformed value of the input data point, error message (if any), overall validation result.

2) Return Type: Multiple values (array formula)

Example

Example 1

In the following example, we will define two fields. Test field A is an integer which cannot be smaller than 0. Test field B is a string which has a maximum length of 3 characters.

With that set up, we use the ajCheckDataItem function to check whether the data item values match their defined properties.

Append three columns after the input fields for the return array of ajCheckDataItem. Select cell C26:E27 , input the formula as shown in A29 and press CTRL+ SHIFT+ ENTER to make it become an Array Formula.

Test field A with the value of -100 does not pass validation. Therefore, an error message is returned. Test field B is 3 characters long which matches its defined property, so there is no error. Notice that the first returned column for Test field A is “#VALUE!” because the value does not pass validation. The third return column is False means the validation failed. E28 shows False because at least one of the data items do not pass validation.

Example 2

By using the example 1, we add a custom validation for Test Field B. Actually we can define any logic in the Cell B37. When it has any value, it means the validation failed and the output will be displayed in the error message.

Click here to download the use case workbooks for further reference.

Error Scenarios

It will return #VALUE! when missing any required parameter or mismatch parameter type. Besides, system will raise error for below scenario(s).

Error Scenario
The header is not a single row or a single column.
The number of headers and the number of data columns does not match.
The data dictionary is empty or does not exist.
The error definition is empty or does not exist.
Data type is missing in data dictionary