web
You’re offline. This is a read only version of the page.
close
Skip to main content

Announcements

No record found.

News and Announcements icon
Community site session details

Community site session details

Session Id :
Microsoft Dynamics NAV (Archived)

Missing results in query

(0) ShareShare
ReportReport
Posted on by

Hi All,

I have a strange problem what sometimes happens.
I export to a file all SalesInvoiceLine's per header. Sometimes not all SalesInvoiceLine's are exported.
When i do the export again then all the rows are exported.
Does somebody has an idea?

Below the simplified code.

SalesInvoiceHeader.SETFILTER("EDI Send Inv",'=%1',FALSE);
SalesInvoiceHeader.SETFILTER("EDI Ref",'<>%1','');
SalesInvoiceHeader.SETFILTER("SLee Customer Ref",'=%1','');
DeliveryNoteNo:='';
IF SalesInvoiceHeader.FIND('-') THEN BEGIN
REPEAT
SalesInvoiceLine.SETFILTER("Document No.",'=%1',SalesInvoiceHeader."No.");
IF SalesInvoiceLine.FIND('-') THEN BEGIN
REPEAT
//In this loop whe create one file per SalesInvoiceHeader.
//Per SalesInvoiceHeader we create one row per SalesInvoiceLine.
UNTIL SalesInvoiceLine.NEXT=0
END;
UNTIL SalesInvoiceHeader.NEXT=0
END

Thnaks Patrick

*This post is locked for comments

I have the same question (0)
  • Suggested answer
    Stefano Demiliani Profile Picture
    37,166 Most Valuable Professional on at

    Try placing SalesInvoiceLine.RESET before the filter for Document No. Like:

    SalesInvoiceLine.RESET;

    SalesInvoiceLine.SETRANGE("Document No.",SalesInvoiceHeader."No.");

    P.S. it's better to use SETRANGE when you have to filter a field for a value.

  • 4BzSoftware Profile Picture
    6,073 on at

    Hi Patrick,

    I think the loop logic is ok.

    What is inside SalesInvoiceLine loop?

    (//In this loop whe create one file per SalesInvoiceHeader.

    //Per SalesInvoiceHeader we create one row per SalesInvoiceLine.)

  • Community Member Profile Picture
    on at

    Hi Khoa,

    This is what is inside the loop. Also look at the first line after the loop.

    i.e.:  SalesInvoiceHeader.CALCFIELDS("Amount Including VAT",Amount);

    This also gives the wrong results (sometimes) for the cumulative values.

    In the same cu i also calculate the vat s from table 254 Vat Entry.

    The strange thing is that this gives the correct values.

    I also have a comparible problem with delivery lines in another codeunit.

    A difficulty is that i cannot reproduce the problem.

       IF SalesInvoiceLine.FIND('-') THEN BEGIN

         REPEAT

           GTIN:='';

           IF SalesInvoiceLine.Type = SalesInvoiceLine.Type::Item THEN BEGIN

             IF Item.GET(SalesInvoiceLine."No.") THEN BEGIN

               GTIN:= FORMAT(Item.GTIN)

             END;

           END;

           IF SalesInvoiceLine.Type = SalesInvoiceLine.Type::Resource THEN BEGIN

             IF Resource.GET(SalesInvoiceLine."No.") THEN BEGIN

               GTIN:= FORMAT(Resource.GTIN);

             END;

           END;

           IF SalesInvoiceLine.Type = SalesInvoiceLine.Type::"G/L Account" THEN BEGIN

               GTIN:= '8716376999832';

           END;

           //Door te controleren op aantal = 0  worden tekstregels (staan ook in resources) niet meegenomen.

           IF SalesInvoiceLine.Quantity <> 0 THEN BEGIN

             XMLDOMMgt.AddElement(XMLLinesNode, 'Line', '', XMLRootNode.NamespaceURI, XMLCreatedNode);

             XMLDOMMgt.AddElement(XMLCreatedNode, 'Article_Eancode', GTIN, XMLRootNode.NamespaceURI, XMLCreatedNode2);

           END;

         UNTIL SalesInvoiceLine.NEXT=0

       END;

     SalesInvoiceHeader.CALCFIELDS("Amount Including VAT",Amount);

    Thanks.

  • Community Member Profile Picture
    on at

    I changed this. I cannot reproduce the error and so i do not now if it will help.

  • Suggested answer
    4BzSoftware Profile Picture
    6,073 on at

    Hi Patrick,

    The logic inside SalesInvoiceLine loop seems ok.

    Problem may be inside XMLDOMMgt codeunit.

  • Community Member Profile Picture
    on at

    Hi All,

    Is it possible that the problem is caused by the fact that creation of invoice (rows) is slow?

    I first get the header and after that the invoice rows. Could it be possible that when I get the rows the creation of all the rows is not finished?

    And if this is the cause how can I handle it?

    Thanks Patrick

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Introducing the 2026 Season 1 community Super Users

Congratulations to our 2026 Super Stars!

Congratulations to our 2025 Community Spotlights

Thanks to all of our 2025 Community Spotlight stars!

Leaderboard > 🔒一 Microsoft Dynamics NAV (Archived)

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans