How can I set a field to automatically search existing records for possible duplicates of the record I'm about to create?
I have a database where I create new clients as well as add numerous interactions with those clients in a few related tables.
When I create a new record and enter the client's name I would like an alert if that client already exists so that I can add anew interaction to the existing client rather than create a duplicate client record.
Is this possible with Ninox and how do I do it?
Thank you!
Alex
14 replies
-
Hi Alex,
You can use a formula like the following, in order to avoid duplicates.
let myFirstName := 'First name';
let myLastName := 'Last name';
let cntFirstName := cnt((select Contact)['First name' = myFirstName]);
let cntLastName := cnt((select Contact)['Last name' = myLastName]);
if cntFirstName > 1 and cntLastName > 1 then
alert("Duplicate!");
'First name' := null;
'Last name' := null
endBest regards, Jörg
-
Hello,
Is it possible to get the same formula but to get the cells in another color to see the duplicates? Without popup alert.
Thanks in advance for your help.
Have a nice day,
David W.
-
Thank you so much for the formula. However, I’m not sure where to enter it? Please could you help a bit more?!
Cheerio
Alex
-
Thank you so much for the formula. However, I’m not sure where to enter it? Please could you help a bit more?!
Cheerio
Alex
-
Ok, I created a new formula field and entered the formula (modifying it to match the names of my fields and table!). However, when I click “ok” I get the error message: “This formula may not modify data”.
What am I doing wrong?!!
Cherio
Alex
-
On the left side of the Edit Fields window you will see "Trigger on create". Enter the formula there.
-
Thank you so much for your help.
I also needed to put the formula in the ”Trigger after update” window to make it work.
Cheerio
Alex
-
Hello again! Is it possible to receive the alert about a possible duplicate entry but then go ahead and make the new record anyway? Two people may have the same first name and surname. The script provided seems to prevent me from creating a record with the same name. Thank you so much!
Cheerio
Alex
-
It's been a while since I've looked at this... I don't see where the record is created in the code above, but if it's working for you then that's all that matters. You could use a dialog function instead of the alert function.
let duplicate := dialog("Attention", "Create a duplicate record?", ["Yes", "No"]);if check = "Yes" thencreate 'YourTableName'end -
Sorry, it should be...
if duplicate = "Yes" then -
Ok, my brain caught up. The record has already been created, but the code won't let the duplicate information be entered. Here's a solution...
if cntFirstName > 1 and cntLastName > 1 thenlet duplicate := dialog("Attention", "Create a duplicate record?", ["Yes", "No"]);if duplicate = "Yes" then'First name' := myFirstName;'Last name' := myLastNameelse'First name' := null;'Last name' := nullend -
Sorry again! Please use something other than "duplicate" because that is already command in Ninox. You could use "confirm"
let confirm := dialog("Attention", "Create a duplicate record?", ["Yes", "No"]);if confirm = "Yes" then -
Thank you! I’ll see if I can make it work!
Cheerio
Alex
-
Hi again! It was actually simpler than you thought - I just had to change
‘First name’ := null;
’Last name’ := null
end
to
’First name’ := myFirstName
’Last name’ := myLastName
end
Thanks again!!
Content aside
- 5 yrs agoLast active
- 14Replies
- 6012Views