How to retrieve autoinc ID-value from buffered view

[Nifrabar]

Hi to all!
Next scenario:
I am having a dataform which has as main datasource a buffered view.
There are other tables involved in this form which have relation with maindata-source.
Problem:
After I create a new record using append blank the buffered view shows value of autoinc field 0 (zero).
As I need that value to keep relations with other tables involved I am looking for a generic solution.
I tried GETAUTOINCVALUE function but as far as I found that function retrieves the correct value after I have committed my new record. That is unwanted while still data must be input prior to commit that new generated record.

Any suggestions how to retrieve correct value?

TIA
-Bart

 

[Olaf Doschke]

there is no way, as the value is only generated when saved to the DBF. What you need to do is work with a temporary value (-1,-2,-3), after Tableupdate() requery() to get the real autoinc values and propagate them to foreign key fields in connected views.

If you don't like that don't use autoinc or always first commit a new row and refresh it before generating child rows, but then the parent table will already contain the parent and if the data entry is cancelled you need to delete this unneeded parent perhaps.

Using GUID is easier, as you don't need to propagate updates, you can make the keyfield updatable and generate GUIDs at each client, that will not be temporary and thus don't need a propagated update when commiting data.

Bye, Olaf.

 

[Mike Lewis]

Bart,

My approach would be to commit the table before you call GETAUTOINC(), but to wrap all the updates in a transasction in case there is a failure between inserting the parent and updating the child.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro tips, advice, training, consultancy
Custom software for your business

 

[Olaf Doschke]

Good suggestion, Mike, I would however requery the view instead of peeking at GetAutoincValue(), as GetAutoincValue() will only show you the last ID, which doesn't help much, if you commit more than one record, and commiting rowwise makes the save process a little slower.

Requery() after Tableupdate(.t.,.t.) will pull over the generated Ids and be very reliably the IDs you need as foreign keys. To reidentify which record had which temporary IDs before, you need some identifier besides the id, eg checksum computed without the ID (SYS 2017) or some other unique field besides the autoinc id.

Bye, Olaf.

 

[Mike Lewis]

Yes, I agree with all that, Olaf. I would think it's more common to insert a single parent record, and then insert one or more childs, in which case the issue of committing multiple parents doesn't arise. On the other hand, it's good if the same code can handle all likely cases.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro tips, advice, training, consultancy
Custom software for your business

 

[Olaf Doschke]

Yes, in that simple case a single record update is fine, but the table hierarchy could even have three or more levels, so there would be grandchild tables.

Bye, Olaf.

 

[Nifrabar]

Hi Olaf and Mike,

Thanks for your feedback on my Q.
I now intend to commit and requery the maindatasource prior to allow my users to make relations based upon the unique record ID.
The suggestion of using Sys(2017) as a way to retrieve this particular record after the requery makes sense.
But....
Once I got he checksum, how can I use that checksum to lookup the record after requery?

-Bart

 

[Olaf Doschke]

Well, to lookup, which record has the same checksum again, you need to scan through all records, perhaps someone else has a better idea. You could also make a checksum field part of the table of course, as kind of a temporary unique key.

Plus of course you need a helper cursor with the structure of tempID (the negative ID), checksum and finalID (the one generated in the database and requeried). Before requerying you store tempID and checksum, with the checksum you reidnetify the record and then insert the newID. NOw you have a translation table you can use to update child tables.

And the checksum is just one idea. As soon as you have add some field to the table you can store whatever reidentifiying key in there, eg also a SYS(2015) value or whatever. It just needs to be unique within the records you have in the view that will be requeried, not throughout the whole table.

Bye, Olaf.