Unique Value Formula
I am trying to create a formula to check for and correct a username to make it unique.
Looking at https://forum.ninox.com/t/60hr83k, Jörg mentioned something like this:
let myField := Field; if cnt(select Table where Field = myField) > 1 then alert("This content does already exist, please enter another one!"); Field := null end
However, I am looking to automate the process. So I was thinking:
let myUsername:= Username; if cnt(select Table where Username = myUsername) > 1 then Username := result + 1 end
However, this formula will not work. Yes, it will add a digit to the end of the username making it unique. However, if there are multiple SMoore usernames, the the result could ultimately be SMoore111.
I would like the value to increment by 1 each time. So SMoore1 , SMoore2 , SMoore3 and so on..
What is the best route to take for this?
There maybe more elegant solutions to come on this but
In one of my early databases, I used a simple solution of the username plus a separate incrementing number field. then I used to formula field to bring them together and tested on that for the unique value.
which would give you - Smith1, Jones2, Smith3
but won't work if you want Smith1, Smith2, Jones1 etc
Always meant to make it more elegant but to date have not gotten around to it based on if it ain't broke why fix it method of programming !
This could work for you but it always takes the highest number after the name+1, so if you delete for example SMoore2 in your list of 4, the next one will be SMoore4 (you always have one SMoore without number).
let u := extractx(Username, "\D+"); let m := number(max((select Table1)[extractx(Username, "\D+") = u].extractx(Username, "\d+"))); if cnt((select Table1)[extractx(Username, "\D+") = u]) > 1 then Username := u + text(m + 1) end