Define a Business Logic Sheet
Before we start
Using a blank sheet, go to initialize model or start with the REST API Sample Model. Now, we are going to build two business logic sheets based on the table tb_customer. One is Get Customer which will get a record from the DB with name as the input. The other one is to create a record into the DB with the object as the input.
Since the DB connection is required, go to AlchemyJ ribbon, click Add Definition and select External Resources from the dropdown list.
Define the Data Source Configuration section in ##ExternalResources worksheet.
Get Record
- Create a new worksheet and name it GetCustomer.
- We assume the input value will be set in cell C12 and use it to concatenate the SQL statement to retrieve the record from the database table. It will be used in ajDBRunSQL .
SELECT CUSTOMER_NAME, DOB, GENDER, CUSTOMER_LVL, TELEPHONE FROM TB_CUSTOMER WHERE CUSTOMER_NAME ='MERRY SO';
- Select B4:F4 and input the following formula just as in B7. Press CTRL+ SHIFT+ ENTER after you have finished inputting the formula to make it into an Array Formula.
=IF(AlchemyJ_FunctionPoint = "Get Customer", ajDBRunSQL(B10), "")
In this sample, the result will be an object. Thereafter, we need to use the Data Relationship Schema to define the data relationship which will be used in the API definition. Go to the AlchemyJ ribbon, click Insert Snippet and then select Data Relationship Schema. A table will be inserted.
Key in the information in the column Table and Table Address as shown below. The table address is the Excel cell range for data return. GetCustomer!B3:F4. Cells B3:F3 is the data range of data header and they will be used as object definition automatically when generating the API. Cells B4:F4 is the range we inputted the formula in step 3.
Create Record
- Create a new worksheet and name it CreateCustomer.
- The input range is B3:F4. Cells B3:F3 are data headers and they will be used as object definition automatically when generating API. B4:F4 is the input range for data input.
- Use the input fields to concatenate the SQL statement to insert a record from the database table which will be used in ajDBRunSQL .
INSERT INTO TB_CUSTOMER (CUSTOMER_NAME, DOB, GENDER, CUSTOMER_LVL, TELEPHONE) VALUES ('Test User','1988-01-01','F','1','1111');
- Input formula in C12 to execute the SQL.
=IF(AlchemyJ_FunctionPoint = "Create Customer", ajDBRunSQL(B10), "")
In this sample, it needs to define the data type since the input is an object. Otherwise, all fields will be treated as a string. Go to the AlchemyJ ribbon, click Add Component and then choose Data Dictionary. A new Data Dictionary Worksheet will be inserted into the workbook. Define the Data Type and Date Format for the input fields.
Go to the AlchemyJ ribbon, click Insert Snippet and then select Data Relationship Schema. A table will be inserted.
Key in the information in the column Table and Table Address as shown below. The table address is the input data range in Excel as mentioned in Step 2, CreateCustomer!B3:F4. The used data dictionary address in Data Dictionary Address.
For REST API, the response can only be a JSON object. Then, insert another Data Relationship Schema in Cell B20, the table address where we input the formula in Step 4. Since the header is on the left, so set the position as left.
