reinnovate database

Other Automation Features – Part 1

There are a lot more handy features that are useful and funny as well.

Auto Numbering of Key Cell

In a worksheet, each record entry has an unique number, or identifier, in the key cell. For example, Invoice No. is the entry number in the Invoice worksheet. The entry number must be unique and cannot be duplicated in a worksheet. In some applications, you may like the program to automatically generate an entry number included with an incremental sequence number. It can be accomplished by a single formula.
invoice no generated when saving record
Let’s have a look of the Invoice worksheet and see how to automate it’s key cell – Invoice No. Click out the cell setting screen of “Invoice No.”. In the input box of “Initial Value”, select “AutoNumber” in its pull-down list.
click out the cell setting screen of invoice no.
select autonumber in default initial value
Then go to Worksheet Setting and select the “INV” entry which is the setting of the invoice worksheet. In “Auto Doc No.”, add the formula for the entry number.
add formula to generate entry number automatically
The invoice number is generated by this single line of formula :
Select "ZT"+"@000@"+"-"+format('tblInv.InvDate',"yyyy") from tblDummy
In the formula, the single quoted field ‘tblInv.InvDate’ retrieves the value of invoice date on the worksheet. The formula takes the Year part of the invoice date and adds it to the end of the entry number. The middle part “@000@” is a 3 digits incremental sequence number. Let’s say the year is 2015, the entry number format will be ZTxxx-2015. The middle 3 digits sequential number is counted and incremented based on the number of record entries that shares this same format of entry number.

Default Initial Cell Value

Default Initial Cell Value is a convenient feature to add preset value to a cell. The value can be a fixed value or a value generated from a formula. Default values will be ready when you enter a new entry in the entry form.
default values in the order worksheet
Click out the cell setting screen of each cell and put the value in “Initial Value”. The default value of Order Date is always the current date and the keyword “Today” is used. The default values of the cells “:a” and “:d” are fixed values which represent the order status and the currency respectively.
default value of order date
default value of order status
default currency of the order
Default value can also be generated with a formula. The below example shows the default Delivery Date is 7 days after the Order Date:
default delivery date is 7 days after order date
formula of default delivery date is 7 days after order date
This formula adds 7 days to the date value of Order Date:
Select dateadd("d", datevalue('tblInv.InvDate'), 7) from tblDummy

Input Selection Grid

It’s a fun feature to have the input selection items to be displayed in a grid, so that you can select the item by clicking on the grid cell directly instead of rolling through a long list of items.
Single Selection Grid
This example select supplier number in the Purchase Order:
display selection input of supplier in a grid
In cell setting screen, select “TextCmb” and put in the formula:
set the formula in cell setting screen
This is the formula to display the selection grid. GridSingle is the keyword for showing grid with single selection. The middle word “Suppliers” is the grid description. AutoHeight|3 means the cell height is automatically adjusted with 3 cell columns. The formula retrieves the Supplier Nos for selection.
GridSingle;Suppliers;AutoHeight|3;Select CustNo from tblCust where CustType="VEND"
Multiple Selection Grid
There are cases when multiple selection is necessary in a cell. The below example selects multiple Order Nos and pulls in the customer requested articles to the Purchase Order.
select multiple order numbers in the purchase order
In cell setting screen, select “TextCmb” and put in the formula:
set the formula in cell setting screen to select multiple items
This is the formula to display the multiple selection grid. GridMulti is the keyword for showing grid with multiple selection. AutoHeight|3 means the cell height is automatically adjusted with 3 cell columns. The middle part “Workflow : Order => Purchase Order” is the grid description. The formula retrieves the Order Nos for selection.
GridMulti;Workflow : Order => Purchase Order;AutoHeight|3;Select InvNo as [Order No] from tblInv where DocType="ODR" order by InvNo desc