Unique Field in a table
sorry but I didnt fund it in the documentation... my bad probably
How can I force a field in a table to be unique? eg a field that is used in a N:1 table relationship...
I dont want the linked table to have several copies of the same reference record in the N:1 relationship
Preventing duplicates is not possible yet. Though you can use a function to identify duplicates.
If you have questions, also visit one of our webinars:
Really? You cannot specify a UNIQUE constraint on a column?
Why is this "not possible yet"? It's standard SQL and also part of SQLite which is what Ninox uses (for the app anyway, no idea bout Cloud version). How can they possibly have not yet made it possible. It's such an important feature of a database. This is extraordinarily remiss of Ninox.
WHEN is this important (and standard SQLite) feature going to be finally adopted in Ninox.
Furthermore, what happens if the UNIQUE constraint is applied to the SQLite database. Would Ninox simply fall over if it ever tried to insert a duplicate value or what?
Seriously, this is an important lack in any database. Ninox need to address this as a matter of urgency.
Still no way to create create a unique field? I'm creating a database that needs the passport (or another existing unique person id) as a unique (key) field. This way the client needs to enter only one time the customer information, and also has a way to search for the client in the database, or to search for all the invoices for this client... and also avoid to enter the same client a lot of times...
This way the client needs to enter only one time the customer information, and also has a way to search for the client in the database, or to search for all the invoices for this client... and also avoid to enter the same client a lot of times...
This is all taken care of by Ninox with the ability to use their reference fields to link records together. Additional unique information, like a passport number, is a candidate key and ensuring unique values is not difficult to implement with the scripting tools available.
Unfortunately the term - "No code" is really just marketing speak by the industry and is a response to the so called demand for database/app creation that should be as easy a building a simple spreadsheet for non programmers! Apparently - that is what users wanted and that is what the industry have provided. The term is mis understand and very mis used and should really be advertised as "Low Code" so as to be realistic.
It is perfectly feasible to create some complex Ninox apps without a single line of code. It's approach is somewhat different to Excel/access etc and once you have your head around some aspects it really is very good. However adding scripts etc really boosts Ninox's ability!. I have a simple one line piece of code to test for duplication of a unique value and to warn me/users.
I have asked support many times for a unique field and a sequenced number field too (or Auto Number in your Access speak!) but for now there are workarounds that don't prevent or stop me from using a great piece of software.
I use this piece of code to check for duplicate serial numbers when adding to the Serial Number field.
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 !!"
'DUPLICATE FLAG' := null
dupe01(xSN, xCurrRec, 'DUPLICATE FLAG')
"!! DUPLICATE SERIAL NUMBER !!" = a formula field type as it can be styled.