"Serialnumber" or "sequential" number in Xano!

endrefyl

Hi there! Does anyone know a good way to add a sequential number to each row in xano?

Query the ,mates number always gets me duplicates :P

Michael Udinski

Hey @endrefyl - here are a couple of different tutorials that cover creating incremental/sequential logic. You should be able to adapt a flavor of these :)

https://youtu.be/jUAk788kwmo

https://youtu.be/JFOueW2l4_Q

A Unique Index should also help enforce uniqueness on the field.

endrefyl

Hi, and thank you so much for your reply @Mikhael Udinski! The logic in those videos are 100%! The only issue is when/if several users are making calls at the same time on the same table/object. I don't like that the calls may get denied from the API pov. (unique index), so my wish was if there exist a solid way to make this redundant without costing to much of the server capacity and highly complex functions, just like the "id" which always is unique and incremented +1. Since I want to show each user their respective "serialnumber" I can't just use the primary id it self :/

The videos above would work if there was a way to "queue" the requests depending on the req/seconds, or delay the function withing the api etc. (without just delaying and starting every call simultaneously).

Example from my "stress test" earlier, wher I clicked 7-8 times pretty fast (made the post create call from the front end). And yes, I could make a pause front end for each user or even maybe for every user within that table, but I would really try everything else first :)

Guillaume Maison

@endrefyl
i recommend you to do this :
1. create a table representing a key/value pair

2. each row represent some kind of a unique sequence you may use.
3. create a function that just increments the value field of a specific sequence and returns the value :

4. whenever you need a new sequence value, you call the function …

it'll be treated in sequence.. i stressed it with multiple clicks at once on a test app… no duplicates …
May be another stress test can be made by implementing several call at once …

Moreover, you can do a more specific function by, for example, defining a sequence per user (adding a user column) and using the according parameters and get record criterias …

Anyway, you got the idea :)

endrefyl

Thank you so much for your reply @Guillaume Maison! Appreciate it :)

I tried this function below as a test (also with the table refference as above), but still was able to create duplicates unfortunately :( The ironi is that xano is so fast creating new rows, that this kind of logic is is tricky since the function could be triggered practically at the same time (I first create the record, then run this function and return the id, and at last edit/update it with the respective number.

endrefyl

I did try to set up a unique index on the user + seq id field, and now I got them empty when it tried to "stress test" again. Maybe I could just then "loop and try" every empty field, since the row exist just not the serial_id field?

endrefyl

Hi again!

Do anyone know if there is a way to make a function continue even though the "Edit record" returns an internal error 500 (i think)? It's because the update has been trying to update a duplicate to a unique field in a table, and I want to try a new iteration with a +1 value, but it seems like the function stops even though I have sat it up with an "if" ? @Guillaume Maison any thoughts? If this could work, a 100% sure guaranteed non duplicates values will occure.

Guillaume Maison

@endrefyl i've thought again on your problem, and perhaps the simplest way woud be to have as many tables as you need sequences, each with just the id & created_at.
And each time you need a new id :
1. you do an insert into the needed table (ie the sequence_name parameter and the according conditionals)
2. retrieve the inserted row into a variable
3. user the variable.id value as your unique sequence
(all of the above can be in a function)

and have a task that deletes on a daily basis whatever is in the table (the table id sequence is not based on table content).

MattLaro

The problem you have here is that you're trying to do a serial operation using asynchronous calls. By the nature of asynchronous calls, the tradeoff is that it will be impossible for your calls to know if an ID is really taken or not and hence, to guarantee that no duplicate IDs will be created unless the ID generation itself is handled outside of your asynchronous call (ex: incrementation directly in a database). Even if your call is so short that it almost always work, almost always will haunt you at one point or another.

There are some ways to go around this, but it does require substantially more efforts when you do not have control of your DB. You could, as example, queue all your processing and then, by task job, process that data and make sure the next call waits that the previous one ends (with a For Each). The tradeoff here is that your client calling your endpoint will not know if everything went well or not unless there's some Webhook callback configuration. The data also won't be available until the task to process that data has been executed.

The other way would be to use a non sequential ID (like a UUID), which is a pretty safe way to go as well.

@Guillaume Maison 's suggestion would also work since, in this case, it is managed by the DB on insertion.

endrefyl

Thank you for your replies @Guillaume Maison and @MattLaro ! :) I have really tried, but as you wrote, it's on a level I cannot access. So I must just do an assesment of which tradeoff is worser than the other in this case.

I might make the ID column unique at the table, and have a task job to edit/update potential losses in case of "duplicate error".

(FYI: I first add the record with all its "static" values first, and second edit the "ID" values at last to make sure the main record is added).

Thanks again!

endrefyl

Just for future reference:

I solved this by making a workspace just dedicated for "ID-tables". I then use the metadata-API (from the orginal workspace) to create a new table for each user/customer/company etc. to make each individual row unique and individual. Works like a charm :)

Best regards

Guillaume Maison

@endrefyl thanks for the feedback :)
And great "hack" :)

Ambroise

It's "funny" how no-code forces people to reinvent the wheel. And this one is square.

Auto-increment is something natively supported by Postgre. https://www.tutorialspoint.com/postgresql/postgresql_using_autoincrement.htm

I'm just gonna use uuid v4 or similar to avoid running into this mess.