Small and medium business | Business Central, N...

To Sum up the reciept line values of purchase invoice line

(0) Share

Report

Posted on by Community Member

Hi Expects,
I have to find the inv amount using Inv line that has been invoiced using the reciept lines.
Can some one help me with the codeunit optimization.

the dataitem i choosed was:
Expanded Data Type Data Source Name Include Caption
1 DataItem Purch. Rcpt. Header <Purch. Rcpt. Header> No
0 Column "Purch. Rcpt. Header"."Buy-from Vendor No." VendorNo No
0 Column "Purch. Rcpt. Header"."No." GrnNo No
0 Column "Purch. Rcpt. Header"."Order No." PoNo No
0 Column "Purch. Rcpt. Header"."Order Date" PoDate No
0 Column "Purch. Rcpt. Header"."Document Date" GrnDate No
1 DataItem Purch. Rcpt. Line <Purch. Rcpt. Line> No
0 Column "Purch. Rcpt. Line".Quantity PoQuantity No
0 Column "Purch. Rcpt. Line"."Direct Unit Cost" * "Purch. Rcpt. Line".Quantity Amount No
0 Column "Purch. Rcpt. Line"."Quantity Invoiced" QtyInv No
1 DataItem Purch. Inv. Header <Purch. Inv. Header> No
0 Column "Purch. Inv. Header"."No." InvoiceNo No
0 Column "Purch. Inv. Header".Amount InvoiceAmount No
1 DataItem G/L Entry <G/L Entry> No
0 Column "G/L Entry".Amount PaymentAmount No
0 Column "G/L Entry"."Posting Date" PaymentDate No
0 Column "G/L Entry"."Document No." paymentDocNo No

Codeunit for this is:

Purch. Inv. Header - OnPreDataItem()
IF "Purch. Inv. Header"."Order No." = '' THEN BEGIN
sumamt := 0;
"Purch. Inv. Line".SETRANGE("Purch. Inv. Line"."Receipt No.","Purch. Rcpt. Line"."Document No.");
IF NOT "Purch. Inv. Line".GET("Purch. Rcpt. Line"."Document No.") THEN BEGIN
IF "Purch. Inv. Line"."Receipt No." = "Purch. Rcpt. Line"."Document No." THEN REPEAT
sumamt := sumamt + "Purch. Inv. Line".Amount;
UNTIL "Purch. Inv. Line".NEXT = 0;
"Purch. Inv. Header".Amount := sumamt;
"Purch. Inv. Header".INSERT;
END;
END;

this shows an error as :

Microsoft Dynamics NAV

The Purch. Inv. Header does not exist. Identification fields and values: No.=''

As i have to sum the values for showing the purchase tracker report I coulnt end up getting the result.
Thanks in advance.

All responses (5)

Answers (0)

Sort by

Suggested answer

Stefano Demiliani 37,172 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!

This GET is wrong:

"Purch. Inv. Line".GET("Purch. Rcpt. Line"."Document No.")

Document Type and Line No. here are missing (and they're part of the primary key of the table).
Community Member on at

Like (0)

Report
Copy link

Link copied!

I have changed the code as you said :

IF "Purch. Inv. Header"."Order No." = '' THEN BEGIN

sumamt := 0;

"Purch. Inv. Line".GET("Purch. Rcpt. Line"."Document No.");

"Purch. Inv. Line".SETRANGE("Purch. Inv. Line"."Document No.","Purch. Rcpt. Line"."Document No.");

IF "Purch. Rcpt. Line"."Document No." = "Purch. Inv. Line"."Document No." THEN

REPEAT

sumamt := sumamt + "Purch. Inv. Line".Amount;

UNTIL "Purch. Inv. Line".NEXT = 0;

"Purch. Inv. Header".Amount := sumamt;

"Purch. Inv. Header".MODIFY;

END;

I am getting an error as:

Microsoft Dynamics NAV

---------------------------

The Purch. Inv. Line does not exist. Identification fields and values: Document No.='107128',Line No.='0'

---------------------------

OK

---------------------------
Suggested answer

Stefano Demiliani 37,172 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!

I think that you miss some basic concept about C/AL. The GET clause retrieves a record via its primary key. The primary key of the Purchase Invoice Line table is Document No.,Line No., so your GET clause is missing of a parameter (Line No). Then, a SETRANGE after a GET has no sense.

I think that you want to loop through records, so:

"Purch. Inv. Line".SETRANGE("Document No.","Purch. Rcpt. Line"."Document No.");

if "Purch. Inv. Line".FINDSET THEN

repeat

//Write your logic here

until "Purch. Inv. Line".NEXT = 0;
Community Member on at

Like (0)

Report
Copy link

Link copied!

I have to prepare a report for purchase tracking based on vendoeNO.

If i recieve and invoice in purchase order i get the details correctly.

but if i recieve in PO and Invoice seperately in the Purchase Invoices page using Get reciept lines function.

I get only receipt Number in the pur Inv line.

The reciept No matches with the Reciept line doc no.

but we cannot match the datalink as

"Purch. Inv. Line".SETRANGE("Document No.","Purch. Rcpt. Line"."Document No.");

Sice the docNo are different.
Suggested answer

Marco Mels Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!

Hello,

We currently do not have dedicated Dev support via the Dynamics 365 Business Central forums, but I wanted to provide you some additional resources to assist. If you need assistance with debugging or coding I would recommend discussing this on one of our communities.

www.yammer.com/dynamicsnavdev

dynamicsuser.net/.../developers

I will open this up to the community in case they have something to add.

Thanks.