How to enforce unique values in text field?

michaelbroesdorf
6 yrs ago
17replies

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

- 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
  
  11 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
  
  11 mths ago
  
  Reported - view
  Take a look at this post. You can also use Dashboards/Pages to control some parts of the UI.
- erik
  
  11 mths ago
  
  Reported - view
  thank you for soon reply. failed to do, is there any video how to do this?
- Sean
  
  11 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
  
  11 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
  
  11 mths ago
  
  Reported - view
  Thanks a lot Sean for sharing file w sample. Very helpful
- erik
  
  11 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

Welcome to the Ninox community

How to enforce unique values in text field?

17 replies

Content aside

Categories

Overview