The ajCheckDataItem function verifies the data items against their properties defined in a data dictionary..
Below are some of the data item properties. Please refer to the Data Dictionary for the full list.
- The field name.
- The database and table that each field exists in.
- The data types. For example: integer, number, 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(header,data, data_dictionary, [error_definition], [transform])
|Specify the data item names (field name) 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
|Specify 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. |
|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. |
|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 DataDictionarySheetName!AlchemyJ_DataDicErrorDef to refer to that area.
|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. |
The function will return:
1) Content type: Transformed value of the input data point and error message (if any)
2) Method: Cell array
In the following examples, 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 max length of 3 characters.
With that set up, we use the ajCheckDataItem function to check whether the data item values match their defined properties.
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.