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.
In the below example, the currency table is used as the data source for input selection in the “Currency” cell：
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”
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.
The formula :
Select val('tblInvDtl.Price') * val('tblInvDtl.Qty') from tblDummy
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.
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('tblInvDtl.Qty')
