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 ago
    • 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
      • 8 mths ago
      • 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
      • 8 mths ago
      • Reported - view

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

      • erik
      • 8 mths ago
      • Reported - view

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

      • Sean
      • 8 mths ago
      • 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.

      Hide duplicate icon.ninox
      • Fred
      • 8 mths ago
      • 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
      • 8 mths ago
      • Reported - view

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

      • erik
      • 8 mths ago
      • Reported - view

      good option, thanks a lot

    • Laurent
    • 6 yrs ago
    • Reported - view

    +1

    • michaelbroesdorf
    • 6 yrs ago
    • Reported - view

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

    • Personal
    • Art
    • 5 yrs ago
    • Reported - view

    Thank you, this was helpful.

    • Rusty
    • 4 yrs ago
    • Reported - view

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

    • individual
    • ekaterina
    • 3 yrs ago
    • Reported - view

    GooD!

    • BAJABI SL
    • jjrecort
    • 2 yrs ago
    • 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 ago
      • 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 ago
    • 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!

    Screenshot 2022-10-14 at 11.14.08
      • BAJABI SL
      • jjrecort
      • 2 yrs ago
      • Reported - view

      Alan Cooke

Login to reply

Content aside

  • 8 mths agoLast active
  • 17Replies
  • 2974Views
  • 5 Following