Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Dynamics 365 Community / Forums / Microsoft Dynamics NAV (Archived) / Missing results in query
Microsoft Dynamics NAV (Archived)

Missing results in query

(0) ShareShare
ReportReport
Posted on by Community Member Microsoft Employee

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

  • Community Member Profile Picture
    Community Member Microsoft Employee 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

  • Suggested answer
    4BzSoftware Profile Picture
    4BzSoftware 6,071 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
    Community Member Microsoft Employee 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.

  • Community Member Profile Picture
    Community Member Microsoft Employee 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.

  • 4BzSoftware Profile Picture
    4BzSoftware 6,071 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.)

  • Suggested answer
    Stefano Demiliani Profile Picture
    Stefano Demiliani 37,162 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.

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

Congratulations 2024 Spotlight Honorees!

Kudos to all of our 2024 community stars! 🎉

Meet the Top 10 leaders for December!

Congratulations to our December super stars! 🥳

Get Started Blogging in the Community

Hosted or syndicated blogging is available! ✍️

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 291,622 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 230,354 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans