XMLport - filter sales line using sales header field.

Business Central/NAV Developers Forum
1

Hi

I have created an XMLport which should export Posted Sales invoice lines in CSV format. the invoice lines should be filtered using salesperson code from the header table.

I have kept only sales Invoice line dataitem(node) and filtered the lines using below code in onaftergettrigger.

IF SalesInvoiceHeader.“Sales Person Code” <> ‘FH’ THEN

Currxmlport.SKIP;

The above code works fine. but it takes long time to run the xmlport due to huge no of records in sales invocie line table.

If I add new sales invoice header dataitem(node) and make it as parent to sales invoice line dataitem and filter the header row using tableview property I face two issues.

  1. Blank row gets created in csv for every header record.

  2. only last header’s line information only show in csv.

Please advice.

2

Do you only have Sales Invoice Line as a dataitem in the xmlport? If yes, then you have alot of lines then of course it will take a looot of time, as you will have to read all lines first.
If you add sales invoice header to the dataset, then you will be able to filter on the salesperson code directly in the header. And the you would link the headers and lines. That would change it so that only the correct sales lines are read from the database.

3

And for the blank line, that’s the settings of your xml port. Hard to say without seeing what you did.

4

Hi Erik,

Thanks for the reply, I tried the ame thing which you said. but the xmlport is not like reports.

We are not able to keep parent child dataitem relationship in xmlport. if we indent two table element node we face below error message

Microsoft Dynamics NAV Development Environment

An element with source type Table cannot have Table element children.

OK

If we dont indent but just link the two element using properties, only first sales header’s line data only exported to csv.

Also the blank row comes in csv even we set the min occurs property to zero.

Could you please check and help me on this.

Thanks,
Shyam

5

sales invoice.png
sales invoice.png815×587 22.5 KB

6

Hi

I found the answer for this.

We can use Integer table as a data source and create a temp sales line variable and insert the filtered records in temp sales line variable.

Then use the temp table to loop as many number times.

Sample code for better understanding.

PWTrxLine - Export::OnPreXMLItem()
CLEAR(gtTrxLines);
gtTrxLines.DELETEALL;
lPWTrxHeader.RESET;
lPWTrxHeader.SETRANGE(“Document type”,lPWTrxHeader.“Document type”::Shipment);
lPWTrxHeader.SETFILTER(“Client Program”,‘321’);
IF lPWTrxHeader.FINDSET THEN
REPEAT
lPstdTrxLine.RESET;
lPstdTrxLine.SETRANGE(“Document type”,lPWTrxHeader.“Document type”);
lPstdTrxLine.SETRANGE(Document,lPWTrxHeader.Document);
IF lPstdTrxLine.FINDFIRST THEN
REPEAT
CLEAR(gtTrxLines);
gtTrxLines.TRANSFERFIELDS(lPstdTrxLine);
gtTrxLines.INSERT;
UNTIL lPstdTrxLine.NEXT = 0;
UNTIL lPWTrxHeader.NEXT =0;
CLEAR(gtTrxLines);
PWTrxLine.SETRANGE(Number,1,gtTrxLines.COUNT);

PWTrxLine - Export::OnAfterGetRecord()
IF PWTrxLine.Number = 1 THEN
gtTrxLines.FINDFIRST
ELSE
gtTrxLines.NEXT;