none
How to Get Next AutoIncrement Value

    Question

  • Dear All,

    Is there any way to get next AutoIncrement value from VFP table? Because it can't be max value if I have deleted any record from last/max value.

    Best Regards
    ShahidSQL (India)
    • Edited by ShahidSQL Sunday, April 7, 2019 5:27 AM
    Sunday, April 7, 2019 5:25 AM

All replies

  • The table designer knows it as it's stored in the header. But there is no use case for getting at it before adding the next record.

    When you delete a record it won't decrement, the deleted record is not deleted, just marked deleted and so for a primary key index, you still can't reuse that number. It always only increments. There's no need to fiddle with it.Decreasing it is wrong for many reasons, eg when you don't have cascading delete of detail records and reuse a number, detail data of a deleted header suddenly becomes child data of a new record it doesn't belong to.

    If you want to align the autoincrement counter after importing some data with APPEND you have to use ALTER TABLE to reset it, but that's not for the usual business of deleting single records.

    Bye, Olaf.

    Sunday, April 7, 2019 6:44 AM
  • Here's a small demo of how the autoincrement works.

    Cd GetEnv("TEMP")
    
    Set Deleted Off
    
    Local lcDatabase,lcTable
    lcDatabase = "autoinc"+Sys(2015)
    lcTable = "autoinc"+Sys(2015)
    Close Tables All
    Close Databases All
    Erase autoinc*
    
    Create Database (lcDatabase)
    Create Table (lcTable) (iid int autoinc nextvalue 1 step 1, cdata c(10) default Sys(2015), primary key iid tag pid)
    
    Set Fields Global
    Set Fields To All
    Set Fields To therecno=Recno()
    
    Append Blank 
    Browse 
    
    Append Blank 
    Delete
    Browse 
    
    Append Blank 
    Browse 
    
    Pack
    Set Fields Global
    Set Fields To All
    Set Fields To therecno=Recno()
    
    Append Blank 
    Browse

    Up to the PACK you'll have autoincvalue = recno(), even after deleting records. But when you pack you see the counter stays and you get autoinc value 4 for recno() 3. That's by design, not a bug, it's meant to be that way to always create a new number. 

    If you want something else program it yourself via stored proc. Sample database has a newid function, you may modify that, but you'll not get happy trying to use that as primary key, because you'll need to PACK data after any delete and if your DBF becomes larger that's a no go. 

    You may then set values to -id before deleting. Well, think about what still happens, if you delete record id=5 as -5, get a new record 5 and then want to delete that.

    Would you use a secondary negative counter for deleted records? You'll not get happy that way, too, forget it. Besides that's even only possible if the field is no autoinc value, as autoinc integer fields are readonly.

    Bye, Olaf.

    Sunday, April 7, 2019 7:47 AM
  • Hi,

    I think olaf's reply can solve your issue.Do you resolve the issue? If you resolve the issue, could you please mark the helpful as answer. it will be beneficial to other community.

    Best Regards,

    Alex


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, May 8, 2019 7:36 AM
    Moderator
  • The next autoincrement value is almost useless in multiuser environment. You can obtain the next value but this value is valid just for one user from all users which are concurrently adding new rows into the table.

    So more useful is the last value which was assigned to the last added row and you may obtain this value by GETAUTOINCVALUE() function call.

    Of course, if you really need to know the next autoinc value generated for the nearest new row then you may use AFIELDS() function call.

    Friday, May 10, 2019 10:31 PM
    Moderator
  • Good idea AFIELDS contains this info.

    Anyway, no good use for example to populate a key of a view or even a foreign key of a related view with the value you might get. In scenarios with a few users that mght usually work out, but traditional counters or GUIDs are a better way to cope with needing to know keys in advance, if its about that problem.

    Most server databases offer a way to determine last keys generated and use them in foreign keys. If you want to work with relations in VFP frontends you need somehting at least temporarily already unique before it's finally saved by tableupdate committing, but that's not only a VFP problem.

    The field stays readonly for you you can't populate it yourself anyway and the final word is on the DBF file itself when actually the record is generated in that file and not just in beffered view or even in the buffer of the workarea hving the DBF open itself.

    You can still make use of autoinc as a simple mechanism of maintaining separate counter tables as were used before we had autoiunc. I showed how to have a neweid stored procedure making use of a single sequence system DBF over at tek-tips: https://www.tek-tips.com/faqs.cfm?fid=7743

    If views or importing CSV is your problem with autoinc integer fields, there's a solution not working with this field type in all your tables but in one source of all keys table and you may also work the way the VFP newid sample code works with a counter for each table. 

    Besides that I'd still recommend using GUIDs as keys as that even works offline in distributed systems.

    Bye, Olaf.

    Saturday, May 11, 2019 7:15 AM