0

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

null
    • Support
    • 5 yrs ago
    • Reported - view

    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

    • David_WILLOX
    • 5 yrs ago
    • Reported - view

    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.

    • Alex_Brown
    • 5 yrs ago
    • Reported - view

    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

    • Alex_Brown
    • 5 yrs ago
    • Reported - view

    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

    • Alex_Brown
    • 5 yrs ago
    • Reported - view

    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

    • Sean
    • 5 yrs ago
    • Reported - view

    On the left side of the Edit Fields window you will see "Trigger on create". Enter the formula there.

    • Alex_Brown
    • 5 yrs ago
    • Reported - view

    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

    • Alex_Brown
    • 5 yrs ago
    • Reported - view

    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

    • Sean
    • 5 yrs ago
    • Reported - view

    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

    • Sean
    • 5 yrs ago
    • Reported - view

    Sorry, it should be...

     

    if duplicate = "Yes" then

    • Sean
    • 5 yrs ago
    • Reported - view

    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 then
    let duplicate := dialog("Attention", "Create a duplicate record?", ["Yes", "No"]);
    if duplicate = "Yes" then
    'First name' := myFirstName;
    'Last name' := myLastName
    else
    'First name' := null;
    'Last name' := null
    end

    • Sean
    • 5 yrs ago
    • Reported - view

    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

    • Alex_Brown
    • 5 yrs ago
    • Reported - view

    Thank you! I’ll see if I can make it work!

     

    Cheerio

    Alex

    • Alex_Brown
    • 5 yrs ago
    • Reported - view

    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
  • 5975Views