mibuso.com
Home› NAV/Navision Classic Client
Options

Error with find / findfirst to modify existing records on a table (codeunit)

catiamatos1991catiamatos1991 Member Posts: 158
in NAV/Navision Classic Client
HI everyone.. I'm having a lot of trouble trying to insert records of a vendor in a table.. In this case I already have a record and I need to update (modify) the content but It raises a error that this record already exists.

I was trying to find the existing record making

prodform."Vendor No.":="Source No.";
IF prodform.FIND('=') THEN BEGIN

I also tried just to do

IF prodform.FINDFIRST() THEN BEGIN

87m1gjp2f9tc.png


But nothing is working.. here's the code and images of the problem... where prodform is a record of "Produtos Fornecedor" table which have the fields Vendor No (supplied by the table Vendor), Data Ultima Compra, Preço, Quantidade, Item No. (linked to Item table) and Vendor Name (flowfield with Lookup(Vendor.Name WHERE (No.=FIELD(Vendor No.))) )

IF "Item Ledger Entry Type"=0 THEN BEGIN
prodform.SETRANGE("Item No.",Item."No.");
prodform.SETRANGE("Vendor No.","Source No.");
Item."Data Ultima Compra":="Posting Date";
Item."Vendor No.":="Source No.";

prodform."Vendor No.":="Source No.";
IF prodform.FIND('=') THEN BEGIN
prodform."Data Ult. Compra":="Posting Date";
prodform.Quantidade:="Invoiced Quantity";
prodform.Preço:=ValueEntry."Cost per Unit";
prodform.MODIFY;
END
ELSE BEGIN
prodform.INIT;
prodform."Vendor No.":="Source No.";
prodform."Data Ult. Compra":= "Posting Date";
prodform.Quantidade:="Invoiced Quantity";
prodform.Preço:=ValueEntry."Cost per Unit";
prodform."Item No.":=Item."No.";
prodform.INSERT;
END;
Item.MODIFY;
END;

bz7nqp415c2e.png

Answers

  • Options
    RockWithNAVRockWithNAV Member Posts: 1,139
    prodform.RESET;
    prodform.SETRANGE("Item No.",Item."No.");
    prodform.SETRANGE("Vendor No.","Source No
    IF prodform.FINDFIRST THEN BEGIN
    // Write here
    END;


    This line of code won't be needed
    IF prodform.FIND('=') THEN BEGIN// Remove this

    What is the primary key of this table - prodform

    While INIT INSERT make sure primary key part ha no issue.
    Thanks

    Blog - rockwithnav.wordpress.com/
    Twitter - https://twitter.com/RockwithNav
    Facebook - https://facebook.com/rockwithnav/
  • Options
    catiamatos1991catiamatos1991 Member Posts: 158
    I change the code as you said ant it raises the same error and with the debugger it stops in the FINDFIRST..

    IF "Item Ledger Entry Type"=0 THEN BEGIN
    prodform.RESET;
    prodform.SETRANGE("Item No.",Item."No.");
    prodform.SETRANGE("Vendor No.","Source No.");
    Item."Data Ultima Compra":="Posting Date";
    Item."Vendor No.":="Source No.";

    IF prodform.FINDFIRST THEN BEGIN
    prodform."Data Ult. Compra":="Posting Date";
    prodform.Quantidade:="Invoiced Quantity";
    prodform.Preço:=ValueEntry."Cost per Unit";
    prodform.MODIFY;
    END
    ELSE BEGIN
    prodform.INIT;
    prodform."Vendor No.":="Source No.";
    prodform."Data Ult. Compra":= "Posting Date";
    prodform.Quantidade:="Invoiced Quantity";
    prodform.Preço:=ValueEntry."Cost per Unit";
    prodform."Item No.":=Item."No.";
    prodform.INSERT;
    END;
    Item.MODIFY;

  • Options
    catiamatos1991catiamatos1991 Member Posts: 158
    And the keys of this table are Item No.,Vendor No.
  • Options
    KeptyKepty Member Posts: 54
    Are you sure? I think the key is Vendor No. only...
    Tomáš Kapitán
  • Options
    RockWithNAVRockWithNAV Member Posts: 1,139
    If the key is Item No.,Vendor No -


    Remove these lines
    prodform.RESET;
    prodform.SETRANGE("Item No.",Item."No.");
    prodform.SETRANGE("Vendor No.","Source No.");
    IF prodform.FINDFIRST THEN BEGIN

    Instead of these 4 lines write
    prodform.GET(Item."No.","Source No.");



    What is the error that you are receiving at FINDFIRST?
    Thanks

    Blog - rockwithnav.wordpress.com/
    Twitter - https://twitter.com/RockwithNav
    Facebook - https://facebook.com/rockwithnav/
  • Options
    catiamatos1991catiamatos1991 Member Posts: 158
    The error is that "Produtos Fornecedor" already exists for that Vendor number
  • Options
    KeptyKepty Member Posts: 54
    Yes, please check the table's key. This error message shows all key fields values, so I assume that the Item No. is not contained in the primary key.
    Tomáš Kapitán
  • Options
    NavNabNavNab Member Posts: 181
    Hello @catiamatos1991

    @Kepty is right. You can clearly see that your primary key is Vendor No from the error message.

    Design your prodform table, go to View->Keys and edit your primary key fields (check for example Table 99).
  • Options
    catiamatos1991catiamatos1991 Member Posts: 158
    Now the error is because item no and vendor no dont exist

    gmvrwn8w1cbv.png
  • Options
    catiamatos1991catiamatos1991 Member Posts: 158
    yes, my mistake but the error persists.. any ideia?
  • Options
    Slawek_GuzekSlawek_Guzek Member Posts: 1,690
    edited 2018-04-03
    I guess that @RockWithNAV by writtig "Remove these lines ... Instead of these 4 lines write ..." meant that this line: 
    IF prodform.FINDFIRST THEN BEGIN
    should get replaced with this 
    IF prodform.GET(Item."No.","Source No.") THEN BEGIN
    and the 3 lines before IF prodform.FINDFIRST... : 
    prodform.RESET;
prodform.SETRANGE("Item No.",Item."No.");
prodform.SETRANGE("Vendor No.","Source No.");
    should be removed.
    Slawek Guzek
    Dynamics NAV, MS SQL Server, Wherescape RED;
    PRINCE2 Practitioner - License GR657010572SG
    GDPR Certified Data Protection Officer - PECB License DPCDPO1025070-2018-03
  • Options
    RockWithNAVRockWithNAV Member Posts: 1,139
    edited 2018-04-04
    @Slawek_Guzek - Absolutely right :smile: , I meant this one only.

    Wasn't this clear in my statement :smile:
    Thanks

    Blog - rockwithnav.wordpress.com/
    Twitter - https://twitter.com/RockwithNav
    Facebook - https://facebook.com/rockwithnav/
  • Options
    catiamatos1991catiamatos1991 Member Posts: 158
    It worked, thanks!
  • Options
    RockWithNAVRockWithNAV Member Posts: 1,139
    @catiamatos1991
    Please verify the answer.
    Thanks

    Blog - rockwithnav.wordpress.com/
    Twitter - https://twitter.com/RockwithNav
    Facebook - https://facebook.com/rockwithnav/
Sign In or Register to comment.