0

Reviewing duplicates in one field

Hello,

I am a brand new Ninox user and in the phase of creating a database. I would like to make sure that in a table called 'laye' one of my fields called 'Identification', which is a text field, does not contain any duplicates. How can I do so? Many thanks in advance for your help.

23 replies

null
    • Ninox partner
    • RoSoft_Steven.1
    • 4 yrs ago
    • Reported - view

    You can put this in the 'Trigger after update' of your Identification field :

    let p := Identification;
    if cnt(select l
    aye where Identification = p) = 2 then
    Identification := null;
    alert("Identification already in use")

    end

    Steven

    • julie
    • 4 yrs ago
    • Reported - view

    Fantastic! That works perfectly.

    Thank you very much Steven for your help.

    Best,

    Julie

    • wstarnes
    • 4 yrs ago
    • Reported - view

    Hi, I'm having difficulty with using this formula, and have tried several variations.

    I have a text field named "ItemID" and I want to identify any record in that field that is a duplicate.

    I want to use this (or some version of this) formula in a separate field (formula field) that will display either "Duplicate" or "Unique", depending on the result.  

    Can someone please give me a bit of guidance about how to formulate this?

    Thank you in advance!

    • Ninox partner
    • RoSoft_Steven.1
    • 4 yrs ago
    • Reported - view

    let p := 'Article No';
    if cnt(select Article where 'Article No' = p) > 1 and 'Article No' != null then
    "Duplicate"
    else
    if 'Article No' != null then "Unique" else "null" end
    end

     

    change 'Article No' to 'Item Id'

    change select Article to select YourTableName

    All this comes in a formula field

    Steven

    • wstarnes
    • 4 yrs ago
    • Reported - view

    Hi Steven,

    I appreciate your response.  However, I'm getting everything marked as "Unique", even known duplicates:

     

    Here is the formula I'm using:

     

    Any idea what I'm doing wrong?  Any help is greatly appreciated! Cheers!

    • Sean
    • 4 yrs ago
    • Reported - view

    This should work...

     

    let t := this;
    if cnt(select 'Duplicate Test' where ItemID = t.ItemID) > 1 then
    "Duplicate"
    else
    "Unique"
    end

    • Sean
    • 4 yrs ago
    • Reported - view

    The formula I posted goes in a Formula field not "Trigger after update"

    • wstarnes
    • 4 yrs ago
    • Reported - view

    Hi Sean,

    Thanks for your response.  I'm getting a bit of a mixed result.  It shows "Duplicate" in form view, but the same field shows "Unique" in table view:

     

    I'm scratching my head here.  Any ideas?  (I pasted your formula without modification.)

    Thanks again, in advance!

    • Sean
    • 4 yrs ago
    • Reported - view

    Try clicking the Refresh icon in the upper left corner or close and reopen the database 

    • wstarnes
    • 4 yrs ago
    • Reported - view

    So i reopened the database with filter set to return a small number of records (10-12) and it works:

     

    Then i set the filter to give more records (69 in this case) and it gives me the mixed result:

     

    I have done this filtering several times to make sure I'm getting a consistent result, per above, which I am. I cannot imagine what I'm doing wrong.    

    I have 60K records I'm ultimately wanting to search for duplicates.  

    • Sean
    • 4 yrs ago
    • Reported - view

    I don't have a similar data set to work with so I can't really troubleshoot this. Do you get the correct result without using the filter? Maybe a stupid question, but did you try the refresh button after you set a new filter? I would also add another Formula field with just this line in it to see what the result is...

     

    cnt(select 'Duplicate Test' where ItemID = t.ItemID)

    • wstarnes
    • 4 yrs ago
    • Reported - view

    I have tried the refresh button but it doesn't appear to make a difference.  It seems I only sometimes get duplicates identified is if I filter a small subset of records (with known duplicates).

     Do you mind if I send you an invite so you can experiment on this test data? I have this table set up in a standalone "team" that I'm happy to share.

    • Sean
    • 4 yrs ago
    • Reported - view

    I don't mind and I've got some time to look at it today. Do you have the Mac app? I'm wondering if that makes any difference.

    • wstarnes
    • 4 yrs ago
    • Reported - view

    I appreciate it!

    I'm using the online app.  Do not have the Mac app.

    If you want to send me an email at wes@mondotrade.com with your email I'll send you an invite.

    Thanks!

    • Sean
    • 4 yrs ago
    • Reported - view

    It looks like the solution of using the "select" command is impractical for that many records...

     

    Screen Shot 2020-01-20 at 12.10.43 PM

     

    A better algorithm would be to iterate through the records ordered by ItemID, check for duplicates and then store the result in a text field. You should contact Ninox Support directly to see if they already have a utility or script for doing that. I'm looking at it, but don't know how soon I can come up with a solution. Please post a follow up if they give you a solution.

    • Sean
    • 4 yrs ago
    • Reported - view

    This is a more appropriate screenshot...

     

    Screen Shot 2020-01-20 at 1.11.08 PM

    • wstarnes
    • 4 yrs ago
    • Reported - view

    Hi Sean,

    Thanks for the input and for having a look.  I know it is a lot of records and suspect it is choking on that.  I'll see what support says and will post their reply.  Thanks again!

    • Sean
    • 4 yrs ago
    • Reported - view

    I'm looping through the records now

    • Sean
    • 4 yrs ago
    • Reported - view

    I waited almost an hour for the loop to run and gave up so I downloaded the file and ran it in the Mac app and it took less than 5 to process. I tried to upload the file 5 times and it failed every time. I don't know much about permissions, but I figure an Admin ought to be able to upload!

     

    I'm sending the file via email. I didn't mark both duplicates, just the second one. Take a look at the "Test B" form to see what I mean.

    • Sean
    • 4 yrs ago
    • Reported - view

    I'm not sure what's going on. I was able to import the file to my team and it works fine, but I still can't import it to your team.

    • Sean
    • 4 yrs ago
    • Reported - view

    Ok, I forgot I could login and use the Mac app to run the code on the "Duplicate Test" database, D'oh! The database is updated and you have 571 duplicates and it took approximately 38 seconds to process the records. I tried to use the cloud version to update the records on my team, but I shut it down after about 17 minutes. There must be some limitation that I'm not aware of. It is well worth $35 to get the Mac app and it should keep you from pulling your hair out trying to wait for the browser version to do its job.

    • wstarnes
    • 4 yrs ago
    • Reported - view

    Hi Sean,

    BIG THANKS for taking up my burden on this! Sorry I got busy today and just now looking back to this.

    I got errors/issues trying to import the file you sent me yet I see it actually installed 8ish iterations of DuplicateTest4. Not sure how that happened.  

    I am just starting to look at the function you installed.  It apparently works, albeit a bit differently than I was trying for, but I can definitely work with it.  I am just starting to play with it to try and understand the logic. Good stuff!

    Sounds like the Mac app can process a lot faster.  I get bogged down in the processing a lot of the time, especially with 60k+ records.  I have a Mac but work on a PC.  I'm not clear how to use both since all my team uses the cloud version.  

    Thanks again.  Will post more as I learn.  Cheers!

    • Sean
    • 4 yrs ago
    • Reported - view

    Wes,

    You're welcome. That was a satisfying bone for me to gnaw on yesterday. The code was the first version that would mark the duplicates so I went with it. I have to say with that many records it is easier to find the duplicates at a glance instead of having to separate "Unique" from "Duplicate".

    If you are responsible for developing a solution with Ninox, do yourself a favor and get the Mac app. You can log in to your team and the UI is almost identical.

Content aside

  • 4 yrs agoLast active
  • 23Replies
  • 3642Views