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

[thorsten.langner]

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…

Result:

The result will be like:
UN-01
UN-02
…
UN-09
UN-10
UN-11
…
UN-99
UN-100
UN-101
…

The Expressions in more readable:

texttointeger(
               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