Perform CRUD Operation on Excel - Part 2 of 2

Updated: Jun 22

We will work on updating and deleting records in the database today with AlchemyJ.

If you notice the pattern on both reading records and creating records, the process is almost the same. The splits into 6 steps as below

  1. Configure the ##RestAPI

  2. Configure ##RestEndpointGroup

  3. Configure the BizLogic

  4. Define the IO_Schema

  5. Complete the #RestEndpointGroup configuration

  6. Generate API and test


The above process is a simple way of how you can create an API using AlchemyJ. Let's proceed with the tutorial.



Updating Records in databases


So the same as the create records, we will start with the ##RestEndpointGroup.

Let's configure the Function Points the same as the screen capture below.


Function Point = Update Record

*this will be shown when we select the Run Function Point

Path = /updateRec

*the Path is the second part of the API address

Operation = PUT

*select PUT from the drop-down menu

Response Data Address =

*skip this field for now

DB Operation Type = Transactional

*select from the drop-down menu

We can leave the rest as default for now.

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


Now, type the formula below in cell E28 for the update records process.

=IF(AlchemyJ_FunctionPoint="Update Record", ajUpdateRec("customers_info", B27:C27, B28:C28, 0, A31),"")

*B27:C27 is referring to the header of the table and B28:C28 is the input

*for more information on ajUpdateRec, refer here.

Next, we will type the formula below in cell A31 so that the output can be used by the update record process.

=A27& " = '" &A28 &"'"

Once you have done with BizLogic, we will proceed to the next step to define the IO Schema. In IO_Schema, select cell A13 then go to AlchemyJ ribbon and select Insert Snippet then select the Data Relationship Schema. A table will automatically be created for you as below.

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

For best practice and easy maintenance, go to the Formula ribbon and select the Name Manager. Scroll down and look for "AlchemyJ_Snippet_Data_Relationship_Schema_tbl".


Change the table name to Output_Schema_UpdateRecord.


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


*cell C16 type in '=ajGetAddress(' then use your mouse to select the BizLogic tab and select the Output table for the update (from E27:E28) then press Enter.

Now we complete the IO_Schema configurations, go to the ##RestEndpointGroup change the Response Data Address cell D15 to the below.


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


Next, go to the Input Parameters and fill in as below.


Now we are ready to test our third function, the update record, and see if it works.

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


Go to the AlchemyJ ribbon and select the Run Function Point. You should have a pop up then select Create Record.

You should be able to see under the Number of records update table is 1.


If you get the above result, then your Excel is now updating records on the database.


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

*if there's an error, make sure the AlchemyJ and DB_OP API (Spring Boot command prompt) is closed

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 create a new record into 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 created.


Now that your API is working as well, let's move on to the last process which allows us to delete records in the database.



Deleting Records in databases


Let's start with the ##RestEndpointGroup.

Let's configure the Function Points the same as the screen capture below.

Function Point = Delete Record

*this will be shown when we select the Run Function Point

Path = /deleteRec

*the Path is the second part of the API address

Operation = DELETE

*select PUT from the drop-down menu

Response Data Address =

*skip this field for now

DB Operation Type = Transactional

*select from the drop-down menu


Now, proceed to the BizLogic tab. I've prepared a table for the delete records process as below.

We will type the formula below in cell A43 so that the output can be used by the delete record process.


="Name = " & "'" & A40 &"'"

Then type the formula below in cell E40 for the delete records process.

=IF(AlchemyJ_FunctionPoint="Delete Record", ajDeleteRec("customers_info",,,0,A43),"")

*A43 is referring to the output of the Selection Criteria

*for more information on ajDeleteRec, refer here.

Once you have completed the above process, we will proceed to the next step to define the IO Schema. In IO_Schema, select cell A19and select Insert Snippet then select the Data Relationship Schema. A table will automatically be created for you as below.

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


Let's change the table name in the Name Manager. Change the table name to Output_Schema_DeleteRecord.

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

*cell C22 type in '=ajGetAddress(' then use your mouse to select the BizLogic tab and select the Output table for the update (from E39:E40) then press Enter.

Now we complete the IO_Schema configurations, go to the ##RestEndpointGroup change the Response Data Address cell D16 to the below.

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

Next, go to the Input Parameters and fill in as below.


Now we are ready to test our last function, the delete record, and see if it works.

Go to the BizLogic tab and fill in the name under the Name Input field.


Go to the AlchemyJ ribbon and select the Run Function Point. You should have a pop up then select Create Record.

You should be able to see under the Number of records deleted table is 1.


If you get the above result, then your Excel is now deleting records from the database.

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

*if there's an error, make sure the AlchemyJ and DB_OP API (Spring Boot command prompt) is closed

Let wait for your API to be ready then 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 create a new record into 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 created.


With this, we complete all our CRUD process in Excel.


With this, we complete our CRUD process in Excel.

19 views

About    Contact    News    Partners    Privacy    Terms   

  • LinkedIn
  • YouTube
  • Facebook
  • Twitter

Copyright © 2019 Axisoft. All Rights Reserved.