Data Validation (Backend)

APIs often require users to provide input parameters. To make APIs secure to use, check whether the input values are valid. There are multiple ways to achieve this in AlchemyJ. An easy and yet powerful way is to use the Data Dictionary Component.

In this example, we will set up a data dictionary and use ajCheckDataItem to validate and transform the input. The model contains a business logic worksheet, ValidateCustomerInfo, which contains cells that holds the input of a customer record. It contains name, date of birth, gender, customer level and telephone.

The objective of this example is to add validation rules in the model to validate the following:

Name – String, mandatory, capitalized case

Date of Birth – Date, optional

Gender – String, only accepts F or M, be converted to upper case automatically

Customer Level - Integer, mandatory, default is 3, must be between 1 and 4

Telephone - String, 15 characters max

Setting up a data dictionary

1. On the AlchemyJ toolbar, click Add Component and click Data Dictionary to add a new data dictionary component to the existing workbook. Rename the sheet name to DataDictCustomer.

A Data Dictionary worksheet contains two sections. The above section is for error message definition. The below section is a table that contains the data item table. To make this example simple, the error message definition would be left as default. You can change it if you would like to modify the error message returned by ajCheckDataItem.

2. The following are the properties that would be set in this example. For the details of the full list of properties, refer to Data Dictionary Worksheet.

   Configuration	Description
   Display Name	The field name appears in the error message.
   Data Type	The data type of the value to be validated.
   Required	Y indicates the field is mandatory. N indicates the field is optional.
   Default Value	The pre-set value if nothing is keyed in.
   Maximum	Maximum value of a number item.
   Minimum	Minimum value of a number item.
   Max Length	Maximum number of characters of a string item
   String Value List	A list of valid values, separated by -||-. For example: A-||-B-||-C
   Case Conversion	Letter case conversion options such as upper case and lower case.

3. To set up the data items, input the fields listed in Data Items section and define their properties as shown below. Please note that the columns not used in this example have been hidden in the screenshot.

Adding the ajCheckDataItem function

1. After the data dictionary has been set up, it is time to add the formula to validate the input against the data dictionary. In the ValidateCustomerInfo worksheet, add two columns next to the Input column. These two columns will be the output of the ajCheckDataItem function which has two parts. The first column contains the transformed value and the second column contains the error message if there is any.

2. The ajCheckDataItem function has 5 input parameters. For the details of the function, please refer to ajCheckDataItem.

   ajCheckDataItem(Header, Data, Data_dictionary, [Error_definition], [Transform])
   

   Parameter	Value
   Header	Put the field name range here which is B7 to B11.
   Data	Put the field value range here which is C7 to C11.
   Data_dictionary	Put the name of the data dictionary here. As the worksheet name of the data dictionary is DataDictCustomer, the name of the data dictionary is DataDictCustomer!AlchemyJ_DataDic.
   Error definition	Leave it blank as the default error messages will be used.
   Transform	Put TRUE here since we have defined Case Conversion for Name and Gender.

3. As ajCheckDataItem returns a range of values, it should be input as an array formula. Select cell D7:E11, input the following formula as shown in B4 and press CTRL+ SHIFT+ ENTER to make it become an Array Formula. Note that an IF function with the condition, AlchemyJ_FunctionPoint="Validate Customer Info", is used to make sure ajCheckDataItem will only run when function point Validate Customer Info is run.

   =IF(AlchemyJ_FunctionPoint="Validate Customer Info", 
       ajCheckDataItem(
             B7:B11,
             C7:C11, 
             DataDictCustomer!AlchemyJ_DataDic, 
             , 
             TRUE
        ), 
        ""
   )
   

4. Insert a Data Relationship Schema on B13:E14. Go to AlchemyJ toolbar, click Insert Snippet and select Data Relationship Schema. A schema is then inserted. To make it convenient to read, some optional columns are removed.

5. Add the function point Validate Customer Info to the Functions section in your Java API Class Worksheet.

   

6. Now everything has been set up. Input some test data and run Validate customer Info function point to test the function. Below is an example of invalid values. ajCheckDataItem returns the error message of each data item in the error message column. Notice that the Transformed Value are all #VALUE! since values will not be transformed unless they pass validation.

7. Below is an example of input values that can pass validation. Name has been converted to capitalize case and Gender has been converted to upper case.

Returning the result

Based on the configuration at earlier steps, the table at B6:E11 will be returned as an object which includes the field name, input value, transformed value and error message.