Hi!

>Hi Vlad - I am pretty desperate for a look at the document you mentioned - is there anyway you could send it to me?

Ok, it is below. I also sent it to you by EMail.


Most popular RDBMS propose auto-increment field
for primary key for table, specially for normalized databases. VFP don't have such feature. I suppose following ideas will be useful for all VFP programmers that deal with databases.

Use default value for key field to fill it automatically by new key value each time new record added. As the default expression use function that returns this key. For example:

GetNextID("companies")

GetNextID - function in database. In project you can use it either as separate .PRG file or as function inside of your main program file or procedure file, but it is not recommended to duplicate it in two places, because you may call database trigger functions from VFP code when that database is current.
This function should return new ID key value.

Following is sample of such function. This way is the most flexible way with great speed, but it require a lot of programming and maintenance.
You can calculate key using special table (IDS), where you store all tables and their latest ID KEY numbers. Following is sample of implementation of such function:


PROCEDURE GetNextID
LPARAMETER tcTable
IF !USED("ids")
USE MyDatabase!ids IN 0 order table
ELSE
set orde to table in ids
ENDIF
IF !SEEK(UPPER(tcTable), "ids")
INSERT INTO ids VALUES(UPPER(tcTable),0)
ENDIF
DO WHILE .T.
IF RLOCK("ids")
REPLACE last WITH last + 1 IN ids
UNLOCK IN ids
EXIT
ENDIF
ENDDO
RETURN ids.last

The structure of IDS table is following:

Field name | type
___________|__________________________
table......|character (25)
last.......|integer

table - table name in which last key field value maintained
last - last key value

You require also to add indexes for ids table:
index name | expression
_____________________________
Table......| "UPPER(table)"

You may do this using command
INDEX ON UPPER(table) TAG Table Additive


This function works quickly because uses small table (ids), record locking in it affects only IDS table and conflicts will occur only in rare case (when 2 users in network will try to add the same record to same table simultaneously). Conflicts resolved by record locking.
The disadvantage is that you need to make maintenance program for IDS table to assure that all ID values in that table are latest. This is needed only when you copy separate table data from one database to another, that is very rare.

Alternatively, when you're sure that some table is small and accessed by users rarely, you can use direct calculation method based on indexes, or even SELECT with MAX aggregate function.
Finally, you may use both methods, so you will have only large and oftenly accessed tables in IDS table for less maintenance, when all other tables will use direct calculations.

Little note about views. If you do not want to requery view each time after new record saved by view that based on table with auto-inrement key fiel, do following:
1. Define default value for key field in view by the same way as in table.
2. Make key field updatable. This is needed, otherwise your key value will be ignored and table default value will be used, that means GetNextID funtion will be called twise and different values will be in table and view, that may cause problems when adding new records to child tables as well.
3. DO NOT use SetFldState() function to mark key field in view by '4' - new and changed. SetFldState is buggy. To force VFP to overwrite field in table by View's key value anyway, use 'Replace KeyField with KeyField' command.