XMLport - filter sales line using sales header field.

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.

  • 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.
  • In reply to Erik P. Ernst:

    And for the blank line, that's the settings of your xml port. Hard to say without seeing what you did.
  • In reply to Erik P. Ernst:

    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
  • In reply to Shyam Rao:

    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;

Related