Data Validation and Record Merging

We cannot ensure that everyone will submit data according to the requirement we've set and sometimes we are not sure if the records were created or not before. Today we will look into how to solve this issue together.

First, we work on ensuring everyone will submit data according to some rules we set by using Data Dictionary.

Let's start by using the earlier model that we built on the CRUD process. You can find the tutorial here.

Once the file is opened, save the file under a new file name. I'll save mine as DB_OP with Data Validation.

(ensure "Excel Macro-Enabled Worksheet" is selected)

Now we have done that, let's go to the DataDictionary tab.

*if there's no DataDictionary tab, you can also add it from AlchemyJ ribbon, select Add Component -> Data Dictionary.

In the DataDictionary tab, let's filled in as the table below.

Then, go to cell P12 and filled in the String definition for Name and Gender as below.

Next, we continue to fill in the Number definition for Age at the cell I13 as below

*for more information on Data Dictionary, refer to here.

Once we completed the Data Dictionary, let's add the Data Schema.

Go to the AlchemyJ ribbon -> Add Component -> DB Schema.

A new tab called DB Schema will be added automatically and it'll prompt you to input the database name. Our database name is customers_info, so let's input that.

Your table should be automatically populated with information as below screenshot.

Now we need to fill in the cell from P5 onwards as below screenshot.

Merge Records on the database

Merging records will ensure that you will not have duplicate entries in your database. If it's already in the database, it'll update the entry instead of creating a new one.

So the same as the CRUD process, we will start with the ##RestEndpointGroup.

Let's configure the Function Points the same as the screen capture below but instead of a new line, we will change the Create Record entry.

*assuming you are using the community edition, you can only have 4 function points at one time. So we need to change the Create Record to Merge Record

Function Point = Merge Record

*from Create Record to Merge Record

Path = /mergeRec

*from updateRec to mergeRec

Operation = POST

Response Data Address =

*skip this field for now

DB Operation Type = Transactional

We can leave the rest as default for now.

Let's proceed to the BizLogic tab. I've prepared another table for the merge records process as below.

If you notice, the table for Merge Record is different from our other tables. We have new fields like the Output &Transformed Value, Error Message, and Input Error.

Let me break it down.

Output & Transformed Value - this is where we take the input field and verify it against the Data Dictionary

Error Message - will check for error and show here if there's error

Input Error - check if there's any error then return value

Let work on the formula next.

First, the formula for us to check the value against the Data Dictionary. We are going to use the formula below.

=IF(AlchemyJ_FunctionPoint="Merge Record",ajCheckDataItem(A51:A53,B51:B53,AlchemyJ_DataDic_tbl[#All],,TRUE),"")

*for more information on ajCheckDataItem, refer here.

This formula is going to be in the output table as an array formula. So select F52 to G54. Then type in the formula above, and press CTRL+SHIFT then Enter.

Next, we will use the below formula to check is there's any input error based on Error Message.

=IF(LEN(CONCAT(G52:G54))>0, TRUE, FALSE)

We will put the above formula in cell F56. This will check all the error messages and if there's an error message, the result will return true else false.

Lastly, on Records Affected (cell F59), we will type in the below formula. This is to merge the records in the database directly.

=IF(AlchemyJ_FunctionPoint="Merge Record",ajMergeRec("customers_info",E52:E54,F52:F54,AlchemyJ_DBSchema_tbl[#All],,NOT(F56),TRUE),0)

*if you notice, we refer to cell F56 to check if there's any error. If it's true then the function will not write into DB

*for more information on ajMergeRec, refer to here.

Once you have done with BizLogic, we will proceed to the next step to define the IO Schema. In IO_Schema, select cell A25 and insert the Data Relationship Schema.

For this function, we will name the table as Output Schema Merge Record.

Then we proceed to change the table name from "AlchemyJ_Snippet_Data_Relationship_Schema_tbl" to Output_Schema_MergeRecord.

Let's fill in the Output Schema Update Record table as below screen capture.

*cell C28 type in '=ajGetAddress(' then use your mouse to select the BizLogic tab and select the Record Affected for the Merge Record(from E59:E59) then press Enter.

Now we complete the IO_Schema configurations, go to the ##RestEndpointGroup

change the Response Data Address cell D14 to the below.

Response Data Address = =ajGetAddress(Output_Schema_MergeRecord[#All])

Next, go to the Input Parameters and fill in as below and remove all the create record input parameters from the table.

Now we are ready to test the function, the Merge Record, and see if it works.

Go to the BizLogic tab and fill in the table under the Merge Record table. I've filled in as below.

We purposely filled the field with all small letters so that we can see the value transformed into the format we required.

Now proceed to the AlchemyJ ribbon and select the Run Function Point. You should have a pop up then select Merge Record.

You should be able to see the transformed value as below with 1 record affected.

If you get the above result, then your Excel has successfully performed the merge records on the database.

Let's try to put some value that's wrong and see the error, I've filled in mine as below.

And you'll see the error handling is working as intended as below with no records affected.

Now let's generate the API. Go to the AlchemyJ ribbon and select Generate API.

Once you get the Spring Boot command prompt, your API is ready.

Type the below address to your browser.

http://localhost:8080//openapi/index.html

You should land on the OpenAPI (Swagger) Specification page where you can test your API.

Try to test and see if your API can merge records in the database. On this page, you should be able to select the Try it out as below.

Your database response body should be as below showing that 1 record is affected.

With this, we completed the Data Validation and Record Merging tutorial.