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.
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.
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.
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:
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.
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 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:
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.
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.
You can print data or images with label format.
The image is put on the 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!
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!
Hi Eric
Happy New Year!!!!!!
I wish you a wonderful year. Peace, Love, Happiness and Health in your life. May the year 2018 be very successful.
Pierre
Hi Pierre,
I wish you a prosperous and happy new year too. May health and love be with you.
Hi Eric,
Hope you’re well.
Just one thing. When do you think the last 2 articles wil be ready?
Have a nice day!
Pierre
Hi Pierre,
The 2 articles can be delivered in 2 weeks.
As always, it’s nice to hear from you and thanks for your reminder.
Cheers
Eric
Hello Eric,
Thanks for your reply.
Have a wonderful weekend.
Best regards
Pierre
Hi Eric
Just to get some news. How are you? I hope you’re well.
Hi Pierre,
I’m fine, and I hope you all is well too.
I have been busy with a customer project and gained much positive experiences from that project that help make bau db better.
Your message is just in time. I am now ready to focus on finishing the left over part of the user manual.
Cheers
Hi Eric,
I wonder if it is possible to run Bau DB on linux with wine or something like that. What do you think about it?
Good day
Pierre
Hi Pierre,
It’s a good idea to extend bau db to the other platforms. Technically, I think it’s possible. It can be scheduled in later update.
Cheers