Edit Worksheet, Manage Database Data
> Edit Worksheet Part 1 > Part 2
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.
Worksheet Caption and Location in Main Menu (or Menu Page)
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.
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.
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.
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.
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:
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.
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.
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.
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.
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 Cells
Click on any row to insert or delete a cell. The small buttons that appear on the right hand side can copy, insert or remove a row.
Click on any row to insert or delete a cell. The small buttons that appear on the right hand side can copy, insert or remove a row.
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.
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.
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.
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
“1” – Single occurrence cell with flexible width
“2” – Multiple occurrence cell with fixed width
“3” – Single occurrence cell with fixed width
“4” – Label cell
The various cell types displayed on a 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”:
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”:
