release updates Archives

Release Updates – Remote Exchange Data, Working with Picture and More

November 16, 2017 eric 9 Comments release updates

Hello all,

Originally, it’s planned to launch the Pro version in this release. Since some features are requested by users, this release is still about feature updates. Since these features are very useful, I am sure you will like it.

Interchange Data with Business Partners

You are working close with your business partners. A fast and easy way for exchanging data is very important. In this release, there are 3 methods of data importing introduced that help you collaborate conveniently with your partners. These methods are all using spreadsheet for data exchange.

Exchange Data in Entry Format

Exchange data in entry format enables you to do business electronically with your business partners. It saves you the manual works to re-enter data into your system after receiving a document from another company.

Usually data is handled in tabular format which is easy and simple. But it is less readable and it is usually used for bulk data exchange.

Therefore, entry format is introduced which is both human readable and machine readable, and yet it’s also simple. With entry format, you can send and receive data on entry basis and work closely with your partners.

This is a sample of spreadsheet file in entry format. The fun part of it is that you can change the visual layout and formatting as you like, that enhances human readability without affecting machine readability.

An entry format in a spreadsheet with furnished layout.

In entry format, a cell caption followed by a colon “:” means the data is in the cell to the right of the caption, and a cell caption followed by two dots “..” means the data is in the cell below the caption.

In the worksheet, for single value cells, you can choose to use “:’ or “..” to place the data. For multiple value cells, you can use “..” to place the data below the multiple value captions, and leave a blank cell in the first column after the last row of values to indicate the end of multiple values. These are the only rules for the machine readable part. You can make up the visual layout with different colors, font sizes in the spreadsheet at your own choice. With human readable format, the advantage is that you know what you are importing.

To import the spreadsheet of data in entry format, the first step is to add the cell captions of spreadsheet into the corresponding cells of BAU DB worksheet. Since importing of more than one entry format is allowed, you can enter a few names in one cell separated by semi-colons.

Map Excel spreadsheet column name in cell setting screen.

Then, click the downward button “v” and select “Import Entry Format”.

Select the files :

In this example, the purchase order of your customer becomes your sales order immediately. Another fun part of this feature is that the imported data will trigger the calculation formulas of the cells at the same time, so that you can manipulate the imported data on the fly.

Formulas auto-triggered after import of entry data.

The import data only sit on the worksheet. You can further manipulate the data on the worksheet. When you have finished manipulating the data, you can press “Save” button to save the entry.

There is also the export feature so that you can export an entry easily. Just click the upward arrow in Input Pane. The exported file is already in entry format with both single cells and multiple cells using the double dots “..”.

An entry is exported to Excel spreadsheet:

An exported spreadsheet file of an entry

You can also create an Excel template file and click the “Print” button to export an entry.

With this export and import feature in entry format, you can exchange data with your business partners on daily basis and collaborate more closely, and saved much of your manual works.

Import Simple Tabular Data (Excel or CSV File)

CSV (Comma Separated Values) file is a common format of passing data. It’s basically a simple table with rows of data in a number of columns.

This is a typical table data, with the first row being the column names.

A typical CSV - column separated value spreadsheet file.

Same as importing entry format, enter the column names in worksheet cell captions for cell mapping first.

Then click the downward arrow “v” in the top right corner and select “Import Table Data”. The data from the spreadsheet file is imported into the worksheet by mapping with the column names.

Select Import Table Data for CSV Spreadsheet

Select the importing file:

Again, formulas on the worksheet will be triggered. Please note that single value data and multiple value data can be imported together.

When you have finished manipulating the data, you can press “Save” button to save the entry.

Import Data Directly into Database

This feature move data directly from Excel into the database file without data sitting on the user interface of the worksheet. With this feature, you can easily move the data from your old application into BAU DB. Before that, we have to use Ms Access or MDB Viewer to move the data. Now, you can use this convenient feature to get the job done.

Click the downward arrow, choose “Import Data into Database” and select the importing file.

Select the importing file:

Select Import Database Format Spreadsheet

The importing file:

You are now in the cell mapping mode. Same as the other importing methods, you can also enter the column names in the cell captions for cell mapping. You can also select the spreadsheet columns on the screen.

Please note that in one import file, you can only import data into one table in the database. It is therefore data for the single cells and multiple value cells have to be done separately since they are using different tables.

When cell mapping is completed, press “Import Data” and the data is imported into the database directly and refreshed to display in the list view.

This example imports the data for the single value cells only, you can then select and import another file for the multiple value cells.

With this handy feature, you can move your existing data into new applications created in BAU DB easily.

Working with Pictures

Some features are introduced that help you work more conveniently with pictures from capturing to printing.

Capture Image with Camera

You can now capture a picture directly by calling a camera from the worksheet. To use this function, you have to plug in an USB Camera into your computer. To use the camera, set the Input Box to “Camera” in cell setting. You can use formula to create the name of the picture file automatically.

Click the camera button to display the camera window:

Insert Image in Excel Spreadsheet

In an Excel template file, use the dot dot command “..InsertPicture” followed by an image file name. The picture will be put inside the cell when the entry is printed out.

Auto insert picture into Excel spreadsheet

Print the image files:

The images are put on the Excel spreadsheet:

Printing in Label Format

A new printing format is introduced that allow you to print the data in label format. Label format divided a paper into a number of rows and columns, and the printing will print the data column by column and then row by row.

To define the format, add ..FormatLabel in an Excel template file and followed by Column x Row.

Printing in label format in Excel spreadsheet

You can print data or images with label format.

The image is put on the Excel spreadsheet:

Printed image file in label format in Excel spreadsheet

New Formula for Bulk Updating of Data

There are times when you need to amend you data in a batch. For example, when you have imported data from an old database and you want to update the data to match with the data format in the new database.

Another scenario is that a new processing is introduced in the worksheet, and you want to update the existing data to match with the new process.

As usual, you can update data in “Amend Data” in Main Menu using SQL formulas. This formula is introduced which is more convenient for updating bulk data.

This is an example of the formula:

Select SerialNo, InvNo, StkNo from tblInvDtl where InvNo in (Select InvNo from tblInv where DocType="INV")
Update tblInv Set StkNo=(Select tblInv.InvNo from tblInv, tblInvDtl where tblInv.InvNo=tblInvDtl.InvNo and tblInv.DocType="STK" and tblInvDtl.SerialNo="value") where InvNo="value1"

This formula is composed of two parts. The first part is the Select statement:

Select SerialNo, InvNo, StkNo from tblInvDtl where InvNo in (Select InvNo from tblInv where DocType="INV")

And the second part is the Update statement:

Update tblInv Set StkNo=(Select tblInv.InvNo from tblInv, tblInvDtl where tblInv.InvNo=tblInvDtl.InvNo and tblInv.DocType="STK" and tblInvDtl.SerialNo="value") where InvNo="value1"

The Select statement creates a set of bulk records that need to be processed, and each record is then updated by the Update statement. The Select statement provides the updating fields and criteria values for the Update statement to update data.

In the Update statement, the value to be updated is calculated from another Select query, and “value” is the criteria value which refers to the first value in the first Select statement – in this case SerialNo. A maximum of 9 criteria values can be used.

Improvements & Fixes

There are a number of improvement and fixes in this release.

· Display of formula error for Processing After Save (Countering).

· Continue processing of formulas after formula error in Calculation Formula.

· Improved display of field selection in worksheet setting.

· Use better name View01, View02, … for list view name in default workbook and worksheet.

· No auto change of table field in list view setting which is less prone to error.

· In default worksheet, the single value cells below multiple value cells are now using fixed width type.

· Improved display of picture in worksheet.

· Improved printing of entry (Input Pane) into spreadsheet.

How do I Update

As usual, download from here and install it on top of your current installation.

This is the coming schedule:
1) Finish the documentation about Worksheet Settings and SQL formulas;
2) Create discussion forum, which is also long awaited;
3) Launch of the Pro Version!

See you again!

Release Updates – Improved Screen Design, Worksheet Setting, List View Printing and More

August 4, 2017 eric 16 Comments release updates

Hello all,

How are you? I wish you have not been waiting too long for this release. I would like to give you a head start news, as I have replied Pierre in the comment, that this is the last update release before the launching of the Pro version. So, let’s enjoy the updated features of this release first.

The features of this update are all suggested from you and some come out of the projects working with you.

Improved Worksheet Setting

Thanks for Alex’s comment. You may have experienced that if the worksheet setting do not go properly, especially the row and column numbers, you cannot get the worksheet displayed properly, or it will just standing still in the “Loading worksheet …” screen. Now it is improved with the below features:

Checking is added when you save the worksheet. Non-continuous numbers in column or row is not allowed. Blank table name or field name is not allowed.
That said, there are exceptions in Input Pane, where you can add Label cell for adding description in the worksheet, or you can create a Blank cell for displaying related information, and these 2 kinds of cells have no need to adhere to a database table field.

If the column number in Browse Pane is not continuous, warning comes out when saving.

When copy or delete a row (using the small button on the right), the row and column number will adjust itself automatically. In this way, the numbering will always getting right.

Row and column number will be adjusted automatically when the row is copied or deleted

It will be much easier now to get the worksheet setting right. Let me know if you still discover some cases that crash the worksheet setting.

Auto numbering with ::upadd as default value

You can also use numbering in the rows of multiple value cells. You can apply “::upadd” in the default value, so that when you enter the cell, the number in the upper row increased by 1 will be input automatically. Same as in worksheet setting explained above, the numbers are auto adjusted when you copy or delete the rows.

Auto row number can be used in other worksheet as well.

Value of Upper/Lower Row: ^’table.field’ and v’table.field’

In applying formula, you can now add “^” or “v” in front of a single-quoted field to refer to the value in the upper or lower row in multiple value cells of Input Pane (Record Form). It is a very useful feature when the formula is handling some relationship between rows of input data. For example, it is used in the checking of continuous column number in worksheet setting as described above. It is also useful in display formula, calculation formula and default value formula in other sue cases as well.

Upper/lower row value is very useful when applying formula.

Improved Screen Design – List View / Record Form Only

Some of you reported the screen design is clustered and what you want is a simple screen to work with. Okay, simple design is always the best design and your voices are heard. Now, when you create a new worksheet, by default it is shown with the Record Form only. You can click the top left button to go to the List View and back again.

Besides that, background color of record form is changed and more space are added between rows of cells. These changes will let you feel more comfort when working with the worksheet.

With a button click, or double click a row in List View, it changes to Record Form only.

It looks much better with the simple design of list view only in the screen.

You can always drag the screen by using mouse so that you can work with the List View and Record Form together. Also, you can always change the value of Pane Size
in worksheet setting, to decide the default dividing of the screen. V:0 displays Record Form only while V:1 display List View only, and a value between 0 and 1 will make List View and Record Form display together.

You can always set the vertical division of the screen with value from 0 to 1.

Improved List View Better for Report Printing

Thanks for Sagar’s suggestions. You can always export the list view directly into an Excel spreadsheet by clicking the arrow button. Now some features are added that make it more useful as a way to create report.

Before that, the width of all columns in Excel will be automatically expanded. Now, the column widths in the exported Excel spreadsheet are kept in the same ratio as defined in List View. This make it better to fit all columns in one page of the spreadsheet. The exported spreadsheet is also set to by default print all columns in one page.

List view is exported to Excel keeping the column widths equal.

You can now set the format of displaying number (integer, long, single, double) and currency data type in List View. You can add a format string in the “Formatting” cell in Browse Pane.

“$###,##0.00” means using thousand separator and 2 decimal digits. No matter of what your country is using, always use “,” as thousand separator and “.” as decimal mark in the format string.

You can also add any currency symbol in the first position of the format string. For “Currency” data type, if there is no format string defined, the default format follows the currency setting defined in Windows.

You can now control the displaying format of number in each column of list view.

If no format string is used, Currency data type follows the Windows setting.

The currency format in Windows setting starts from Control Panel > Clock, Language and Region > Change the data, time or number format > Formats : Additional Settings > Currency :

Start from Control Panel, you can set the system currency format.

Font Styles for Part of Text in a Cell when Printing to Excel Spreadsheet

This is a feature to add more fun in printing record entry to Excel. If you want to change the font style of part of the text inside a cell, you can now add these meta keyword to do so – some words for Bold, some words for Italic and some words for underline. It is a convenient feature when you are using formula to create some text together with the font style.

These font style keywords can change the font style displayed in Excel spreadsheet.

Sync Database Tables/Fields Definition Button

BAU DB is not only a convenient tool for end users to create self made databases, it is also a great tool that help you create applications for other people to use. This new feature helps you deploy application changes to users more easily.

It is common for an application to add some changes or enhancements at any time. A button is now added to synchronised all the tables and fields definitions in the worksheet settings (.dbp file) with the actual database file (.mdb file). When you deliver the changes to your users remotely, what you need to do is to send the “.dbp” worksheet setting file to your users, and let them click this button to update the actual database file on their side.

The location of the button is Main Menu => Db Tables => Update All Tables/Fields Settings

Press this button to sync worksheet settings with the database file.

User Login for Single User Edition

Thanks for Sagar again for the reminder that even in Single User Edition, a user login will be useful and more secure because the computer may be used by other people. And here you go the user account login in the free single user edition.

Single User Edition can now use password login too.

Some Bug Fixes

Fieldname can now start with number character.

Checking of “Double” data type is corrected.

How do I Update

As usual, download from here and install it on top of your current installation.

It’s worthy to share here the experience in the recent customisation projects. I cannot forget the scene the users clapped their hands happily when they saw how the customised applications can help their works tremendously, as they were manipulating data manually over spreadsheet before that. And what I had done is only some quick settings in bau db in order to shape their business logics and workflows. This proved bau db is a useful database tool to help people and help people to help people as well. Also, thanks have to be given to the users who have pointed out the weaknesses and it’s lucky that bau db is apt to change and become better and better.

Okay, see you next time in the launch of BAU DB Pro edition.

Release Updates – Improvements in UI, Change Unique Field’s Data Type and more

May 2, 2017 eric 4 Comments release updates

Hello everyone,

This is another release. This time a number of small glitches on the user interface (screen) are fixed, and there are also a few small new features. Let’s start with the new features first.

Change Data Type of Key Cell

Each worksheet is associated with a key cell (or key field), which acts as an unique identifier of records in the worksheet. In most of the use cases, the data type “text” is enough and applicable. For example, you can create invoice numbers like “INV/00001”, “INV/00002” and so on.

Each database worksheet has a key cell which acts as the unique record entry number.

The default data type of a key cell is Text :

The default data type of key cell is Text.

Sometimes you may want to do thing more specific. You may be working with a daily log and want to use date value as the unique identifier of each log entry. In this case, you’ll like to change the data type of the key fields of all tables – including the main table and all related multiple value tables in the worksheet, in a convenient way.

The key cell of a worksheet can also be a Date value.

In a typical worksheet, only the key field of the main table is defined and you have no way to change the data type of the key field of other tables. Never mind, you can now change the key field of all related tables just by changing its definition in the main table.

In this example, only change the data type of i.DocNo to “date” in Record Form’s Cell Properties is sufficient, where “i” is the main table of the worksheet. After saving, click Update DB Tables.

Change the data type of the key cell of main table to Date only is enough.

Since this worksheet also includes the related multiple value tables “i1” and “i2”, these 2 fields – i1.DocNo and i2.DocNo are also changed with the new data type, although you can’t find them in Record Form’s Cell Properties. These fields are used to link up the records in the 3 tables – “i”, “i1″ and ‘i2”, and therefore they must use the same data type.

The key cells of all related tables are also changed.

Auto Numbering for Key Cell of Date or Number

After changing the data type of a key cell, you may also want to create an auto number for its record entries. In a standard worksheet, the default formula in Auto Doc No – Select “L”+”@000000@” from tblDummy, is used for text field with “L” may be any letter. It has to be changed when the key cell is a date or a number.

The setting of auto numbering in a default worksheet:

Default auto-numbering of a database worksheet.

When the key cell’s data type is a date, instead of using AutoNumber and its formula, you may apply a default value for the key cell. For example, if you are creating a daily log and you can put the default value of the key cell as “Today”, so that current date will be appeared on the key cell automatically. With this configuration, it is assumed that one record entry is entered for each day.

Setting default date value for the key cell.

If the key cell’s data type is an integer, you can apply AutoNumber as the default value of the key cell and use this formula to increment the record number from 1, 2, … and so on:

Select "@@" from tblDummy

Default Value of Date and Time

These are the default values that you can use when working with date and time:
Today – Displays the current date, e.g. : 2017/4/19;
Today1, Today2, … Today1 – display the next day’s date, Today2 display the next next day’s and so on …;
Now – Displays the current date and time, e.g. : 2017/4/19 PM 05:57:04;
FullTime – Displays the current time in full format, e.g : PM 05:57:04;
Time – Displays the current time with hours and minutes only, e.g. : 17:57

Please note that before this release, “Now” displays the current time in full format and it is replaced by FullTime for the same now.

Tabs for Label Cells

Using label cells is a good way to add some descriptions or instructions in input pane (or record form) so that users of the database program can understand how to read or enter data immediately. Now, label cell has an added usage. You can add a tab button at the top right corner of input pane in order to navigate to the label cell quickly. It works like an internal anchor.

To do so, simply turn Nature of a label cell to “Tab” in worksheet setting.

Working with Decimal Comma

Instead of using a dot to represent decimal number, some countries use comma decimal like 123.456,78 or 123 456,78. Thanks for Pierre pointing out the issue. Some problems inside the program are fixed, and you can now work smoothly with decimal numbers in comma.

You can calculate decimal with comma easily in bau db.

To work with decimal numbers, firstly turn the fields to some decimal data types. You can select either “currency” or “double” for that. Remember to click “Update DB Tables” after saving its worksheet setting.

Setting data type of a decimal to currency or double

In formula, apply this function – CDbl(), which means convert to double, to convert the cell text into a decimal value for calculation.

Use Cdbl() function to convert a screen text into a decimal (double) value.

To calculate a column of decimal value, apply the same formula using “Sum”:

Apply Sum to calculate the sum of a column of decimal values.

Improvements of User Interface

A number of small improvements that will happen when you are using the screen :

Jumping of Cell
Previously, when a cell has not defined with any Input Box, cell focus cannot be stayed on the cell no matter you are mouse clicking it or using keyboard “Enter” to shift to the cell. The focus will always jump to the next available cell. Now, you can stay on a cell with no definition of Input Box. Although you cannot edit the cell data when no input box is defined, you can add display formula to the cell for related data referencing. If you really want to jump across the cell without stopping by, select “Jump” in Input Box of the cell.

Use Jump if you want to skip pass a cell without stopping by.

Scrolling of Input Text Box
In “Column” of Input Pane’s Properties of Worksheet Setting, input text box can now scroll normally as its whole grid is scrolling.

In the first column of multiple value cells of a default worksheet, input text box can scroll normally as its grid is scrolling.

Width and Location of Input Text Box
The width of input text box changes and follows its column’s width as the column width is changed by mouse dragging.

Input text box shifts and follows its column as the width of a column on its left are changed.

Filter Box’s Value
In the previous version, when a record entry is saved and the cell value of a filtering field is different from the current value in the filter box, the filter box value will be replaced by the cell value. This action is not appropriate and is removed now.

Also, when a record is saved, and the cell value of a filtering field is not in the list of the filter box, the cell value will be added to the list.

Entering Value in a Cell
Previously, when a value is being entered in a multiple value cell and the cell being entered is scrolled out of its grid, and the record entry is then saved, the value cannot be updated to the saved record entry. It is now corrected.

Previously, when a value is entered in a cell and the input cursor has not left the cell, the worksheet does not know the current content has been changed, and user can click out of the worksheet without saving the record entry. It is now corrected.

Description of Display Formula
Before that, if the word “Select” appears in the description portion of a display formula, it will be mistook as the start of the formula portion. It is rectified now and you can freely write any words in the description portion.

Now, can use the word Select inside formula description.

Added Short-name in Worksheet Setting
Two short-names are added in worksheet setting making it more convenient when calling these settings:

Countering – Processing After Record Entry Saved
Validation – Rule Checking Before Saving Record Entry

Short name for processing of data is Countering, and checking of input data is Validation.

Others
Previously, a record entry displayed in Input Pane but not shown in List View cannot be deleted. Now, you can do so.

Previously, the default numbers of rows of multifields (multiple value field) in the worksheet of a default workbook (Book1) are a bit too large and making the grid a bit too long. It is now reduced making the scrolling of multifields more convenient. The same is done in Worksheet Setting.

Previously, in the rightmost column of multiple value cells, the cell setting button overlay with the scrollbar. It is now rectified.

In List View, or Browse Pane, if a column is date value, sorting of it is default to be sorted in reversing order first. It’s more convenient because the use case of viewing the newest records is more common than the oldest.

The length of the reminder label in list view after a record is saved is now corrected.

Disintegration of Records

It’s worth sharing a support case here. Thanks for Lew’s question about his use case. Usually, in most of the daily operations, a database application’s job is to enter some raw data records and then do some grouping or summary on a set of records to generate some useful information. Lew’s work is to do thing reversely – by entering a summary information and let the database dis-integrate and generate the raw data records.

Well, in order to streamline a working process, this can happen because you may want to collaborate with the next stage’s application, and the application just needs that data format. Let’s have a look of how bau db can dis-integrate data.

In an record form, you enter an item and the quantity of that item.

What you want to do is to automatically generate the same number of records as the quantity of that item.

Disintegrate a number into rows of records.

This formula is used to expand the numbers into rows of record:

Insert into f2 (DocNo, A, B) Select "TxtSearch", 'f1.A', "Item A" from tblWeekD where Diff>0 and Diff< =val('f1.B')

This formula is used to transfer a number into rows of data.

There is an internal table tblWeekD with record values from 1 to 48, which is originally used for displaying date selection list in Info Pane. It is borrowed here to expand the input number into rows of records.

This table help create the date value selection in info pane.

tblWeekD can be used to dis-integrate a number into rows of records.

In the actual implementation, a Delete formula is used to give a clean start of generation of records whenever the record entry is saved, otherwise multiple copies of records will be generated.

Delete * from f2 where DocNo="TxtSearch"

Since the greatest value is 48 in the table, if you want to dis-integrate a greater number of records, you can either add more rows to the table tblWeekD, or just create another table for the same purpose.

This example is worth mentioning because it demonstrates the flexibility of BAU DB as a database tool for manipulation of data. Also, it shows that your input is vital as in the above example, you may even discover some new usages of BAU DB. 🙂

How do I Update

As usual, download from here and install it on top of your current installation.

Some of the updates above are based on your opinions and inspirations, and some program “improvements” are based on my own imagination. So, feel free to tell me if you like these changes or not, because seeing things from various different perspectives is not an easy task anyway.

As I know, you have been waiting for quite a while for the launch of BAU DB Pro. Although no fixed schedule yet, I can give some hint here – it is very close, and therefore please extend your patient a little bit more 🙂

Release Updates – Form Mode, Formula Commenting, UI Improvements and More …

October 20, 2016 eric 11 Comments release updates

Due to the easiness and rapid development style of BAU DB, it is a great tool for people, developers or consultants, who make applications for other people to use. Many friends of BAU DB have expressed love to use BAU DB to create database or business applications for people to use.

This release comes with some features that are useful when creating applications for other people, and some user interface and internal improvements as well. Just like all previous releases, thanks have to be given to all of you who have reported, suggested and inspired on these improvements.

Modes of Record Form (Input Pane)

Currently, each cell’s properties (settings and formulas) can be updated through clicking the “..” button on the top right corner of each cell. It’s just convenient to go with the on-screen setting.

When creating applications for other people to use, you might like to keep away the end users from wrecking the program. In this case, you can change the mode of the record form (input pane) to turn off the button. Thanks for Matthew’s inspiration on that.

Click this button to open cell properties page.

Before that, record form can be changed to “Report” mode already, so that record entry cannot be edited and becomes read only. Now, these two features are combined to become the different modes of the input pane.

In Worksheet Setting -> General Settings -> Form Mode, you can choose these options:
Input / Design – Can edit data and do on-screen setting
Input Only – Can edit data only, no on-screen setting
Read / Design – Cannot edit data, can do on-screen setting
Read Only – Cannot edit data, no on-screen setting

Select various modes for the input pane.

Automatic Upgrade of Working Files

This is a feature arising from the implementing of the above. Originally, there was the setting for the report mode of a worksheet with the options of True or False. The database field holding that was a True/False data type, which is either 0 or 1. In order to incorporate the various form modes now, the database field has been changed to a Text data type which stores directly the mode names.

Since that is a change of setting in each of the existing working app files, the program will automatically apply the change when you open an existing workbook that was created before this release. This mechanism of upgrading working files will also go alongside in the future releases whenever new features happen that require changes in the infrastructure of the working files.

Adding Comments to Formulas

The formulas of BAU DB are fun and simple to be used, and you can easily understand the behaviour of each worksheet by reading each line of formulas one by one. The openness of all settings and formulas is also a good point for end-users, so that they can get help from anyone in the future to ensure the continual growing of their applications.

Although the formula, which is in SQL format, is already easy enough and understandable, it will be more convenient if comments or remarks are added, so that, no matter for the sake of the original developers or the other developers and end users as well, people can know immediately what the formulas are doing at a glance. And the good news is that you can now do so handily!

You can type in comments in any part of a formula, and enclosed with a pair of curly braces. You can add comments in formulas from all areas – formulas used in record form, record list view, data processing and validation.

Comments can be added anywhere inside a formula.

Better Annotated UI of Worksheet for Newcomers

The 3 panes design of the worksheet UI (user interface) is to simplify the user experience. Users can search and edit record entry in the same screen without swapping back and forth. Though, some people who approach BAU DB the first time, may fall into a trap and don’t know where to start with. Okay, your voices are heard. 🙂 Thanks for Jan and Chris. The followings are added to make BAU DB more user friendly:

Names of the 3 Panes at Startup
In a blank new worksheet, the names of the 3 panes are displayed. With these annotations, it’s easier to understand how to go with the worksheet.
Record Form – Input Pane, for editing record entry
Record List – Browse Pane, for listing and searching of record entry
Assist Info – Info Pane, for displaying related information or selection list

Descriptions in Worksheet Setting
The titles of each part of worksheet setting are also changed to match with the above.
General Settings – edit the general properties of the worksheet.
Record Form’s Cell Properties – edit the properties of each cell in input pane.
Record List’s Column Properties – edit the properties of each column in browse pane.
Filter Box’s Properties – edit the properties of each box in filter pane.
Processing After Record Entry Saved – do some necessary data processing after a record entry is saved.
Validation Before Saving Record Entry – ensure the integrity of data by adding some rules and checking for the data entry.

The titles of different sections of worksheet setting.

The Link “Docs” in Menu Page
The program is now “connected” with the online documentations. It’s more handy to get help from the documentations, by clicking the link from Menu Page.

Access to online documentations handily with the link.

Better Navigation of Worksheet Setting

Again, thanks for Matthew’s report. The followings are improved for better user experience working with the worksheet:

Shifted Positions of “Save” Buttons
The “Save” buttons are shifted a little bit to the right, so that you can still scroll on the horizontal scroll bar of the grid of multiple fields when they are collided on the same level.

the grid is still scrollable when collides with Save button

Shortened Grid Heights
In worksheet setting, the grid heights of the multiple value cells were too large and difficult to navigate. The heights are now shortened and the whole grids can now completely show on a screen without the need to move up and down.

The grids can now show up completely in the screen.

Removed Continuous Row Increment when Scrolling Down Multiple Value Cells
This feature seem to be of no value to user. The continuous row increment as the grid is scrolling down is now removed. The movement of the grid will be stopped when it is scrolled down to the bottom row.

Multiple value cells is now stopped at the last row of the grid.

Countering Button Comes Out Right After Setting

This is the part two of the feature introduced in last release. Countering button is an on-screen button that you can add on the worksheet and adhere with some formulas as a way of on-demand data processing, and was explained in the previous release.

Now, this button is created immediately, right after its setting is confirmed in the cell setting screen, without the need to wait for the next startup of the worksheet.

Countering button comes out right after the setting is confirmed.

Some Other Improvements

Removed Confirmation Message when Creating a New Worksheet
Before that, a confirmation message “Insert worksheet setting completed.” would be popped out when a new worksheet is created. This message is now removed for a smoother operation.

No More Displacement of Default Value Button
The default value button was displaced a little bit upward in some situations. This is now corrected.

Loading a Record Entry Faster
It’s found that a record entry is loaded twice each time it is called into the record form. It is now normalised with only one shot of loading and you should feel it become faster.

Loading Screen Text Faster
The codes has been changed with the loading of screen annotations in a worksheet becomes faster now.

Formula for Currency Selection Corrected in the Sample Application
The formula is now corrected and pointing to the petty table to display the currencies.

Corrected formula for displaying currency in the sample application.

User Manual – More Styles of Excel Spreadsheet Reporting

This part of user manual about the various styles of Excel spreadsheet reporting is completed. You can learn more dot dot commands that are useful in making reports. I am sure you will get a lot of funs from it.

How do I Update

As usual, download from here and install it on top of your current installation. This installation has updated the setting file – bauSetup.dbp. If your have changed anything in that file, make a backup of it and apply your own changes to the new file after installation.

Do you like the updates of this release? As always, your comments makes BAU DB better and better. I truly understand that only you, who are sitting in front of the program and using it, can tell the good and bad of it.

Release Updates – Word Automation, Windows 10 Installation, Accumulative Summation Across Records

May 25, 2016 eric 23 Comments release updates

Hello friends,
This release comes with the interesting and exciting features that you will love. Let’s go!

Automated Document Using MS Word

Thanks for Pierre’s suggestion, apart from printing the content of a record entry onto a pre-formatted Excel spreadsheet, you can now choose to use Microsoft Word to create automated document template. This added option of using Word makes the feature of automated document more complete because some documents are better created and formatted in Word.

Using Word document to create template is the same as using Excel spreadsheet, just embed cell captions enclosed with double colons in the document template file. Multiple value cells can be populated in a table of Word.

Using MS Word for automated document template.

Document template of Word is filled with data.

Removed Blurred Screen Effect in Windows 10

This installation program fixed an unwanted effect in Windows 10.

If you have selected to scale up the sizes of items in Windows 10 in display setting, the screens of the program get blurred. This issue does not happen on the other versions of Windows – Win XP, Win 7, WIn 8 etc. It’s just because the handling of Display Scaling has been changed in Win 10.

Actually, to remove the blurred effect, you can change manually the setting in properties of the execution file – baudbs.exe.

Disable DPI Scaling manually by property setting.

Right click the execution file in C:\Program Files(x86)\BAU DB Free\baudbs.exe

In “Compatibility” tab, check the box of “Disable display scaling on high DPI settings” and press “OK”.

To save your work, this installation program creates a registry entry for the execution file to disable display scaling automatically, so that you have no need to change the property manually. This will only happen for Windows 10 installation.

DPI Scaling is disabled by a registry setting created by the installation program.

Accumulative Summation of Record Entries

There are two new formula keywords, AccSum and AutoSeqNo, to be used in Processing Data After Saving Record. These 2 formula keywords are useful for working across a series of record entries in a specific order.

There are use cases, like working with bank account transactions, you will like to calculate the accumulated account balance after each of the transaction record. The accumulated account balance of a transaction is the sum of the account balance of the last transaction with the amount of that transaction.

Calculate the accumulated sums for a group of transactions.

Now suppose we organize each transaction as a record entry in a worksheet. If the transaction records are entered following the real-life order, it’s alright simply to apply formulas to calculate the account balance for the current transaction record only.

However, in actual operations, it’s always not so perfect and the record entries may not be entered in the actual order of the happening of the transactions, and therefore we have better calculate the accumulative sums for a group of records as a whole in the actual order.

These formulas calculate the accumulated balance.

In this example, the formula uses AccSum to calculate and propagate the accumulated balance for those transactions with transaction date after the current transaction.

AccSum AccBal, TranAmt from f where AccNo='f.AccNo' and TranDate>=datevalue('f.TranDate') order by TranDate

If you like, you can enumerate the transaction records in the order of transaction date. This formula uses AutoSeqNo to enumerate the transaction records of the same month:

Update f Set SeqNo=AutoSeqNo where AccNo='f.AccNo' and format(TranDate,"yymm")=format('f.TranDate',"yymm") order by Trandate

The calculated accumulated balances of the transactions.

These two formula keywords shows you how BAU DB can be used to process data in a handy way.

Option to Open Another Workbook and Close the Current One

When you have already opened a workbook, and press the “O” button in Main Menu, it will open another workbook while leaving the current workbook intact. Thanks for Nagendra’s comment, and yes sometimes people may like to close the current workbook and open another to work.

Instead of letting you close the current workbook manually, it’s worthwhile doing something to automate this process. Okay, here you go and you can now choose to open another workbook from the Main Menu with or without closing the current workbook.

This button is used to open another workbook and close the current one.

When you mouse over the “O” button, it splits up into two buttons. The right hand side’s “oc” button, short form for open and close, is the newly added button to open another workbook and close the current one, and the left hand side’s is the same button to open another workbook while keeping the current one.

The fun of the button is that, once you are familiar with the behavior of this button, you can choose to go directly to either the left or right side of the “O” button, so that you can go directly to the button you want.

The same is working for creating new workbook with the “+” button and “+c” button. Wish you will love these 2 buttons 🙂

Smaller Installation Program

The installation program is now downsized from 5.8M to 3.1M, which give you faster access to the amazing features of BAU DB. Thanks for Nagendra again! You reminded me that some system files of Windows can be neglected in the installation file and thus make it slimmer.

At that time of discussion with Nagendra, I had chosen the conservative approach to leave the system files in the installation program as usual, to play safe for the risk if some users really lose any of those system files that BAU DB needs.

Although the worry is not without ground at all, the chance of missing those system files is rare. This fact is further supported by the Windows File Protection feature, with which I have done a testing on it recently. The fat is that whenever a system file is deleted, it is refilled immediately.

Now, the installation program, bausetup.exe, is downsized to 3.1M with the Windows system files removed. All these system files are still available in the installation program with selection of destination location, bausetup-s.exe, just in case when some users really need it.

The above 2 updates illustrate the importance of listening to user’s opinions, which is also the area that I have to pay more attention and improve on my side 🙂

A Fix in Worksheet Setting

In worksheet setting, when a value in “Column Caption” in Browse Pane’s Cell Properties is changed, it will lead to the 2 values, “Table” and “Field”, of a label cell in Input Pane’s Cell Properties filled with values, whereas for a normal label cell, there shall be no value defined for “Table” and “Field”.

This triggering by an action in Browse Pane’s Cell Properties which changes the data in Input Pane’s Cell Properties is due to some wrong indexes of formulas which is stored in the file bauSetup.dbp and these formulas are fixed now.

If you have changed anything in the file – bauSetup.dbp, like added local translation of the screen texts and program messages, you should make a backup of it before running the installation program, and apply your change back to the new file when installation is completed.

A Fix about Page Break in Excel Report

The page break location in Excel reporting of MultiPreprint format is not correct. This is fixed now.

A Fix in Concatenate Column Cell Values

“Concat” is a calculation formula type, which concatenates all the values of the cells in a column of multiple value cells and separates the values with comma. This fix removed the commas that appear in the beginning and end of the string.

And Finally …

Thanks again for the appreciations of BAU DB from all of you, and most importantly all the comments and suggestions that have made BAU DB better and better. Though, this is my job to make BAU DB becomes a great tool, the opinions and thoughts from you – the actual users are irreplaceable. That’s why I call you all friends now. And this is also my duty to open up and listen better to your thoughts 🙂

How do I Update

Release Updates – 2015/12/7

December 7, 2015 eric 38 Comments release updates

This updates is about the user manual. Thanks for your requests and suggestions, the documentation is now completed with the basic concepts of the feature of BAU DB and the contents should be sufficient so that you can get your hand in customizing your own database solutions.

BAU DB is an easy-to-use tool which lets you create powerful and flexible solutions to manage business and work. Apart from that, it is also an easy-to-learn tool. This is a summary of what is included in the documentation so far:

The purpose of the first section – “Quick Starter” is to let you start working with BAU DB immediately.

In “Going Straight to Data Entry“, you will know how to use BAU DB in a single click to create a ready-to-use database application and you can go straight to enter the cell captions and save data on the worksheets.

In “Automated Documents Using Excel“, it explains the simple steps to add some cell captions of BAU DB worksheets into Excel and turn it to a document template immediately so that you can generate automated documents for the data entries you have created.

In “The Actual Database“, it reveals the relationship of worksheets wit the actual database underneath. With this understanding, you can edit your worksheets to add more cells and also apply automation formulas that manipulate the tables and fields of the database.

In “Automating The Data“, you can learn the formulas and apply them to automate selection lists, input validations, calculations and manipulations of data between worksheets.

In “Reusing Pre-Existing Spreadsheet Data“, you will understand how to import your existing data in spreadsheet format into BAU DB worksheets in some simple steps, so that you can continue your works in BAU DB.

In the second section, “More Features” explains more features of BAU DB along with a real life sample, so that you can understand apart from using the default worksheets, BAU DB allows you to create an application from scratch as well.

In “Download The Sample Application“, it shows you the way BAU DB opens an application file is just like Excel opens a workbook file. It also introduces briefly about each worksheets of the application so that you can better understand the examples in the following sections.

In “Settings of a Worksheet“, you will learn how to edit a worksheet with its input cells associated with database fields and different field types. You can also edit the entry list views and the filtering boxes.

In “Data Flows Between Worksheets“, more use cases and examples are shown. You will meet some useful and convenient techniques there.

In “Manipulations of Data After Saving an Entry“, it will shows you the features of data manipulations that simplify things and empower you to apply some formulas to perform some tasks.

In “More Automation Features“, there are more fun and convenient features that sum up and made BAU DB an easy and simple tool for building applications.

Once again, thanks for Jan’s suggestion to add the explanation of database table and field and Ramos’s suggestion to add the steps of importing data into BAU DB. In the coming session, topics like usage of formula, cell properties and reporting will be rolled out. As suggested by Nagendra, forum is also need to build. For the time being, let me know if there are anythings that you want to know and not yet found in the documentation.

Release Updates – 2015/9/17

September 17, 2015 eric 8 Comments release updates

This release includes a small update in the program, some changes in the installation file, release of the Quick Start tutorial and … Thanks Giving.

The experience of making software over the Internet is just amazing. People from all over the world came to visit the site and downloaded the software. Through the website, I could meet with wonderful people who are powerful users and passionate developers. They are always giving invaluable comments and suggestions which are all vital for improving BAU DB.

It’s all due to the fact that they are real users. They are also so passionate that they have spent much efforts to help figuring out how to make the software better. We share the common goal and I know they have saw through the vision of BAU DB already 🙂

If there is an ability that is the most difficult and powerful at the same time, I would say it is the ability to put yourself in other people’s shoes. Making software is also such a job, in which only real users could tell the true feeling about using the software. Instead of thinking all the stuffs out of myself as a developer, now I know you could share with me all the useful comments and suggestions in order to make a great software tool for people to use.

And these people are you.

Documentation

Thanks for Philip, you pushed me to create the documentation and now the Quick Starter guide is completed. The tutorial goes through the overview and features of BAU DB. After reading the tutorial, you could understand why BAU DB is a rapid and easy tool to create database solutions and you should be able to get started using it for your works. That said, I have to gear up my effort in order to complete the other parts of the documentation as well.

Choose Location for Installation

Thanks for Michael, the installation program can now choose the location for installation and could be download here. Since I would like to keep the installation program to be simple, and stupid :), it is now divided into 2 versions. The first one is the standard version which provides the quick installation experience to users while the second version provides the flexibility to choose destination location for the installation files. Nevertheless, BAU DB is light-weighted (yet flexible and powerful) which does not consume much disk spaces.

Program Running on Windows XP

Thanks for Nagendra, you helped to make the program runs better in WinXP. Due to the format about sorting sequence of a few Access files, for which before the program was running in Chinese environment, the program cannot run on WinXP properly. Now I twisted it to a general sequence and now BAU DB can run on WinXP smoothly. This issue has never impacted the other Windows versions and occurred only in WinXP. As for the issue with UXTHEME.dll, the file is now omitted from the installation program so that it will not over-write the patched uxtheme.dll file and crack the Windows. Your effort of checking the other system dll files is valid and excluding them will make a smaller installation program. To play safe and ensure the installation program can run on all computers successfully, these system dlls are kept in the installation program. Besides, I have made some improvements about the alignments of screen controls in Main Menu and About screen and they look much better now in WinXP.

Add Pictures to a Record Entry

Thanks for Matthew, you reminded me of the feature about adding pictures to a record entry, which actually has been a feature of BAU DB for a long time and just that I missed mention it in any description pages. Now the description is completed and you could find it in the Quick Start tutorial and its introduction is added in the feature page too. You also reminded me of giving example database file and the sample app file can be now downloaded here. You sent me the list of comments and suggestions which I have gone through them already. I will put them up one by one on the list.

How do I Update

As usual, to get the update, simply download from the download page and install it on top of your current installation.

Release Updates – 2015/8/10

August 12, 2015 eric 15 Comments release updates

This release is a bunch of small enhancements. These include added warning messages when formula used are not correct, some user interface improvements and some enhancements about formula settings.

Message for Update Data

Warning message is added when formula used to patch data is not correct. To accessing this function, press “Amend Data” button in Main Menu and a window will pop out in the bottom right part.

Inside the window, you can use SQL formula to update or patch data in the database. This feature is convenient when you need to update a number of records altogether, or you need to update some data fields that are not shown in the worksheets, for some special reasons out of your normal operations.

This enhancement is that when your formula applied is not correct, now there is a message notifying what’s wrong with the formula.

warning message pops out when applied database update formula is not correct.

Processing of Data after Saving Record

The other two enhancements are related to data processing after record saving. In each worksheet, you can set up formulas for processing of data that are inside the worksheet or in the other worksheets, in order to fulfill your business automation and requirements.

You can find the formula setting in Main Menu -> Worksheet Setting -> Processing After Document Saved, which is near the bottom of the page of Worksheet Setting.

Press

Value Replacement

In the setting, there are Counter Formula and Update Formula. Counter Formula calculates some values which are then transferred and replaced in the corresponding Update Formula for updating records. To replace values in the Update Formula, these keywords are used : “value”, “value1”, “value2”, … and so on. Before this enhancement, only up to “value9” can be used in the Update Formula. Now, this update allows up to “value99” for replacing values, which should be more than enough for normal use cases.

Press

Dummy Counter Formula

It is possible in some use cases to execute Update Formula only without running query in Counter Formula. Before, a dummy counter formula “Select Dummy from tblDummy” is required, even though no actual meaningful calculation or result is obtained from this expression. This update allows just to leave counter formula blank when there is no calculation or query required to supplement the update formula.

Press

These enhancements are all small fixes that help the program better communicate with end users, and these updates let you get a glimpse of a few convenient features of Bau DB and why Bau DB is an interesting and fun tool 🙂 to create automated database solutions by yourself without much requirement of technical know-how.

How do I Update

As usual, to get the update, simply download from the download page and install it on top of your current installation.

Release Updates – 2015/7/3

July 3, 2015 eric Leave a comment release updates

This update introduces an interesting function – on screen setting of date selection list.

How to Access the Feature

You can easily call out the field setting screen by pressing the small button in the top right corner of each field cell. Inside the field setting screen, you can define a formula in the box of “Lookup Formula”, to display a list of data items in the display pane, for cell entry selection or just to display relevant data for reference purpose. And one of the most common feature that will be used is to define a calendar date list for entry selection when the entry cell is a date field.

database field setting button on the worksheet

This New Update

This update added the very convenient feature of on-screen setting of formula for displaying date list, instead of creating it from scratch. Press the “Date List >” button just under the label “Lookup Formula”, and you can find the screen for setting date list formula.

database field setting button on the worksheet

In the simplest form, just press the “Apply” button, and then press “Save” button in the field setting screen. You can now find a list of date values shown in the display pane.

date list created instantly in the database worksheet

You can always customize the content of the date list by changing the settings on the screen.

Use Case 1

In some applications, you may like to limit the starting date value, you can set the Smallest date values to, for example, Today + [-2] days so that the date list always starts by date value of 2 days before.

Use Case 2

In some applications, you may want to select a date value for every 7 days later, so you change the increment between date values : [7] days, and the displayed date list is like this:

Use Case 3

In some applications, you may want to choose the dates backward and you would like to reverse the date value order. Okay, in this case just select “Descending” for display order. Please note that in descending order, the limit of the starting value is the Largest date value, not the Smallest date value.

As a reminder, always turn Input Box to “TextCmb” for selection list. And in many cases, it will be useful to set Initial Value to “Today” for entry cell which is a date field.

general setting of date field

After all, apart from changing the on-screen settings, you can directly edit the formula in the formula box to custom for your own need. So, do you like it? This on-screen setting demonstrates that BAU DB is very easy and simple to use as an instant database solution. You can always provide flexibility to your applications, while keeping the setting up works to be as simple as possible.

How do I Update

Simply download from the download page and install it on top of your current installation.

Interchange Data with Business Partners

Exchange Data in Entry Format

Import Simple Tabular Data (Excel or CSV File)

Import Data Directly into Database

Working with Pictures

Capture Image with Camera

Insert Image in Excel Spreadsheet

Printing in Label Format

New Formula for Bulk Updating of Data

Improvements & Fixes

How do I Update

Improved Worksheet Setting

Auto numbering with ::upadd as default value

Value of Upper/Lower Row: ^’table.field’ and v’table.field’

Improved Screen Design – List View / Record Form Only

Improved List View Better for Report Printing

Font Styles for Part of Text in a Cell when Printing to Excel Spreadsheet

Sync Database Tables/Fields Definition Button

User Login for Single User Edition

Some Bug Fixes

How do I Update

Change Data Type of Key Cell

Auto Numbering for Key Cell of Date or Number

Default Value of Date and Time

Tabs for Label Cells

Working with Decimal Comma

Improvements of User Interface

Disintegration of Records

How do I Update

Modes of Record Form (Input Pane)

Automatic Upgrade of Working Files

Adding Comments to Formulas

Better Annotated UI of Worksheet for Newcomers

Better Navigation of Worksheet Setting

Countering Button Comes Out Right After Setting

Some Other Improvements

User Manual – More Styles of Excel Spreadsheet Reporting

How do I Update

Countering Button – On Demand Formulas Execution

How do I Update

Automated Document Using MS Word

Removed Blurred Screen Effect in Windows 10

Accumulative Summation of Record Entries

Option to Open Another Workbook and Close the Current One

Smaller Installation Program

A Fix in Worksheet Setting

A Fix about Page Break in Excel Report

A Fix in Concatenate Column Cell Values

And Finally …

How do I Update

Documentation

Choose Location for Installation

Program Running on Windows XP

Add Pictures to a Record Entry

How do I Update

Message for Update Data

Processing of Data after Saving Record

Value Replacement

Dummy Counter Formula

How do I Update

How to Access the Feature

This New Update

Use Case 1

Use Case 2

Use Case 3

How do I Update