Key and Timestamp on VFP Tables

Twice in the last two months I have talked to someone getting errors because the modified fields list was too long in the data they were trying to save. The first person was using views and the second one was using Cursor Adapter. I know that the SYS(3055) function was added to help "work around" this issue. I am not sure how much memory this will use so I am not inclined to use it as a general solution. The problem has been around for many years so why couldn't MSFT just give us a Timestamp data type and we would be all set. Oh, I know why. Because then we wouldn't use SQL Server. :-)

When the first person asked about this problem, Mike and I talked about it and together we rolled a really good solution. I can't believe that we didn't think of this years ago. Here is what you need to do:

First: Add a datetime field named tUpdated to each of your tables. Or at least to the really wide ones.

Next: Add a stored procedure to the DBC where your tables reside. The stored procedure is very simple:

PROCEDURE NewTime
* Updates the datetime field of a cursor with the current
* date and time.
REPLACE tUpdated WITH DATETIME()

RETURN .T.

Third: Call the NewTime() function from the record level rule for each table with a tUpdated field. In case you are not familar with the record level rule, it is located on the Table page in the Table designer. The purpose is to validate a record before saving. I don't know about you, but I have use Valids for more non-validation code than actual validation code. So this is nothing new.

Finally: In your view or cursor adapter, set both the key and the tUpdated fields to be the key. Do not set the tUpdated field to be updatable. You also need to set the Where Clause to just Key Field.

Now, there are two additional things that you might need to deal with. The first is in VFE. A VFE cursor class doesn't like multiple field primary keys too much. So, even though the view uses the key and tUpdated field for the key, the cursor just needs to know about the real key. Therefore, you must set the cPrimaryKey property of your cursor classes to just the name of the key field. Then, the cursor class code won't ask the view what the primary key is.

The next problem is refreshing the tUpdated field. The field is modified in the table. Therefore, the old value will remain in the view until you requery or refresh it another way. In VFE you can simply set the lRefreshRowOnSave property on the business object to .T. If you are not using VFE, you can try to call the Refresh() function. Sometimes Refresh() works and sometimes it doesn't. If it does not work, you will have to retrieve the new tUpdated value from the table and update the view field with the new value.

I hope that this approach will save someone a headache.

xCase 8 - Wow!

Resolution has released Xcase 8.0. In my opinion, this is the most major upgrade to Xcase to date. I've been a big fan of Xcase for a long time; in fact, I was so impressed when I first saw Xcase that F1 Technologies ended up becoming a distributor.

Xcase has always been the most capable database design tool I've ever used, especially when it came to Fox data since it was the only real game in town. Previously I liked Xcase more for what it could do, then for how it did it. What I didn't like was the interface. It had more of a Windows 3.1 style interface that I found to be a bit dated, clunky and sometimes awkward.

Xcase 8.0 eliminates all of my concerns about its interface. What was ugly is now beautiful, what was awkward is now elegant, what was obscure is now easy to find. They've really done an amazing job with this upgrade. The workspace has been completely redesigned. It now has a very XPish interface, is easy to navigate, is snappier and well, it's just plain sexy. The toolbars have been updated with more standard icons and are customizable. The menus adhere to Windows standards. Dialogs have been consolidated into multi-tabbed browsers and so on.

The interfaces changes themselves more than justify the upgrade, but in addition to the new capabilities in the UI there are a number of other worthwhile enhancements. Most importantly Xcase 8 fully supports both SQL Server 2005 and MySQL 5. For SQL Server 2005 Xcase supports Schemas, Partitions, XML collections, XML Indexes, Index Included Columns, Description Extended Property, CLR Objects and much more. For MySQL 5 support has been added for triggers, stored procedures, functions and views.

For more information on Xcase 8 or to download a fully functional demonstration version visit http://www.f1tech.com/xcase. I will also be out and about demonstrating xCase 8 to various user groups this summer. If you’d like to arrange an xCase demonstration at your user group, contact me.

A list of new features can be found at http://www.f1tech.com/xCase/whatsnew.htm. New orders and upgrades can be placed at http://www.f1tech.com/orders.

Linked Form Support in VFE

In my last blog entry I mentioned that we've added support for a new feature named "Linked Forms" to our Visual FoxExpress framework. Linked forms allows the developer to specify a form that's linked to a field or a view parameter. A linked form is specified by setting the "Linked Form Class Name" for the field or view parameter in the data dictionary.

When a field or view parameter has a linked form associated with it the label appears as a hyperlink. If the user clicks the label, the form object is created and a reference to the label is passed to the form's init method. If the form that contains the label has a DoChildForm method, then the linked form is creating using the DoChildForm method. If there isn't a DoChildForm method available then the Application Object's DoForm method is executed.

That's pretty much all this feature does out of the box. What happens in the linked form from there is up to the developer. There are a number of possibilities.

In some scenarios you may opt to do nothing in a linked form when it's called. If you're building your application using local tables and the table used by the linked form is open in the calling form's data session, the record pointer should already be position on correct record when the linked form is opened. If you're using the linked form as some type of lookup, perhaps you'd wait to have the user file in parameter values and then act when a record is selected.

A more common scenario will be to attempt to move to the data associated with the value of the field the linked form is tied to. Remember when the linked form is called an object reference to the label that made the call is passed. From that label, you can easily retrieve data or other properties and act on them. Here's some sample code that sets a parameter based on the label passed to the form and requeries:

*==============================================================================
* Method:      Init
* Purpose:      If a parameter is passed, search for a customer that matches
*          it.
* Author:      F1 Technologies
* Parameters:    toParameter, Object, in this case a label object from the
*          calling form.
* Returns:      None
* Added:      02/01/2006
*==============================================================================
LPARAMETERS ;
 toParameter

IF PCOUNT() = 1
 This.oPresentObj.oBizObj.Search(toParameter.oField.Name, toParameter.oField.Value)
ENDIF
DODEFAULT(toParameter)

* Clear the object reference held in the parameter so we don't have a reference
* to an object in another form hanging around.
This.oParameter = NULL

This example works with a form that is based on a small view that will have already retrieved all of its data automatically by the time the form's init method is called, so all we do here is call the business object's search method to position the record pointer on the appropriate record. If we were working with parameterized data we could've just as easily set a parameter and then requeried.

The most important line in the example is This.oParameter = NULL. By default, when a parameter is passed to a form the parameter is stored in the form's oParameter property. In the case of linked forms, this will be an object reference to a control in another form. This object should reference should always be nullified before the form is destroyed. If you're using a non-modal form, as I was in this example, it's more or less required to release this reference at or near the end of the Init method. If this object reference is left in place and the calling form is subsequently released VFP isn't very happy about it and eventually it will let you know in the form of a crash. If the Linked Form is modal the framework takes care of clearing the oParameter property when the form is destroyed, but it is probably still best to take a cautious approach to this and release the object reference as soon as its no longer needed.

This was a relatively easy feature to implement, but it can be quite powerful. I'm making use of it in several places in the new sample application and someday in the near future we'll actually make that available. In the meantime, give it a whirl. It's pretty straightforward.

Integrated Upates Posted for VFE

Yesterday we posted another set of integrated updates for VFE. The following describes what class libraries were changed and what the changes were. Thanks to Steve Derzi and Troy Neville for finding a few of the issues we've addressed with this update and testing the changes.

Class Library: cVFEMgr

cVFEManager
Changes were made to several methods to eliminate potential runtime errors if the iId fields get out of sync between VFEMeta and Coremeta.
Changes were made to force the iIds to remain in sync between VFEMeta and Codemeta for index records. Occassionally the ids were not updated in VFE which caused a runtime error in cCursor.CreateIndexes()

Class Library: cUtils.

cGlobalHook
Some previously commented out code was deleted.

Class Library: cToolbar

cToolbar
The refresh method was changed so that controls within the toolbar were not always automatically enabeld when the toolbar was refreshed. This change was necessary to enable proper disabling of toolbars not associated with a modal form when the form is open.

Class Library: cSecure

cUsersPresObj

The PopulateGroupsMethod was changed to make use of the groups cursor directly. In the 1/13/06 build we modified security so that all of the presentation objects are loaded using delayed instantiation. This change made it possible to load a different presentation object from the i-layer if desired. This change caused the groups cursor to not open until the groups page has been activated.
We also changed the UIEnable method of the cboGroup_Id combobox to automatically requery when the page is activated. This ensures it reflects all currently available groups.

cUsersEnvironment
An instance of the cGroupsCursor class was added to this data environment to ensure that the groups cursor is available when necessary.

Class Library: cOle

cListViewSelector

The class was created to allow selecting the view in an associated ListView control.

cListView
The OnRowChange_Perform method was modified to check for pending changes in a data entry cursor and act accordingly.
The SetItem method was modified to append an _ to the begining of the key before searching, which makes it function properly.

Class Library: cContrls

cLabel
The modifications made to cLabel were made to support a new feature: Linked Forms. I'll write up a separate entry about linked forms in the near future.

cGrid
The OnRowChange_Perform method was refactored based on some conversation in the forums.
A new method, GetCursorForGrid was added that returns an object reference to the cursor object associated with the grid.
In the BeforeRowColChange method we sdded code to prompt the user to save when a data entry and list cursor business object is in use with the grid and there are changes pending in the data entry cursor.

Class Library: cCustFrm
A couple of minor nits not worth going into detail over were addressed in this class library.

Class Library: cData

cField
Support was added for fields in local VFP tables to the CheckUnique method.

cViewParameter
Corrected an issue with the Auto Foreign Key (lFKParam) property in value_access.

cCursor
Added a few checks to ensure that all of the necessary property values retrieved from DBCX are present before trying to create an index.

Class Library: cDataTree

cDataTree
Changed the LoadBizObj method to better take advantage of business objects that have already been instantiated.

Class Library: cForms

cBaseForm
Added code to enable/disable the main toolbar in the activate method based on the modality of the form.

cPresentObjForm
Added code to only refresh the main toolbar when it is enabled.