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
    • Julien
    • Julien.1
    • 2 yrs ago
    • Reported - view

    HI,

    Despite all the previous help, I fail to prevent double records based on a Location field (which must be unique).

    Is there any special setting to look after ?

    Looking forward to hear from you,

    Regards,

    Julien

    Like
  • This script does not actually work very well, a user has to change a field on every single entry in order for the script to work. IT does not automatically remove duplicates as a person enters, and if a person changes updates multiple fields at once (either by dragging or using the modify all option) it misses all duplicates. It only works if someone updates each entry one by one... the opposite of efficient 

    Like
  • Mine works fine but with the following limitation :

    I use the first text field on my customers form to hold my customer account number (unique)

    Putting the code in the trigger on update or indeed the form trigger on create does not work - However putting it into a button script next to my account field it works.

    The one thing I do check is to ensure that Ninox does not interpret account codes (ACP501 and acp501) as different. So I convert any entered code into upper case before doing the duplicate check.

    Account code field (trigger on updare script)

    (let myChk := Custid;
    Custid := upper(myChk)

     

    Button script

    let myField := Custid;
    if cnt(select Customers where Custid = myField) > 1 then
    alert("This Account Code Already Exists ! , Please enter another one!");
    Custid := null
    end

    This gives me no duplicates and no blank entries

    Mel

    Dunno why Ninox don't give a this filed is uniue no deplicates option

    Access/dataease and other good databases etc have done this for twenty years plus .......

    Like
  • Another approach would be to use a formula field which give a warning there are duplicated fields, which would not require triggers.

    let a := upper(myField1);

    let b := upper(myField2);

    if cnt((select Customers) [upper(myField1) = a]) > 1 then

    styled("myField1 contains Duplicates","red")

    else if cnt((select Customers) [upper(myfield2) = b]) > 1 then

    styled("myField2 contains Duplicates","red")

    end

    end

     

    Steven

    Like
  • Steven

    Like this alternative

    Tried it and it also helps stops users from saving blank records

    also set it to hide itself not to display the actual formula field itself until true

    Also has the advantage that it works on old duplicated recods in error when you re open them and you may not think to use the button to do a retrigger

    Like it !!! Neat :-)

    Like
  • I was incorrect with my post, it works if you put the code from 2018 into update when triggered of the actual field, not the global table update trigger field (if that makes sense) and the equality actually has to be set to =1 ,not =2 or not <1, =2 causes duplicates to be deleted after 2 entries have been made, =1 causes duplicates to be removed after one entry has been made.

    Like
    • Julien
    • Julien.1
    • 9 mths ago
    • Reported - view

    Hi,

    Following my previous post, I succeded in applying a formula to prevent duplicated location.

    let myField := Localisation;
    if cnt(select Immeuble where Localisation = myField) = 2 then
    alert("Adresse déjà enregistrée!");
    Localisation := null
    end

     

    In order to complete the wanted action, how to redirect the user to the already existing location record (and eventually delete the current record, now null), please ?

    Looking forward to hear from you,

    regards

    Like
  • Julien,

    You can use a dialog instead for this:

    let me := this;
    let myField := Localisation;
    if cnt(select Immeuble where Localisation = myField) = 2 then
    let responce := dialog("Warning !", "There is a duplicate found, delete this record?", ["Yes", "No"]);
    if responce = "Yes" then
    delete me
    else
    if responce = "No" then popupRecord(me) end
    end
    end

    You can put his in the setting of the Tab-element 'Trigger after hide' If you don't have a Tab-element, you can add one and place it on the top of the list when editing the form by using the Edit Fields button on the bottom left.

    Steven.

    Like
    • Julien
    • Julien.1
    • 9 mths ago
    • Reported - view

    Thank you very much Steven !

    Instead of a Y/N AnswerBox, how can an only  "OK" button do both, deleting the current record and simultaneously go to the already existing location record, please ?

    Looking forward,

    Best regards,

    Like
  • Julien,

    Try this:

    let me := this;
    let myField := Localisation;
    if cnt(select Immeuble where Localisation = myField) = 2 then
    let responce := dialog("Warning !", "There is a duplicate found", ["OK"]);
    if responce = "OK" then
    let r := first(select Immeuble where Localisation = myField);
    delete me;
    popupRecord(r)

    end
    end

    This code might need some tweaking, let us know how it goes.

    Steven

    Like
    • Julien
    • Julien.1
    • 8 mths ago
    • Reported - view

    Hi,

    It seems to work pretty much perfectly ! I'll let you know if tweak appeared to be necessary.

    Thank you very much Steven,

    Julien 

    Like
    • Julien
    • Julien.1
    • 8 mths ago
    • Reported - view

    Hi Steven,

    How are you?

    Your formula works great! Though, how to make it also work when I link a secondary table to the primary Immeuble:Location, when I type an adress that already exists, please ?

    Looking forward to hear from you, being at your disposal,

    Best regards,

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