FoxPop
 

www.foxpop.co.uk

 

 

POWERBASE TUTORIAL 9

By Laurie Jane Kern

Tips and Tricks

 

We are now at the end of the first series of PowerBase tutorials. Over the past few weeks, in the 11 tutorials so far, you have designed and constructed a database, in the form of an Address Book; then, using that database you have learned about Fields, Field types, records, Tables, Views, Groups and Schemas. The database that has been used throughout the series has gone through many transformations, and now stands at Sample Address Book (09) - it contains 12 records, 5 Groups and 4 Views. In this tutorial I shall show you some advance calculations as well as tips and tricks I have been gathering. There are no major changes to be made to the database this time, but it might be useful to use it to explore the hints, tips and shortcuts described here - so download it now if you would like!  

Compressing the Database

Most desktop databases, including PowerBase, will cache information in an effort to make data retrieval faster.  This caching can increase the size of the database over time; it is there for a good idea to occasionally compress the database. We have all seen DATA do this for at random times and PowerBase will do it randomly too, trust me I have seen it! But it is a wise thing for YOU to do every once in a while. Let me show you how much space it can save. I have a copy of the Sample Address Book (number 06) on my Geofox - one of the series we have been using over the past few weeks. My Fox reports it at 16K in size. I opened the database file, went to the menu, Tools|Compress... and then closed the database. Th file size had been halved to 8K. You don't have to do a compress every day, but just keep it in mind and use the compress function every once and a while!

 

Changing the way PowerBase Looks

Here are some ideas for controlling and customising the "look" of PowerBase.

Select from the menu Tools|Desktop Preferences. Here you can turn off the Title Bar that shows the words "PowerBase" – you can turn off the Find Editor - finally you can determine the sorting order for minimized Views. There are 3 choices: By Type; By Type within a Table; By Table within a Type. I like ‘By Type within a Table’ - that way all Views from a Table are grouped together, and it is easier to choose a different View when you want to change!

Now perhaps you are wondering: "How am I to move forward and backwards through the records in a View if I have turned off all these functions? There are NO menu commands left to do this!"

Well there is an answer! In all Views, there are numbers in the lower left corner that show the current record number and the total number of records in the current View, even if a Group or Filter is applied.  Open a List View in the database; I have just done so and I am on record 3, so my record indicator number reads 3 of 12. To scroll if you turned off the Find Editor, just click on the current record number to scroll backwards - (if I click on the 3, I am taken to record number 2) - and click on the totals record count to scroll forward - (clicking on the 12 takes me back to record number 3!).

Here are some other ways to change the way PowerBase looks:

Did you know that you can turn the tool bar off? From the View menu there is an item Show Toolbar; uncheck this to hide the tool bar.
When any View has focus - be it a List View, Card View, Page View or even the Schema - you can change the View preferences by just going to Tools|Preferences on the menu bar. From here you can change the font sizes and turn off the title bar for any View. There are also options to change the line thickness, as well as turn lines on and off in List View and even remove the column titles. Did you know that you can turn the icons on and off in the Schema as well?
The Zoom button on your machine is specific to the window that has the focus at any time, so between font size and Zoom you can customize each View.
You already know that in List View you can resize a column width by clicking and dragging on the column header. ( see Tutorial4 - or the Hints & Tips page) Well, you can use the same technique to resize row height. Using the same technique, when you are in Card View, you can click and drag on the Field label to adjust the Field height.

 

Easy Entry of Field Values

There are several ways to make your life easier when entering information into PowerBase.

In the Schema for your Contacts Table, double click on any of the text Fields. On the Attributes tab there is an option labeled 'Default value' which will be set from the last record edited. This option is available for most of the Field types. This option means that when you are entering data in a record, and you get to a Field where this is set, PowerBase will automatically fill in the value for this Field from the last record you edited in THIS Table. So let’s assume you are entering lots of Contacts that all live in the same country. It would be handy to turn this feature on for this Field, so you won’t have to re-enter the same data over and over again. The value from the last entry will appear in the Field, and if you accept it, just tab to the next Field. If you don’t want the default value you can just type over it to change it. For example, setting this option for the Last Name Field would not make a lot of sense as this Field is usually different for most of your records.
The next tip I have for you is called the Unique Value List.

Take a look at the Country Field in our Contact Table, we only have 12 entries at the moment but you could have entries like: UK, U.K, United Kingdom, as well as US, USA, U.S.A., United States and United States of America. The first 3 entries and the last 5 are for different countries but each grouping is for the same country, how can you make it easier to make the data consistent? Let me show you how to use the unique value list. Open up the Contacts Table and set the focus in the Country Field, in any record, and press TAB to go into edit mode. Now press Ctl+Tab or Shift+Tab. A dialog box should open up titled Unique Values for Country, now click the list selector and look what you have here, a choice list containing all the UNIQUE values of the Field that are currently stored in the Table. Selecting an item from this list will update the editor to contain the value chosen!

Using a default value.

When you set up the Fields for a Table, one of the tabs in the properties dialog box is Default Value. In the Field URL, where you can specify some one's web page, I have entered the following string: "http://" that way I don't have to type it every time!

 

Dialing a Phone Number

PowerBase also supports the DTMF dialing feature. On the Psion, if you press the keys Fn+Menu a dialer will pop up if a valid telephone number is present. Even though Geofox did not advertise this feature, I assumed since the speaker is on the bottom of the unit, that it would work - and it does!

So here is how to use the DTMF feature. In the Schema double click on the Mobile, Telephone Home or Telephone Work Fields. Then make sure, on the Attributes tab, that the Search for phone number check box is selected - if it is not then select it. Then open a View and set focus in the telephone Field you just checked, press Fn+Menu and voila, you can DTMF dial your friends or business associates!

 

An Advanced Calculation Tip

In Tutorial 7 we used the following expression in the Full Name (calculation) Field:

[Title]+" "+[First name]+" "+[Last name] 

which resulted in a leading space if the Title Field is empty, you even end up with 2 spaces if you leave the first name blank too. Well, a way to prevent that is to use the following expression:

[Title]+left$(" ",Len([Title])>0)+[First name]+left$(" ",Len([First name])>0)+[Last name]

We shall dissect this equation using the first part of the equation that deals with the Title Field: The function Len() returns the number of characters in a string and so Len([Title]) returns the number of characters stored within the Field Title. By then testing Len([Title])>0 we are determining if there is a value at all in the Field Title. Thus the result is either Zero or One (because it evaluates to true/false which is also 1/0) The function Left$(s$,x ) returns the remainder of a string (s$) starting from the position x. So for us, this results in zero or one character being taken from the string " " and if our Field is empty we remove the space.

Cool! Anybody who has done some programming understands an ‘IF… Then... Else’ statement which is not what PowerBase gives us. So by using the test Len()>0 we are doing a similar thing. You may have noticed a similar test in Tutorial 8, where in the Age calculation we tested this: [Birthday]>0. In this test, since Date is a number, we can see if it greater than zero, not a string of no length.

To those of you who are new to databases and programming a bit of an explanation is required about the difference between NULL and a string of zero length. NULL is truly no data, where a string of zero length is recognized as a string, even though there is nothing there! So when PowerBase sees a NULL (nothing) in the birthday or title Fields it says (if it could talk!): "Well since it is NULL, and you want to know if there is something there - there isn’t, so I will tell you there is NOTHING THERE!" And that is why each test will work.   Most programs have functions like ISNULL or ISEMPTY so you can test for each case, and maybe Purple Software will give us that sometime in the future, but for now we have to work with what we’ve got!

There is a caveat to all of this If the Field being tested is a relational Field and you do not enter anything in the Field, technically it is NULL, and PowerBase will give you an error of #####. Well - there is a way round this too!

Another attribute or option you have for Fields is to either allow or not allow blanks. Setting this to NOT allow blanks requires you to always put something in the Field. SO - assume your Title Field is a relational Field (This will be one of our lessons in the next series of tutorials!), the related Table is TITLES, your entries in the Table are Mr. Mrs. Ms. and that Field is used in your Contacts Table. If you allow blanks in the Contacts Table Title Field then your equation above would give you that dreaded #####. So set the default value for the title Field to "" (you guessed it - the double quotes are a zero length string!). And then, when you don't enter anything, PowerBase will enter the "" for you and your equation should work!

 

Using the Templates

I wonder how many of you have made a new database from the templates provided and also specified that sample data should be created too? Now I don't really have any tips about the templates except to say that is you look at the Fields, default values, relational Fields and the calculations you can learn a lot. I did! And I recommend that you take some time to dissect these samples to learn more about what can be done!

 

Final Thoughts

That is the end of the hints tips and workarounds for this tutorial.

There are other resources which you can refer to for further hints tips and shortcuts in using PowerBase. There is a Hints & Tips page that Elisabeth has put together here on FoxPop, as well as the FoxTalk discussion Forum, where we are building up a database of hints, tips and troubleshooting, which you might like to refer to and also contribute to! 

The second series of tutorials will focus on taking the Address Book database, which we have designed and been working with, and transforming it into a Contact database.

I have been building a Contact manager, or should I say Contact database, for my own use, over the past few months, and I hope it will meet your needs too. Actually, managing contacts is not a vital part of my job. I am an engineering systems consultant, and usually I am on the same job(s) for several months at a time. I don't have to follow up on calls and contacts routinely. I do have to keep track of the people I know, however; both the people I work with and people I work for. I need to know where they work and where my fellow employees and I can be reached - and that is what my database does. So stay tuned and we shall have some more fun! ;-)

© LJKern and FoxPop 1999

[Previous] [Index] [Next]