reinnovate database

Edit Worksheet – Part 2

Create Multiple List Views in Browse Pane

In Worksheet Setting, go to Browse Pane’s Cell Properties for editing of List Views. Each row here represents a column in the worksheet’s List View. You can create multiple list views in Browse Pane using different view names.
setting the browse tables
multiple list views of order worksheet
Note that the first column, Column 0, of a View must be associated with Key Table.Field and it’s cell width must be 0.
the first column of a view must be unique key field
View filter is used to control the set of records to be displayed in List View and its displaying order. In the example, the filter condition is to divide records of different “DocType” into different worksheets and the below formula is used:
DocType="ODR" order by tblInv.InvNo desc
use view filter to control the record set to be displayed in list view

Add Filters to List View

It’s also simple to add filter boxes for selecting dynamically the scope of records to be displayed in List View. Go to Filter Pane’s Box Properties for the settings of filter boxes.
Search Box must be present in Filter Pane, it cannot be removed.
add filter boxes to list view for filtering data
It’s often useful to define a pair of filter boxes on a date field to select a range of date for the filtering of records:
use a pair of filter box for date filter
Add the below line to add a filter box on a table field:
filter box on any other field
filter boxes of Order worksheet

Read-Only Worksheet

Sometimes you may want to have a worksheet in which the data are not changeable. In the example application, Invoice worksheet creates stock items and add quantities into Inventory, while Purchase Order worksheet removes quantities from the Inventory. Since all updating of stock records are done by Invoice worksheet and Purchase Order worksheet, the records in Stock worksheet do not need to be updatable.
stock wroksheet is a read only worksheet
To turn the worksheet to Read-only, go to Worksheet Setting and select the entry “STOCK” – the setting of Stock worksheet. In the cell “Reporting Form”, select the value “True” and save the entry.
set the stock worksheet to be read only

Different Worksheets Same Tables

Actually, database is separated from worksheets in BAU DB. It depends on what database tables and fields are defined in List View (or Browse Pane) and Entry Form (or Input Pane) to let the worksheet display the data. Therefore, BAU DB is very flexible and allows different worksheets to work on the same database tables for some specific purposes.
This is one of the example showing different worksheets using the same tables. In the sample application, although Order, Invoice, Purchase Order, … etc are different documents, they are using the same tables – tblInv and tblInvDtl. It is because these documents all shares nearly the same data contents, and therefore using the same data tables will be more convenient.
different worksheets can use the same set of tables
To separate the records of different worksheets, the field “DocType” is created in the key table tblInv. Different default values of the cell “DocType” are set for different worksheets. For example, the “DocType” value of Order worksheet is “ODR” and that of Invoice worksheet is “INV”. When you create an entry, the default value of DocType is saved on the entry.
Properties of the field DocType with default value:
different worksheet has different default value of DocType
In the setting of List View, a filter condition on “DocType” is used to make sure records displayed in each of the worksheets are with certain value of “DocType” only:
use view filter to display certain records only for a worksheet
A view of the table tblInv showing records with different values of “DocType”:
same table holding records of different worksheets
In the sample application, the purpose of Activity worksheet is to display all document types together in one worksheet, and it’s just simple to create the Activity worksheet by copying any one of the worksheets and removing the filter condition on “DocType” to show up all types of document records.
activity worksheet with records of all types of documents