Changes in this version:
- New “cross-table” calculation functions using fast binary searches or regular expressions and sequential searching:
sum_ex(), min_ex(), max_ex(), mean_ex(), quartile1_ex(), median_ex(), quartile3_ex(), mode_ex(), count_ex().
The above functions enables you to generate summaries, reports, statistical information etc. in one table based on values in other tables and the calculations effectively use all the processor cores defined in the “Settings” dialog box.
For example, with tens of thousands of records in the master table and several millions in the slave table(s) updating all calculated fields should be completed almost instantly or in seconds (with some short additional time for internal sorting before the first execution of the first function call).
A sample description from the help page:
sum_ex(table, v, searchField, calcField, options, [emptyValue])
Searches the ‘searchField’ field of the specified ‘table’ for the ‘v’ value and returns the sum of the ‘calcField’ field values for all found records.
If ‘v’ is a name (text string) specified without quotation marks, it’s assumed to be a name of the field from the table where the formula is used.
The ‘table’, ‘searchField’ and ‘calcField’ parameters must be placed in quotation marks.
If ‘table’ is located in a nested folder, its full path must be specified with the inner slashes.
The ‘option’ argument is a sum of the following values:
0 - the ‘table’ will be searched for ‘v’ using the fastest binary searches;
case-insensitive; this is the recommended option if the source record set
contains a large number of records with the ‘v’ values and the external ‘table’ has many
records to search as well.
1 - performs case-sensitive searching,
2 - performs string sorting/comparison (hyphen and apostrophe are
sorted, as opposed to the default word-sorting),
4 - the ‘v’ value is a regular expression; instead of the fast
binary exact searches, slower sequential searching will be used,
8 - allows empty matches when using regular expressions.
The ‘empty_value’ argument specifies what should be returned (a number or a text string) if no ‘v’ values are found. If it’s left empty, the function returns the #NULL! error code.
=sum_ex(“products”, ProductID, “ProductID”, “ProductName”, 0, 0)
=sum_ex(“folder1/products”, ProductID, “ProductID”, “UnitPrice”, 4, -1)
- New “cross-table” calculation functions for processing data from other tables using nultiple filters:
sumIfs_ex(), minIfs_ex(), maxIfs_ex(), meanIfs_ex(), quartile1Ifs_ex(), medianIfs_ex(), quartile3Ifs_ex(), modeIfs_ex(), countIfs_ex().
Although these functions use slower sequential searching, they provide more filtering options and pattern matching and can be helpful for medium-size data set, especially if there is some larger number of processor cores defined in the “Settings > Options”.
Their sample syntax is as follows:
sumIfs_ex(table, calcField, field1, criteria1 [, field2, criteria2, …], [emptyValue])
The detailed list is included in the help file:
https://citadel5.com/help/gsbase/formulas_cross_table.htm
- The “Tools > Show Selected Records” command to display the selected range of records as the current recordset.
- The “Tools > Show Random Records” command to display a given number of randomly selected recods from the current recordset.
- The “Sort > Use the current sort order as the default order” command to make the current sort order the permanent, default table order.