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.
The default data type of a 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.
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.
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.
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:
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.
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
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.
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.
In formula, apply this function – CDbl(), which means convert to double, to convert the cell text into a decimal value for calculation.
To calculate a column of decimal value, apply the same formula using “Sum”:
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.
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.
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 “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.
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.
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 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.
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.
Added Short-name in Worksheet Setting
Two short-names are added in worksheet setting making it more convenient when calling these settings:
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
Validation – Rule Checking Before Saving Record Entry
Others
Previously, a record entry displayed in Input Pane but not shown in List View cannot be deleted. Now, you can do so.
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.
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')
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.
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. 🙂
Hi Eric,
Good work. I can’t wait to buy the pro version. Keep going.
More and more fun to work with.
Regards
Hi Pierre,
Thanks!
Hi Eric,
I wanna take some news from you.. How are you? A lot of work? Your health?
Regards
Hi Pierre,
It’s always nice to hear from you. Thanks for asking, my health is good, and I wish you the same too.
Some people asked to customize applications using bau db and I have spent some time on it. I am happy to see them delightedly accepted and using the applications, as it automated and saved a lot of their works in a simple way, and I can deliver the applications promptly. I have also improved some features along side the user feedback from the projects. Some users expressed that the user interface is convoluted because the record list view and input form are put on the same screen. Well, I can understand that simple design is always the best design, and this will be the feature to be updated in the coming release.
The current planning is that after the coming update release (will be in this month), the bau db pro version will be launched in the next release. I will continue the effort and make bau db to help people, because you and the other users let me know it’s a useful tool.
Cheers