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

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
end

Best 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" then
create 'YourTableName'
end