1

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

null
    • Birger_H
    • 5 yrs ago
    • Reported - view

    Every record has a unique field called "Id"

    Birger

    • Luis
    • 5 yrs ago
    • Reported - view

    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

    • Support
    • 5 yrs ago
    • Reported - view

    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

    • Luis
    • 5 yrs ago
    • Reported - view

    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.

    • UKenGB
    • 2 yrs ago
    • Reported - view

    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.

    • Consultant and developer
    • Javier
    • 2 yrs ago
    • Reported - view

    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...

    • John_Halls
    • 2 yrs ago
    • Reported - view

    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

      • Consultant and developer
      • Javier
      • 2 yrs ago
      • Reported - view

      John Halls Ok then, there is no "easy" way to mark a field as "unique" and ensure that it's not repeated in the database. The only way is to create an script that ensures there is no duplicate.

      I'm familiar with other databases software (Filemaker and Access ) and in both there is the possibility to ensure the field is unique for the entire table. I thought this same feature was available in Ninox, a "no-code" database that needs some coding.

      Thanks for the clarification.

    • Mel_Charles
    • 2 yrs ago
    • Reported - view

    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.

      • John_Halls
      • 2 yrs ago
      • Reported - view

      Mel Charles Spot on there Mel

    • Mel_Charles
    • 2 yrs ago
    • Reported - view

    Javier

    and a copy this field in design mode would be nice too 🤣

      • Gianluca
      • 1 yr ago
      • Reported - view

      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.

    • Alan_Cooke
    • 2 yrs ago
    • Reported - view

    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.

      • Consultant and developer
      • Javier
      • 2 yrs ago
      • Reported - view

      Alan Cooke Thanks for sharing!

Content aside

  • 1 Likes
  • 1 yr agoLast active
  • 14Replies
  • 3744Views
  • 5 Following