Report Writing

I want to get the following information onto a report:

Line information from a posted invoice line with Type: Resource if there is a second line on the invoice with an item that has a specified product group code.

We’re on NAV: 2009 - Sql - classic

Hi Doug,

There are some problems with the logic, as there could be a number of Resource and Item lines on an invoice, and no way of mapping them, and as this is in the End User section we do not know your skill set.

The only option I could think of will display all Resource and Item lines if they exist, create a two level report with ‘Sales Header’ and indent the ‘Sales Invoice Line’ filter the lines on “Document No.” = “No.” and for Types Item|Resource, then add the ‘Product Group Code’ to the ReqFilterFields on the ‘Sales invoice Line’ table properties.

  • DataItemTableView: SORTING(Document No.,Line No.) WHERE(Type=FILTER(Item|Resource),Quantity=FILTER(<>0))
  • DataItemLink: Document No.=FIELD(No.)
  • ReqFilterFields: Product Group Code

You will need the header record as the “Posting Date” is not on the lines and you would likely filter a date range, in the report body only output the Sales Invoice Lines, test this and see if it gives you what you want.

If you are confident in a little code then create a C/AL Global SalesInvLine = Record: Sales Invoice Line

Then Code:

Sales Invoice Header - OnAfterGetRecord()
//Look for the Resource Lines
SalesInvLine.RESET;
SalesInvLine.SETRANGE(“Document No.”,“No.”);
SalesInvLine.SETRANGE(Type,SalesInvLine.Type::Resource);
SalesInvLine.SETFILTER(Quantity,’<>0’);
IF NOT SalesInvLine.FINDFIRST THEN
CurrReport.SKIP;

//Look for the Item Lines
SalesInvLine.RESET;
SalesInvLine.SETRANGE(“Document No.”,“No.”);
SalesInvLine.SETRANGE(Type,SalesInvLine.Type::Item);
SalesInvLine.SETFILTER(Quantity,’<>0’);
IF NOT SalesInvLine.FINDFIRST THEN
CurrReport.SKIP;

If this is beyond your skills then contact your reseller for some report training or to create a report pack for you.

David