Reviewing duplicates in one field
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.
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")
Fantastic! That works perfectly.
Thank you very much Steven for your help.
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!
let p := 'Article No';
if cnt(select Article where 'Article No' = p) > 1 and 'Article No' != null then
if 'Article No' != null then "Unique" else "null" end
change 'Article No' to 'Item Id'
change select Article to select YourTableName
All this comes in a formula field
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!
This should work...
let t := this;
if cnt(select 'Duplicate Test' where ItemID = t.ItemID) > 1 then
The formula I posted goes in a Formula field not "Trigger after update"
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!
Try clicking the Refresh icon in the upper left corner or close and reopen the database
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.
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)
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.
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.
It looks like the solution of using the "select" command is impractical for that many records...
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.
This is a more appropriate screenshot...
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!
I'm looping through the records now
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.
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.
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.
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!
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.
- 4 yrs agoLast active