build apps by user

Add Default, Display and Calculation Formulas

The fun part of BAU DB is the ability to manipulate data with formulas likes working in a spreadsheet. This page introduces the formulas that can be applied to the cells in Input Pane.
input pane properties - default, display and calculation formula

Default Initial Value of a Cell

It’s great to have initial value in a cell so that user has no need to enter, or know what to enter. Default initial value can be defined in  Default .
 Default
Add an initial value to a cell. It can be a fixed value, or determined by a keyword value or a formula. When initial value is defined in a single value cell, the value appears in the cell automatically. When it is defined in a multiple value cell, the value appears only when the cell is clicked or in focus.
There are 3 ways to define a default value :
Define with Fixed Value – Just state any fixed value.
Define with Formula – It is a Select query that calculate and retrieve a value.
Define with Keyword – A keyword generates a value as initial value.
These are the keywords that can be defined as Initial Value :
Keyword Description
Today Current date. Example : 2018/5/17.
Today1 The next date, or current date+1, Today2 is +2 and so on.
Now Current date and time. Example : 2018/5/17 11:49:57 AM.
FullTime Current time in full format. Example : 11:49:57 AM.
Time Current time in hour and minute. Example : 11:49.
LogonName The login user id, if the workbook is set with login account. If there is no user account, AnonymousUser is displayed.
Uppercase Turn all the characters into upper case. Example : abcDef => ABCDEF
AutoNumber AutoNumber is defined in the key cell only. It is used together with the formula defined in  Auto Doc No   to generate an unique number as the entry identifier of a new record entry when it is saved.
These are keywords for the convenience of entering data in multiple value cells:
Keyword Description
::uprow Copy the value of the cell in the upper row if the cells in the left column of current row and upper row have the same value.
::upcopy Copy the value of the cell in the upper row, in disregard of the values in the left column.
::upadd Copy and add 1 to the numeric value of the cell in the upper row, if both cells in the left column have the same value.
Example : 1 (upper cell) => 2 (current cell); ab01 (upper cell) => ab02 (current cell).
An initial value can be combined with the keyword and separated by “|” to define the starting value.
Example : ::upadd|3 means the numeric value in the first row is starting from 3.
::upcopyadd The same as ::upadd, just that there is no dependency on the values in the left column.
::double Copy the value of the the cell in the upper row, if the current row is even number. It’s convenient for the scenario likes double entry in accounting.
::doubleright Copy the value of the cell in the upper row and the right column, if the current row is even number.
::doubleleft Copy the value of the cell in the upper row and the left column, if the current row is even number.
::sumright Sum up the values of the cells in the upper rows of the right column.
::sumleft Sum up the values of the cells in the upper rows of the left column.

It is handy to display some useful data in Info Pane when a cell is in focus. It can acts as a source of reference related with the current cell, or as a selection list that assists the input of cell data.
 Display Formula
It defines the type of display, layout (row height and column widths) and the formula to create the display data. It is composed of 4 parts separated by semicolon “;”.
i.e. : Style; Display Title; Rowheight|Colwidth1|Colwidth2|…; Formula
Please note that  Display Formula is used together with  Input Box . If it is a selection list to assist input, TextCmb must be used in  Input Box .
Style
It defines how the data to be displayed.
Style Description
Multi It displays multiple items in rows of a table. Information related with the item can be displayed across the table in more than one columns. If it is a selection list, the values in the first column are the values to be selected for input.
GridSingle The items are displayed in each of the cell inside a grid. If it is for selection, only one value can be selected.
GridMulti Same as GridSingle, except that user can select more than one value from the grid.
GridKeyboard Display the items as a keyboard for input. The items can be numeric values or letters defined in the formula.
Display Title
A title description about the content of the displayed data. It can be defined by these methods:
Method Description
Fixed Text Any text to be displayed directly as the title caption.
Replacing Value Define a table and field name enclosed in single quote – ‘table.field’, so that the value of the cell associated with the table field is displayed. It can be used combined with fixed text.
Formula Define a Select formula to generate a value as the title.
Row Height and Column Widths
Define the height of the rows and the width of columns of the displayed data. Each value is separated by the divider character “|”.
Use Case Description
Multi In a multiple row table, you can define row height and column widths of more than one column. For example : 500|1200|3000 means row height is 500, the 1st column width is 1200 and the 2nd column width is 3000.
Grid (GridSingle, GridMulti, GridKeyboard) In a grid display, you can define row height and the number of display columns. For example : 500|4 means row height is 500 and there are 4 equal width columns for displaying the items. The keyword AutoHeight can be used to define row height with automatic value.
Formula
It is the Select formula that generates the data to be displayed.
Use Case Description
Multi In a multiple row table, the Select formula can retrieve multiple columns of data to be displayed.
Grid (GridSingle, GridMulti, GridKeyboard) In a grid display, the Select formula only retrieve one column of data. The data generated are then displayed across each of the cell in the grid.

Auto Calculation of Cell Values

It’s convenient that user can enter value in one cell and the values in the other cells are calculated and populated automatically. This feature can be easily implemented by adding formulas. Both  Function   and  Calculation Formula   are used together to define a calculation formula.
 Function
It defines the type of calculation and behavior of a formula.
Function Description
LookupR Calculate or retrieve a value using the formula. The calculated value replaces the current value in the cell.
Lookup The same as LookupR, except that the calculated value will be put in the cell only if the cell is blank (no value). If there is a value in the cell, it will not be changed by the formula.
BulkLookup It is used in multiple value cells. When the formula generates more than one row of data, all rows of data are displayed in the same column of the multiple value cell. It is a handy function to call out a number of items automatically. BulkLookup can be used together with LookupR to enhance the data content across the columns of multiple value cells.
BulkReport A fast version of BulkLookup when a large number of rows are needed to be retrieved, likes creating the content of a report. It cannot be used together with the other function like LookupR. All columns using BulkReport must be contiguous.
Sum Calculates and sums up all numeric values in the column of a multiple value cell.
Count Count the number of cells which have values in the column of a multiple value cell.
CountDistinct Count the number of unique values in the column of a multiple value cell.
LookUpC The formula generates more than one row of data, all rows of data are then concatenated into a single text string with separator character.
Concat Concatenates all the values, separated by comma “,”, in the column of a multiple value cell into a single text string.
CoumtStringItem Count the number of value items separated by comma in a cell.
TextAmt Transform a numeric value of a cell into text words.
 Calculation Formula
It defines the formula to retrieve or calculate the values. Different  Function has different way of defining the formula.
Use Case Description
LookupR The formula is a Select query that retrieve a single value. If the formula retrieves multiple columns or rows of data, only the data in the first row and column is displayed.
Lookup Same as LookupR.
BulkLookup The formula is a Select query that generates multiple rows of data. In the same grid of multiple value cells, a number of multiple value cells can use BulkLookup formulas and these cells have no need to be contiguous. Among these formulas, the Where conditional clause must be the same.
BulkReport Same as BulkLookup, only that all columns in the same grid using BulkReport must be contiguous.
Sum Just state the table.field of the multiple value cell that need to be summed up. For example, to sum up the quantity of items, the formula is item.qty, in which item is the table name and qty is the field name.
Count Same as Sum, state the table.field of the multiple value cell that need to be counted.
CountDistinct Same as Count.
LookUpC The formula is a Select query that generates multiple rows of data. The first column defines the retrieved value, and the second column defines the separator character. For example : Select ItemName, “, ” from InvoiceDetail where InvNo=’PO.PoNo’. In this formula, the first column ItemName is the retrieved value, “, “ is the separator that concatenates the values together.
Concat Same as Sum, state the table.field of the multiple value cell that need to be concatenated. The values concatenated are separated by “,”.
CoumtStringItem State the table.field of a cell, the values in that cell is separated by “,”.
TextAmt State the table.field of a cell that store the numeric value.