reinnovate database

Other Automation Features – Part 2

Petty Tables

There are times when you want to store some small lists of data, such as some frequently used currencies or some record status. These data lists are more or less “fixed” in nature and are usually used for the purpose of input selection of some cells in the worksheets. There is a convenient place – Petty Tables, to store these kinds of data.
Click out Petty Tables from the left panel of Main Menu. You will find that Petty Tables is just another worksheet with each entry inside the worksheet holding a list of data.
click petty tables from the main menu
inside petty tables
In the below example, the currency table is used as the data source for input selection in the “Currency” cell:
petty table used for the selection of currency
formula for adding the selection list of currency
This is the formula to display the data of the currency table in Petty Tables. “PT” is the table name of Petty Tables in the actual database, and “TableName” is the field name in “PT” that is used to distinguish different tables inside Petty Tables.
Multi;Currency Selection;500|2000;Select Text1 as [Currency] from PT where TableName="Currency" order by Text1
For each entry in Petty Tables, there are 2 text fields, 2 numeric fields, 2 date fields and 1 memo field. In the “Currency” table, you can also add exchange rate of each currency in the field “Number1”
there are text fields, numeric fields, date fields and memo field in Petty Tables.

Use of the Dummy Table – tblDummy

There is an internal table, tblDummy, that can be used in formula. Though with its name called dummy, it’s not dummy at all :). Sometimes your formulas may involves only input cells on a worksheet and there is no need to refer to any actual database table and field, then you can apply your formula with tblDummy.
invoice amount equal quantity multiply by unit price
The formula :
Select val('tblInvDtl.Price') * val('tblInvDtl.Qty') from tblDummy
the formula of invoice amount
The actual database table of tblDummy consists of one field – Dummy and one record. It is this single record that enables the return of calculation result in the formula. Although there is little chance that the field Dummy will be used in a formula, this single record must be present and cannot be removed.
the database table tblDummy
Using tblDummy in the formula also allows you to add conditions in the Where clause so that the formula will not return any value when the conditions in the Where clause return FALSE. Let’s say you want to return value to Amount only when the stock quantity – Stk Qty is greater or equal to the invoice quantity – Qty, it’s just simple to add the condition clause at the end of the formula:
Select val('tblInvDtl.Price') * val('tblInvDtl.Qty') from tblDummy where val('tblInvDtl.OrgQty') >= val('blInvDtl.Qty')
the formula with condition in tblDummy