reinnovate database

Edit Worksheet – Part 1

BAU DB is a very convenient tool with which you can create an instant everything-ready database worksheet in a single click. What you need to do then is to add some cell captions and automate with some formulas on the worksheets and link up with some Excel spreadsheet document templates to create custom database solutions for your work.
Nevertheless, BAU DB is very flexible which also allows you to create a custom worksheet from scratch. Let’s go into Worksheet Setting and have a look at how a worksheet is organized.

The captions and displaying locations of worksheets in Main Menu are maintained in Worksheet Setting. Go to Worksheet Setting, and each entry displayed in the List View is the setting of a worksheet. Choose the worksheet to be edited. In Entry Form, you can update the Caption, Row and Column of the worksheet.
set the caption and menu location of worksheet

Key Cell (or Entry Number) of a Worksheet

Each worksheet is equipped with a Key Cell, or Entry Number, which acts as an unique identifier of a record entry. It works just like the Invoice No. in Invoice worksheet and no two Invoice entries can share the same Invoice No. in a worksheet.
In the cell Key Table.Field (or Doc No), you can define the key database table and field that is associated with the Key Cell. In the example, the Key Table.Field is tblInv.InvNo.
Index is used in the database file. The default name of Main Index is “idx” + Key Field’s name. In the above example, the Key Field name is InvNo and therefore the Main Index name is idxInvNo.
setting table field and index to the key cell
Link Up Key Table with Other Tables in a Worksheet
In the Order worksheet, two database tables are used – the Key Table ( or Main Table) tblInv of all the single cells for storing invoice and customer data, and tblInvDtl of the multiple cells for storing item articles data. Since the key database field is InvNo, the key field InvNo exists in both tblInv and tblInvDtl as well, which acts as the common field to link up the two tables.
the tables of single cells and multi cells.
When an entry “ZTC002” is selected on the worksheet, those records in tblInvDtl with the same value in the key field InvNo are loaded onto the entry form of the worksheet.
A view of the database table tblInv:
the records of multiple value cells of an entry.

Edit Entry Form (or Input Pane)

The Actual Database Table & Field
Each cell in Entry Form is associated with a table and a field in the base database table. Apart from the default field names of a.A, a.B, a.C, …, a1.A, a1.B, … etc used by the automatically created worksheet of Sheet1, Sheet2, … etc, BAU DB allows you to use table and field with meaningful names that are easier to remember.
In Worksheet Setting, go to Input Pane’s Cell Properties. To change table and field name, update the cells “Table” and “Field” respectively.
setup of the input pane
Database Field Type
You can also change the data type and field size of table fields, instead of using the default field type of “Text” in the default worksheet. It will be more convenient to use the proper data field type for each cell when it comes to formula calculation and displaying order of the records. To change field type, change the cells “Type” and “Size”. Only “Text” field need to be filled with the Size property.
Cell Location
To change the location of a cell in Entry Form, change the value of the cells “Row” and “Column” as indicated in the above picture.
Add/Remove Database Fields
Click on any row to insert or delete the database fields. The small buttons that appear on the right hand side can copy, insert or remove a row.
add or remove database field using these buttons
Apply Changes to the Actual Database
Whenever the values of Table, Field, Type, Size or Index are changed, remember to click “Update DB Tables” to apply changes to the actual database file.
set the physical database tables and fields

Tables of Single/Multiple Value Cells

Single/Multi Cell Types
There are single and multiple occurrence cells in a worksheet. Multiple occurrence cells are useful when a worksheet is needed to hold data with more than one line of value, such as the multiple product items in an Invoice worksheet.
In Worksheet Setting, enter “1”, “2”, “3” or “4” in the cell “Single/Multiple”:
“1” – Single occurrence cell with flexible width
“2” – Multiple occurrence cell with fixed width
“3” – Single occurrence cell with fixed width
“4” – Label cell
setup of single and multiple fields in worksheet
The various cell types displayed on a worksheet:
setup of single and multiple fields in worksheet
Table of Multiple Cells
For each set of the multiple cells, a new database table is used, which is different from the database table of single cells – the key table or main table. In the example, the table for single cells is “tblInv”, and the table for multiple cells which holds the data of product items is “tblInvDtl”:
set a new table for multi fields