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

catiamatos1991

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




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;

RockWithNAV

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.

catiamatos1991

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;

catiamatos1991

And the keys of this table are Item No.,Vendor No.

Kepty

Are you sure? I think the key is Vendor No. only...

RockWithNAV

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?

catiamatos1991

The error is that "Produtos Fornecedor" already exists for that Vendor number

Kepty

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.

NavNab

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).

catiamatos1991

Now the error is because item no and vendor no dont exist

catiamatos1991

yes, my mistake but the error persists.. any ideia?

Slawek_Guzek

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.

RockWithNAV

@Slawek_Guzek - Absolutely right , I meant this one only.

Wasn't this clear in my statement

catiamatos1991

It worked, thanks!

RockWithNAV

@catiamatos1991
Please verify the answer.