How I can prevent duplicate records being added?

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

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

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,