0

No duplicate absent…

Hi,

 

I created a formula to display duplicates and original in the family field of a database:

 

let myV := Famille;

if cnt(select 'Oiseaux famille genres espéces' where Famille = myV) > 1 then

"Duplicate"

else

null

end

 

I create another one :

 

let myV := Famille;

if cnt(select 'Oiseaux famille genres espéces'where Famille = myV) > 1 then

"Duplicate"

else

 

"no duplicate"

end

 

On the screenshot you can see that with the both codes (Doublon familles, Doublons familles2) there are errors:

 

Only duplicates names are displayed, not the no duplicates…

Yet the codes say "else null" and "else no duplicate".

 

Can someone explain that ?

9 replies

null
    • Fred
    • 2 yrs ago
    • Reported - view

    It looks like the code is working to me.

     

    Lines 23-25 they all have the same Familie so they would all show up as duplicate.

     

    Lines 29-31 look to be singular record with that famiilie so they show up as null or "no duplicate". etc.

     

    I'm not sure what you mean by original or duplicate. You are asking Ninox to take what is in Familie of the record and then find all records that have the same data and if that is greater than 1 then put the text "duplicate" in it. If not then either do nothing or put "no duplicate".

     

    Ninox can not know what is the "original". If the first record with that familie is the "original" then you can ask Ninox to find the first record of all the same Familie then you can do order by Id then take the first. But then I'm not sure where you want to go with the fact that you have the "original". Maybe you can tell us more about what you are trying to do.

    • John_Halls
    • 2 yrs ago
    • Reported - view

    Fred is right. This is working but, depending on why you are doing this, it won't get you any further forward in finding one original and deleting or flagging the duplicates. We had a previous thread where we showed you how to do that with a script in a button. I would use that approach again here.

    • Addinsell
    • 2 yrs ago
    • Reported - view

    Thanks to you both

    Indeed I forgot that, thanks.

    So I tried what you advised me to do before :

     

    1-First I create a formula field with this code :

     

    let myV := Famille;

    if cnt(select 'Oiseaux famille genres espéces'where Famille = myV) > 1 then

    "Duplicate"

    else

     

    "no duplicate"

    end

     

    >I named it "isDuplicate famille"

     

    2-Then I create a button with this code :

    I named it

    let a := "" ;

    for b in (select 'Oiseaux famille genres espéces') order by Famille do

    if a = b. Famille then

    b.('isDuplicate famille' := "Duplicate")

    else

    b.('isDuplicate famille' := "Principal")

    end ;

    a := b.Famille

    end

     

    I named it button "duplicate and no duplicate"

     

    But as you can see  this code has an error :

     

    "This column is not a simple field of datas : isDuplicate famille on the line 4, column 28"

    857EB2BC-0DC2-4F86-8B6F-14172E406022

    • Fred
    • 2 yrs ago
    • Reported - view

    IsDuplicate Familie is a formula field so you can't put data into it. 

    • Addinsell
    • 2 yrs ago
    • Reported - view

    Thanks Fred

    What should I do ?

    Change the formula field in a text field or create a new text field named "isDuplicate Famille" ?

    • Fred
    • 2 yrs ago
    • Reported - view

    I was thinking that wouldn't it be easier for you to just manually set the "principal" record for each familie? How many could that be? Especially if it is the first time you enter a record for that familie to manually set it to "principal".

    • Addinsell
    • 2 yrs ago
    • Reported - view

    That's the problem, there are 796  "principal" and "duplicate" !

    And if I use the "group"  in the family filter I get the "principal" families displayed but not their number...

    So I can't tell you.

    Isn't it a way to use my button ?

    • Fred
    • 2 yrs ago
    • Reported - view

    My first question: are you able to tell which record of each familie group is the principal and which are duplicates? If so then please let us know what field is used to tell the records apart.

     

    For example, the familie Alaudidae which of the two records is the principal and which is the duplicate? What field do you use to know the difference? You can't use the familie field because the only data in there is the familie name. Unless I'm missing something.

     

    If you can't tell the records apart with data in a field then there is no way for Ninox to figure out the difference.

    • Addinsell
    • 2 yrs ago
    • Reported - view

    Thanks Fred.

     

    Well, I finally found the solution.

     

    I created a nex text field that I named "Is Duplicate famille"

     

    I changed the code : I changed  "isDuplicate famille" (formula field) by "Is Duplicate famille" formula text.

     

    let a := "" ;

    for b in (select 'Oiseaux famille genres espéces') order by Famille do

    if a = b. Famille then

    b.('Is duplicate famille' := "Duplicate")

    else

    b.('Is Duplicate famille' := "Principal")

    end ;

    a := b.Famille

    end

     

    Then it works : the column "Is Duplicate famille" contains Principal and Duplicate values and when I sort them with "Principal" I get all the familles one time.

     

    Thanks again anyway.

     

    29ECCCEC-4FBE-4DC1-9B25-A4AABB44CAAC

Content aside

  • 2 yrs agoLast active
  • 9Replies
  • 481Views