![]() |
FoxPop |
www.foxpop.co.uk |
POWERBASE TUTORIAL 10
Look-up Tables and Relational Fields
I was going to wait to discuss Relational Fields in tutorial 11, but I have received
several emails over the past few weeks asking about them. Consequently, I've decided that
it would be a good idea to show you how to set up a relational field now, before you start
to build your Contact Database. So for this lesson you can use Sample
Address Book (09) from the end of Tutorial 8.
Before you go any further, another new phrase I want to introduce is the term "look-up"
table. You will need this type of table in your Contact Database, so this is as good as
place as any to introduce you to both these items.
Relational Fields
Question: What is a PowerBase relational field?
Answer: It is a field that allows the user to reference information from one table within another table.
That is the simplest way to define it. I bet you did not think it was that easy! I should also mention that since the data in the relational field comes from another (secondary) table, the actual data you "see" is not stored in the primary table. What is stored in the relational field is the information or relation about which record is to be used from the second table. Using a relational field can reduce the size of a table, if not the database, as a whole since the storage of the data is held the secondary table; the primary table just holds a reference to the data being displayed. And if you change the information in the related table, it is immediately reflected in the table that is referencing it.
Look-Up Tables
The next important item to understand is the look-up table. A "look-up table" is
the term used for a table that provides "look-up" information for use in another
table (oh, yes, and it does so by using a relational field. See! I tied the two new
concepts together!). In your address book database, the contacts table is an informational
table, and a table that is used to record purchase orders is called a transactional
table. So, if you have a table that is a list of Countries, US States, UK Counties or even
a list of Titles, it is called a look-up table. It is just a term that describes how the
table is used in the database. Again, it is no different from the other tables in the
database, it just has a different use.
Usually a look-up table is "filled in" once at the creation of the database, and may only be changed occasionally. We shall now demonstrate how to build and use a relational field, and build a look-up table. Also note that a relational field does not have to be used just for look-up tables. But if you are using a look-up table in another "primary" table, then you will want to use a relational field to display the data from the look-up table.
Look at the sample address book, you can see that you have a list of people in the contact table and that there is a field labelled "Title". If you look at the entries for the word Mister, you can see that you have several variations ie: Mr and Mr., but you could end up eventually with a list like this one:
![]() | Mr |
![]() | Mr. |
![]() | MR |
![]() | MR. |
![]() | Mister |
All these variations result in inconsistent data, which can then cause problems with filters and groups (you saw this in the lessons about filters and groups). But this is a text field, so almost anything could be typed in the field not just what I listed above. So, how do you make the data consistent?
Remember in Tutorial 9 I showed you how to use the <Ctrl-Tab> feature to display a unique list of entries for a field? You could use that here, but it means you had to have entered a value in the field to begin with. It does a better job of making the data consistent but it does not remove it completely.
This is where a look-up table comes in to the story. By using a look-up, you are saying that the user of the database, must select a value from the list. If you don't know which values are available, then you must look them up in the list and select one. If you don't use this look-up feature, every time you enter something in the "Title" field, you can end up with some variation on the title. However, if you use this technique you are ensuring data consistency. It also means you don't have to keep typing the entry for this field over and over again; you just select something from the list.
Putting it Together
So, let's get down to work! you are first going to make a look-up table then, using a
relational field, add the look-up ability to your Contact table.
Create a new table and name it "Type of Title". Using the words "Type of", at the beginning of a look-up table is a way to group all the look-up tables together, as well as cluing you in that this table is used as a look-up. Some people also use the following mnemonics as part of their naming conventions: LKxxx, LkUxxx, luxxx, or LUxxx so for example,their tables would be named LKTitle, LkUTitle, luTitle, LUTitle.
Drag the "Text" label across from the left hand side to the right, and tap the "Create" button.
Your "Type of Title" table should have one field named "Type", and the properties are as follows:
![]() | the field "Type" is "text", |
![]() | "Display as" is "Capitalised", and |
![]() | the "Maximum length" is "5", since you are using abbreviations. |
The attributes should have the following ticked:
![]() | Allowed to Change, |
![]() | Allow zero value, and |
![]() | Search when Finding. |
Then complete the "List view" for the "Type of Title" look-up table as below:
When you are done creating the table and filling in the information, close it and then switch the contacts table. That's it, you have made your first look-up table - easy wasn't it!
Unfortunately you can not change data types for existing fields and thus you can not change your "Title" field, in the Contact table from text to relational. To use a relational field in lieu of the existing text field means you will have to replace your "Title" field, but for the fun of it, let's just rename your "Title" field, so you can look at it after you add the new field.
Via the Schema, double tap on the "Title" field in the Contacts table and rename this field to "Title Old". Now you can build your new relational field:
![]() | Add a field to the Contacts table. |
![]() | Name the new field Title and select the field type as "Relational". |
There should be 2 new properties enabled on the screen that you have never used before: "Related Table" and "Related Field".
Set the "Related Table" to "Type of Title" and the "Related Field" to "Type" (there is no other choice since you only have one field in the table).
Then tap "OK" and you can now set the remaining field properties.
On the Attributes tab set the following:
![]() | Allowed to change, |
![]() | Allow blank field (because you also have web sites and companies, this is a good idea since these entities don't have a title, but if you don't set "allow blank field" this means you mush select something from the list), |
![]() | Display as Text and also |
![]() | set the format as "Capitalised". |
On the Display tab you now have to fill in what field you want to display. (This should look familiar)
Insert the "Type" field (it is the only field you can select at the moment) in to the expression area and tap "OK". IF you had more than one field in the related table (remember it does not have to be a look-up table) you can use the expression evaluator to concatenate several fields together, which could then be displayed, or you could display some text, based upon a value in one of the related fields.
If you now look at the Schema at your "Title Old" field you will see that the icon for the field type is 2 letters, now look above your "Title Old" field, and you should see the icon for your new "Title" field. The icon should look like 2 arrows pointing to each other (use the zoom to make it bigger, if you can't see it). This is the icon for a relational field.
Well, you now have a relational field in your table, but if you open the contact table in card or list view, you will see - or not see, as the case may be - that the new "Title" field is not visible. Add the "Title" field to the view of your choice. I added mine to the card view, just under the "Title Old" field, so I can see them at the same time.
All right, here we go! Let's test your relational field on our old friend Charles Dickens. If you look at his record, you can see, his old title was Mr (no full-stop). Now, tap on the "Title" field (the new one) and press <Tab> so you can edit the field, now press <Tab> again, to bring up the list of values you can select from. This list comes from your look-up table.
Notice that the title of the box is "Unique Values", that is because PowerBase is displaying a unique list from the look-up table. For example if "Ms." was in the table twice, it would only appear here once. From the list, select the entry for Mr. and then save the change to Charles. Wasn't that easy?
Now go to the record for Emily Bronte, enter edit mode and type Ms (no full-stop) and try to leave the field. Did you get a message box with the following text: "Do not understand entered data"? PowerBase is telling you that the value you typed does not exist in your "related" look-up table. Press the continue button and retype the entry as Ms. (with the full-stop), you should now be able to save your entry. If you download Sample Address Book (10), you can finish the job off from this point.
Conclusion
As I indicated at the start of this lesson, using a look-up table to provide the range of
acceptable values for a list guarantees consistency, you cannot enter a value in the field
that does not exist in the look-up table. The way to use a look-up (secondary) table in
primary table is via the relational field type. Also remember that the information
displayed in via a relational field does not have to be from a secondary table, it can be
from a primary table. As you build your Contact Database you will be using relational
field to display information from other primary tables as well as secondary (look-up)
tables.
See you next time.
© LJKern and FoxPop 1999