Reviewing duplicates in one field
J

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.

Ninox Profile

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

J

Fantastic! That works perfectly.

Thank you very much Steven for your help.

Best,

Julie

Ninox Profile

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 Profile

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

Ninox Profile

Hi Steven,

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

Known Dups 2

Here is the formula I'm using:

Dup Formula 2

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

Ninox Profile

This should work...

 

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

Ninox Profile

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

Ninox Profile

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:

Known Dups 4=

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

Thanks again, in advance!

Ninox Profile

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

Reply