Rudy, I suggest you use both Nigel and Arnaud's suggestions: ID (key field) field is UUID and autoincrement
Add a separate field for the sequence number I f you aren't using autoincrement already I suspect it's because you need control over the number sequence and want to avoid gaps. That's complicating the key field management and I'd avoid that kind if design going forward. I'd make the sequence number non-unique and manage duplicates myself - especially is you ever need to import data. Those sorts of numbers, like invoice numbers, aren't as reliably unique in the real world as we need them to be in a database structure. Currently, let's say a user begins a transaction and the new record gets the sequence number. Before that transaction is completed another user starts a new record (or that user starts another process to create another record) - and gets the same sequence number. The first one to save wins. Make the key field a UUID and it doesn't matter. Resolving the record number issue is relatively easy to do, even more so when you don't have to run around your database changing all sort of foreign key fields to maintain logical integrity. If changing the key field to UUID isn't an option then I think you need to design the input forms so they are collecting all the data in variables first. To save you start a transaction, create the records, save, complete the transaction and now you can display the actual records. I do this when users want to create new orders. There's a huge amount of 'stuff' associated with an order, of course, but only a handful of data is needed to actually start one. So I capture that handful first, validate it, make the record and then let the user have at it. On Wed, Mar 8, 2017 at 4:26 AM, Two Way Communications via 4D_Tech < 4d_tech@lists.4d.com> wrote: > Hi all, > > I ran into an issue while using SEQUENCE NUMBER in 4D v15.4, in a > client-server setup. > > I put the result in a field called ‘ID’, which is the primary key for that > table, and by default is unique. Auto increment is off. > The code is running in a transaction. > > Frequently, users get the error ‘Cannot save the record, DUPLICATE KEY’. > Then, I go to the structure and rebuild the index on that field (ID). > > They try again, and the record is saved without a problem. > > After a while, though, and after other users have added records to this > table, the problem re-occurs. > Rebuilding the index again fixes the problem. > > I have exported the data to a tab delimited file, truncated the table, and > imported the data. Problem is still there. > I have compacted the data file. Same problem. > > Any ideas what can be the cause? > > Kind regards, > > Rudy Mortier > Two Way Communications bvba ----------------- > > > ********************************************************************** > 4D Internet Users Group (4D iNUG) > FAQ: http://lists.4d.com/faqnug.html > Archive: http://lists.4d.com/archives.html > Options: http://lists.4d.com/mailman/options/4d_tech > Unsub: mailto:4d_tech-unsubscr...@lists.4d.com > ********************************************************************** -- Kirk Brooks San Francisco, CA ======================= ********************************************************************** 4D Internet Users Group (4D iNUG) FAQ: http://lists.4d.com/faqnug.html Archive: http://lists.4d.com/archives.html Options: http://lists.4d.com/mailman/options/4d_tech Unsub: mailto:4d_tech-unsubscr...@lists.4d.com **********************************************************************
