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 ribbon, click Add Component and click Data Dictionary to add a new data dictionary component to the workbook.

   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.
   Date Format	Date Format is required when the data type is Date.

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 three columns next to the input fields. These columns will be the output of the ajCheckDataItem function. ajCheckDataItem will return #Value! when it fails the validation. Please note that REST API will encounter error when there has error values in the output range. Thus, we add a output table and using IFERROR formula to replace error with empty string.

   

2. The ajCheckDataItem function has 5 input parameters.

   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 configuration table here. The name of the data dictionary is AlchemyJ_DataDic_tbl[#All]. Alternatively, use ajAddress and select the range of the data dictionary table.
   Error definition	Put the name of the error definition configuration table here. The name of the Error definition is DataDictionary!AlchemyJ_DataDicErrorDef. Alternatively, use ajAddress and select the range of the error configuration table in the data dictionary worksheet.
   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 set as an array formula. Select cell D7:F11, 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,
                 AlchemyJ_DataDic_tbl[#All],
                 DataDictionary!AlchemyJ_DataDicErrorDef,
                 TRUE
          ),
          ""
   )
   

   

Adding the function input and output

1. Click AlchemyJ ribbon Insert Snippet and select Data Relationship Schema (Single Table) . A schema table will be inserted. The input range is B7:C11 and the header position is left.

2. Click AlchemyJ ribbon Insert Snippet and select Data Relationship Schema (Single Table) to insert another schema table for output. In the sample, it is expected to return the error message and error flag (if any) so the return range is B14:C18.

   

3. Click Add Definition button in AlchemyJ ribbon, select REST API Endpoint Group to add ##RestEndpointGroup worksheet.

4. Add the function point Validate Customer Info to the Function Points and Input Parameters sections. The Response Data Address in Function Points should point to the output data relationship schema table while the Data Address in Input Parameters should point to the input data relationship schema table.

   

5. Add the worksheet name in Related Worksheets section.

   

6. Click Add Definition button in AlchemyJ ribbon, select REST API to add ##RestApi worksheet. Provide the API name, Package name and Versions in Basic Settings and add the REST API Endpoint Group worksheet name in the Endpoint Groups section.

   

Preview the function

Now everything has been set up. Input some test data and run Validate Customer Info function point to preview 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. The third return column will return false. This means that the validation has failed.

Below is an example of input values that can pass validation. Name has been converted to title case capitalization and Gender has been converted to upper case. The third return column will be TRUE means all input values are valid.