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
Thanks!
luis
14 replies
-
Every record has a unique field called "Id"
Birger
-
Hi
(thought I had replied- but don’t see it so sorry if this creates a duplicate)
i know about the id but that is not the solution - what I need is eg to ensure when nnimport is made that I duplicated records exists ... for instance by defining a field as Key
i don’t seem to get this in ninox
-
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:
https://zoom.us/webinar/register/WN_zoSk2qyNSz6vLRFF1zlakg
Best, Alex
-
ok... think it is a pity eg that there isn't a "Key" field that ensures "unique" i.e. whatever tests I have to create should be automatic
But at least I know now that it is not something I oversaw.
Thanks for the Webinar Link.
-
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...
-
Hi Javier
Javier said:
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.
Regards John
-
Javier
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.
-
Javier
and a copy this field in design mode would be nice too
-
Mel Charles Really!
I'm definitely not an expert but I like Ninox a lot and I think that it is much easier in its approach, comparing to Filemaker or other DB softwares I've used in the past, but I've found out that some things I took for granted in a database program are not as much granted as i thought
This copy and paste thing is definitely one of them.
About the way to prevent the creation of duplicate records: I remember i used to do it in Filemaker with a self relation and then comparing simultaneously both the ID and the other field i needed to be unique: if the Unique field was the same, but the ID was different the record couldn't be created at all.
Maybe there's a way to replicate this kind of trick in Ninox? Even just producing a "delete" of the record just created. What I want to do, though, is avoiding the use of buttons when inserting data. I think that it's much better if one can work without taking his hands off the keyboard. I usually use buttons to generate reports or to connect different tables of the database.
-
-
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 !!"
else
'DUPLICATE FLAG' := null
end
end;
dupe01(xSN, xCurrRec, 'DUPLICATE FLAG')
"!! DUPLICATE SERIAL NUMBER !!" = a formula field type as it can be styled.
Content aside
-
1
Likes
- 2 yrs agoLast active
- 14Replies
- 3863Views
-
5
Following