Data Manipulation
> Other Automation Features Part 1 > Part 2
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.
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.
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.
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.
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 can also be generated with a formula. The below example shows the default Delivery Date is 7 days after the 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:
This example select supplier number in the Purchase Order:
In cell setting screen, select “TextCmb” and put in the formula:
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.
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.
In cell setting screen, select “TextCmb” and put in the formula:
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