0

How to enforce unique values in text field?

Is it possible to define a text field to contain unique values only (no duplicates)?

17 replies

null
    • Support
    • 6 yrs agoTue, August 28, 2018 at 4:46 PM UTC
    • Reported - view

    Hi,

    If I understand you right you want to ensure that you are not inserting duplicates during the input. 

    That can be done with a formula on the "Trigger on Update" in the options of the datafield:

     

    let myInput := Table1.'Text_field';
    if cnt((select Table1)['Text_field' = myInput]) > 1 then
    alert("Duplicate, choose another value.");
    'Text_field' := null
    end

     

    Best regards, Jörg

      • erik
      • 1 yr agoSat, March 30, 2024 at 7:11 PM UTC
      • Reported - view

       Thanks. I found that this approach is not workable when you click "duplicate" icon in top right corner of the screen. new record had been created with duplicate value. I guess you need to add "unique value" feature for editing field. Btw how to disable "duplicate" icon for some roles?

      • Fred
      • 1 yr agoSun, March 31, 2024 at 12:14 AM UTC
      • Reported - view

      Take a look at this post. You can also use Dashboards/Pages to control some parts of the UI.

      • erik
      • 1 yr agoSun, March 31, 2024 at 7:53 PM UTC
      • Reported - view

       thank you for soon reply. failed to do, is there any video how to do this?

      • Sean
      • 1 yr agoSun, March 31, 2024 at 8:57 PM UTC
      • Reported - view

       

      I have attached a simple demonstration DB. You can replace the field isDisplayed with a user role. The Formula field Toggle Duplicate Icon can't be hidden with Display field only, if set to null. It needs to be styled to hide it.

      • Fred
      • 1 yr agoSun, March 31, 2024 at 11:47 PM UTC
      • Reported - view

      You can look at my sample DB in this post. If you have users use a dashboard/Page then you can control how data gets entered.

      • erik
      • 1 yr agoTue, April 2, 2024 at 6:40 PM UTC
      • Reported - view

       Thanks a lot Sean for sharing file w sample. Very helpful

      • erik
      • 1 yr agoTue, April 2, 2024 at 6:42 PM UTC
      • Reported - view

      good option, thanks a lot

    • Laurent
    • 6 yrs agoTue, August 28, 2018 at 7:53 PM UTC
    • Reported - view

    +1

    • michaelbroesdorf
    • 6 yrs agoWed, August 29, 2018 at 6:50 AM UTC
    • Reported - view

    yes, that is exactly what I was looking for - thanks a lot!

    • Personal
    • Art
    • 5 yrs agoTue, May 21, 2019 at 1:20 AM UTC
    • Reported - view

    Thank you, this was helpful.

    • Rusty
    • 4 yrs agoWed, July 22, 2020 at 2:14 PM UTC
    • Reported - view

    uhm.... Table1.'Text_field' not work for me. I can't select the table in formula.... 

    • individual
    • ekaterina
    • 3 yrs agoSat, August 14, 2021 at 2:58 AM UTC
    • Reported - view

    GooD!

    • BAJABI SL
    • jjrecort
    • 2 yrs agoThu, October 13, 2022 at 5:55 PM UTC
    • Reported - view

    Thanks a lot, Jorg, It Seems to be working for me, but it just erases the duplicated serial, and no alert appears. I'm my case, I need unique serial numbers

     

    this is my code

    let myInput := Serial;
    if cnt((select Stock)[Serial = myInput]) > 1 then
        alert("Already in Stock!");
        Serial := null
    end

     

    I'm new at NINOX, coming from Filemaker, which is just a checkbox on the field options menu.

    Thanks a lot.

      • Alan_Cooke
      • 2 yrs agoThu, October 13, 2022 at 7:24 PM UTC
      • Reported - view

      jjrecort If you are using the Cloud version alerts do not work on Trigger after update.  I had exacty the same issue (serial numbers and have resolved it this way.
      I have a field "SERIAL NUMBER" and a Text field named:  "DUPLICATE FLAG" (Display if: set to null as default.  I have a Formula field named "Duplicate Flag Display" who's formula is 'DUPLICATE FLAG' - style set to red.

      Code:

      let xCurrRec := number(Id);
      'SERIAL NUMBER' := upper('SERIAL NUMBER');
      let xSN := 'SERIAL NUMBER';
      "Scans table for duplicate serial numbers";
      if 'SERIAL NUMBER' != null then
          let x := cnt((select 'Project Equipment')['SERIAL NUMBER' = xSN]);
          if x > 1 then
              'DUPLICATE FLAG' := "!! DUPLICATE SERIAL NUMBER !!"
          else
              'DUPLICATE FLAG' := null
          end
      end;
      dupe01(xSN, xCurrRec, 'DUPLICATE FLAG')

      Let me know if this works for you.  If not I can help.

    • BAJABI SL
    • jjrecort
    • 2 yrs agoFri, October 14, 2022 at 9:19 AM UTC
    • Reported - view

    Thanks a lot, Alan!

    I'm very new to NINOX, and I just discovered yesterday there are two versions of NINOX: Desktop app and web-based, which I guess is the NINOX Cloud.

    I solved it with the following lines.

     

    let myInput := Serial;
    if cnt((select Stock)[Serial = myInput]) > 1 then
        alert("Already in Stock!");
        Serial := "Duplicat"
    end

     

    I'm keeping the original alert, just in case I move to the desktop app.

    If my input is a duplicated serial number, then I get the "Duplicated Serial" directly on the field

    This way is very clean and visual for the user.

     

    Now to fight on how to lookup data from other tables without manual input! :)))

     

    Thanks a lot!

      • BAJABI SL
      • jjrecort
      • 2 yrs agoFri, October 14, 2022 at 9:20 AM UTC
      • Reported - view

      Alan Cooke