AlchemyJ by Axisoft

4.0

ajCountRec function

Description

The ajCountRec function is used for counting the number of 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

ajCountRec( table_name, [filter_type], [filter_condition], [table_schema], [data_source_id], [run_condition], [run_by_function_point_only] )

Argument NameDescription
table_name
(required)		Specify the name of the table to read from.
filter_type
(optional)		Specify the type of filtering. 3 types are supported 0, 1 and 2.
filter_condition
(optional)		Specify the range that defines the filter condition.

When

filter_type = 0. The range defines the where clause of a SQL statement. The range will be concatenated into a single string. For example Name = 'peter' and class = 'B'.

filter_type = 1. The range defines the filter condition in the Kendo grid style. There are 6 columns in the range. The first row defines the header. They are Data Name, Operator 1, Search Criteria 1, And / Or, Operator 2, Search Criteria 2. The following operators are supported:
  • eq - equal to
  • neq - not equal to
  • lt - less than
  • lte - less than or equal to
  • gt - greater than
  • gte - greater than or equal to
  • isnull - is null
  • isnotnull - is not null
  • contains - contains
  • doesnotcontain - does not contain
  • startswith - starts with
  • doesnotstartwith - does not start with
  • endswith - ends with
  • doesnotendwith - does not end with

filter_type =2. The range defines a filter condition similar to the format used in MS Query. Two columns are used to define one filter condition. So the number of columns should always be a multiple of 2. E.g. 2, 4, 6. The first column defines the operator and the second consider defines the value. For example, < 10

* Notes
An error will occur if the range value is not in the format of the corresponding filter type.
For filter_type = 1 or 2, if the operator is available but operand is empty, it will be treated as '' (i.e. empty string).
If filter_type = 2, it will validate the correctness of Column Header and DataType against database schema.
table_schema
(optional)		Specify the range that defines the DB Schema.
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 extended 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 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

Count all records from the table.

Example 2

Count the records where Gender is F using filter_type 0.

Example 3

Count the records where Customer Level is 1 or 2 using filter_type 1.

Example 4

Count the records where Gender equals 1 and Customer Level is greater than 2 using filter_type 2. In addition, the table column names are mapped to the data name in DB Schema. Therefore, it uses the data name in the filter_condition.

image-20200119013140213

← ajCreateRecajReadRec →