ajMergeRec function
Description
The ajMergeRec function is used for updating records in a database table if it exists. Otherwise, it will be created. Please take note that to run this function from Excel, you need to set up the DB Connection String from the Properties dialog box.
Syntax
ajMergeRec( table_name, column_headers, data, table_schema, [data_source_id], [run_condition], [run_by_function_point_only] )
| Argument Name | Description |
|---|---|
| table_name (required) | Specify the name of the table to be updated. |
| column_headers (required) | Specify the columns to be updated. It could 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 (required) | Specify the range that defines the DB Schema. You need to either provide the filter_type+filter_condition or the DbSchema to call this function. When DbSchema is provided, the key column(s) must be included in the Column_headers and Data. Otherwise, an error will be raised. |
| data_source_id (optional) | Specify the data source ID. The parameter is used to define which database shall be executed for the function. If you do not specify anything, the default value will always be 'primary'. |
| 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.
2) Method: Within a cell / cell array
Example
We will use the following table in our examples. The table name is tb_customer. It has 5 columns and 5 rows.
Example 1
NAME in this example is defined as a key in DBSchema. ajMergeRec looks up CUSTOMER_NAME, MICKY MAI, from tb_customer but it is not found. Therefore, a new record with the data is created.
Example 2
In the example, the record can be found, therefore it is updated with the data provided instead.
Check the result from DB.
