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

catiamatos1991
Member Posts: 158
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;

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;

0
Answers
-
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/0 -
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;
0 -
And the keys of this table are Item No.,Vendor No.0
-
Are you sure? I think the key is Vendor No. only...Tomáš Kapitán0
-
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/-1 -
The error is that "Produtos Fornecedor" already exists for that Vendor number0
-
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án0
-
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).
0 -
Now the error is because item no and vendor no dont exist
0 -
yes, my mistake but the error persists.. any ideia?0
-
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 thisIF 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-030 -
Thanks
Blog - rockwithnav.wordpress.com/
Twitter - https://twitter.com/RockwithNav
Facebook - https://facebook.com/rockwithnav/0 -
It worked, thanks!0
-
@catiamatos1991
Please verify the answer.Thanks
Blog - rockwithnav.wordpress.com/
Twitter - https://twitter.com/RockwithNav
Facebook - https://facebook.com/rockwithnav/0
Categories
- All Categories
- 73 General
- 73 Announcements
- 66.6K Microsoft Dynamics NAV
- 18.7K NAV Three Tier
- 38.4K NAV/Navision Classic Client
- 3.6K Navision Attain
- 2.4K Navision Financials
- 116 Navision DOS
- 851 Navision e-Commerce
- 1K NAV Tips & Tricks
- 772 NAV Dutch speaking only
- 617 NAV Courses, Exams & Certification
- 2K Microsoft Dynamics-Other
- 1.5K Dynamics AX
- 320 Dynamics CRM
- 111 Dynamics GP
- 10 Dynamics SL
- 1.5K Other
- 990 SQL General
- 383 SQL Performance
- 34 SQL Tips & Tricks
- 35 Design Patterns (General & Best Practices)
- 1 Architectural Patterns
- 10 Design Patterns
- 5 Implementation Patterns
- 53 3rd Party Products, Services & Events
- 1.6K General
- 1.1K General Chat
- 1.6K Website
- 83 Testing
- 1.2K Download section
- 23 How Tos section
- 252 Feedback
- 12 NAV TechDays 2013 Sessions
- 13 NAV TechDays 2012 Sessions