0

How I can prevent duplicate records being added?

Hi. I'm a new user on my first project and have set up a simple database. I need to find out how I can prevent duplicate records from being entered in a table.  For example, if first-name and last-name and license-number are identical to an existing record, do not permit the record to be added.  Thank you.

37replies Oldest first
  • Oldest first
  • Newest first
  • Active threads
  • Popular
  • Hi,.

    you can use the following formula on the "Trigger after update" at the field you want to check:

     

    let myField := Field;
    if cnt(select Table where Field = myField) > 1 then
    alert("This content does already exist, please enter another one!");
    Field := null
    end

     

    Best, Jörg

    Like
    • peter
    • peter.1
    • 4 yrs ago
    • Reported - view

    Thank you Jörg, but I'm unclear how that would be coded where the contents of 3 separate fields need to be compared eg field1, field2 and field3, where the new record may match on any 2 of those field values but not all 3?

    Like
    • Support
    • Support
    • 4 yrs ago
    • Reported - view

    Hi Peter, 

    you can connect the condicions after the if with an and like that: 

     

    let myField1 := Field1;

    let myField2 := Field2;

    let myField3 := Field3;

    if cnt(select Table where Field1 = myField1) > 1 and select Table where Field2 = myField2) > 1 and select Table where Field3 = myField3) > 1  then
    alert("This content does already exist, please enter another one!")

     

    Best, Jörg

    Like
    • peter
    • peter.1
    • 4 yrs ago
    • Reported - view

    Thank you Jörg - appreciated!

    Peter.

    Like
  • Jorg

     

    I tried the above but it only appears to work on the app version on on the online version. Very strange????

    Any ideas?

    Thanks in advance

    Tim

    Like
  • 'on' = 'not'

    Like
    • Support
    • Support
    • 4 yrs ago
    • Reported - view

    Hi Tim, 

    Yes you are right. The alert does only work in the App for now. We have it already on our change request list and hope to release the feature also for the browser version soon. 

    Thank you very much for your patience.

    Kind regards, Jörg

    Like
    • ESM
    • ESM
    • 3 yrs ago
    • Reported - view

    Hi, I have a table with a field date and I want to keep this unique; I tried to use the above formula but it does nothing; can u pls help me to use this formula with dates field?

    Like
    • Nick
    • Nick
    • 3 yrs ago
    • Reported - view

    This code works:

     

    let myDate := Date;
    if cnt(select TableName where Date = myDate) > 1 then
    alert("This Date does already exist, please enter another one!");
    Date := null
    end

     

    Maybe some typo;

     

    Nick

    Like
    • ESM
    • ESM
    • 3 yrs ago
    • Reported - view

    Hi Nick,

    I did include the code you sent me (attached screenshots) and I still can enter same date on the next fields of my table without any warning. Any hint?

    Captura de pantalla 2018-10-15 a la(s) 8.42.34 p. m.

    Captura de pantalla 2018-10-15 a la(s) 8.44.51 p. m.

    Like
    • Nick
    • Nick
    • 3 yrs ago
    • Reported - view

    I work with Mac app. Maybe web app is different?

    Like
    • ESM
    • ESM
    • 3 yrs ago
    • Reported - view

    I use the web app; seems Ninox does not support this option on web

    Like
    • Support
    • Support
    • 3 yrs ago
    • Reported - view

    Hi, 

    That is right: The alert function does only work on buttons in the web app at the moment, but we are already working on that. Thank you for your patience. 

    Best, Jörg

    Like
    • blackie
    • blackie
    • 3 yrs ago
    • Reported - view

    Can dialog() be used instead of alert()?

     

    i have been playing with dialog(), and it seems to work on the web app.

    Like
    • blackie
    • blackie
    • 3 yrs ago
    • Reported - view

    I guess not. I forgot dialog() is only used with buttons.

    It looks like the if statement is not executed if it has alert() or dialog().

    If you take the alert() out, it will not allow you to add identical dates. There will be no warning, but it is better than the if statement not executing.

     

    let myDate := Date;
    if cnt(select TableName where Date = myDate) > 1 then
    Date := null
    end

    Like
    • Support
    • Support
    • 3 yrs ago
    • Reported - view

    Hi, 

    You are right, also this function does only work on buttons in the web app at the moment. We are already working on that too. 

    Best, Jörg

    Like
    • peter
    • peter.1
    • 3 yrs ago
    • Reported - view

    This concerns me also.  It would be helpful to have a "unique with" field attribute available as a feature rather than needing to hard code the function.  EG Field-A in conjunction with Field-B (and more) must be unique. Scenario (Family-name is UNIQUE WITH Given-name and Date-of-birth).  Means that where those conditions all are true then the entry is rejected.  

    Like
    • ESM
    • ESM
    • 3 yrs ago
    • Reported - view

    Unique field is important on web app; hope Ninox will solve this soon

    Like
    • John
    • John.3
    • 2 yrs ago
    • Reported - view

    Yes I am needing the same solution. Making Inventory Table... Do not want duplicate part #s

    Like
  • Hi, 

    Ninox provides the unique field "Id" which is automatically created by Ninox and can not be canged.

    Kind regards, Jörg

    Like
  • Hi all,

    update: suggested script block (...)

    let myField := Field;
    if cnt(select Table where Field = myField) > 1 then
    alert("This content does already exist, please enter another one!");
    Field := null
    end

    (...) the GUI alert

    * is correctly displayed on macOS app

    * is not displayed on web app (Win10, FireFox 68.2.0esr (64-bit).

    Pls inform.

    wolf

    Like
  • @ Wolfgang, where did you put your script? In the Trigger after update of the 'Field' is the right place. 
    try using =2 instead of >1 in the script. 
    this works for me in the webapp windows 10 chrome.

    Also see : https://ninoxdb.de/en/forum/use-cases-5abd0b9c4da2d77b6ebfa395/reviewing-duplicates-in-one-field-5de7ee877ea35873cb8dab6d?post=5de8081c7ea35873cb8dab74&page=1

    Steven.

    Like
    • NIOXUS
    • NIOXUS
    • 2 yrs ago
    • Reported - view

    Hi Wolfgang - We are going to be going over this very topic in our Ninox Learning Lab at 12:00 Noon EST.  You can register for the FREE event here >>>  https://www.nioxus.com/webinars

    All attendees will receive a free Global Function that can be copied and pasted right into any database that will enable you to test for and prevent duplicates.

    I hope to see you there.

    Andy.

    Like
    • Christian
    • Christian.1
    • 2 yrs ago
    • Reported - view

    The script works kind of ok for me in (on the Mac) in that it does prevent me from adding a duplicate. However, it adds an empty record now. Is there a way to programmatically (i.e. in the script) remove it or do I have to klick on the waste bin icon and then confirm deletion of the empty record?

    Like
  • To delete it in the script you can use: delete this

    Steven

    Like
Like Follow
  • 8 mths agoLast active
  • 37Replies
  • 11247Views