![]() |
FoxPop |
www.foxpop.co.uk |
POWERBASE TUTORIAL 8
If you need to, please download the Sample Address Book (08) which is the database as you left it at the end of the last lesson. In this tutorial you are going to be working with some of the other Field types such as Boolean and Date, and looking briefly at Floating-point and Integer. Then, next time, tutorial 9 will go over some advanced calculations as well as offering some tips and tricks I have been gathering.
Boolean Fields
Boolean in programming terms usually implies either True or False. It can also mean any of the following:
![]() | 1 or 0 (one or zero) |
![]() | Yes or No |
![]() | On or Off |
![]() | Black or White |
Well, Purple Software have taken it one step further by giving us several of these choices and a new one as shown in the list below:
![]() | 1 or 0 (one or zero) |
![]() | Yes or No |
![]() | On or Off |
![]() | True or False |
![]() | Male or Female |
You may be thinking, "That's nice, but what do I use this for?" Adding a Boolean Field to your Contacts Table will show you! Make sure that either the Schema or Contacts Table is open and has focus. From the menu select File|Create new|Field... and enter the new Field name as 'Gender' and select Boolean as the Field type then click OK. In the Properties dialog box that appears next, set the display as to Male/Female and leave all the other options to the defaults so just click OK. If you look at the Schema, you will see that this new Field has an icon of a check mark. We now have a Gender Field in our Table, but it is not visible in any of our Views. So, using what you learned in the last lesson, add this Field to either the List or Card View, or both (click here to return to Tutorial 7).
Notice that you have not set a default value for new records since it might be wrong to assume that most of our contacts are either male or female! Besides, what about those companies you have in your list?
Position your cursor in the Gender Field of the record for Mr Charles Dickens, and press Tab to enter edit mode. Now press Tab again to bring up the Field editor, which is shown in the screen shot below. (I have added the Gender Field to the List View, and I positioned it in the first column to make it easily visible).
Notice that you have your choice of ZERO or ONE but you set your properties to display Male/Female.Change the value to 1 and click OK, you should see the word MALE in the Gender Field. (Personally, I would have made ZERO represent male and ONE for female, but let's not get off track). Now, go through the remaining entries and set the correct Gender, leaving the entries for the companies or web sites blank.
Here is another example of a way to use a Boolean Field in a Contact database: Say that you maintain the Geofox Users database. In the Contacts Table you have one Field, which is Text, which shows which "User Group" a person belongs to. Next to that is a Boolean Field, set up to display as True/False and is labeled "Coordinator". This Boolean Field is used to designate which user is the Group coordinator. Then applying a Group, based upon User Group, it is easy to identify which user is the Group's coordinator. There can also be Group based upon Coordinator, which can be used to display all the people in the Table who are coordinators.
DATE FIELDS
There are seven different ways to display date and time in PowerBase! Please refer to the Help file or User's Manual to see what they are, but for now it is enough to note that you can use Date Fields many different ways. I will show you two main ways here:
The first method is to use a Date Field to show when a record in the Table has been added or changed and the second way is to record a person's birthday. (I am now assuming that you know how to add a new Field, if you need to refresh you memory refer to Tutorial 3)
Record Changed
Begin by adding your Field, name it 'Changed', set the type to DATE, and click OK. In the next dialog box, on the Details tab, set the display to Short Date. Then on the Default Value tab enter the following expression: NOW (yes just the word NOW), then click OK to close. If you look in the Schema you will see the new Field, Changed, has a little clock icon. Again, add this Field to one of your views so we can test it. Go to our friend Mr Dickens and enter edit mode, you don't have to change anything, just move through each Field, making sure you pass through our new Field Changed, when you get there, press TAB and bring up the editor box. As you can see below (I am editing Jane Austen), the editor shows the date as well as time; but when you click OK, it will show the short date, as seen in Mr. Dickens' record. Any time you EDIT a record you should change the date so you know the last time you modified some information about this record. IF you add a new record, the current date will automatically be entered into the Field, Changed, when you pass through it, I leave it up to you the user to try this out for yourself. Do you see why we made the default value 'NOW'?, that is what the function NOW does, it grabs the system date and time.
Birthday
On to your next Field which is Birthday. Make a new Field of type Date, name it 'Birthday' and set the display to either Long Date or Medium Date or Short Date and click OK. Add the Field to one of the Views. Now enter the following birthday for our friend Bill Gates: April 1, 1956
I know, you are thinking, that was not so hard! But wait, you are going to go one step further and add a calculated Field, to show how OLD our friends are...!
Age
Go ahead and add another Field to our Contacts Table, name it AGE and set the type to Calculated, then change the 'Display as' attribute to Integer. Now on the calculation tab enter the following:
([Birthday]>0)*(now-[Birthday])/(365+((5*3600+48*60+45)/(24*60*60)))
which is simply the number of days from Birthday to now divided by 365 days, 5 hours, 48 minutes and 45 seconds (which is the length of the tropical year in 2000AD) expressed as a decimal number of days. Again add this Field to our View, and take a look at the screen shot below.
This shows the age of Bill as 42, a whole number. Even though we have a calculated Field we chose to display it as an Integer. (So now you know what a Field of type Integer is). As an additional experiment you can go back to the Field properties for AGE and change the 'Display as' to Floating-point, then change the format to Fixed point and set the decimal places equal to 2. Now you will see not only the years but also a value that really gives age as an estimate of "the time you have been alive". Floating-point numbers usually have decimal places after them, and since we chose 2 decimals we have actually selected to use a FIXED-point notation. Using a currency format is also a type of Floating-point number as is scientific notation. The Help file and User Manual go into more detail on the maximum and minimum values that can be entered into each type of Field.
And before we leave this topic, Chris at Purple Software sent along this calculation that can be used for a more traditional anniversary based calculation. This calculation returns the answer as a formatted string (and then you should also change the display properties to Text) Try it out and then decide which one you like best!
str$(Year-Year([Birthday]-(Month<Month([Birthday])))+" years "+str$(Month-Month([Birthday])+12*(Month<Month([Birthday])))+" months"
Conclusion
Now that you have learned about some other types of Fields, I thought you might like to know that we have almost finished the Basics of PowerBase, which is the first section of the tutorial sequence. Our next tutorial will conclude this section. THEN starting with tutorial 10 we will begin a discussion about "database design" and get into redesigning your database so you can use it as a Contact Manager. See you then!
Download Sample Address Book(09) which incorporates all the additions and changes we have made so far.
© LJKern and FoxPop 1999