#### Worksheet Properties (Editing Worksheet)

= Add Default, Display and Calculation Formulas (Input Pane)

## 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.

### 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.

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. |

### Related Info or Selection List in Info Pane

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. :

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.

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.

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. |