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
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? 

11 replies

null
    • Mel_Charles
    • 1 yr ago
    • Reported - view

    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 !

    • Ninox partner
    • RoSoft_Steven.1
    • 1 yr 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+");
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

    Steven

      • Mel_Charles
      • 1 yr ago
      • Reported - view

      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 !!

      • Ninox partner
      • RoSoft_Steven.1
      • 1 yr 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....

      • Fred
      • 1 yr 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.

      • Ninox developper
      • Jacques_TUR
      • 1 yr ago
      • Reported - view

      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.

      • Ninox partner
      • RoSoft_Steven.1
      • 1 yr 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.

      • Mel_Charles
      • 1 yr ago
      • Reported - view

      Fred Cooool - thanks Fred

    • Mel_Charles
    • 1 yr ago
    • Reported - view

    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

      • Ninox developper
      • Jacques_TUR
      • 1 yr ago
      • Reported - view

      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

      • Mel_Charles
      • 1 yr ago
      • Reported - view

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

Login to reply

Content aside

  • 1 yr agoLast active
  • 11Replies
  • 243Views
  • 5 Following