does Ninox have a tool to search for duplicates ? and… approximative duplicates ???
and even more powerfull :
did anyone develop a tool to look for "approximate" duplicates ?
lets say i have a text field
i want to find texts that "seem" to be the same…
we could imagine having a sort of cursor in order to tell the tool "how approximate" we want the search to be…
i think plenty of us would use such a tool
don't you think ?
a hint might be with the use of a word counter or something…
Anwer from an support member in another post you could have found too by searching for "duplicates":
Thursday, October 11, 2018 5:09 PM
Hi, you can use a formula to identify duplicates.
Here is an example:
You can insert a formula of the following form into the appropriate table (in the example Contact) in the field "Trigger after update" of the table properties:
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
'First name' := null;
'Last name' := null
The system checks whether the fields First name and Last name already contain the same combination of names in another data record.
If this is the case, a message "Duplicate!" appears and the fields are cleared again.
If you do not want to perform the check at table level (i.e. every time any field content is changed in the table), you can also insert the script at each of the individual fields in the same option ("Trigger after update") of the field properties.
Apologies in advance for this very basic question, but I can't seem to find an answer that's written for someone as new to this as I am. I want to adapt this formula to make sure my CustomerID field is unique within my Contacts table, but I'm obviously missing something fundamental, as I can't make it work. Please help? Thank you!
No easy answer my workaround is to create a formula in the table you want to search which looks like this
let ORDERCHK := 'Field name You want to Check';
let ORDERCHK := cnt((select 'Table You want to Check')['Field name You want to Check' = ORDERCHK]);
if ORDERCHK > 1 then "Duplicate" end
Then filter the view on the formula for anythinng which is not blank with ! NULL
Then once you have finished de-duping your data clear the filter against the formula
Arrgg.. I need to catalog Ninox blog posts better... There was a thread a while ago about various finding duplicate strategies... While I am old.. so my memory is a bit fuzzy... I recall the concensus was to create a routine that would look for dups as opposed to using a Formula Field.. Reason being is that the formula is evaluated each time the row is touched / displayed.. etc.. and could be slow if doing large table scans.
I only have around 11000 records with around 35 fields and yes it does take a little time on the copied view, but as this should only really be an adhoc task after a dodgy import or some bad input, its only that view which is affected the original view seems fine and responsive as normal once you have deduped, you could also put it as a trigger when the field is populated to check for the value which you don't want to duplicate, not sure how this would work with csv updates though.