DDH Software, LLC Making a big world smaller

This tutorial will walk you through the design of a database in a step-by-step fashion. This tutorial assumes some familiarity with the HanDBase program, namely that you should have already gone through Tutorial 1 before following this tutorial. If you follow along using the HanDBase application, you should be able to better create your own databases in the future.
Please Note: Where Windows Mobile is mentioned this means a Windows Mobile Classic/Professional (Pocket PC) Device with a Touchscreen. The current version of HanDBase for Windows Mobile Standard (Smartphone) devices, without a touchscreen, does not have the ability to create databases on the device.

Design: We will be designing a database employees could use as a Punch-Clock. This should make it easy to total up a weeks worth of time for their employer to pay their employees accordingly.

Layout: We would want to track the Date, Time Started, Time Ended, Out for Lunch Time, In from Lunch Time, Employee Name, and Total Time for that day, and a running total of total time across all of the records in view.

Steps: 1. Launch the 'HanDBase' application.
Palm: Press the 'Applications' button. This is the button to the top left of the Graffiti area. You should see a list of Applications available on your handheld. Search for the 'HanDBase' icon. This has a hand giving the 'Thumbs Up' symbol. Tap this with your stylus to start HanDBase.
Windows Mobile: Turn on the device. Tap on the Windows logo (Start) and select 'HanDBase' from the menu.
Desktop: Double-click on the 'HanDBase Desktop' icon in the desktop or launch from your Start Menu under Start--Programs(All Programs)--HanDBase 4--HanDBase Desktop.

2. If this is the trial version of HanDBase, you may see a startup splash screen indicating that this is a trial version. Press OK to continue.
Palm & Windows Mobile: You will now be in the 'Choose Database' screen.
Desktop: You will now be in the main window.

3. Once you are in this screen, you may create a New database.
Palm: Press the 'New' button to create a New database.
Windows Mobile: Press the 'Create a new database' button, then tap on the 'General' button.
Desktop: Click on the first icon in the toolbar (from left to righ), then click on 'General'.

4. You will now see the 'General Settings' screen, where you can name your database.
Palm: You may also set its category and other various settings regarding this database.
Windows Mobile: Categories are not supported in this device.
Desktop: You may also set other various settings regarding this database (Categories are not supported in the desktop).

5. Name this database 'Time Clock', by selecting the area next to 'Database Name" and writing 'Time Clock'.

6. Now lets begin defining the fields, tap on 'OK'. You will now be in the 'Edit Fields' screen. Select Field 1.
Windows Mobile & Desktop: Tap on 'OK', this takes you to the 'DB Properties' screen, now select 'Field'. Now, select Field 1.

7. Let's make the first field be the Date. First we will give the field a name by tapping the line next to 'Field Name', and writing 'Date'.

8. Now set this field to be a 'Date' by selecting the trigger to the right of 'Field Type', which should currently be 'Not Used', and then selecting 'Date'.

9. You are now given a list of properties for this field. The 'Behavior' parameter is currently set to 'Date Record Added', meaning that the date will automatically default to the date when the record was first added. Other choices include forcing the user to select a date, the date the record was last modified, and always showing the current date. Date Record Added is probably the most efficient for this example, so we will leave things as they are. Press the 'OK' button to continue.

10. Now you will return to the 'Edit Fields' screen again. Select 'Field 2' from the list by tapping it.

11. Let's make the second field be the Start Time. First we will give the field the name 'Start Time'.

12. Now set this field to be a 'Time' by selecting the trigger to the right of 'Field Type', which should currently be 'Not Used', and then selecting 'Time'.

13. You are now given a list of properties for this field. The 'Behavior' parameter is currently set to 'Time Record Added', meaning that the time will automatically default to the time when the record was first added. Other choices include forcing the user to select a time, and the time the record was last modified. Most likely the employee will create a new record when he/she gets to work, so it would probably be best to default this to 'Time Record Added', so we will leave things as they are. Press the 'OK' button to continue.

14. Now you will return to the 'Edit Fields' screen again. Select 'Field 3' from the list by tapping it.

15. Let's make the third field be the Lunch Out Time. First we will give the field the name 'Lunch-Out'.

16. Now set this field to be a 'Time' by selecting the trigger to the right of 'Field Type', which should currently be 'Not Used', and then selecting 'Time'.

17. You are now given a list of properties for this field. The 'Behavior' parameter is currently set to 'Time Record Added', meaning that the time will automatically default to the time when the record was first added. Since the user may or may not take a lunch each day, let's default this behavior to 'Ask User For Time'. This field will then show up as 'No Time' unless the employee enters a time manually. Press the 'OK' button to continue.

18. Now you will return to the 'Edit Fields' screen again. Select 'Field 4' from the list by tapping it.

19. Let's make the fourth field be the Lunch In Time. First we will give the field the name 'Lunch-In'.

20. Now set this field to be a 'Time' by selecting the trigger to the right of 'Field Type', which should currently be 'Not Used', and then selecting 'Time'.

21. You are now given a list of properties for this field. The 'Behavior' parameter is currently set to 'Time Record Added', meaning that the time will automatically default to the time when the record was first added. Since the user may or may not take a lunch each day, let's default this behavior to 'Ask User For Time'. This field will then show up as 'No Time' unless the employee enters a time manually. Press the 'OK' button to continue.

22. Now you will return to the 'Edit Fields' screen again. Select 'Field 5' from the list by tapping it.

23. Let's make the fifth field be the Time Out Time. First we will give the field the name 'End Time'.

24. Now set this field to be a 'Time' by selecting the trigger to the right of 'Field Type', which should currently be 'Not Used', and then selecting 'Time'.

25. You are now given a list of properties for this field. The 'Behavior' parameter is currently set to 'Time Record Added', meaning that the time will automatically default to the time when the record was first added. Since we don't know when the user will leave, let's default this behavior to 'Ask User For Time'. This field will then show up as 'No Time' unless the employee enters a time manually. Press the 'OK' button to continue.

26. Now you will return to the 'Edit Fields' screen again. Select 'Field 6' from the list by tapping it.

27. Let's make the sixth field be the Employee Name. First we will give the field the name 'Employee Name'.

28. Now set this field to be a 'Text' by selecting the trigger to the right of 'Field Type', which should currently be 'Not Used', and then selecting 'Text'.

29. Press the 'OK' button to continue.

30. Now you will return to the 'Edit Fields' screen again. Select 'Field 7' from the list by tapping it.

31. Now it is time to do some calculations. Just as an overview, we want to have a total for the day in each record.
Here's how we will calculate this:
Total Time for the day = End Time - Start Time - (Lunch Out - Lunch In).

32. Let's make the seventh field be the Total Time for the Day.

33. Now set this field to be a 'Calculated' by selecting the trigger to the right of 'Field Type', which should currently be 'Not Used', and then selecting 'Calculated'. The result of our operation will be of type 'To 2 Decimal Places', this will give us the hours total plus the fraction. This is selected in the Result Format popup.

34. Here is where we need to set up the math. In order to enter the calculation we use the 'Formula Editor', this is enabled by pressing the 'Configure' button.
The Formula Editor works basically the same way than a regular calculator does (it even looks pretty much the same). You are also allowed to enter several parenthesis and most importantly: fields. You select fields by taping the 'Field' popup, the actual name of the field is not shown on the formula, but the field number (position) is. If you need to select the previous field number you check the 'Previous' checkbox before selecting a field. You may traverse the formula by using the '<' and '>' buttons.

35. Let's enter the formula: Tap on the '(' button, select the field popup and then 'End Time', tap on the '-' (minus) button, select the field popup and then 'Start Time', tap on the '-' (minus), tap on the '(' button, select the field popup and then 'Lunch-In', tap on the '-' button, select the field popup and then 'Lunch-Out', tap on the ')' button twice. Since the result is given in seconds, let's divide by 3600 in order to have the number of hours (60 minutes x 60 seconds = 3600): Tap on the '/' button, then tap on the buttons '3' '6' '0' '0'. The formula should look like the following:

36. You are done with that, tap on the 'OK' button to return to the field editor screen. One more tap on the 'OK' button takes you back to the 'Edit Fields' screen.

37. Let's make the eighth field be a running total for the time, which can be later used by the employer to sum up an employees hours. This will require adding the 'Total Time Day' field to the previous record's 'Running Total' field (this very field). First we will give the field the name 'Running Total'.

38. Now set this field to be a 'Calculated' by selecting the trigger to the right of 'Field Type', which should currently be 'Not Used', and then selecting 'Calculated'.

39. Assuming the employer would like the total in hours with two decimal places of precision, we will select 'To 2 Decimal Places' as the 'Result Format'. The formula is simple compared to the previous: Total Time Day + Previous Running Total (in other words Field 7 plus previous Field 8). The result should be like the following:

40. Press the 'OK' button, and then again the 'OK' button.

41. Now you will return to the 'Edit Fields' screen again. At this point we are done designing the database, for the most part. Press the 'OK' button again, and the Database will be created. Press on the next 'OK' button.

42. Looking at the new database in the 'List View', you should be able to see the 'Date', 'Start Time', and 'Lunch-Out' fields- or at least portions of them. Since several employees may be using this Time Clock, it would probably be most useful to show the Date and the Employee name in this view. This way, each employee can find their record much easier in order to update it throughout the day. So we will need to hide all of the fields between Start Time and End Time. To do this, start with the 'Start Time' field, and press the field name in this view. Select the 'Hide Field' option and the field will disappear from the 'List View'.

43. Repeat the previous step for the fields 'Lunch-Out', 'Lunch-In', and 'End Time'. Now the screen should show 'Date', 'Employee', and 'Total Time'. This should work out fine.

44. Let's also set the program to sort by 'Date' so that everything is always in date order. Select the field name 'Date' and select 'Sort Forward'. Now all entries will sort by date as they are added.

45. Now the database should be ready to use. Create a new record.
Palm: Tap the 'New' button to add a new record.
Windows Mobile: Tap on the New document icon (white page).
Desktop: Click on the 'New Record' button.

46. The Date should automatically be filled with today's date, though it can be overridden manually. The start time will show the current time as a default and can also be overridden. Let's select 8:00AM as the start time. Tap the time as it is on the screen right now.
Windows Mobile & Desktop: The time is adjusted directly on the record editor, using standard MS-Windows methods. Which is by selecting either the hours, minutes or seconds, then scrolling up or down as needed.

47. You will now be able to select a time using the 'Time Picker' screen. Select '8' from the AM side of the Hours (international and 24 hour time users just select the 8), and then both '0' choices in the minutes section. Now press the 'OK' button.
Windows Mobile & Desktop: Does not apply to these platforms.

48. The time should now read 8:00AM (or equivalent). Let's assume this employee went to lunch from 11:30AM - 12:30PM. Select their Lunch-out and Lunch-In times just as you set the Start Time above.
Windows Mobile & Desktop: The time is adjusted directly on the record editor, using standard MS-Windows methods. Which is by selecting either the hours, minutes or seconds, then scrolling up or down as needed.

49. Now lets assume the employee left work at 5:00PM. Select the time of 5:00PM as you selected the 'Start Time' above.
Windows Mobile & Desktop: The time is adjusted directly on the record editor, using standard MS-Windows methods. Which is by selecting either the hours, minutes or seconds, then scrolling up or down as needed.

50. You will notice that the 'Total Today' and 'Running Total' fields have been automatically calculated. They are probably somewhere close to 8.00. If they are slightly less, the reason for this is because the 'seconds' portion of the time picker in the 'Start Time' field wasn't cleared, so your time might be a few tenths short of the 8 hours total. You can avoid this by having the user clear the seconds, if necessary, or by having the employer round up to the nearest minute when totaling.

51. Lets write the name 'Dave' in the Employee name. But rather than just writing it, lets make it a popup choice so that 'Dave' would never have to write his name in again, but can just select it from the list. Select the field name 'Employee Name' and select 'Edit Popup List'.

52. You will now be taken to the 'Edit Popup' screen for the 'Employee Name' field. Press 'New' and write 'Dave'. Now press 'OK', and repeat this process, also adding some other names like, 'Robert', 'Michelle', and 'Mary' to the list. Press the 'OK' button when finished to return to the record being edited.

53. Now select the field name for 'Employee Name' again, and you should be able to choose 'Dave' from the list.

54. So this record is now complete. Now let's add another record by pressing the 'New' button.

55. Fill out some information for another employee for this same date.

56. Now press 'New' again, and add another entry for 'Dave' for tomorrow's date. To do this, touch the date in this new record and select a new date from the calendar.

57. This time press the 'OK' button, as we would like to see what is going on in the 'List View'.

58. You should see three entries in the list- two for 'Dave' and another for the other employee you added (in this case, Michelle). Next to the 'Sort' button at the bottom right of the screen you may see a small calculator icon. This icon means that a recalculation is deemed necessary by the program. To recalculate, press the calculator button. It should then disappear. If you don't see this button, it is because your preferences are set to 'Auto Recalc as Needed'. When this setting is set, HanDBase will always recalculate when it is necessary. This is the safest option, but it may slow down the program when the database gets large. For now, lets go and turn this option on. Palm: Press the 'Menu' button, which is to the bottom left of the Graffiti area, and select 'Preferences' from the options shown.
Windows Mobile & Desktop: Not applicable.

59. Now check the 'Auto Recalc as Needed' option, if it isn't already checked, and then press 'OK'. From now on, the database should always automatically recalculate as needed.
Windows Mobile & Desktop: Not applicable.

60. So now we have a database which tracks each employees times. But the running total is for all employees, which probably isn't all that useful. Here is how an employer would run his/her report on a weekly basis.
Palm: Press the 'Tools' button to show the 'Tools' popup menu, then select the 'Filters' button.
Windows Mobile: Select the 'Actions' menu, then 'Filter...'
Desktop: Click on the 'Filters' button.

61. Enable the first filter by pressing 'Filter 1 Enabled'. Select the field for filter one to be 'Date' if it isn't already.

62. Now set the 'Lower Limit' to be the beginning of this week, and the 'Upper Limit' to be the end of this week.

63. Enable the second filter by pressing 'Filter 2 Enabled'. Select the field 'Employee Name' for this filter. Now select the 'Must Contain' trigger and select 'Dave' from the Popup list.
Windows Mobile: Pop-up list does not apply.

64. Make sure the 'AND' designator is highlighted, since we want to show all records within the date range AND belonging to 'Dave'. Now press the 'OK' button to return to the 'List View'.

65. Now you should see only the records for Dave from this week. If you press the right button at the bottom right of the screen, you should scroll enough to be able to see the 'Running Total' for Dave's entries for this week. So now the employer knows to bill 'Dave' for that appropriate amount.

Windows Mobile & Desktop: To move the records to the right, use the scrollbar located at the bottom of the screen.

66. This should give you a good idea of how to use Calculated fields as well as Date and Time fields to your advantage in a database. Remember that Calculated fields can work with most any field, including dates, times, integers, popups, checkboxes, floats, DB Popups, and more!

Exercises:
1. Some offices may prefer to have a copy of the 'Time Clock' running on each individual's handheld (of course, each user would need a registered copy of HanDBase!). If that were the case, you should be able to take advantage of the beam records functionality, and have each employee beam their data to the employer on a weekly basis (remember that HanDBase now allows peer-to-peer synchronization). Just have them set the filters to show only that week, and then use the 'Beam Records' option to get them to the Employer. In addition, you may want to set the Employee Name's field to default to that employee. This is done by going into the Field Properties and tapping the 'Default' button, and then writing in the name in the Text box. That is just one less item for each employee to have to fill out on their own handheld.

2. It may prove quite useful to have a formatted report for this applet. This can easily be done in Microsoft Word, using the mail merge feature. Using the HanDBase Desktop, you can convert the data to a CSV (Comma separated values) file and use this as the source for the Microsoft Word mail merge. Better yet, if you have HanDBase Enterprise Edition and use the HanDBase ODBC Driver, you can make this database an ODBC Data source and use this in Word directly with no conversions required!