ajCreateRec function
Description
The ajCreateRec function is used for creating records in a database table. Please take note that to run this function from Excel, you would need to set up the DB Connection String from the Properties dialog box.
Syntax
ajCreateRec( table_name, column_headers, data, [table_schema], [return_type], [transpose], [include_header], [data_source_id], [convert_to_text], [run_condition],[run_by_function_point_only] )
| Argument Name | Description |
|---|---|
| table_name (required) | Specify the name of the table where records will be created. |
| column_headers (required) | Specify the columns to be updated. It can be a range of single row or column where each cell is a table column name or a data name defined in DB Schema. CLOB and BLOB are not supported. |
| data (required) | Specify the range where data is located. The orientation can either be landscape or portrait. The orientation should be the same as column_headers. |
| table_schema (optional) | Specify the range that defines the DB Schema. |
| return_type (optional) | Specify the return type of the function. 1 - The record that is just created. Only the columns that are specified as Out or InOut in the Db Schema would be included. This return type only works in the Oracle database. 2 - Number of affected records. |
| transpose (optional) | If it equals FALSE, the return records will not be transposed. If it equals TRUE, the return records will be transposed from row to column. If you do not specify anything, the default value will always be FALSE. |
| include_header (optional) | If it equals TRUE, the return result will include the column headers as the first row. If it equals FALSE, the return result will contain data only. This only applies when ‘return_type’ is 0.If you do not specify anything, the default value will always be FALSE. |
| data_source_id (optional) | Specify the data source ID. The parameter is used to define which database should be executed for the function. If you do not specify anything, the default value will always be 'primary'. |
| convert_to_text (optional) | Specify if you would like to convert all values into text. |
| run_condition (optional) | The function will run when the value is TRUE. Otherwise, it will not run. If you do not specify anything, the default value will always be TRUE. |
| run_by_function_point_only (optional) | If it equals FALSE, the function can be executed through ‘Excel Calculation’ (can be either Automatic or Manual, Calculate Now or Calculate Sheet) or Run Function Point. If it equals TRUE, the function can be executed with Run Function Point (AlchemyJ toolbar \ Run Function Point) only. If you do not specify anything, the default value will always be TRUE. |
The function will return:
1) Content type: Number of affected records or the record just created.
2) Method: Within a cell / cell array
Example
We will use the following table in our examples. The table name is tb_customer.
Example 1
This example creates one record in tb_customer. It returns the number of record affected as return type is omitted (default value is 2).
Example 2
This example creates a record in tb_customer with return_type is 1. When return_type is 1, table_schema need to be defined and it only works in the Oracle database. It will return a record set with columns defines in DB Schema. Only the columns specified as Out or InOut in DB Schema would be returned. In this example, the CUSTOMER_NAME column value was returned since it's InOut type is InOut in DB Schema.
