>>>I have a one to many form with a grid for child records. I use this to add records.
>>>The key that links the tables has a primary index (machnum) on the parent and a regular index on the child. I get the next Machnum by..
>>>select parent
>>>set order to Machnum
>>>go bottom
>>>NewMachnum=Machnum+1
>>>Insert into machine (Machnum) values (NewMachnum)
>>>...in the init of the form. Right now I also am starting a transaction in the init. I don't like this because then I have an open
>>>transaction till the form is exited. I need this however because I need the primary index so I can insert child records with
>>>that same index into the child table. I also need the ability to back out of the transaction if a user decides to cancel the form.
>>>What other method could I use so that I still have a new index but not an open transaction?
>>
>>Joe,
>>First getting a new primary key with that method is dangerous. You could use the sample NextId method in tastrade.app to get PkId. However lets think it's correct and talk about rest of problem.
>>Use buffering. General tendency is to use row level buffering for parent and table level buffering for child. You could use optimistic type buffering and only in your save-discard method start-end a transaction.
>>Roughly a save method could look like :
>>

begin transaction
>>if tableupdate(2,.t.,'parent') and tableupdate(2,.t.,'child')
>> end transaction
>>else
>> rollback
>>endif

You really don't need to start a transaction to insert records into child. Buffered or not you have access to parent.pkid value. At any moment you could insert into child :
>>

Insert into machine_child (ParentId) values (Machine.Machnum)

Cetin
>
>
>Funny... After looking at NewID I could see that it was the way that I always used to get pk's. I stopped because I
>didn't like the idea of leaving unused pk's. In retrospect, maybe it's not all that bad.

Joe,
It was why I said dangerous. In a multiuser app more than one user has a high chance to get the same id. Or even a single computer might get the same id (user deletes some from bottom and thus gets previously used id - it would be a real problem if children also not deleted and packed).
Yes it was NewId what I was referring. It's not bad since an integer is large enough to accomodate a table limit even with multipl unused PKs (in theory you could hit the limit of an integer before table limit but in practice? - further you could do a maintanence to recover unused PKs). And PKs are relation insurances not things that users should or would see.
My NewId is sligthly modified version :
Ids: Table c(15), NextId i && Warning if you have a chance to give a tablename more than 15 chars either take that into account in ids structure or do something like windows shortnaming convention. Otherwise you could take always 1 as a PK for a particular table.

FUNCTION NewID
LPARAMETERS tcAlias
LOCAL lcAlias
* Uppercase Alias name
lcAlias = upper(iif(parameters() = 0, alias(), tcAlias))
lcOldExact = set('exact')
set exact on
* Lock reprocess - try once
SET REPROCESS TO 1
IF !USED("IDS")
USE ids IN 0
ENDIF
* If no entry yet create and return 1
IF !SEEK(lcAlias, "Ids", "table")
INSERT into ids (Table, NextId) values (lcAlias,1)
set exact &lcOldExact
RETURN ids.NextId
ENDIF
set exact &lcOldExact
* Lock, increment id, unlock, return nextid value
DO while !RLOCK('ids')
* Delay before next lock trial
lnStart = seconds()
DO while seconds()-lnStart < 0.01
ENDDO
ENDDO
* Increment
REPLACE ids.NextId WITH ids.NextId + 1 in 'ids'
UNLOCK in 'ids'
RETURN ids.NextId
ENDFUNCIntentionally automatic reprocess is not used and a short delay is given between trials to prevent deadlock.
Cetin