Developing a Database Application:
The dBASE™ PLUS Tutorial

Ken Mayer, based on work by Michael Nuwer and Ken Mayer


Phase III: Creating the Data Modules


  Back to the tutorial menu     Back to previous part of tutorial: Creating the Database   Proceed to the next part of the tutorial: Creating Custom Components 

Goals and Objectives

The goals of phase III of this tutorial are:

Additional Reading

Data objects? What in the world are Data objects?

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:

Query Object

The query object is a representation of an SQL statement that describes a query. It encapsulates the rowset object.

Rowset Object

The rowset object contains data that results from an SQL statement in a query object. It encapsulates the fields array object.

Fields Array Object [Field1, Field2]

The rowset's fields array contains a Field object for each field in the row.

Field Object 1 Field Object 2

Each field object corresponds to one of the fields from a table. It contains properties that describe the field.

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 data module can be used to contain database objects and query objects. Rowsets are contained automatically by queries, so while a data module 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.

A Detailed Breakdown of Each Data Object

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, data module). 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 overriding 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 ("User BDE 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 its 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.DBASETUTORIAL1 = new DATABASE(this)
            with (this.DBASETUTORIAL1)
               left = 1.0
               databaseName = "DBASETUTORIAL"
               active = true
            endwith
         
            this.CUSTOMER1 = new QUERY(this)
            with (this.CUSTOMER1)
               left = 5.0
               top = 2.0
               database = form.dbasetutorial1
               sql = "select * from CUSTOMER.DBF"
               active = true
            endwith

The designer streams out the database object automatically, as the table is in the database "dbasetutorial". Note that in the query the code that is streamed out includes a reference to the database. This is how OODML works -- we have to have references as shown.

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. In dBASE™ PLUS 11 there appears to be a bug where dragging the table to the design surface the way we did, the database object is not streaming out a 'top' property ...

The first statement of the query 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:

            d = new database()
            with( d )
               databaseName = "mydatabase"
               active = true
            endwith
            
            q = new Query()
            with( q )
               database = d
               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:

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

Getting Started with Data Objects

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 two icons on the form surface, which are used to represent non-visual objects (when the form is run, the user will never see these objects). One icon will be the database, the other will be the query (has the letters 'SQL' under the image). In addition to placing the icon on the designer surface, dBASE™ PLUS will set the database property to point to the database object, the SQL property, and the active property will default to "true".

If your table is not one that is associated with a database then the designer will not stream a database object out, or will there be any connection to a database. Since our tables are set up to work with the "dBASETutorial" database alias, the designers will recognize that alias and stream out the appropriate database object as needed.

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 handler 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.

What Is a Data Module?

A data module is a container for data objects. The purpose of the data module 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 data modules is that you can have a custom data module which can be used for some basic setup, which then can have that setup inherited by the data modules that are subclassed from it. We will be using a custom data module 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 data modules that we need for this application.

In the tutorial project we will use data modules 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 data module and encapsulating all of that, so that if you need it in another form, you can save yourself a lot of work. Use data modules a lot. They will make your life as a developer much easier.

In dBASE™ PLUS 11 there is a new Data Module Designer tool which includes aspects of dBASE called "dmAssist™". We will not be working with the "dmAssist" options as we will be doing things in other ways. The new designer offers visual tools for modeling, entering and filtering data.

A Custom Folder

In order to keep our files just a bit more organized, we are going to store all of our custom files (like Custom Data Modules, below) in a folder called "custom". If the folder does not exist in your workspace, open Windows File Explorer and follow below:

You can close the File Explorer for now.

Source Code Aliases

The purpose of the custom folder you just created is to keep the files for the tutorial organized. In a more complex application you might actually have several folders for different types of files:

Source Code Aliases only work in the development environment, although there is a related concept called Dynamic External Objects (DEO) that can be used for a deployed application. The goal of these aliases is to be able to store your source code in an organized manner, but to access it, you then have to first create an alias that dBASE recognizes, then use it when opening the files in your code.

For our custom folder, we are going to create a Source Code Alias to store all of the custom files for the project and access them in the same way. Since we have the folder, let's create the Alias:

A Custom Data Module

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 data module we can put the definition in a custom data module and inherit the properties in the inherited data modules.

To get started, we need to open the Data Module Designer. In the Navigator, click on "Data Modules", and notice that there are two icons: "[New Data Module]" and "[New Custom Data Module]". Since we want a new Custom data module, double-click "[New Custom Data Module]". You should see:


We want to use tables in the "dbasetutorial" database, so double-click that in the left side of the designer. The screen will now display the tables for the database, and it will add both a Session object and a Database object.

Save the data module (File | Save), change the folder to the "custom" folder, and give it a name: "base".

Exit the Data Module Designer, by clicking the 'x' in the titlebar on the right.

If you open this custom data module in the source editor (using the Navigator, select the "custom" folder, then right click the custom data module, and select "Open in Source Editor"), you will see code like:

            ** END HEADER -- do not remove this line
            class baseCDATAMODULE of DATAMODULE custom
               this.DBASETUTORIAL1 = new DATABASE(this)
               with (this.DBASETUTORIAL1)
                  left = 19.0
                  top = 135.0
                  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. If you did the above, use the Navigator "Look in:" to move back to the main "dBASETutorial" folder.

A Data Module for a "Customer Data Entry Form"

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 data modules 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 data module so that if we need to generate, say, a report that lists the customers we will not need to re-create the lookups.

In the Navigator, double-click the "[New Data Module]" icon.

We want this new dmd to inherit the objects and properties from the base data module. Select "File" from the menu to do this. Then choose "Inherit New" | "Data Module.". (If prompted, you do not need to save the current data module.) The Select Custom Datamodule dialog form will open. Choose the "custom" folder, and then "Base.cdm," and click the Open button. The new data module is now subclassed from the base data module. We can tell this because we see the session and database objects from "Base.cdm".

Now you are designing a data module 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 data module, not all data modules in the application). If you wanted to affect all data modules in an application you would want to make changes in the custom data module.

Next we need to add the Customer query to this data module, 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 checkmark) -- and a list of the tables in our database. You may need to expand the treeview if you don't see the tables (click the "+" next to the database name). 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 represents the Customer1 query object contains the fields that are included in this query object. In the bottom pane of the 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 data module, 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, right-click on the query object. Select the "Set Order" option.


In the Select Index dialog form, click "Names" from the list of index tags and then click the "Set Order" button. That is all that is necessary to set the index.

If you look at the grid at the bottom of the screen you will see the data is now sorted on the last names, and if you scroll through the data (don't change any -- the data is live here!) you will see that if there are duplicate last names, the data is also sorted by first name ...

The rowset property of a data module defines it's controlling rowset. Oddly, unlike with a form, this is not automatically set. If the inspector is not showing, click the "View" menu, then select "Inspector" (or press the F11 key). Make sure the inspector is pointing to "form" (in the combobox at the top of the inspector), find the rowset property, select "CUSTOMER1".

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.

Save your work by choosing File | Save, make sure that you are in the "dBASETutorial" folder, not the "dBASETutorial\custom" folder, and name the file "customer.dmd" (you may need to type the extension).

As you can see, creating data modules isn't that difficult, except that it helps to have an idea what you need to do.

Close the Data Module Designer.

In order to see the Source Code Alias in action, right click the customer.dmd file in the Navigator, and select "Open in Source Editor". Notice the code at the top of the Data Module:

            ** END HEADER -- do not remove this line
            //
            // Generated on 01/09/2017
            //
            class customerDATAMODULE of BASECDATAMODULE from ":DT_Custom:base.cdm"
               with (this.DBASETUTORIAL1)
                  left = 19.0
                  top = 135.0
               endwith

Note the bold text above. First, because we are basing this data module on a custom data module, the syntax is a little different, and note inside the quotes we see :DT_Custom: -- this is our Source Code Alias. This tells dBASE that when we work with the data module to replace the alias at the time the file is opened with the path referenced by it when we created it. If you were to go to the Properties dialog and change the path, that is where dBASE would then look for the file "base.cdm".

For each of the data modules we are creating for this project, you will want to reference the custom data module in the "custom" folder when creating it, and make sure you save the data modules back out to the main folder.

Create the Inventory Data Module

The Inventory Datamodule will be similar in design to the Customer data module. This data module should be subclassed from base.cdm. Begin by creating a new dataModule (Navigator, double-click "[New Data Module"]).

Next select "File" | "Inherit New" | "DataModule" from the menu. Choose the "custom" folder and then "Base.cdm," and click the Open button. A new data module subclassed from the base data module is now in the Data Module Designer.

Next use the Drag & Drop list like we did with the Customer data module, and add the "Inventory" table.

Right-click on the Inventory1 query object. Select the "Set Order" option. Then set the indexName to "Description".

If the inspector is not showing, click the "View" menu, then select "Inspector" (or press the F11 key). Make sure the inspector is pointing to "form" (in the combobox at the top of the inspector), find the rowset property, select "INVENTORY1".

Save the data module as (making sure you are in the main folder, not the "custom" folder) "inventory.dmd" and exit the Data Module Designer.

Create the Supplier Datamodule

The Supplier data module will be set up nearly exactly like the customer data module.

You should subclass it from base.cdm (follow the steps given above). Add the "Supplier" table to the design surface.

Select the query object and set the indexName to "Company".

Be sure to set the data module's rowset to "SUPPLIER1".

Save the file (again make sure you are in the main folder, not the "custom" folder as "supplier.dmd" and exit the Data Module Designer.

Create the Invoice Datamodule

The Invoice data module 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 datasource 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 data module will create a parent/child/grandchild type of relationship.

Begin by creating a new data module inherited from base.cdm (see instructions above).

Next, add the Customer table to the design surface.

Next, right-click the "customer1" query and select "Set Order". Set the index to "Names."

After that select the customer1 query again, right-click the mouse and select "Rowset Properties". 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 data module 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 data module (making sure you are saving in the main folder, not the "custom" folder) with the name "invoice.dmd" and close the Data Module Designer.

When we use the Data Module Designer 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 the designer's drag and drop, these properties are automatically set in your data module.

Data Validation

Now that we've created the basic data modules that will be used in our application, let's consider a few additional elements that can be added to a data module. 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 it's 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. In the Navigator simply double-click the data module and it will open in the designer.

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 if it is not open and will set it to be pointing at the rowset object of this query.

Select the Event tab in the inspector and locate canSave in the list of events. Click this option and note that a wrench (or spanner) 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, or event handler, 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 += "- POSTAL 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. You may need to save the data module (<Ctrl>+S) just to be sure that the changes have been saved.

After creating the function for canSave, we can test to see if the code works in the designer interface. On the toolbar click the "Add New Row" button. A blank row will appear in the grid window. Select the "First Name" field, enter your first name in the appropriate field 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.) Click the "OK" button on your dialog, then click the "Abandon changes to row" button to get rid of the blank row.

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.

LookupSQL

The lookupSQL property of the field object 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 data modules. First, open Customer.dmd into the designer (unless it's already open). In this data module 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 let's look at the data in the customer table. In the lower pane of designer 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 let's set the lookupSQL properties.

For more information on lookups, click here: Supplemental: Lookups. This is a "not-required" but hopefully helpful look at working with lookups in dBASE.

Right-click on the Customer1 query object. Select the "Table and 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).

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 its 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.

Unfortunately the current version of the Data Module Designer does not refresh the grid easily, it is easier to close the Designer and re-open it.

Now let's see what has happened. In the lower pane scroll the Customer1 data grid to the right if needed and locate the "State" and "Country" fields. You will see that the data contains the full names rather than 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 data module and close the designer.

Next, open Supplier.dmd into the Data Module Designer. Set the lookupSQL property for both the "State ID" and "Country ID" fields the same as the customer data module but without the "order by" clause -- just use "select * from <tablename>"). (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 data module, and close the designer.

The next lookupSQL we need is in the Inventory data module. So open Inventory.dmd into the designer.

Click on the Inventory1 query object and right click the mouse. Select the "Inspect Query" option.

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.

To see the changes, you will want to widen the "supplier id" field in the grid. Save the changes and close the designer.

The final lookupSQL we need is in the Invoice data module. Open Invoice.dmd into the Data Module Designer.

In this data module 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 data module and exit the designer.

Calculated Fields

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 data module called Base.cdm. This way, all our standard data modules will have access to the method.

Open Base.cdm into the designer. You will recall that this custom data module contains the database object. We are now going to add a method to this class.

Click the "View" menu and "Source Viewer" (or press F12) . Then move the cursor to the bottom of the class constructor code -- above the "endclass" statement.

In this data module, there are no methods in our base class, so lines 2-9 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 data module 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 data module that is subclassed from Base.cdm. (Many thanks to Michael Joyce for showing us this "goodie.")

We can now save the data module, close the Source Code Editor, and close the designer.

Open Customer.dmd.

Select the Customer1 query object.

Right click the mouse and select "Table and 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:

            function customer1_onOpen()
               class::calcField("Full Name",30)
            return

This line will call the calcField method and pass "Full Name" as the field name and 30 Characters as the field length.

Save this data module and exit the designer. We need to reopen the CUSTOMER.DMD data module in order to initialize the new field. Double-click the data module in the Navigator to re-open it.

Next we will use the beforeGetValue event to actually perform the calculation.

Select the CUSTOMER1 query object, and then the "Full Name" field.

Right click the mouse and select "Field Properties".

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

When you close the source editor, if you get a message that says "class does not exist ...", click "Ignore", then close the Data Module Designer. Re-open the data module in the designer.

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 "Full Name" and the data should read as first and last name combined.

Save the Customer data module. We are done with creating our first calculated field.

After All That, So What?

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 data modules will work for forms and reports (and labels), and while this application will not need them for this, we could use the same data module on different forms.

The other feature that shows up throughout the product (dBASE™ PLUS, that is) is the ability to create a data module (or form or report) and inherit from that, which gives you true object orientation. (You do not have to use custom data modules, forms, or reports, that is just the main way of doing things. All data modules, forms, reports and other objects can be subclassed, not just custom ones.)


  Back to the tutorial menu     Back to previous part of tutorial: Creating the Database   Proceed to the next part of the tutorial: Creating Custom Components