Get Incremented Value for next insert

Hi Guys

Please may you assist.

We have a form to fill in for a DB record but we are using a unique Ref that is structured for internal use. Eg. REF00001

We want to have the next time the form needs to be filled into auto increment the REF00001 to REF00002 automatically and display it in the input text box (Disabled so the user is not able to change it) this should also be then used in the insert for the form being filled out.

Any advice would be greatly appreciated. (Loving the Wappler Platform)

Wappler supports integer or UUID autoincrement fields however if all that changes is the number then just use integer and use a formatter to present the integer in the string format when displayed I.e. 2 is simply reformatted to REF00002 on display.

Thank you Mr Brain, in the DB the Record is saved as a starting point REF00001 and from there on the query need to check the latest record and increment the last record/ID and display what would be the next insert.

Sorry i am fairly new with wappler all i learned was from your videos :smiley: UUID is still unfamiliar to me

That is really a bad way to do this. Auto increment fields are a feature of databases but you should never do this manually.
A transaction takes a finite time, if you read the last record,increment by one, then write back, what if another transaction reads the record between the read and write? You will end up with duplication. That us why relational databases have auto increment keys,to automate the increment process and ensure duplication does not occur

1 Like

how would 1 go about using this UUID in the matter that we would need it? going the right way?

Asking a bit of guidance?

I don’t think you want UUID, instead of REF00001 you’d end up with some gibberish of characters, like:

550e8400-e29b-41d4-a716-446655440000

This is the issue, you should not store it like this, but rather just the integer, e.g.:

REF00001 -> 1
REF00002 -> 2
REF00003 -> 3

The correct way is you don’t check the latest record, and you don’t include any specific ID in the Database Insert step. The database software will automatically take care of inserting the correct auto-incremented ID if the column is set to AUTO_INCREMENT.

@Apple You are absolutely correct thank you, I would like to pursue this way of using INT for the REF00001 -> 1

How would I add the “REF-” in front of the DB value 1,2,3,4 of each row?

I tried to have this column set to AUTO_INCREMENT but got this query error:
Query error:
#1075 - Incorrect table definition; there can be only one auto column and it must be defined as a key

the table already has a main_id column which runs as the PK and the is set to AUTO_INCREMENT

Thank you for your assistance thus far.

I think the message explains all, you can only have one auto increment key per table, perhaps you added a new one while the old was active?
Perhaps a screen shot of your db structure would help