How to prevent user from entering same number again (How to increment more complex table ID's)


I have an app which use unique number to create the new ID every time when user key in the unique number. I have others data which will be tied to those unique number every time they key-in

My question is how can I prevent user from key-in the unique number again? and it will show error when the user accidentally keyed-in the unique number again.

I have tried some conditions but it allows user to key in that unique number again and overwrite the data that is tied to the unique number. but it will not create new ID since ID cannot be duplicate

Only second time if the user key-in that unique number again, then the error will pop out.

Need help with this.

how should i put the conditions in “If” to prevent user from key-ing in the same unique number again?

If you change the action on Table records to “Create Record” instead of “Create or Load Record” then you cant load this record a second time.

Isn’t that what you want?

yes, it works. thanks

can i ask you one more thing?

from the table above, lets say I already have my ID as Un-01, Un-02, Un-03…
what if i want to create that ID as Auto incremental by just click button without key in the Un-01, Un-02 etc, etc.

I have seen the video about auto incremental ID, but it explained only using number. How can I make it works with alphabet on it?

> this is from the video tutorial i have tried

You need to make sure, to extract the number value, increment it and then re-attach it.

It is easyer if it is always the same structure like UN-01 but it is more safe to catch all contingencies.

So you could grab the last two digits, but what if you reach 100?
The more secure way is to look for the “-” and grab everything bevore as string and behind as number. then count the number up and re-combine it with the “-”

Then the only condition is, you have to always have only numbers aftzer the first and only “-”…

I’m not sure but maybe I can set up an example later.

Create a query with limit 1 and sorting new to old:

Create an aggregation with Mode on ID:

  • Extract the string after the “-” (e.g. “01”) make it an Integer, increment it by one
  • Check if you want to add a “0” when the number is lower than 10 and make it a string.
  • Extract the string before “-” (or hardcode “UN-”) and add the new number-string
  • create a record with this one…


The result will be like:



The Expressions in more readable:

               right(@Table Aggregation.Latest_ID , ( 
                                                      len(@Table Aggregation.Latest_ID )                 // length of the ID-String
                                                      - find('-', @Table Aggregation.Latest_ID ))        // - number of characters befor "-" 
                                                      - 1)                                               // - 1 (the "-" itself)                --> amount of digits from right to "-"
                     + 1                                                                                 // increment by one
left(@Table Aggregation.Latest_ID , 
                                    find('-', @Table Aggregation.Latest_ID ))                          // - number of characters before "-" 
                                    +1                                                                // +1 for "-" itself
                                    + if(
                                          @Variable.counter  < 10, '0' + @Variable.counter            // add 0 if < 10 and make it a string
                                          , '' + @Variable.counter )                                  // if >= 10 make it a string with no leading 0
1 Like

Thanks much for the help!!

You’re welcome,

since this is a more frequently asked question, I changed the title, to make it more findable.

I hope thats fine to you…

Yes. can go ahead with the title changes. no worries at all :smiley: