0

# Unique Value Formula

Hello,

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
Field := null
end
``````

However, I am looking to automate the process. So I was thinking:

``````let myUsername:= Username;
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?

11replies Oldest first
• Oldest first
• Popular
• 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 !

Like
• RoSoft_Steven
• Ninox partner
• RoSoft_Steven.1
• 3 mths ago
• Reported - view

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+");
if cnt((select Table1)[extractx(Username, "\D+") = u]) > 1 then
Username := u + text(m + 1)
end
``````

Steven

Like
• RoSoft_Steven thats a good idea Steven - I don't like the idea of deleting a name in middle tho. I much prefer never to delete a name etc but archive or flag name as deleted that way your number solution would stay intact and gets my thumbs up !!

Like
• RoSoft_Steven
• Ninox partner
• RoSoft_Steven.1
• 3 mths ago
• Reported - view

Mel Charles

Thanks anyway,

If you put this code in the trigger after update of the Username field you will at least get unique usernames. Sequential numbers or not...., it's like the ID's of ninox, once you delete a record,  you can't have the same Id number anymore.

Maybe a smarter guy can figure this out (Fred or Jacques ?)

This could also be extended to look at capital letters or not.... or also spaces but my regex is not so good....

Like
• Fred
• Fred
• 3 mths ago
• Reported - view

RoSoft_Steven Thanks for lumping me in with Jacques . Most of the time I’m just drafting in his wake.

Mel Charles , this site is good to test your RegX code. I haven’t found a good RegX tutorial. I’ve tried several online but nothing that speaks to me.

Like
• RoSoft_Steven If I were really smart, I would tell you that this is not true. Thank you all for your sharing intelligence and humility .

For the calculation of the name, here is a formula to place in the trigger after updating the name field. It checks if the name already exists and if so, adds the first free number as a suffix.

``````var n := Name;
var t := this;
var x := 0;
while count(select 'Table of name' where Name = t.Name) > 1 do
x := x + 1;
Name := n + x
end``````

If the name is unique, the program does not go into the while loop and the name does not change. Otherwise, the name field is assigned the value name + 1. If it also exists, then the loop continues with name + 2, etc... As soon as a name + x is unique, the loop stops.

Like
• RoSoft_Steven
• Ninox partner
• RoSoft_Steven.1
• 3 mths ago
• Reported - view

Jacques TUR

Looking at your solution, I see that I am making things way too complicated when it can be simpler. But hey, I learned some regex again this way....

Thanks Again.

Like
• Fred Cooool - thanks Fred

Like
• Steven

not come across extractx before.. just looked it up in EN2022 - interesting..

I get the extracting from the string of data the username bit but not quite got my head around, extra bit ie "\D+" bit  - care to explain it? so i can understand this better

Like
• Mel Charles It is a regular expression (RegExp), a kind of meta language that allows to find elements in a string of characters.
The RegExp " \D+ " extracts all non-numeric and consecutive characters. From a text like "123Jacques456", it returns only "Jacques".

Search RegExp or Regular Expressions in google.
You can also look at this page dedicated to the RexExp function of JavaScript, it's what Ninox uses behind the extractx function : https://developer.mozilla.org/en-US/docs/Web/JavaScript/Guide/Regular_Expressions

Like
• Jacques TUR Brilliant..... As ever I'm always playing catch up

Like
Like Follow
• 3 mths agoLast active
• 11Replies
• 117Views
• 5 Following