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.
37 replies
-
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
-
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
-
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
endThis 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 .......
-
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
-
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 :-)
-
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.
-
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
-
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
endYou 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.
-
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,
-
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
-
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
-
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,
Content aside
- 2 yrs agoLast active
- 37Replies
- 12010Views