Auto-incrementing IDs in Retool Database

Queries and Resources
1

I can't seem to solve what seems like a relatively straightforward problem.

I'm using a Retool Database to store a Users table. Each new User added to table need an auto-incrementing ID. The ID needs to be 8 digits in length, as it's used to generate a barcode on an employee ID.

Retool Database has a built in auto-incrementing integer ID field, but as far as I can tell, there's no way to manipulate the starting value of that integer to, say, 10000001 so the subsequent value is 10000002 and so on. Is there any way to do this directly in the database, such that when a new row is added via a form, an input in the ID field can be omitted since it would be auto-generated at time of row insertion?

Related question: can the above be accomplished, while also concatenating a character or string? So each new row in the table would be auto-assigned an ID like U-10000001, U-10000002, U-10000003 and so on?

Thanks! Open to other solutions to accomplish this.

2

Hi @AutomateAnything.io,

I don't know much about the RetoolDatabase but you can automate this by creating a new column for the users table and using a JS Query to save your form:

  1. Create a JS Query -> handleUserSave
  2. Create a Query -> getMaxId
  3. Create a Temporary state variable -> newUserId
  4. Make your submit form handler call handleUserSave

getMaxId:

SELECT MAX(id) AS currentMaxId FROM users

handleUserSave:

// get the current max id
await getMaxId.trigger()
// update the id to the next id
const id = getMaxId.data[0].currentMaxId + 1
// pad the string with zeros
newUserId.setValue( 'U-' + id.toString().padStart(8, '0') )
// call your current save user query
await saveUser.trigger()

This could have some issues with simultaneous users but ...

2 Likes
3

Thanks, Ron! This is a great solution. I implemented it nearly verbatim. Works as expected. :pray:

1 Like
4

Nice - like I said the only real issue I can see is if multiple users were saving at the same time - you could have a contention problem where two records get the same new id. Another option to prevent that would be to leave that new column blank, and then update it AFTER you save the initial record. If you think concurrency will be an issue.