Developing a Database Application:
The dBASE Plus Tutorial
Ken Mayer, Senior SQA Engineer, dBASE Inc.
Michael Nuwer, dBASE user
|
Developing a Database Application:
|
|
Creating The DataModules |
The goals for Phase III of the tutorial project are:
Before Starting This Phase. It is very important that before you begin working on this phase you make sure that the "Look in:" combobox is pointing to the "dBASEtutorial" folder, not to the "dBASEtutorial\Tables" folder (a step that was included at the end of the previous phase, but you may have missed it). Select the "Data Module" tab in the Navigator and make sure you are pointing there now.
dBASE has taken the world-class object model that was created for Visual dBASE 5.x, and moved it over to the tables and the databases. What this means is that you can now use tables, records, and fields as objects, with each having it's own set of properties, events, and methods. This gives the developer a lot of control over the code, and allows the developer a lot of the object oriented power and reusability that you can get from other objects in dBASE Plus.
So, what are all these objects, and how are they related? Before we get into the "in-depth" discussion, let's step back and take a look at the bigger view of the data objects in dBASE Plus.
Most of these objects are containers. This means that they can hold other objects. An example that you will see a lot of, is the query object. This object holds a rowset object, so it is a container for the rowset object. Unlike some containers, there can be only one rowset object for each query object. The rowset object is a container for a fields array. The fields array holds pointers to each field object that is contained in the rowset. Hence, you might draw out a diagram something like the following:
|
The rowset object would have a parent, which is the query, the fields array would have a parent, which is the rowset object, the field objects have a parent, which is the fields array.
Note that you cannot define a rowset object independent of a query object, but you can define a field object independent of a fields array or rowset (but while you can use it outside of the rowset, it's kind of tricky unless you add it to the fields array of a rowset).
Now, to make things more interesting, a database object refers to a database Alias. A database, by its very nature, contains tables. In a way, the database object is a reference to an object -- the database itself. However, that is rather simplifying matters. You should normally use a database alias for reasons that will be discussed when we get to the database object.
A datamodule can be used to contain database objects and query objects. Rowsets are contained automatically by queries, so while a datamodule contains the query, it doesn't directly contain the rowset.
So, how confused are you now? Hopefully not very. All of this is leading to something.
Each of the data objects, as noted elsewhere in this document, has properties, events and methods.
Properties
Properties are the attributes of controls and include among others identification,
and other properties. A property setting can be changed programmatically as
well as in the designer surfaces (form, report, datamodule). The setting is
normally a single value, for example, a character string, number or reference
to another object.
Events
Events are something that controls respond to, a mouse click, a control getting
focus, a change in the control's value, etc. Events are always "firing" provided
the event is occurring, however, in order to have something happen, you have
to "hook" code to the event. (Windows itself is really just a big "event" handler
-- it does not do anything until some event is fired.)
Events whose names begin with ON, such as onOpen, occur after the action to which they refer. Thus, the onOpen event occurs after the query is opened. The second thing is that events that begin with CAN, such as canClose, must return a logical value that will determine whether the indicated action may proceed. So, a query's canClose event must return true before the query may close. Returning a value of false will prevent the query from closing. Failure to return a Boolean value may yield unpredictable results.
Methods
Methods are code that performs an action. Objects have a number of built in
methods that are called through the control, i.e., form.rowset.next ().
One thing you should remember is that, if you use the built-in methods as a programming hook to insert your own code (this is called over-riding the method), you will nearly always want to call the original built-in method before, during, or after your code. (This would be achieved by entering: SUPER::methodname() into the code.)
Database
The database object is used when you use Database Aliases to handle your databases.
An alias is absolutely required when working with SQL Server tables (such as
Interbase, Oracle, etc.), and is optional but useful when using local tables.
We are using a database alias in this project. There are a number of good reasons why we do this. Here are a few:
Creating a Database Object Reference
You can create a database object reference easily enough. There are only a few
things you must set:
// for local tables d = new Database() d.databaseName := "MyAlias" d.active := true
Query
The Query object is the most often seen data object. The query object is used
to reference the individual tables of your database, and is required
if you are going to do any work with tables using the OODML.
As such, it is very important that you understand this one. It is the container of the rowset, which is where most of the data manipulation methods and events are, as well as the rowset being a container for the fields.
How Do I Use a Query?
We will keep coming back to this topic, but at it's most basic, and assuming
that you are starting with local tables, you need to instantiate the query,
assign the SQL property, and then set the active property to true.
q = new query() q.sql = "SELECT * FROM mytable" q.active = true
One thing that sometimes worries new users of dBASE Plus is that they "have to know SQL". No, you don't. The above command is the most SQL you absolutely have to know to do some very detailed applications in dBASE Plus. Most of what you might want to do is covered by properties, events, and methods of the rowset object, which we will get to presently.
The "*" in the SELECT statement is a wildcard -- it means "all fields".
The above three statements are all fine and dandy if you are creating a program that needs to manipulate data, but what if you want to use a form or a report?
It couldn't be more simple. Bring up a form in the forms designer, click on the navigator "Tables" tab, and drag the table you wish to use onto the form (or report) surface. You will see an icon used to represent the query object. This has the letters "SQL" on it. The designer surfaces automatically fill in the required information, although the code looks different if you examine the source code:
this.CUSTOMER1 = new QUERY()
this.CUSTOMER1.parent = this
with (this.CUSTOMER1)
left = 52.5714
top = 2.5
sql = "select * from customer"
active = true
endwith
The designers stream out the left and top properties so that every time you open the form, the query icon is in the same location. When you run the form, the query icon will not appear on the form -- it is there only during design mode.
The first statement creates an instance of a query object, but note that rather than "CUSTOMER1" you see "this.CUSTOMER1" -- "this" refers to the form. Note also that the parent property is being assigned for you, and again "this" refers to the form.
You could write the same code that was originally shown in a similar fashion in your program:
q = new Query()
with( q )
sql = "select * from mytable"
active = true
endwith
This is just as valid as the previous way of writing the code.
SQL SELECT Statements
If you examine any book on SQL, you will find that there are a lot of options
that you can use with the SQL Select statement.
As noted above, you do not really need to know a lot about SQL Select statements to use these data objects. However, you should know a few things -- particularly if you are familiar with SQL:
WHERE clauses work fine, but there are some caveats.
query.sql = "select * from parts where part_id > 1"
query.sql = "select * from parts where ;
date_field = '01/01/1999'"
// or
query.sql = [select * from parts where ;
date_field = ']+date()+[']
// etc.
The reason dates must be enclosed in quotes is to differentiate them from
mathematical operations. This allows the BDE to know that 04/15/1998 is a
date and not the literal value 4 divided by the literal value 15 divided by
the literal value 1998.
Read Only Queries
Local SQL (SQL statements used on local tables) will often generate read only
queries. The following will generate read-only queries:
Rowset
Once you have the query set and it is active, what can you do with it? Well,
not all that much. To work with the data, you have to work with the rowset and
field objects.
The rowset object is the real workhorse of the data objects in dBASE Plus. A lot of the functionality found in the old XBase DML commands and functions is now in the rowset object's properties, events and methods. We will explore some of this functionality shortly.
Fields Array
This is a very simple topic. The fields array itself is an array that is contained
by the rowset, and holds pointers to the field objects for the rowset.
What can you do with the fields array? Well, the fields array is how you address the fields. You can add or delete fields in the fields array (useful for calculated fields, a topic we'll get to later). You can find out how many fields are IN the fields array. There isn't much else.
As noted, this is fairly simple. The most important thing is that this is an associative array, which means that you can select a field either by a number (it's position in the list) or by it's name. You can do the following to find the value of a field:
? form.rowset.fields[ 1 ].value // or ? form.rowset.fields[ "fieldName" ].value
Rather than spend a lot of time here, let's look at the field object. There's a lot going on there.
Field
The field object is used to allow access to individual fields in a table, and
work with properties, events and methods to modify the behavior and value of
the field. Each element in the Fields Array is an object of the Field class.
And each field object corresponds to one of the fields returned by the query.
Some of the Field object's properties provide useful information -- like fieldName, length, or type. The value property reflects the current value of a field for the current rowset; assigning a value to the value property assigns that value to the row buffer.
The following code, for example, will loop through each of the fields in the query "q" and print the fieldName of those that are empty.
for i=1 to q.rowset.fields.size
cFieldName = q.rowset.fields[i].fieldname
if empty( q.rowset.fields[cFieldName].value )
? cFieldName
endif
next
Now that we've examined the major data objects in dBASE Plus let's take a look at using them a bit.
Most of the following is going to assume you are using forms (and/or reports, the ideas are the same for at least a lot of it), and so the syntax for the commands shown will assume forms, or in many cases the onClick event of pushbutton controls on forms. Keep in mind that the same types of things can be done programmatically.
Using a Table On a Form
After all that brouhaha, and detail, it sure sounds like this is going to be
complicated, doesn't it?
Actually, it couldn't be more simple. To use a table on a form, the simplest method is to open a new form in the designer, click on the navigator window in dBASE Plus, click on the "Tables" tab of the navigator, and drag your table to the form designer surface.
What happens is that dBASE Plus will place an icon on the form surface, which is used to represent a non-visual object (when the form is run, the user will never see this object). The icon will have the letters "SQL" on it -- this is a query object. In addition to placing the icon on the designer surface, dBASE Plus will set the SQL property, and the active property will default to "true".
If you are using a database alias, you will get, in addition to the query object, a database object as well. The form designer will automatically make the correct connections between the database object and the query object.
One thing that it is important to note is that the form itself has a property called "rowset". dBASE Plus automatically stores a reference to the rowset of the first query object placed onto the form in this property of the form.
Navigating Through Tables
Once you have a table on a form, and presumably some controls for fields, you
will, at the very least, need to allow your user to navigate through the table.
If you have set the indexName property of the rowset object, the table will be navigated based on whatever sequence is set in the expression of the index.
Navigation is done via methods of the rowset, which were discussed way up in the rowset section of this document. There are some things you should note:
When you write your own navigation code, here are some things to consider:
Editing
You can set a rowset to be not editable until the user explicitly decides to
edit (we find this to be a good idea -- it's very easy to accidentally change
something) by setting the rowset's autoEdit property to false (it defaults to
true). This has the effect of disabling all controls that are dataLinked to
the fields of the rowset.
If you decide to use the autoEdit property set to false, then you will need to provide a way for the user to edit the current row. This can be done with:
form.rowset.beginEdit()
and you can place this code in the onClick event for a pushbutton.
You should probably provide a "save" option and a "cancel" option by calling the rowset's save() and abandon() methods.
Deleting Rows
The OODML does not support the XBase "soft delete" (basically this is the ability
to continue to display a deleted row on the form) directly. It is possible to
do this via the BDE's API, and there is an example of this in the dUFLP library.
The reason for this is that the .DBF is the only table format that allows this functionality -- all others basically assume that a deleted row is gone. Don't panic, however -- if your user decides to delete a row, it is actually still in the table, but you cannot show it to the user, and you cannot (using the OODML) allow the user to recall (undelete) that row.
Finding Rows in a Table
There are several ways to "find" rows in a table. You can use "findKey()" and/or
"findKeyNearest()", you can use "beginLocate()" and "applyLocate()", and a lot
more.
Filtering Rows in a Table
As with "Finding" rows, there are quite a few ways to filter rows in a table,
including the "filter" property of the rowset, the "beginFilter()" and "applyFilter()"
methods, the "setRange()" method, and the "canGetRow" event of the rowset.
A datamodule is a container for data objects. The purpose of the datamodule is to set-up your data objects in whatever way that you might need in one place, so that if you need to re-use the same set-up (for example, you might have a set of tables set on a form, and need the exact same set-up for a report, or maybe for another form) you can, with no extra effort.
Another useful feature of datamodules is that you can have a custom datamodule which can be used for some basic setup, which then can have that setup inherited by the datamodules that are subclassed from it. We will be using a custom datamodule in the application we are creating which will have the database object on it. That is all that will be there, but this will be used for all of the subsequent datamodules that we need for this application.
In the tutorial project we will use datamodules for all our data access objects. At this point we just want to say that in any situation where you are working with more than one table, or you are setting up some fairly complex code (perhaps in canAppend events and such) you are better off using a datamodule and encapsulating all of that, so that if you need it in another form, you can save yourself a lot of work. Use datamodules a lot. They will make your life as a developer much easier.
dQuery is the dataModule designer tool and the center piece of dBASE Plus. It offers visual tools for modeling, entering and filtering data. It also generates reports, desktop applications, and web applications. Many of the dQuery tools are designed so that you can interact with your data. But there are also tools provided for the developer.
In this Tutorial, we will use dQuery to create our dataModules. We will not take advantage of the many filtering tools nor of the reporting tools.
For our application we will be using a database alias, which means that we will need a database object. Rather than creating this database object on each datamodule we can put the definition in a custom datamodule and inherit the properties in the inherited datamodules.
To create a custom datamodule, the easiest method is to go to the dQuery menu and select File | New | Custom DataModule.
This will refresh the design surface and open a open a new custom dataModule. We are now going to add a database object to the design surface. Be sure the "Drag & Drop" combobox has "Databases and Tables" selected. You should then see a list of all your database connections in the "Drag & Drop" listbox . Find dBASETutorial in the list and double-click it. This will add the database object to the dataModule.
Save the datamodule (File | Save), give it a name: BASE.CDM (you might need to type the file extension), and, if needed, change the folder location to C:\dBASESEtutorial.
If you open this custom datamodule in the source editor (press the F12 key), you will see code like:
class BaseCDATAMODULE of DATAMODULE custom
with (this)
left = -1
top = -1
endwith
this.dBASEtutorial1 = new DATABASE()
this.dBASEtutorial1.parent = this
with (this.dBASEtutorial1)
left = 21
top = 16
databaseName = "dBASEtutorial"
active = true
endwith
endclass
We will come back to this code later in this document, but for now you can close the Source Code Editor (but leave dQuery open).
One of the many things we will need to do in our application is to create forms for interacting with the data. In order to do this, we need to set up some datamodules that handle a lot of the details (rather than re-creating them each time we need them).
One of the forms we will create is a form for interacting with the Customer table. This table will need to do some lookups on the State table and the Country table. Rather than creating the code to do this in the form, we will do it in a datamodule so that if we need to generate, say, a report that lists the customers we will not need to re-create the lookups.
In dQuery select File | New | DataModule from the menu.
We want this new dmd to inherit the objects and properties from the base datamodule. Select "File" from the menu to do this. Then choose "Inherit New" | "DataModule." (If prompted, you do not need to save the current datamodule.) The Select Custom Datamodule dialog form will open. Choose "Base.cdm," and click the Open button. The new datamodule is now subclassed from the base datamodule.
Note: "Subclassing" is the technique of using a class as a "base" class -- creating a new class that inherits all of the properties, methods, and events of the base class.An example of this is creating a form in dBASE Plus (never mind "custom" forms) -- there is a "base" form class that is built in to dBASE Plus. When you create a new form, it is a subclass of the base form. It has all the properties, events and methods of the standard form in dBASE Plus.
Once you have subclassed an object you can modify its properties, events and even hook your own code into the methods of that class, without modifying the base class's definition.
Now you are designing a datamodule that is subclassed from the custom one we created earlier. You should see the database object (dBASEtutorial1). This object is inherited from Base.cdm. We are not going to change any properties of this object, but we could (these changes would only affect this datamodule, not all datamodules in the application). If you wanted to affect all datamodules in an application you would want to make changes in the custom datamodule.
Next we need to add the Customer query to this datamodule, so that we can set it up for use in the data entry form we will be creating later.
In the "Databases and Tables" list, you should see the dBASETutorial database connection (it should have an icon with a green "on" dot) and a list of the tables in our database. You may need to expand the treeview if you don't see the tables. Click on the "Customer" table and drag it to the design surface -- you will see a new object on the design form with the name Customer1 -- this is a query object.
The listbox that represent the Customer1 query object contains the fields that are included in this query object. In the bottom pane of the dQuery designer you see the query's rowset displayed in a grid. This query selected all the field from the Customer table so the query's field list and the table's field list are the same. This is not, however, necessary when you create a query. In fact, a query can contain a subset of fields from one table or it can contain fields from multiple tables. This feature is part of what makes the query object such a powerful tool. However, in the current datamodule, the Customer1 query is a replica of the Customer table.
We want a specific index to be active, which will be the "Names" index. To use this click on the query object and right click the mouse. Select the "Set Index (Key)" option. In the Select Index dialog form, click "Names" from the list of index tags and then click the "Set Index" button. That is all that is necessary to set the index.
Next we need to set the datamodule's rowset property so that it points to the "customer1" rowset. Right click on the Design Surface and select "Properties" from the popup menu (or press the F11 key). Be sure that the combobox at the top of the Inspector has only the word "Form". In the properties list, select "Rowset" and then select "customer1" from the dropdown list.
The rowset property of a datamodule defines it's controlling rowset. This will be useful when we create our forms. There are many coding statements that will reference the controlling rowset without needing to know the name of the query object. Close the Inspector.
Save your work by choosing File|Save, and name the file "customer.dmd" (you may need to type the extension).
As you can see, creating datamodules isn't that difficult, except that it helps to have an idea what you need to do.
The Inventory Datamodule will be similar in design to the Customer datamodule. This datamodule should be subclassed from base.cdm. Begin by creating a new dataModule (File | New | DataModule).
Next select "File" | "Inherit New" | "DataModule" from the menu. Choose "Base.cdm," and click the Open button. A new datamodule subclassed from the base datamodule is now in dQuery designer.
Next use the Drag & Drop list like we did with the Customer datamodule, and add the "Inventory" table.
Set the datamodule's rowset property to point to the "inventory1" rowset.
Click on the Inventory1 query object and right click the mouse. Select the "Set Index (Key)" option. Then set the indexName to "Description".
Save the datamodule as "inventory.dmd."
The Supplier datamodule will be set up nearly exactly like the customer datamodule.
You should subclass it from base.cdm (follow the steps given above). Add the "Supplier" table to the design surface.
Set the datamodule's rowset property to point to the "supplier1" rowset.
Select the query object and Set the indexName to "Company"
Save the file as "supplier.dmd."
The Invoice datamodule is going to be a bit more complicated and will require that you follow along carefully. We're going to use three tables for this one. The Customer table will be the datasouce for the primary query. We will use it as the "parent" query for the Invoice query, which, in turn, will be the "parent" query for the LineItems table (these are the individual items ordered by the customer). Together this datamodule will create a parent/child/grandchild type of relationship.
Begin by creating a new datamodule inherited from base.cdm.
Next, add the Customer table to the design surface.
Click on the dQuery design surface, bring up the popup menu and select Properties. In the Inspector find the rowset property and select "customer1" as the controlling rowset.
Next select the customer1 query, right click the mouse and select "Set Index". Set the index to "Names."
After that select the customer1 query again, right click the mouse and select "Inspect Rowset." Set the autoEdit property to false.
You now have the customer1 query configured. Next you will add the Invoice table
Drag the Invoice table to the design surface, and set the Index to "Customer ID." It is very important that this be the index, because that is how a match will be found against the Customer table. Don't forget to open the Inspector and set autoEdit to false.
Next select the Customer1 query object and click the "Customer ID" field. Click the left mouse button and, while holding it down, drag the field onto the Invoice1 query object. Let up on the mouse button. You should see an arrow from the Customer1 query object pointing to the Invoice1 query object. You have just used drag-and-drop to set a parent-child relationship between these two queries.
The third table you will use in this datamodule is the LineItem table. The LineItem table is designed such that we can have one or more line items per invoice. Drag this table to the design surface.
Set the Index to "Invoice ID" and remember to set autoEdit to false.
The Linking field between the Invoice1 query and the LineItem1 query is "Invoice ID". Select that field in the Invoice1 query object, than drag and drop it onto the LineItem1 query. Would you believe that this is all you have to do to set up a parent/child relationship?
Save the datamodule with the name "invoice.dmd."
When we use dQuery to set the parent/child relationship between two queries, there are two properties of the child rowset that are modified. The Invoice1 rowset has a masterRowset property set to Customer1. The second property is the masterFields, which identifies the key field in the parent rowset and must match the index used in the child rowset. When you use dQuery's drag and drop, these properties are automatically set in your datamodule.
Note: If using tables that are not local (.DBF or .DB) you would need to use the query object's masterSource property instead of the masterRowset and masterFields properties of the rowset object. This is discussed in detail in various of the Knowledgebase documents.
Now that we've created the basic datamodules that will be used in our application, let's consider a few additional elements that can be added to a datamodule. The first thing we must do is ensure that the user of our applications enters a customer's last name and postal code when a new row is added to our system. It is not uncommon for a user to start appending new information, but then get called way by the phone (or any number of other office distractions). In other words, we want the last name and postal code to be required fields.
If either field is blank, we want to alert the user that the data is missing and that it must be entered before the row can be saved. If the user does not have a last name or a postal code to enter they should abandon the data entry until that information is known.
There are two main types of data validation that can be added to a query object. The first is field-level validation. Each field object in the rowset's field array has a canChange event. Each time the field value changes, this event fires and validation code will run if its been coded. The canChange event fires when the user tries to move out of a field to another field or to another object (like a lookup list).
This event should be used with great caution, because there are several drawbacks with field-level validation. The primary drawback is that if the user doesn't know what to enter it is difficult to escape from the entryfield and close the form.
The most appropriate place to validate data entry is at the row level. We can use the rowset's canSave event to check the values of any field before the data is saved. The row-level canSave event fires only when the user tries to save the record. This event offers the developer greater flexibility for guiding the user, or gracefully abandoning the modifications.
We are not going to use extensive validation in this application. We will choose a few examples that illustrate how this is typically coded into an application.
To make the customer's last name and postal code require fields, we will begin by re-opening the customer.dmd Datamodule. The easiest way to do this is to choose File | Open from the menu. In the Open datamodule dialog, choose the customer.dmd datamodule.
Bug: At the time of this writing there is problem in dQuery (version 1206) that will cause errors in the following steps. To avoid the errors, you should close dQuery, reopen it (File | dQuery), and then reopen the customer.dmd datamodule.The problem arises when you 1) open a datamodule while another datamodule is already open, and 2) use the Inspector to create an event handler. It is likely that this bug will be fixed in a future version of dQuery, which means this bug might not be a problem for you.
dQuery will open the datamodule and you will see a list box that represents the customer1 query object. Click your mouse inside that query object and click the right mouse button. In the popup menu, select "Rowset Properties". This will call up the Inspector.
Select the Event tab in the inspector and locate "canSave" in the list of events. Click this option and note that a wrench icon appears at the right. Click the wrench. The Source Editor comes up with the cursor positioned inside a function. The code that we will enter into this function executes each time the user attempts to save the customer1 rowset. This function must return a boolean value that controls whether dBASE Plus will or will not save the row.
function rowset_canSave
local cErrors, bRetVal
cErrors = "" // String for errors
if empty( this.fields[ "Last name" ].value )
cErrors += "- LAST NAME cannot be blank" + chr(13)
endif
if empty( this.fields[ "postal" ].value )
cErrors += "- ZIP CODE cannot be blank" + chr(13)
endif
if "" # cErrors
msgbox( "Can't save current entry because:" + chr(13) + ;
cErrors, "Bad entry", 48 )
bRetVal = false
else
bRetVal = true
endif
return bRetVal
Save and close the source editor (Ctrl+"W"). Close the Inspector. After creating the function for canSave, we can test to see if the code works in the dQuery interface. On the toolbar click the "Add New Row" button. A blank row will appear in the dQuery Live Data window. Select the "First Name" field, enter your first name and click the "Save Changes to Row" button on the toolbar. The "Bad entry" message box should popup. (If it doesn't popup, you should check your code.)
If user mistakes are caught when the data is being entered, we will save a lot of time later when the data is being manipulated. If we do not validate data entry we might find that a customer's last name does not print on one of our reports. We must then go back and locate this customer's information, find their actual last name, enter it into the table, and reprint the report. Which can take a lot of time.
The LookupSQL property is an often touted feature that really confuses some people.
What exactly is "lookupSQL" and why should you even care? How many times have you tried to use proper "normalization" for your data, to the point that you have a lookup table with a code field and some values associated with the code in other fields, and had to write the program code to get your primary table to interact with the lookup table?
Isn't that a lot of work? Loading arrays to display data in comboboxes, scanning through the data to find the proper code and storing that value in the primary table ... all of that can be a lot of code.
Not any more! LookupSQL is a feature that will do this automatically (even if it's not as complete as some of us would like).
In the sample database that we are using for this project, the customer table has a field called STATE ID, which is a two character field. This stores, as you might imagine, the standard US two character state codes (CA = California, etc.).
For your users, however, you may wish to display the full state name. To do this using the OODML of dBASE Plus, what you need to do is use the lookupSQL property (the steps are below) and enter a simple SQL Select statement to select the fields you need in that table. (The lookupSQL property is a property of the field object.)
For this to work properly, you must select the code field in the lookup table as the first field (the type must match that of the field you will store it into), and then the field you want to display. If your lookup table is designed that way in the first place, you could simply use:
select * from lookuptable
Let's add lookupSQL to our datamodules. First, open Customer.dmd into dQuery (unless it's already open). In this datamodule we need to set lookups for the "State ID" and "Country ID" for the customer, which will allow us to have dBASE Plus automatically display the actual States and Countries associated with the two character codes that are stored in the Customer table.
Before we do this lets look at the data in the customer table. In the lower pane of dQuery scroll the Customer1 data grid to the right and locate the "State" and "Country" fields. You will see that the data contains two character strings for each row. Now lets set the lookupSQL
Click on the Customer1 query object and right click the mouse. Select the "Query Properties " option. Make sure that the customer query ("form.customer1") is what is displayed in the inspector (use the combobox at the top of the inspector to select it). Also make sure the Properties page is selected. Look down the left hand column of the Properties tab and click on rowset. To the right you should see the word "Object" and an "I" button (the "I" is for "inspect") -- this means that if you click it, you want to inspect the object that is referenced here. Do so. The inspector will now be viewing the properties of the rowset object (form.customer1.rowset).
Note: Throughout the tutorial we use the term "drill down" when we discuss using the inspector. What this means is to click on an object reference in the inspector (such as above, we selected rowset) and then click on the "Inspect" button (the button with the letter "I" on it). This moves you through the object hierarchy to get to an object that is contained by another object. (The query contains the rowset.)When we use the term "drill down" we are referring to this process. So if we suggest you "drill down to the fields array", it means click on the fields object in the inspector, and click on the "Inspect" button.
Next, locate the fields object and the "Inspect" button. This will set you to inspecting the fields array (form.customer1.rowset.fields).
Select the "State ID" field, and click on the "Inspect" button in the inspector. (You may need to click the "+" and expand the "Array Elements".) This will now bring the "State ID" field into the inspector and we are examining the properties of this field. Click on lookupSQL and type:
select * from state order by state
and make sure you press the <Enter> key. If you set this property correctly the value property of the "State ID" field will show something much larger than two characters on the form (this is called "data morphing" -- using online help you may want to look up the terms "morphing" and "lookupSQL"). In addition we are ensuring that the display is alphabetical by the "state" field with the "order by" clause. (Normally, we will not be using the "order by" clause -- in some cases with local tables this creates a "read only" query. In the case of a lookup, this is not a problem as the lookup is, by it's very nature, read-only.)
We need to do this again for the "Country ID" field. However, we have drilled down to the field object for "State ID". How do we move back up? Find the parent property, which says "object" -- click on the "Inspect" button, and we're back at the fields array. (You can also use the "Left Arrow" button at the top of the Inspector -- this takes you to the previous object inspected.)
Select the "Country ID" field, click the "Inspect" button, select lookupSQL, enter:
select * from country order by country
and press <Enter>. Close the Inspector with the "x" button.
Now lets see what has happened. In the lower pane of dQuery scroll the Customer1 data grid to the right and locate the "State" and "Country" fields. You will see that the data contains the full names rather then the two digit codes. (You may need to resize the columns to refresh the display. This is done with the mouse by dragging the column divider in the grid's header.)
Save this datamodule.
Next, open Supplier.dmd into dQuery. Set the lookupSQL property for both the "State ID" and "Country ID" fields the same as the customer datamodule. (Drill down to the fields array, click in the individual field and drill down to it, click on the lookupSQL property, and enter the appropriate SQL select statement.)
Save this datamodule.
The next lookupSQL we need is in the Inventory datamodule. So open Inventory.dmd into dQuery.
Click on the Inventory1 query object and right click the mouse. Select the "Inspect Query" option.
select supplier.'supplier id', company from supplier order by company
and press <Enter>.
This particular lookup is selecting specific fields to use because if we didn't we would end up displaying the wrong field. We need the company name to appear on the Inventory form, not the Contact in the company, which is what would appear if we were not specific (the lookupSQL property uses the fields in the sequence of the table if you do not specify the fields -- the first field in the supplier table is the "supplier id" field, but the next field is "contact" -- we want to display the "company" field). In addition, we are specifying "supplier.'supplier id'" because the fieldname has spaces in it -- it appears this is the only way to get the correct fieldname to work using a SQL select statement.
The final lookupSQL we need is in the Invoice datamodule. Open Invoice.dmd into dQuery.
In this datamodule we need to set a lookup on the "Item ID" field to look in the inventory table so that we display the description field. Go to the fields array, and select the Item ID field, and drill down. Select the lookupSQL property and enter:
select inventory.'item id', inventory.'description' from inventory
and press <Enter>.
Save the datamodule.
Calculated fields are rather important in a lot of applications. These are simply fields that display a calculation or a value not in the table for the user (some folk think of a calculated field as specifically being aimed at math, but combining two fields into one is considered to be a calculated field). The user cannot directly interact with them, because they are, by their nature, read-only -- they are not directly datalinked to a table. (The user can actually edit these unless you set the readOnly property on the field, but it won't make a difference to the table itself.)
To create a calculated field, you must create an instance of a field object, set some properties, and then add the field to the fields array for the rowset. If set properly, as the user navigates through the table, this field will be calculated (using the beforeGetValue event), and the results will be displayed on the form (or report).
The following is a simple example of creating a calculated field to display a "full name" from first and last name fields in a table. Keep in mind that any valid dBASE Plus expression will work. This example assumes that the field object is being added in a query's canOpen or onOpen event:
// instantiate the field object
f = new Field()
f.fieldName := "Full Name"
f.readOnly := true
// "this" in the following statement refers to the
// field object (important information)
f.beforeGetValue := {|| trim( this.parent["first name"].value ) ;
+ " " +this.parent["last name"].value }
// "this" in the following statement refers to the query:
this.rowset.fields.add( f )
It is very important that you use the beforeGetValue event to actually perform the calculation, or the value will not update as you navigate through your table. If you assign the "value" property instead, it will display the value assigned for the first row seen, and not update as you navigate.
You can have multiple calculated fields for a rowset, you would just want to make sure that each had a unique fieldName property.
At this point in the tutorial we will use a simple calculated field to display the customer's "full name" from first and last name fields in the Customer table. Later in this tutorial we will need to create some additional calculated fields, so let's write a single method for creating these field objects. We will put this method in the custom datamodule called Base.cdm. This way, all our standard datamodules will have access to the method.
So open Base.cdm into dQuery. You will recall that this custom datamodule contains the database object. We are now going to add a method to this class.
Use the Designer popup to bring up the source editor. Then move the cursor to the bottom of the class constructor code.
A Class is a definition of an object. As Alan Katz once pointed out: "Many beginners mistake a CLASS for a program or procedure. A CLASS is NOT executable code. It is a blueprint only. Just like a real blueprint, you can't live in the house represented by the blueprint until you build it." The Class Constructor Code is part of the "blueprint" for an object. It includes all the properties and their values. It does not include the methods (or functions) of the class. In the case of a container object, like the form or datamodule, the Constructor also includes the definition of all objects it contains -- like a database object or a query object. A method is the code that executes the behavior of an object. Methods are functions that belong to an object.
In the present case, there are no methods in our base datamodule class, so lines 2-17 are the class constructor code. You must move the cursor so that it is below the last "endwith" and above "endclass". This is where we will insert a new method.
Go to the "Method" menu, and select "New Method ...". We are going to add a method into the datamodule that will be "unattached" to any specific event. Change the statement "function Method" to "function calcField(cName,nLength)", and add the code below (the "function" statement and the "Return" statement should already be there, but you will want to modify them so that they look like what is shown below):
function calcField(cName,nLength)
local oField
oField = new field()
ofield.fieldname := cName
oField.length := nLength
this.rowset.fields.add(oField)
return
This method can be used to create a calculated field within any datamodule that is subclassed from Base.cdm. (Many thanks to Michael Joyce for showing us this "goodie.")
We can now save the BASE datamodule, close the Source Code Editor, and return to the Customer datamodule to create the Full Name field.
Open Customer.dmd.
Caution: You may need to close dQuery, reopen it, and then open customer.dmd. (This refers to a bug noted earlier in the document.)
Select the Customer1 query object.
Right click the mouse and select Query Properties.
The Inspector will come up. Click the Events tab (be sure "form.customer1" is the object being inspected).
Click the onOpen event and then click the wrench at the right.
Enter the following code into the customer1_onOpen function:
class::calcField("FullName",30)
Bug: Be sure there are no spaces in the field name. A space in the field name can cause problems later when the beforeGetValue method is created. If you create a beforeGetValue method for a field with a space the designer will write:function Full Name_beforeGetValue returnSince a function name cannot contain a space, the above method will cause an error. To avoid the problem use field names without spaces for your calculated fields.
This line will call the calcField method and pass "FullName" as the field name and 30 Characters as the field length.
Save this datamodule. We now need to reopen the CUSTOMER.DMD datamodule in order to initialize the new field. To do this, create a new datamodule (File | New | Datamodule), then reopen CUSTOMER.DMD.
Next we will use the beforeGetValue event to actually perform the calculation.
Select the CUSTOMER1 query object.
Right click the mouse and select Rowset Properties.
We need to "drill-down" in the inspector to the new field object. So find "Fields" in the properties list and click the "I" (Inspect) button. In the Fields Array find the "FullName" object (this is our calculated field) and click the "I" button.
Next click the Events tab and then select the beforeGetValue event.
Click the wrench button and enter the following code:
function FullName_beforeGetValue
local a
a = trim( this.parent["first name"].value ) + " " ;
+this.parent["last name"].value
return a
For those who like to economize on their code, the above method could be alternatively written as:function FullName_beforeGetValue return trim( this.parent["first name"].value ) + " " ; +this.parent["last name"].value
You can now look at the results of the calculation in dQuery's lower pane. Scroll to the far right of the data grid. The last column should be labeled "FullName" and the data should read as first and last name combined.
Now save the Customer datamodule. We are done with creating our first calculated field.
Ok, what you just did was set up re-usable modules that can work for both forms and reports, and have, in some cases, lookups defined (which in earlier versions of dBASE took a lot of code). In one case you have a set of three related tables set up ... not too shabby for a small amount of work. The best part is the "reusable" part. Most of these datamodules will work for forms and reports (and labels), and while this application will not need them for this, we could use the same datamodule on different forms.
The other feature that shows up through out the product (dBASE Plus, that is) is the ability to create a datamodule (or form or report) and inherit from that, which gives you true object orientation. (You do not have to use custom datamodules, forms, or reports, that is just the main way of doing things. All datamodules, forms, reports and other objects can be subclassed, not just custom ones.)
Last Modified: November 11, 2002
The Legal Stuff: This document is part of the dBASE Plus Tutorial created by Ken Mayer and Michael Nuwer. This material is copyright © 2002, by Ken Mayer and Michael Nuwer. dBASE Plus is copyrighted, trademarked, etc., by dBASE, Inc., the BDE (Borland Database Engine) and BDE Administrator are copyrighted, trademarked and all that by Borland, International. This document may not be posted elsewhere without the explicit permission of the authors, who retains all rights to the document.