Find function not working as expected (nav newbie)

hru71

Hello,
Has been reading af lot of great tips and advices here as being a navision newbie programmer.
Hope anyone can help with a very stupid issue (I'm probably missing something very native).
I'm going to change a customized solution for a customer where they import updated item prices from vendors.
Solution is (simplifyed) - a dataport is running on data item Purchase Import Price Line (imports are saved in this table). From On after Import Record
function TransferData in this table is called. This function is first updating items, then Purchase Price (table 7012) and then Purchase Line Discount.
I'm looking at the existing code first (this have to be changed, so that identical Purchase Prices must be modifyed with Ending Date to imported Purchase Price Start Date -1 day. The imported price must of course be inserted.
The primary key in Purchase Price is Item No.,Vendor No.,Starting Date,Currency Code,Variant Code,Unit of Measure Code,Minimum Quantity.


The existing code has a local PurchasePrice var. where values for all primary key field except "Variant Code" is filled with the imported.
Then we make a Find. I was expecting to find records in Purchase Price tables with this values, but in several tests - and with counting the function finds
all records in the table. It will make my day if anyone can tell me what point I'm missing here ? (working with NAV 4 SP3).

PurchasePrice.LOCKTABLE;
PurchasePrice.VALIDATE("Item No.","Item No.");
PurchasePrice.VALIDATE("Vendor No.","Vendor No.");
PurchasePrice.VALIDATE("Starting Date","Starting Date");
PurchasePrice.VALIDATE("Currency Code","Currency Code");
PurchasePrice.VALIDATE("Unit of Measure Code","Unit of Measure Code");
PurchasePrice.VALIDATE("Minimum Quantity","Minimum Quantity");
IF PurchasePrice.FIND THEN BEGIN
PurchasePrice.VALIDATE("Direct Unit Cost","Direct Unit Cost");
PurchasePrice.VALIDATE("Ending Date","Ending Date");
PurchasePrice.MODIFY(TRUE);
END ELSE BEGIN
PurchasePrice.VALIDATE("Direct Unit Cost","Direct Unit Cost");
PurchasePrice.VALIDATE("Ending Date","Ending Date");
PurchasePrice.INSERT(TRUE);
END;

David_Cox

If you look in Codeunit 80 or 90 at the way the invoice buffer is used, they do not validate!
use one Variable with two dimensions!

PurchasePrice[1].INIT;
PurchasePrice[1]."Item No." := "Item No.";
PurchasePrice[1]."Vendor No." := "Vendor No.";
PurchasePrice[1]."Starting Date" := "Starting Date";
PurchasePrice[1]."Currency Code" := "Currency Code";
PurchasePrice[1]."Unit of Measure Code" := "Unit of Measure Code";
PurchasePrice[1]."Minimum Quantity" := "Minimum Quantity";
PurchasePrice[2] := PurchasePrice[1];
IF PurchasePrice[2].FIND THEN BEGIN
  PurchasePrice[2].VALIDATE("Direct Unit Cost","Direct Unit Cost");  
  PurchasePrice[2].VALIDATE("Ending Date","Ending Date");
  PurchasePrice[2].MODIFY(TRUE);
END ELSE BEGIN
  PurchasePrice[1].INIT;
  PurchasePrice[1].VALIDATE("Item No.","Item No.");
  PurchasePrice[1].VALIDATE("Vendor No.","Vendor No.");
  PurchasePrice[1].VALIDATE("Starting Date","Starting Date");
  PurchasePrice[1].VALIDATE("Currency Code","Currency Code");
  PurchasePrice[1].VALIDATE("Unit of Measure Code","Unit of Measure Code");
  PurchasePrice[1].VALIDATE("Minimum Quantity","Minimum Quantity");
  PurchasePrice[1].VALIDATE("Direct Unit Cost","Direct Unit Cost");
  PurchasePrice[1].VALIDATE("Ending Date","Ending Date");
  PurchasePrice[1].INSERT(TRUE);
END;

However as you are updating or inserting then why not!

PurchasePrice.INIT;
PurchasePrice.VALIDATE("Item No.","Item No.");
PurchasePrice.VALIDATE("Vendor No.","Vendor No.");
PurchasePrice.VALIDATE("Starting Date","Starting Date");
PurchasePrice.VALIDATE("Currency Code","Currency Code");
PurchasePrice.VALIDATE("Unit of Measure Code","Unit of Measure Code");
PurchasePrice.VALIDATE("Minimum Quantity","Minimum Quantity");
PurchasePrice.VALIDATE("Direct Unit Cost","Direct Unit Cost");
PurchasePrice.VALIDATE("Ending Date","Ending Date");
IF NOT PurchasePrice.INSERT(TRUE)THEN
  PurchasePrice.MODIFY(TRUE);

HTH

David

hru71

Thank you, David - very nice code-examples. However and sorry for asking again - but I'm still struggling :?
I changed the code like below. But I still expect the find to return 1 record which matches the values (hardcoded for test):
Item No. Vendor No.Starting Date Currency Code Variant Code Unit of Measure Code Minimum Quantity Direct Unit Cost Ending Date
70000 30000 29-01-13 EUR STK 1,00 0,00

However making a count on PurchasePrice[2] returns a number of 21 (which is the total of all records in the Purchase Price table)


PurchasePrice[1].INIT;
PurchasePrice[1]."Item No." := '70000';
PurchasePrice[1]."Vendor No." := '30000';
PurchasePrice[1]."Starting Date" := TODAY;
PurchasePrice[1]."Currency Code" := 'EUR';
PurchasePrice[1]."Unit of Measure Code" := 'STK';
PurchasePrice[1]."Minimum Quantity" := 1.00;
PurchasePrice[2] := PurchasePrice[1];
IF PurchasePrice[2].FIND THEN BEGIN
MESSAGE(FORMAT(PurchasePrice2.COUNT));
//PurchasePrice[2].VALIDATE("Direct Unit Cost","Direct Unit Cost");
//PurchasePrice[2].VALIDATE("Ending Date","Ending Date");
//PurchasePrice[2].MODIFY(TRUE);
END ELSE BEGIN
MESSAGE('NOT FOUND');

David Cox wrote:

If you look in Codeunit 80 or 90 at the way the invoice buffer is used, they do not validate!
use one Variable with two dimensions!

PurchasePrice[1].INIT;
PurchasePrice[1]."Item No." := "Item No.";
PurchasePrice[1]."Vendor No." := "Vendor No.";
PurchasePrice[1]."Starting Date" := "Starting Date";
PurchasePrice[1]."Currency Code" := "Currency Code";
PurchasePrice[1]."Unit of Measure Code" := "Unit of Measure Code";
PurchasePrice[1]."Minimum Quantity" := "Minimum Quantity";
PurchasePrice[2] := PurchasePrice[1];
IF PurchasePrice[2].FIND THEN BEGIN
  PurchasePrice[2].VALIDATE("Direct Unit Cost","Direct Unit Cost");  
  PurchasePrice[2].VALIDATE("Ending Date","Ending Date");
  PurchasePrice[2].MODIFY(TRUE);
END ELSE BEGIN
  PurchasePrice[1].INIT;
  PurchasePrice[1].VALIDATE("Item No.","Item No.");
  PurchasePrice[1].VALIDATE("Vendor No.","Vendor No.");
  PurchasePrice[1].VALIDATE("Starting Date","Starting Date");
  PurchasePrice[1].VALIDATE("Currency Code","Currency Code");
  PurchasePrice[1].VALIDATE("Unit of Measure Code","Unit of Measure Code");
  PurchasePrice[1].VALIDATE("Minimum Quantity","Minimum Quantity");
  PurchasePrice[1].VALIDATE("Direct Unit Cost","Direct Unit Cost");
  PurchasePrice[1].VALIDATE("Ending Date","Ending Date");
  PurchasePrice[1].INSERT(TRUE);
END;

However as you are updating or inserting then why not!

PurchasePrice.INIT;
PurchasePrice.VALIDATE("Item No.","Item No.");
PurchasePrice.VALIDATE("Vendor No.","Vendor No.");
PurchasePrice.VALIDATE("Starting Date","Starting Date");
PurchasePrice.VALIDATE("Currency Code","Currency Code");
PurchasePrice.VALIDATE("Unit of Measure Code","Unit of Measure Code");
PurchasePrice.VALIDATE("Minimum Quantity","Minimum Quantity");
PurchasePrice.VALIDATE("Direct Unit Cost","Direct Unit Cost");
PurchasePrice.VALIDATE("Ending Date","Ending Date");
IF NOT PurchasePrice.INSERT(TRUE)THEN
  PurchasePrice.MODIFY(TRUE);

HTH

David

hru71

The issue was all about lack of understanding. The customer has run a lot of testimports, which means I thought I was supposed to look for several entries with different start dates, in fact it will always only be the one from last import - my mistake.

hru71 wrote:

Thank you, David - very nice code-examples. However and sorry for asking again - but I'm still struggling :?
I changed the code like below. But I still expect the find to return 1 record which matches the values (hardcoded for test):
Item No. Vendor No.Starting Date Currency Code Variant Code Unit of Measure Code Minimum Quantity Direct Unit Cost Ending Date
70000 30000 29-01-13 EUR STK 1,00 0,00

However making a count on PurchasePrice[2] returns a number of 21 (which is the total of all records in the Purchase Price table)


PurchasePrice[1].INIT;
PurchasePrice[1]."Item No." := '70000';
PurchasePrice[1]."Vendor No." := '30000';
PurchasePrice[1]."Starting Date" := TODAY;
PurchasePrice[1]."Currency Code" := 'EUR';
PurchasePrice[1]."Unit of Measure Code" := 'STK';
PurchasePrice[1]."Minimum Quantity" := 1.00;
PurchasePrice[2] := PurchasePrice[1];
IF PurchasePrice[2].FIND THEN BEGIN
MESSAGE(FORMAT(PurchasePrice2.COUNT));
//PurchasePrice[2].VALIDATE("Direct Unit Cost","Direct Unit Cost");
//PurchasePrice[2].VALIDATE("Ending Date","Ending Date");
//PurchasePrice[2].MODIFY(TRUE);
END ELSE BEGIN
MESSAGE('NOT FOUND');

David Cox wrote:

If you look in Codeunit 80 or 90 at the way the invoice buffer is used, they do not validate!
use one Variable with two dimensions!

PurchasePrice[1].INIT;
PurchasePrice[1]."Item No." := "Item No.";
PurchasePrice[1]."Vendor No." := "Vendor No.";
PurchasePrice[1]."Starting Date" := "Starting Date";
PurchasePrice[1]."Currency Code" := "Currency Code";
PurchasePrice[1]."Unit of Measure Code" := "Unit of Measure Code";
PurchasePrice[1]."Minimum Quantity" := "Minimum Quantity";
PurchasePrice[2] := PurchasePrice[1];
IF PurchasePrice[2].FIND THEN BEGIN
  PurchasePrice[2].VALIDATE("Direct Unit Cost","Direct Unit Cost");  
  PurchasePrice[2].VALIDATE("Ending Date","Ending Date");
  PurchasePrice[2].MODIFY(TRUE);
END ELSE BEGIN
  PurchasePrice[1].INIT;
  PurchasePrice[1].VALIDATE("Item No.","Item No.");
  PurchasePrice[1].VALIDATE("Vendor No.","Vendor No.");
  PurchasePrice[1].VALIDATE("Starting Date","Starting Date");
  PurchasePrice[1].VALIDATE("Currency Code","Currency Code");
  PurchasePrice[1].VALIDATE("Unit of Measure Code","Unit of Measure Code");
  PurchasePrice[1].VALIDATE("Minimum Quantity","Minimum Quantity");
  PurchasePrice[1].VALIDATE("Direct Unit Cost","Direct Unit Cost");
  PurchasePrice[1].VALIDATE("Ending Date","Ending Date");
  PurchasePrice[1].INSERT(TRUE);
END;

However as you are updating or inserting then why not!

PurchasePrice.INIT;
PurchasePrice.VALIDATE("Item No.","Item No.");
PurchasePrice.VALIDATE("Vendor No.","Vendor No.");
PurchasePrice.VALIDATE("Starting Date","Starting Date");
PurchasePrice.VALIDATE("Currency Code","Currency Code");
PurchasePrice.VALIDATE("Unit of Measure Code","Unit of Measure Code");
PurchasePrice.VALIDATE("Minimum Quantity","Minimum Quantity");
PurchasePrice.VALIDATE("Direct Unit Cost","Direct Unit Cost");
PurchasePrice.VALIDATE("Ending Date","Ending Date");
IF NOT PurchasePrice.INSERT(TRUE)THEN
  PurchasePrice.MODIFY(TRUE);

HTH

David