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.