0

Error in a script to find duplicates

Hi,

I tried to remove the duplicates of names in a Ninox database with this script found on the forum but I have

 

this error :

"A table has not been find Oiseaux on the line 1, column 31".

The script :,

for i in select Oiseaux famille genres espéces do

while cnt(select Oiseaux famille genres espéces where Nom= text(i))!=1 do

delete last(select Oiseaux famille genres espéces where Nom= text(i))

end

end

 

The table of the name is : "Oiseaux famille genres espéces"

The names to be found as duplicate are "Name"

 

can someone help me ?

Thanks 

19 replies

null
    • saddinsell
    • 3 yrs ago
    • Reported - view

    I tried also with simples quotes

     

    for i in select 'Oiseaux famille genres espéces' do

    while cnt(select 'Oiseaux famille genres espéces' where 'Nom'= text(i))!=1 do

    delete last(select 'Oiseaux famille genres espéces' where 'Nom'= text(i))

    end

    end

     

    I get no error but the button does not work…

    • John_Halls
    • 3 yrs ago
    • Reported - view

    I had a go at this, changing text(i) to i.text in the two lines where it is used.

     

    It did work and removed duplicates in a small table but then hung and I had to quit out and come back in again, so I could't reccommend using this bit of code.

    • John_Halls
    • 3 yrs ago
    • Reported - view

    How about

     

    let t := "";
    for i in (select Table) order by Field do
       if t = i.Field then
          i.(Field:= "xxx")
       else
          t := i.Field
       end
    end;
    delete (select Table)[Field = "xxx"]

     

    Regards

    John

    • saddinsell
    • 3 yrs ago
    • Reported - view

    Thank you John

     

    Why do you say you had to quit out and come back in again ?

    I don't understand very well. It works or it doesn't ?

    • saddinsell
    • 3 yrs ago
    • Reported - view

    let t := "";

    for i in (Oiseaux famille genres espéces) order by Field do

       if t = i.Field then

          i.(Field:= "xxx")

       else

          t := i.Field

       end

    end;

    delete (Oiseaux famille genres espéces)[Field = "xxx"]

     

    I tried it but I get that error :

     

    "A column has not been found : Oiseaux on the line 2, column 17"

     

    Curious, "Oiseaux famille genres espéces" is not a column but the name of the base…

    • John_Halls
    • 3 yrs ago
    • Reported - view

    Strange but true I saw it remove the duplicates but then I couldn't do anything. Try the above which renames duplicates with 'xxx' and then bulk deletes them.

    • saddinsell
    • 3 yrs ago
    • Reported - view

    Ok, but did you see the error I got ?

    • John_Halls
    • 3 yrs ago
    • Reported - view

    Sorry I missed that. I think Oiseaux famille genres espéces needs to be in single quotes 'Oiseaux famille genres espéces' whereever it's used, and Field has to be replaced with your field name that has the duplicates.

    • saddinsell
    • 3 yrs ago
    • Reported - view

    Thanks again John

    So I tried : 
    let t := "";
    for i in 'Oiseaux famille genres espéces' order by Nom do
       if t = i. Nom then
          i.(Nom:= "xxx")
       else
          t := i. Nom
       end
    end;
    delete 'Oiseaux famille genres espéces’[No = "xxx"]

    and now I get : ”a column has not been find : Oiseaux famille genres espéces on the line 2 column 42”

    • Addinsell
    • 3 yrs ago
    • Reported - view

    By the way should I have put also simples quotes on the name ("Name") of the field ?

    • John_Halls
    • 3 yrs ago
    • Reported - view

    Hi

    You are missing the select statement in line 2. On line 9 change No to Nom. You only need to put the table and field names in single quotes when they have spaces in them.

    let t := "";
    for i in (select 'Oiseaux famille genres espéces’) order by Nom do
       if t = i.Nom then
          i.(Nom:= "xxx")
       else
          t := i.Nom
       end
    end;
    delete (select 'Oiseaux famille genres espéces’)[Nom = "xxx"]

     

    Regards John

    • Addinsell
    • 3 yrs ago
    • Reported - view

    Thanks a lot

     

    Now I get that error :

     

    "A table has not been find : Oiseaux famille genres espéces’) order by Nom do if t= i.Nom then i. (Nom:="xxx") else t :=i.Nom end end; delete(select on the line 9, columnne 23"

    • John_Halls
    • 3 yrs ago
    • Reported - view

    Can you post a screenshot of the code

    • Addinsell
    • 3 yrs ago
    • Reported - view

    Infortunetly one can no more add screen shot in the forum for a while…

    How can I post you a screen shot ? By mail ?

    • Addinsell
    • 3 yrs ago
    • Reported - view

    41A46310-35E5-4C8B-9B22-65ACDBE82383

    • John_Halls
    • 3 yrs ago
    • Reported - view

    How this happened I don't know, but we have two types of single quote going on

    ' ASCII code 39 is at the start of your table name

    ’ ASCII code 146 is at the end of your table name

    Make them both the same, using the one at the start. 'Oiseaux famille genres espéces'. Change this in both places it's used.

    • Addinsell
    • 3 yrs ago
    • Reported - view

    I did it.

     

    let t := "";

    for i in 'Oiseaux famille genres espéces' order by Nom do

       if t = i. Nom then

          i.(Nom:= "xxx")

       else

          t := i. Nom

       end

    end;

    delete 'Oiseaux famille genres espéces'[Nom = "xxx"]

     

    But : "a column has not been find : Oiseaux famille genres espéces on the line 2, column 42" as before…

    • Addinsell
    • 3 yrs ago
    • Reported - view

    Sorry, I forgot the select statement !

     

    let t := "";

    for i in (select 'Oiseaux famille genres espéces') order by Nom do

       if t = i.Nom then

          i.(Nom:= "xxx")

       else

          t := i.Nom

       end

    end;

    delete (select 'Oiseaux famille genres espéces')[Nom = "xxx"]

     

    but I got :

     "a column has not been find : Oiseaux famille genres espéces on the line 2, column 50" as before…

    • Addinsell
    • 3 yrs ago
    • Reported - view

    635575EF-D170-4B64-8D2A-D015A8C7EC59

Content aside

  • 3 yrs agoLast active
  • 19Replies
  • 940Views