Knowledgebase

Tutorial III: Using the Link and Linked field types

Product: General HanDBase Topic

Article Number: 86

Creation Date: 12/03/2001

Tutorial 3: Using the Link and Linked field types

Tutorial 3: Using the Link and Linked field types

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, you should have already gone through Tutorial 1 before following this tutorial. If you follow along using your HanDBase program, you should be able to better create your own databases in the future.

Purpose: The purpose of this tutorial is to familiarize you with the use of the Link and Linked fields in HanDBase. The Link and Linked fields enable you to establish and maintain a One to Many relationship between two databases. This One to Many relationship is One Parent record to Many Child records. This means that when you create a record in the Parent database, Clients in this example, you can then follow the Link, by clicking the Link button displayed in the Edit Record screen, to be taken to the Child database, Phones in this example, where you can create many Phone Number records that are related specifically to that Client.

The way this works is, when you create a record in the Parent database, a unique number is assigned to the Link field. You will not see this unique value as it is hidden from view. Then when you follow the Link, by tapping the Link button, any records you create in the child database will have that same unique number assigned to the Linked field in that record. Again, you will not see that value in the Linked field. However if you chose a field for the 'Show Value From' option in the Link field, as we will do in this tutorial, you will see that value displayed next to the Linked field button in the Child database.

Design: We will be designing a database to keep track of clients/customers with with a Link to a database of Phone Numbers using the Link and Linked fields.

Background: Many people who deal with Clients/Customers and need to keep track of many phone numbers, usually more than the built in Address Book or Contacts list can handle. Using the relational capabilities of HanDBase makes it posible to store the Phone Numbers in a separate database so you can keep track of all the necessary info.

Layout: This is a multi tiered approach to laying out a database for Clients/Customers. The top level will have the Primary Contact information, including name, nick name, DOB, etc. In addition, this database will have a link field pointing to it's child database, which keep track of the each of the phone numbers for this client. The phone number database will have fields for name, phone number, type of phone number and extension.

Tutorial Notes: This tutorial is designed for Palm OS, Pocket PC and HanDBase Desktop for Windows users. Therefore there are images for the steps for each platform. When you see the images the will be in the following order, when there are 3.
Palm OS
Pocket PC
HanDBase Desktop for Windows

Preliminary Steps:

Following are the different steps for getting to a point in any of the HanDBase applications for creating a database. There are separate steps for Palm OS users, Pocket PC users, and HanDBase Desktop users.

HanDBase for Palm OS users

Turn on your handheld device.
Tap the Palm OS Applications Menu icon in the Upper Left corner of the Graffiti Area. This will take you to the Palm Applications Menu.
Scroll through the list of Applications until you find HanDBase 3, This has a hand giving the 'Thumbs Up' symbol. Tap the HanDBase 3 icon with your Stulys to start HanDBase.
If this is the trial version of HanDBase, you will see a startup splash screen indicating that this is a trial version. Press OK to continue. You will be at the Main Screen. From this screen tap the New button.
If this is not the trial version, you may either arrive at the Main Screen, or perhaps inside an already existing database. If the latter is true, you will want to tap the Home Button to get to the Main Screen and then select the New button.
Proceed to the Database Design Steps.

HanDBase for Pocket PC users:

Turn on your handheld device.
Tap the Start menu and select Programs. You should see a list of Applications available on your handheld.
Search for the HanDBase 3 icon. This has a hand giving the 'Thumbs Up' symbol. Tap this with your stylus to start HanDBase.
If this is the trial version of HanDBase, you will see a startup splash screen indicating that this is a trial version. Press OK to continue. You will be at the Select an Option screen. From this screen tap the [Create a New Database] button.
If this is not the trial version, you may either arrive in the Select an Option screen, or perhaps inside an already existing database. If the latter is true, you will want to tap the File Menu and select New.
Proceed to the Database Design Steps.

HanDBase Desktop for Windows users:

If you see the HanDBase Desktop 3.0 icon on your Windows Desktop then double click it to start the application.
If you do not see the HanDBase Desktop 3.0 icon on your Windows Desktop then tap your Start Menu and select Programs or All Programs.
Search for the HanDBase 3.0 folder and click it.
From that list of items select the HanDBase Desktop 3.0 icon.
From the File Menu select New to create a new database.
Proceed to the Database Design Steps.

Database Design Steps

You will now see the DB Properties screen. From here you can set the different properties of your database including naming the database and setting up the fields.
NOTE: Palm OS users will be taken into the General Settings screen automatically.
You will create the Clients database first.
Tap the General Button to be taken to the General settings, where you can give your database a name.
Name this database "Clients", by selecting the line next to "Database Name", if it isn't already selected, and writing, or typing, "Clients".
Tap the Ok button to save these settings and return to the DB Properties screen.
Now lets begin defining the fields:
Tap the Fields button to be taken to the Edit Fields screen.
NOTE: Palm OS users will automatically be taken from the General Settings screen to the Edit Fields screen after tapping Ok from the General Settings screen, when creating a new database.
Select Field 1 by tapping it with the stylus.
Let's make the first field be the Name. This will actually be a Nick Name for the person, or how you normally address them. First we will give the field a name by tapping the line next to 'Field Name', and writing 'Name'.
Now set this field to be a 'Text' by selecting the drop down to the right of 'Field Type', which should currently be 'Not Used', and then selecting 'Text'.
You are now given a list of properties for this field. Let's leave these as is for now, and press the 'OK' button.
Since you will want to search for Clients names quickly let's make the Name field be the Quick Search field. At the bottom of the screen where it indicates Quick Search tap/click the selector and choose the Name field you just created.
Now you will return to the 'Edit Fields' screen again. Select 'Field 2' from the list by tapping it.
Let's make the second field be the 'Title', ie Mr, Mrs, etc.. First we will give the field the name 'Title' by writing it.
Now set this field to be a 'Popup' by selecting the drop down to the right of 'Field Type' and then selecting 'Popup'.
You will then want to define the Popup list so tap the [Edit Popups] button.
Tap the [New] button to add a popup item.
Make the First Popup 'Mr.' by writing 'Mr.' in the input panel. Then tap the Ok button.
Tap the [New] button to add another popup item.
Make the next Popup 'Mrs.' by writing 'Mrs.'. Then tap the Ok button.
Now tap the Ok button to save the Popup list and return to the Field Properties. You can always come back later and add more items to the list.
The Default Value will not yet be set so let's tap the selector next to Default Value and select the first item in the List, Mr.
When finished tap Ok to save the Field Properties.
You will return to the 'Edit Fields' screen again. Select 'Field 3' from the list by tapping it.
Let's make the third field be 'First Name'. This will be the person's actual first name. First we will give the field the name 'First Name' by writing it.
Now set this field to be a 'Text' by selecting the drop down to the right of 'Field Type' and then selecting 'Text'.
Let's leave the properties as is for now, and press the 'OK' button.
You will return to the 'Edit Fields' screen again. Select 'Field 4' from the list by tapping it.
Let's make this field be 'Last Name'. This will be the person's actual last name. First we will give the field the name 'Last Name' by writing it.
Now set this field to be a 'Text' by selecting the drop down to the right of 'Field Type' and then selecting 'Text'.
Let's leave the properties as is for now, and press the 'OK' button.
You will return to the 'Edit Fields' screen again. Select 'Field 5' from the list by tapping it.
Let's make this field be the 'Suffix' ie Jr., Sr., etc. First we will give the field the name 'Suffix' by writing it.
Now set this field to be a 'Text' by selecting the drop down to the right of 'Field Type' and then selecting 'Text'.
You will notice that this Text field also has an Edit Popups button. Let's define the Popup list to make data entry easier. Tap the [Edit Popups] button.
Tap the [New] button to add a popup item.
Make the First Popup 'Jr.' by writing 'Jr.' in the input panel. Then tap the Ok button.
Tap the [New] button to add another popup item.
Make the next Popup 'III' by writing 'III' in the input panel. Then tap the Ok button.
Now tap the Ok button to save the Popup list and return to the Field Properties. You can always come back later and add more items to the list.
Let's leave the rest of the properties as is for now, and press the 'OK' button.
You will return to the 'Edit Fields' screen again. Select 'Field 6' from the list by tapping it.
Let's make this field be 'Date Added'. This will allow you to keep track of when you added this record. First we will give the field the name 'Date Added' by writing it.
Now set this field to be a 'Date' by selecting the drop down to the right of 'Field Type' and then selecting 'Date' By default the behavior is set to 'Date Record Added', which will default the date for each client's record to the date each record was added.
The date is not something we need to see in the List view of the Database so to remove it from there we want to change the value of 'Pixels Shown' to 0(zero).
Let's leave the rest of the properties as is for now, and press the 'OK' button.
You will return to the 'Edit Fields' screen again. Select 'Field 7' from the list by tapping it.
This next field will the the Link to the Phone Number Database we will create later so we'll make this field be 'Phones'. First we will give the field the name 'Phones' by writing it.
Now set this field to be a 'Link' by selecting the drop down to the right of 'Field Type' and then selecting 'Link'.
You'll now need to set up the other properties for the Link field.
In the option for 'Database Name' we will write in 'Phones', as that is what we will call the other database when we create it.
Now set 'Show Value From' option to be 'Name' by selecting the drop down to the right of 'Show Value From' and then selecting 'Name'. This will make it so the person's Name is displayed next to the Linked field in the Phones database you will create.
Leave the other properties as is for now, and press the 'OK' button.
You are now done creating the database. Tap OK to save these settings and return to the Edit Fields Screen.
Tap OK again to exit the DB Properties Screen.
You will now be at the List View Screen of the database.
Palm OS and HanDBase Desktop users tap the [New] button, Pocket PC users tap the New record icon, the one that looks like a small sheet of paper, so you can see what this database will look like.
We aren't ready add any records yet because we need to create the Child databases first. So, Tap the Cancel button (Palm OS/HanDBase Desktop) or the Cancel icon (Pocket PC), the one that is a red circle with a white X on it, to exit this screen.
If you are doing this on a Pocket PC or with the HanDBase Desktop you will need to Save this database so we can go on to create the others. Tap the File menu and select Save.
On the Pocket PC and in the HanDBase Desktop, in addition to giving the database a name you must give the file a name as well. By default the file name will be the same as the Database name so 'Clients' or 'Clients.pdb' should already be filled in the field next to 'Name:' or 'File Name:'. Leave all the other options as is and tap the Ok button or click the Save button to save this database.

NOTE: HanDBase Desktop users will be prompted to select the user name of the handheld you wish to install this database to. You will want to select the appropriate user name and click OK.
Now let's move on to creating the Child database. Palm OS users tap the Home icon to return to the Main Screen and then tap the New button to create the next database. Pocket PC and HanDBase Desktop users Tap or click the file menu again and select New to create the next database.
You will now see the DB Properties screen. From here you can set the different properties of your database including naming the database and setting up the fields.
NOTE: Palm OS users will be taken into the General Settings screen automatically.
You will create the Clients database first.
Tap the General Button to be taken to the General settings, where you can give your database a name.
Name this database "Phones", by selecting the line next to "Database Name", if it isn't already selected, and writing, or typing, "Phones".
Tap the Ok button to save these settings and return to the DB Properties screen.
Now lets begin defining the fields:
Tap the Fields button to be taken to the Edit Fields screen.
NOTE: Palm OS users will automatically be taken from the General Settings screen to the Edit Fields screen after tapping Ok from the General Settings screen, when creating a new database.
Select Field 1 by tapping it with the stylus.
The first field in this database will need to be the Linked field since that is what we specified in the Clients Database. Since we chose the Name field as the 'Show Value From' option in the Link field we will want to call this field 'Name'. First we will give the field the name 'Name' by writing it.
Now set this field to be a 'Linked' by selecting the drop down to the right of 'Field Type' and then selecting 'Linked'.
You'll now need to set up the other properties for the Linked field.
In the option for 'Other Database' (Pocket PC) or 'Other DB we want this to indicate the Clients database, as that is the Parent database in this design. Since we have already set this database up you can tap the selector, to the Right, to select the Clients Database. Tap the selector, and from the list of databases presented select the Clients database. This will fill in the 'Other Database' option.
Leave the other properties as is for now, and press the 'OK' button.
You will return to the 'Edit Fields' screen again. Select 'Field 2' from the list by tapping it.
Let's make this field be the 'Type', ie Home, Work, etc. First we will give the field the name 'Type' by writing it.
Now set this field to be a 'Text' by selecting the drop down to the right of 'Field Type' and then selecting 'Text'.
Now let's define the Popup list to make data entry easier. Tap the [Edit Popups] button.
Tap the [New] button to add a popup item.
Make the First Popup 'Work' by writing 'Work' in the input panel. Then tap the Ok button.
Tap the [New] button to add another popup item.
Make the next Popup 'Home' by writing 'Home' in the input panel. Then tap the Ok button.
Tap the [New] button to add another popup item.
Make the next Popup 'Mobile' by writing 'Mobile' in the input panel. Then tap the Ok button.
Tap the [New] button to add another popup item.
Make the next Popup 'Pager' by writing 'Pager' in the input panel. Then tap the Ok button.
Now lets sort these items so they are in alphabetical order. To do this check the box that indicates 'Sort Popups'. When finished your list shold look something like in the image below.
Now tap the Ok button to save the Popup list and return to the Field Properties. You can always come back later and add more items to the list.
Since this is a list of Clients, most of the Phone Numbers you will collect will be for that person's Work. Therefore let's set the Default Type to be Work. To do this tap the [Default Value] button.
You will see the Default Value Screen. In the text entry area below the 'Default Value' label write or type the word 'Work'. By doing this when a new record is created in this database the Default Value for the 'Type' field will be Work.
Let's leave the rest of the properties as is for now, and press the 'OK' button.
You will return to the 'Edit Fields' screen again. Select 'Field 3' from the list by tapping it.
Let's make this field be 'Phone Number'. This will be the person's phone number. First we will give the field the name 'Phone Number' by writing it.
Now set this field to be a 'Text' by selecting the drop down to the right of 'Field Type' and then selecting 'Text'.
Let's leave the other properties as is for now, and press the 'OK' button.
You will return to the 'Edit Fields' screen again. Select 'Field 4' from the list by tapping it.
Let's make this field be 'Extension'. This will be the extension for the phone number, if applicable. First we will give the field the name 'Extension' by writing it.
Now set this field to be a 'Text' by selecting the drop down to the right of 'Field Type' and then selecting 'Text'.
Let's leave the other properties as is for now, and press the 'OK' button.
You will return to the 'Edit Fields' screen again. Select 'Field 5' from the list by tapping it.
Let's make this field another 'Date Added' field. This will allow you to keep track of when you added this record. First we will give the field the name 'Date Added' by writing it.
Now set this field to be a 'Date' by selecting the drop down to the right of 'Field Type' and then selecting 'Date and we will set the behavior to 'Date Record Added', which will default the date for each client's record to the date each record was added.
The date is not something we need to see in the List view of the Database either, so to remove it from there we want to change the value of 'Pixels Shown' to 0(zero).
Let's leave the rest of the properties as is for now, and press the 'OK' button.
We are now done editing the fields in this database so tap the Ok button to save the Fields properties.
Tap the Ok button again to exit the DB Properties.
Pocket PC and HanDBase Desktop users will need to save the database. To do this tap or click the File menu and select Save. By default the file name will be the same as the Database name so 'Phones' should already be filled in the field next to 'Name:'. Leave all the other options as is and tap the Ok button to save this database.

NOTE: HanDBase Desktop users will be prompted to select the user name of the handheld you wish to install this database to. You will want to select the appropriate user name and click OK.
Palm OS and HanDBase Desktop users will now need to reopen the Clients Database.
Tap or click the Fields button to access the 'Edit Fields' screen.
Select the field labeled 'Phones' by tapping or clicking it.
In the option for 'Other Field Name' you should already see it indicate 'Name'. That is the Name field that is the Linked field in the Phones database. Just to be sure it is properly selected tap or click the drop down selector to the right and re-select the Name field from that list.
Tap or click the [Ok] button to save the properties for that field.
Tap or click the [Ok] button to close the DB Properties screen.
HanDBase Desktop users will need to save the database. To do this tap or click the File menu and select Save.
NOTE: HanDBase Desktop users will be prompted to select the user name of the handheld you wish to install this database to. You will want to select the appropriate user name and click OK.

Conclusion:
You should now have a good understanding of how the Link and Linked fields can be used to establish relationships between databases. This will open up many possibilities for you. For example, a Doctor might use this functionality to create a database of Patients with a Link to a database for Visits. Making it easy to track the multiple visits for a particular patient.

Exercises:

Since many clients will also have various addresses, and since we did not add fields to store address information, you can now refer to the steps you followed to create the Phones database to create a similar Addresses database. You will need to be sure to add another Link field to the Clients database for Addresses.
To make finding different clients easier you may want to add a Category field to the Clients database. That way you can easily Sort or Filter the records by the Category. You could have categories such as Active, Inactive, Prospect, etc. You can even take this one step further and create a View for each category so you can quickly switch between the different groups.

Questions, Comments, Suggestions?
Email: support@ddhsoftware.com

DDH Software Home Page

This article has been viewed 81918 times.

Return to Knowledgebase Home

DDH Software, LLC Making a big world smaller