(Graphic courtesy of Gary White)
This document was created for Visual dBASE 7, and is one of the documents used to help create the original tutorial for dBASE. All of the information below should still pertain to the current versions of the product. For differences, you should check the online help, and other documents in the Knowledgebase. Not all references to Visual dBASE have been updated to dBASE or dBASE Plus.
DISCLAIMER: This document is aimed at helping you get started, and cannot possibly cover every single permutation of what you, the developer, might want or need to do. One of the powers, and also one of the confusing factors of dBASE is that it is flexible. Because of that flexibility, a developer can get very confused trying to find the right way to do something for their (or more importantly, their client's) needs.Your best bet is to use this document, other documents mentioned, and the best resource available, the Visual dBASE newsgroups provided by dBASE, Inc.. If you cannot figure out how to do something, visit the newsgroups and post a query. Most questions are answered within a few hours ...
dBASE, Inc. 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.
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.
Most of these objects are containers. This means that they can hold other objects. An example which 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 independant 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 -- Gary White is considering working out a way to create events using the field object ...).
Now, to make things more interesting, a database object refers to a database or BDE 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 consider using a BDE alias for reasons that will be discussed when we get to the database object ...
A session object is used to handle simultaneous database access. (The most likely scenario would be an MDI application where multiple forms accessing the same tables might be opened ...)
Stored Procedures can only be used with SQL Server tables, and are used to access a stored procedure on the server database. "Standard" (local) tables do not recognize these, and attempting to use them on local tables may cause heartburn ...
A datamodule can be used to contain: database objects, stored procedure objects, session objects, and query objects. Rowsets are, as noted previously, 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 ... wish I knew what it was ...
NOTE: A very confusing aspect of working with data objects in Visual dBASE is that the data objects do not have a form (or report) reference. This means that when setting up code from a data object's events or methods, you cannot refer to objects on a form or report directly. There are ways around this, but we won't discuss them here, as I do not wish to confuse you beyond where you may be already.
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 name 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 which 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 ...)
SESSION
A session object is sort of like a container. It can be used
with either a database object or directly with a query
object (however, if you are using a database object, you should
use the session with the database, not the query ...).
In earlier versions of dBASE, sessions were necessary when an application allowed multiple access to the same tables within one "session" of the application. If you opened the tables up in their own sessions, you avoided some conflict issues (not all).
In Visual dBASE 7, the only real purpose for using a session object is if you want to use the onProgress event (to hook into a progress bar) or if you are using encrypted tables (you can "auto login". Other than that, they really just confuse the issue and add an extra layer to what the developer is doing.
Sessions have the following properties, events and methods:
To create a new session, all you must do is:
sMySession = new Session()
To use a session you must assign a session property for those objects (database and query) that are to be affected by that specific session.
For example:
sMySession = new Session() dMyDatabase = new Database() dMyDatabase.databaseName := "MyAlias" dMyDatabase.session := sMySession dMyDatabase.active := true qMyQuery = new Query() qMyQuery.database := dMyDatabase qMyQuery.session := sMySession qMyQuery.sql := "select * from MyTable" qMyQuery.active := true qMyQuery2 = new Query() qMyQuery2.database := dMyDatabase qMyQuery2.session := sMySession qMyQuery2.sql := "select * from MyTable2" qMyQuery2.active := true
Default Session
Note, there is always a default session, which is accessible
through the application object's default database object:
?_app.databases[1].session.access()
If you want to use sessions in your application, you should consider setting them on your datamodules, forms or reports (depending on your application design). See online help for more details on using sessions.
DATABASE
The database object is used when you use BDE Aliases to handle
your databases. For more details on BDE Aliases, you should
read MISCCODE.ZIP at the author's website, as well as the
Developer's Guide that ships with dBASE and the BDE's
help ...
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.
If you opt to use an alias, you should use the database object in your code and on your forms, reports, datamodules, etc.
If you are working only with local tables, why should you consider using a BDE Alias, and therefore a database object?
There is a discussion in the HOW TO document titled "MISCCODE" at my website on using and creating BDE Aliases. If you are unfamiliar with this area, you should check it out.
Here are the properties and methods (there are not events) of the database object (some of these are not covered in great detail here, as the author does not use SQL Server tables ... see online help for more details).
The Default Database
It is important to note that dBASE has a default database,
which points to the "current directory". This can always
be accessed via the application object:
_app.databases[1]
and any methods of the database object can be called:
_app.databases[1].packTable( "MyTable" )
for example.
The default database's active property has no effect -- you can attempt to set it to false but nothing will happen. The default database has a default session as well (see below).
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 // for a SQL Server database d = new Database() d.databaseName := "MyAlias" d.loginString := "username/password" d.active := true // Assign a new session: s = new Session() d.databaseName := "MyAlias" d.session := s d.loginString := "username/password" d.active := true
Transaction Processing
One of the things that you may wish to use the database
object for is transaction processing. Many applications
these days no longer use this, but it is still something
that you may need for your own application(s) or your
users may prefer it.
Transaction processing is a method of posting transactions at a specific point, rather than performing interactive processing. This is sometimes called "batch" processing ...
When you activate your database, you can start a transaction set, with the database's beginTrans() method:
// for local tables
d = new Database()
d.databaseName := "MyAlias"
d.active := true
d.beginTrans()
// do whatever -- start the application,
// or load the form and allow the user to
// do their work ... etc.
d.commit() // will save the transactions, and update
// the table(s) appropriately.
// OR
d.rollback() // will cancel all transactions since
// the beginTrans() method was called.
Table Maintenance Methods
There are several methods that are attached to the database
object that replicate some of the XBase DML commands.
Briefly these are:
STORED PROCEDURE
If you are working with local tables only, then the
STOREDPROC object is useless to you. However,
if you work with SQL Server (or "backend server") databases,
then this object may be quite useful.
I have no direct experience with these, so please bear with me. After describing the properties and such I won't have a lot to say ...
"Use a StoredProc object to call a stored procedure in a database. Most stored procedures take one or more parameters as input and may return one or more values as output. Parameters are passed to and from the stored procedure through the StoredProc object’s params property, which points to an associative array of Parameter Objects.There is more information in online help if you need more assistance in using these."Some stored procedures return a rowset. In that case, the StoredProc object is similar to a Query object; but instead of executing an SQL statement that describes the data to retrieve, you name a stored procedure, pass parameters to it, and execute it. The resulting rowset is accessed through the StoredProc object’s rowset property, just like in a Query object.
"Because stored procedures are SQL-server-based, you must create and activate a Database object and assign that object to the StoredProc object’s database property. Standard tables do not support stored procedures.
"Next, the procedureName property must be set to the name of the stored procedure. For most SQL servers, the BDE can get the names and types of the parameters for the stored procedure. On some servers, no information is available; in that case you must include the parameter names in the procedureName property as well."
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 ... Before we talk about how to use it, let's look at the properties, events and methods of the query.
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 Visual dBASE 7 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 Visual dBASE. 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, like it is in DOS -- it means "all fields" when used here.
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:
Next ...
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 ...
ROWSET
The rowset object is the real work horse of the data
objects in Visual dBASE. 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 (see
X2OODML.ZIP).
Wow! A lot of stuff is in there. The problem is, that was really just a quick glance, and you may wonder how to actually use all that! Don't despair, I'll cover some of it in more detail here, and there are other examples both in the HOW TO documents at my website and in online help ... Right now we're just looking at the parts and pieces, later we'll start putting them together.
Rather than spend any time looking at manipulating the rowset right now, we need to finish an examination of the other data objects ...
INDEX
The index object, which is unfortunately undocumented in the
online help and the Language Reference is the way
you create indexes in the OODML. There are actually two
different index objects, one of which is a subclass of the other.
The primary index object is called INDEX, the other is
called DBFINDEX, which is specifically aimed at the .DBF
table format. There are minor differences between the two.
We'll start with the INDEX object and examine it's properties,
and then note the differences between the INDEX and the DBFIndex.
The DBFIndex has the following properties:
As you can see, while the two index objects are very close, they are not quite identical. This is because dBASE indexes have always been a bit more flexible than most SQL Server indexes.
To define a .DBF index, and then create it, you must do the following:
i = new DBFIndex() i.indexName := "MyIndex" i.expression := "field1 + upper(field2)" // another undocumented feature: _app.databases[1].createIndex( "tableName", i )
That's really all that is required. If you want to do more, you can set the type to "1" for a .NDX (note, however, that the OODML doesn't know what to do with a .NDX file), you can set the unique property to true, you can set the descending property to true, and so on. Remember that the expression must be a proper expression.
More on Expressions
For the moment, I'm assuming you are working with .DBFs, as
they are the native table type for dBASE. As such, let's
briefly discuss a few things.
Numerics
Numeric indexes are not generally a good idea. You should
index on character strings for most situations.
"Most key fields are essentially character, even if
they contain numerials. For example, telephone numbers,
zip codes, part numbers and social security numbers
have nothing numeric about them. A good rule of thumb is
that if you don't have to do arithmetic with it, it should
be character." -- Gary Thoenen [dBVIPS]
Dates
Dates can be interesting. If you are using the American
date format of MM/DD/YYYY, and you set an index on the
date "as is", what happens if you have dates that span
several years? Your sorting will be by month, rather than
by year and month.
So, let's say you set your date format to YY/MM/DD, which is logical (Year, Month, Day). What happens when the year 2000 rolls around? You end up with all dates that are in 2000 appearing in your index before 1999. This is probably not desireable. The best way to index on dates is to include the DTOS() function. DTOS() returns dates as a character string, in the format: YYYYMMDD (no slashes).
The handy thing is that this works for all international date formats.
Expression Lengths
The actual length of the expression can be 100 characters,
but that is a really long expression. If you are working
with multiple fields and you combine them into an expression
that is longer than 100 characters, dBASE won't let you
do it. How to get around it? Use the left() function to
extract enough of each field so that you have unique
values, and you should be fine. Example, if you are
working with a CD collection, many album title scan
get pretty lengthy (one that comes to mind is: "The Myths
and Legends of King Arthur and the Knights of the Round Table"
by Rick Wakeman ...). You might want to index on the
artist name and then the album title. But if the artist
field is, say, 40 characters, and the title field is
100, you get into problems. To create an expression for this,
you can probably get the whole artist name, and then
just the first 40 or so characters (or maybe less)
of the title:
i.expression := artist+left( title, 40 )
Case Senstivity
DBF index expressions have always been case sensitive. You
never really know what a user will do with your
software. Your safest bet is to make your expression case
insensitive. How do you do that? Index on the upper-case
of your character strings, and make sure that your search
routines always search on the upper-case of the value
entered by the user. To create the expression, you
might use:
i.expression := upper( artist+left( title, 40 ) )
Viewing Information About Your Index Tags
It is not easy to get to information about your index
tags, unless you use the undocumented TableDef object.
This is covered in detail in the author's ICon 98 paper on Undocumented Features at his web site ...
A Few Misc. Things
Keep in mind that the more index tags you have for
a table, the longer it takes to update a table, which
includes adding new rows, editing rows (especially
if the user is allowed to modify fields used in the
expressions for your index tags), and so on. If you
are doing programmatic changes to your tables, this
can get worse. There are ways around some of this,
but we're not going to get into them just yet.
There is more information on indexes in the author's ICon 98 paper on Undocumented Features at his web site ...
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. Here's the list of properties and methods:
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. There are actually
several different types of FIELD objects. The second two are
subclasses of the main FIELD object. If you are working with
a .DBF, then you will automatically be using a DBFFIELD, or
in the case of a table having been CONVERTed (XBase DML command
to add the _DBASELOCK field), you will have a LOCKFIELD. You
will see also PDXFIELD and/or SQLFIELD, each of which has
properties specific to that field type.
The following includes all field properties, and if not common to the FIELD class, a note for which field type is given.
Hopefully at this point things are starting to come together. Either that, or your eyes are starting to glaze. If the latter, then step away from the computer for a bit ... this will still be here.
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? The
following is true for reports and labels as well as
forms ...
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 Visual dBASE 7, click on the "Tables" tab of the navigator, and drag your table to the form designer surface.
What happens is that dBASE 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 will set the SQL property, and the active property will default to "true".
If you are using a BDE 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.
If you need to do more, such as setting an index expression to be the controlling index, you need to do a "drill down" in the inspector. Click on the SQL icon for the table, and in the inspector, click on the word "rowset". It will say "Object" in the second column, and have a button with the letter "I" (for "Inspect") on it. Click that. You are now looking at the rowset object associated with the query. Click on the "indexName" property, and select the index tag you wish to use. It's that easy.
You will see a "fields" palette normally. If you do not, right click on the design surface for the form, and check the "Field Palette" option. This will display the fields for your table. You can drag those directly to the design surface if you wish ...
The basics are pretty easy, eh? There's a lot more, which there is not a lot of room to get into here if other topics are to be covered. The author will attempt to create a tutorial at some point in the future on creating an application, and some time will be spent on creating your forms ... However, in the meantime you should see "CONTROLS.ZIP", "CUSTCTRL.ZIP", "GRID.ZIP", and other related HOW TO documents at the author's web site.
One thing that it is important to note is that the form itself has a property called "rowset". dBASE 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:
One thing that is very handy, is that the samples that ship with Visual dBASE include some routines to handle navigating through a table for you (these are included in the CUSTOM\DATABUTTONS.CC file). In addition, the author has expanded the abilities a bit of these buttons and created his own set, which can be found in the dUFLP library (at the author's site) in the file CUSTBUTT.CC.
If you decide to 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 (I 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.
An Issue That Comes Up With Comboboxes ...
If you use this method of disabling controls, the
combobox appears to be active even when it isn't. The
user can select a value, and have it appear as if
this value was changed in the row, until the user
navigates off the row and back, or goes into
actual edit mode (or performs any other action
which causes a notification to the datalinked controls
of the values in the fields).
This can be a bit disconcerting. However, Gary White has provided the following work around for this problem. It requires that you hook the following code into the onGotFocus and onChange events for the combobox:
function combobox1_onGotFocus
this.savedValue = this.value
return
and in the onChange event:
function combobox1_onChange
// this = combobox
// datalink = field
// parent = field array
// parent = rowset
if this.datalink.parent.parent.state == 1
this.value = this.savedValue
endif
return
Note that for this to work, you must use both of these events. (You can also add more code in the onChange, you just want to keep that code ... maybe add a 'return' before the endif statement).
A Similar Issue That Comes Up With Editors ...
The editor control is even worse in this aspect, because
it actually will ignore the rowset's state.
However, Gary White also came up with the following fix ...
function key
/*
This code by Gary White is provided to
get around a problem with rowsets that
have the autoEdit property set to false,
and editors. The editor seems to be immune
to this property once you make a change
in it -- if you then save or abandon, you
can actually edit the contents of the editor
object ...
*/
// this = editor
// dataLink = field
// parent = fieldArray
// parent = rowset
if type( "this.datalink.parent.parent" ) # "U"
r = this.datalink.parent.parent
if r.autoEdit == false and ;
( r.state # 2 and r.state # 3 )
return 0
endif
endif
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 "X2OODML.ZIP" at the author's website
(and in the dUFLP library at the same site).
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. These are discussed in "MISCCODE.ZIP" at the
author's web site in a lot of detail.
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. These are discussed in "MISCCODE.ZIP" at the
author's web site in a lot of detail.
Relating Tables
In the older versions of dBASE, you had to use several commands
to set up relationships (SET RELATION, SET SKIP ...). In
Visual dBASE 7's OODML, this has gotten easier. There
is a fairly extensive discussion of this in the "MISCCODE.ZIP"
HOW TO document at the author's website.
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, and getting
a value from a lookupRowset is 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 (as discussed below), 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 Visual dBASE 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.
Rather than duplicating information that is already written, you should examine the ICon 98 paper on Undocumented Features at the author's website. This particular object is covered at some length, with sample code, and more in that paper.
For more detals on the UpdateSet object, see "MISCCODE.ZIP" at the author's website. This is covered in quite a bit of detail there and it's easier to just reference that document than to try to keep duplicate information in multiple places.
The point of a datamodule is that you can set up your table(s) and any code you wish to set up for those tables in a datamodule, and use all of that code without having to re-create it for each form or report.
You can have custom datamodules, which allow you to subclass a basic data layout, and use the subclasses for your actual forms (with specific modifications in the subclassed datamodules), and more. (An example of this would be to set your database, and maybe a session object, into a custom datamodule, and then derive other datamodules from that, without having to re-do your session and data objects!)
This document has already gotten rather lengthy, and rather than spend a lot of time expounding on datamodules, I 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.
A note, however, to help with datamodule usage:
A datamodule does not know what a "form" (or a report) is, just like the other data objects do not know what a "form" is. This means you cannot use code like the following inside a datamodule (you will get an error "form" not found or something along those lines):
form.entryfield1.value := "myvalue"
When you place a datamodule onto a form, you are actually placing a "datamodRef" object on the form, which has a "ref" property, which points to the actual datamodule. The reason for this is that you can use the same datamodule, at the same time, on multiple forms (or reports) -- how is the datamodule itself going to know which form or report is its "parent"? If you were to refer to "form" in the datamodule, how would it know which form (or report) you mean?
This is a tough one to get used to, since most objects contained by a form or a report understand what they "belong" to.
It is possible, although not a good idea, to refer to a form's entryfield from within a rowset contained by a query, contained by a datamodule, along these lines:
// this = rowset // parent1 = query // parent2 = datamodule // parent3 = form this.parent.parent.parent.parent.entryfield1.value ...
The reason it is not a good idea is that if you decided to use the same datamodule on a report, there is no such thing as an entryfield on the report. You may want to use the datamodule on a different form -- is the name of the entryfield always the same between forms?
There are lots of references to other documents at the author's website, these documents can be found at:
http://www.mindspring.com/~hirschv/dbase/dbase.htm
In addition, I cannot suggest strongly enough that you also read, if you have not already, Alan Katz' OODML.HOW, which covers a lot of theory of the data object model in Visual dBASE 7.
DISCLAIMER: the author is a member of dBVIPS (dBASE Volunteer Internet Peer Support) -- a group of volunteers who provide technical support for dBASE, Inc. on the Visual dBASE newsgroups (news.dbase2000.com). If you have questions regarding this .HOW document, or about Visual dBASE you can communicate directly with the author and dBVIPS in the appropriate newsgroups on the internet. Technical support is not currently provided by private E-Mail by members of dBVIPS..HOW files are created as a free service by members of dBVIPS to help users learn to use Visual dBASE more effectively. They are edited by both dBVIPS members and dBASE, Inc. Technical Support (to ensure quality). This .HOW file MAY NOT BE POSTED ELSEWHERE without the explicit permission of the author, who retains all rights to the document.
Copyright 1999, Kenneth J. Mayer. All rights reserved.
Information about dBASE, Inc. can be found at:
http://www.dbase.com
EoHT: BEGDATA.HTM -- March 15, 1999 -- KJM