web
You’re offline. This is a read only version of the page.
close
Skip to main content
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
    RE: Missing results in query

    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
    RE: Missing results in query

    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
    RE: Missing results in query

    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
    RE: Missing results in query

    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
    RE: Missing results in query

    Hi Patrick,

    The logic inside SalesInvoiceLine loop seems ok.

    Problem may be inside XMLDOMMgt codeunit.

  • Community Member Profile Picture
    on at
    RE: Missing results in query

    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

Responsible AI policies

As AI tools become more common, we’re introducing a Responsible AI Use…

Pallavi Phade – Community Spotlight

We are honored to recognize Pallavi Phade as our Community Spotlight honoree for…

Leaderboard > 🔒一 Microsoft Dynamics NAV (Archived)

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans