How could I create an numeric sequence for an...

(Pedro Paulo Sant'Anna) #1

How could I create an numeric sequence for an ID field as an UNIQUEID() function?

(Philip Garrett) #2

@GreenFlux

Doing all of the Adds while all users are connected helps a lot because it reduces the length of the “window” where two or more users can create records with the same sequential key., but it does not eliminate the “window” where the problem can occur.

Imagine you have two users called A and B who do the following: 1. User A read the table and the highest Id is current 100. 2. User B read the table and the highest id is still 100. 3. User A adds a record and it is assigned the Id 101. Remember that the client is running the MAX formula locally using the data they read in step 1.

  1. User B adds a record and it is assigned same Id value of 101. This happens because this client is running the MAX formula locally using the data they read in step2. 5. User B does a sync. This write a records with Id 101. 6. User A does a sync. The server looks to see if a record with Id 101 already exists. It finds the record that user B added, so it turns user A’s Add into an Update. This overwrites user B’s data in that record with user A’s data. In database lingo, turning an Add (aka Insert) into an Update is said to be an “Upsert”. We are talking about adding other conflict resolution strategies in the future and allowing you to choose among them, but we currently do an “Upsert” when such a conflict occurs.

The longer the duration between step 2 and step 5, the more likely the duplicate Id problem. That windows can be really long if either or both of the users are offline between steps 2 and 5.

By the way I want to thank you for jumping in and helping out on the community. It really helps. I only jumped in on this one because this is an obscure area of database lore that is not widely understood. I have been working on databases and transaction processing for fifty years. I managed database and transaction

processing development groups at Burroughs, Tandem Computers, and Microsoft for almost that entire time.

(GreenFluxLLC) #3

First, make sure you have the Column type set to Number. Then, in the Column definition>Initial Value, try:

MAX([ID Field])+ 1

(Philip Garrett) #4

@Pedro_Paulo_Sant_Ann

I general this is impossible if you have more than one user of your app for the reasons described in this article help.appsheet.com - Sequential Keys

Sequential Keys help.appsheet.com

(GreenFluxLLC) #5

@Philip_Garrett_Appsh Good info! Thanks for sharing. So does that mean that it IS possible if the developer chooses to disable of-line record creation?

(Pedro Paulo Sant'Anna) #6

@GreenFlux Thank you!