reinnovate database

Step by Step Guide of Creating a To-Do Application

Let’s have a look at how to create a workable database worksheet just by using some mouse clicks only. You will find that the process is just like working with a spreadsheet – open a new worksheet, add some cell captions and change some cell (or column) widths.
This is a real life example to manage works of to-do items, and the below description is also written according to the actual order of the thinking process. During the process, there is no need for special design phase or planning, just add what you need at any time, straight from your head to the worksheet.

Create a New Worksheet With a Single Click

Let’s start by creating a new workbook, and then use the default worksheet inside the workbook.
When the program is started, click the “+” button on the top left corner to create a new workbook.
create a new database workbook
A screen prompts you to select the workbook name and location.
enter new database workbook name and selection location
A workbook “Book3” is created. You can choose to use one of the 3 default worksheets inside the workbook. You can also choose to create a new one by choosing a vacant cell and clicking the “+”. Either way, you will get a new blank worksheet.
create a new database worksheet
Assuming you have clicked “Sheet 1”, the worksheet appears. This is already a workable worksheet, and you can really start to use it by entering and saving data.
a ready to use database worksheet

Add Cell Descriptions

Before really entering data on the worksheet, let’s add the necessary cell captions, so that you will know what you are entering.
To add caption of a cell, click the top right button “..” on each of the cell.
click out the cell setting page
A cell properties screen pops up, you can add the caption of the cell in the top row of the screen.
change cell caption in cell setting screen
Add the cell caption and click Save button.
update cell caption and save
The first thought of the data that are required for a to-do item is its description, creation date and it’s status. Therefore, these cells are needed – “Work Item”, “Created Date” and “Status”. Since “Work Item” is expected to need more space, it is placed in the second line of cells in the worksheet.
add single and multiple value cell captions
Apart from the simple “description” and “status” of an work item, this “to-do” worksheet can also store the “details” of each work item. The “details” can be added to the multiple value cells that are already included in the default worksheet that we created. In this way, you can add multiple lines of content to the “details”.
Tow columns are added here, “Plan” is for actions that will be taken and “Answer” is for issues, solutions or results of that action.
add multiple value cell captions

Change Cell Widths

The basic cells are in place now. The next step is to alter the widths for cells which will hold longer texts. These cells are “Work Item”, “Plan”, “Answer”.
The default width of a single value cell is 1000, and the width of “Work Item” is set to 3000 to triple it.
change the cell width of a cell
You will find that the widths of single cells are relative to each other, the actual widths vary with the width of record form.
single value cell's width
Since “Plan” and “Answer” are multiple value cells, the widths are fixed – they do not change with the width of the record form. Since the default width of multiple cells are 2000, let’s change it to 8000 for both of the cells.
setting the widths of multiple value cells
the 2 column widths are set.

Change Worksheet Name

We have created the basic content for the to-do record entry. The worksheet name is still marked as “Sheet 1”, so let’s give a name to it. You can click the “Main Menu” button on the top right corner of the worksheet, or just click out the worksheet, in order to get to the Main Menu.
worksheet name is displayed in the bottom left corner.
From the Main Menu, click the “Worksheet Setting” button.
go to worksheet setting to change worksheet name.
In worksheet setting, you select the entry “Sheet 1” in browse pane by clicking the row. In input pane, change the value of the cell “Caption” to “To Do” and then save it.
select the worksheet setting record and change sheet caption.
Click out the Worksheet Setting and get to the Main Menu, and you will see the name of Sheet 1 is changed to To Do.
worksheet name is changed to to-do.

Apply Changes to Date Field – “Created Date”

In the worksheet, the cells “Work Item”, “Status”, “Plan” and “Answer” are all text items, and since the default data type of a cell is “Text”, these cells have no need to change. For the cell “Created Date”, we would like to work with date value, and therefore let’s change its data type to “Date”.
Go to worksheet setting and select “Sheet1”. Go to “Record Form’s Cell Properties” to look for the setting of the cell “Created Date”. Click into the cell in the column “Type” and change the value from “Text” to “Date”. The value of “Size” will automatically change to “0”.
After that, press the “Save” button to save this setting, and then click the “Update DB Tables” button in order to change the data type in the actual database which stores the data. Whenever the data type of a cell is changed, it is necessary to apply the change to the actual database by clicking “Update DB Tables”.
change data type of a cell.
Since the creation date of a to-do item is usually the same date it is entered, let’s add “Today” as the default initial value of “Created Date”.
Initial value of a cell can be set in worksheet setting or on-screen cell setting screen.
add default value to a Date cell.
Click out the cell setting screen of “Created Date” from the worksheet. In “Initial Value”, select “Today”.
add default value from cell setting screen.
Now, let’s try entering some records. The preset default value of the cell “DocNo” is AutoNumber with a preset formula. You have no need to do anything, and the record will be saved with DocNo : A000001, A000002, … and so on. The cell DocNo is the primary key cell, or you can call it the unique identifier of the record entry.
Up to now, you can say that the worksheet is completed, as you can use it to store work items already, right? 🙂
Anyway, let’s go a bit further to make the worksheet even better.
entering record entries in a worksheet with auto record numbers.

Add Input Selection – “Status”

The status of an newly entered work item shall always be “Open”, and so let’s give a default initial value to the cell “Status”. The value “1-Open” is used to make sure that it will be the first choice on the selection list.
add default value of a cell
The status of an work item can be one of the few commonly used value – it’s either “Open”, “Close” or something else. You can create a selection list to assist the input of the cell. To create a selection list, change the value “Input Box” to “TextCmb”.
use TextCmb for input selection.
In “Lookup Formula”, enter this formula:
Multi;Select a status;500|2000;Select distinct a.B from a
In the formula, the first keyword “Multi” means it is a selection list. The second portion “Select a status” is the title, and “500|2000” is the row height and the column width of the list. The content is filled by the actual formula, which is English like and easy to understand :
Select distinct a.B from a
You can see from the top of the screen, the actual database’s table name is “a” and field name of the cell is “B”. It means that the cell value of “Status” is stored in the table field “a.B”. To translate the formula into English (oh, actually it’s already in English 🙂 ), it is to list all of the Status possible values of the current work items.
adding display formula for input selection of a cell
Since the keyword “distinct” is used in the formula, duplicated values are not displayed, so that you can select from the distinct list of values.
displaying distinct values as input selection of a cell.

Edit Browse Pane

The original record list view displayed in browse pane is like below. It’s time to make some adjustments to it.
change column caption in browse pane.
Change Column Caption
The column heads of Entry List View, or Browse Pane, are still using its original caption – A.A, A.B, A.C, … Let’s change them to the same captions used in Input Pane.
Follow the same step to go to worksheet setting and select “Sheet1”. This time, let’s scroll down to the Record List’s Column Properties. In Column Caption, select the captions for each of the used columns. The used columns are A.A – Created Date, A.B – Status,and A.E – Work Item.
change column caption in browse pane.
change column captions of browse pane in worksheet setting.
Change Column Position
Since A.C and A.D are unused columns, let’s shift the column “Work Item” before them. Enter “5” as the new Column Position of A.C, go down the cell and all columns beneath will be shifted positions too.
shifting column position of browse pane in worksheet setting
After entering “5” for A.C, A.D is changed to “6” and “Work Item” is changed to “7” automatically. Since “Work Item” is to be placed before A.C, its new position is 4. Enter “4” in its Column Position and go down.
shifting the column positions
After entering “4” for Work Item, the column position of A.F will become “5”, so let’s change it to “7”, which is its original position.
change back the original column position.
Now, let’s save the setting, and the columns are sorted in order in Worksheet Setting, and the actual worksheet as well.
the updated cells are sorted according to the column positions.
the column is shifted in position.
Change Column Width
Since the text is expected to be longer for Work Item, let’s increase its column width for better viewing. Default column width is 2000, let’s make it to 6000.
change column width of columns in browse pane.
column width is enlarged.
Change record’s display order
The default sorting order of displaying records in List View is Doc No. For the To-Do items, you may want to see the “Open” records to be displayed first, and therefore let’s change the displaying order to be sorted by “Status”.
Scroll the grid of Record List’s Column Properties to the right and find the column “View Filter”. In the first row of “Doc No”, add this :
Order By a.B, a.DocNo
Again, this is an English like formula. The keyword “Order By” in the clause changes the displaying order of records, which is sorted by firstly with the column a.B – “Status” and then by a.DocNo – the record key.
records in browse pane are sorted by status
Records with status “1-Open” is displayed first, and on top of “2-Closed”.
Actually, up to this point, the job is done. The worksheet looks perfect and you can start to work with it already.
records in list view are sorted by Status.

On-Going Changes

Usually, along the way of working with the worksheet, some bright improvement ideas might come up and you would want to implement them in your worksheet. With BAU DB, you can make changes to your applications at any time and see the results instantly.
Add some more cells
You may discover some work items have time limit, and want to add a cell “Deadline” to it. And you may also want to add a cell “Remarks” to put down some information about the work items, apart from its “Status”.
Let’s put the cells “Deadline” and “Remarks” at the same row of “Work Item”. It can be done simply by clicking the on-screen cell setting buttons of A.F and A.G respectively.
adding cell - deadline
Let’s give a bigger width for the cell “Remarks” and put 2000 as the Width.
add the cell - Remarks
The 2 new cells are added to the worksheet.
Create a date selection list
“Deadline” is a date field, and let’s change it to a date value in worksheet setting. Again, after saving the worksheet setting entry, press “Update DB Tables” to apply the change to the actual database.
change the data type of a Date field.
You can add a date selection list to assist the input of value of “Deadline”. It’s also done with a few clicks only.
From the To-Do worksheet, click out the on-screen cell setting screen of the cell “Deadline”. Since it’s a selection list, change “Input Box” to “TextCmb”, and then click the button “Date List”.
The default value of “Smallest date value” is “-30”. Since the deadline is usually some date in the future, let’s put “0” to it, so that the first date displayed is today. After that, press “Apply” button.
add a date list for input selection of a Date cell.
After clicking the “Apply” button, the formula for displaying the date list, or calendar, is generated. Since the formula is generated, let’s leave it there without understanding it for the moment. Just click the “Save” button to save the setting.
formula for display date list is automatically generated.
Click the cell “Deadline” in the worksheet, and you can see the calendar.
click the Date cell to display calendar selection.
Changing cell positions
One day, you may come up with the idea of moving the cell “Work Item” to the top of input pane, because you want to enter data for “Work Item” first before everything else. You can do so by moving the whole row of cells up one level.
It’s similar to changing the column position in browse pane, only that this time is changing the row position.
In Worksheet Setting => Record Form’s Cell Properties, change “Row” of “DocNo” to “2”, because it is moving to the second row. After entering “2” and moving down, all the cells of row 1 will be changed with row value “2”.
change cell rows
Since “Work Item” is to be moved to the first row, enter “1” in it’s “Row”, moving down, all other cells of the same row change to “1” automatically.
change the row number of the cells
With the values of “Row” swapped, the worksheet setting can now be saved.
after saving, the cells are sorted according to row number.
the cells are shifted in row.
Change label cell caption
The default caption of the label cell is “Multiple Value Fields 1”, let’s make it more meaningful.
change label cell caption
In worksheet setting => Record Form’s Cell Properties, find the row of the label cell which is just above the cell “Plan”. Change the value in the column “Default” from “Multiple Value Fields 1” to “Details”.
change default value of a label cell
change default value of a label cell
label cell caption is the default value.
Adding Last Updated Date to records in browse pane
Although you can see the last updated date of each record entry in the bottom of input pane, it’s also convenient to let it to be displayed alongside the records in browse pane.
add last updated date of record entries to browse pane.
The internal table field of “Last Updated Date” is LastTranDate, this field is a Date value and created automatically in all tables. Let’s add it as a column in the browse pane.
In worksheet setting=>Record List’s Column Properties, go to the row “Work Item”, and then click the small button on the right – “Copy Row”. It will copy a row of data same as “Work Item”.
copy a row of column setting
Since “Last Updated Date” is to be added beside “Work Item”, let’s go to the row beneath and change its info.
First, type “Last Updated” in Column Caption.
change the column caption as Last Updated
Then enter “5” in its “Column”. After pressing [Enter], the values of the other columns will be changed automatically.
change column position
Fill in “LastTranDate” as its field name.
add field name LastTranDate
Finally, correct the “Width”, 2000 is enough. You can now save the worksheet setting.
change column width of Last Updated
The column Last Updated is added