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

December Spotlight Star - Muhammad Affan

Congratulations to a top community star!

Top 10 leaders for November!

Congratulations to our November super stars!

Tips for Writing Effective Suggested Answers

Best practices for providing successful forum answers ✍️

Leaderboard

#1
André Arnaud de Calavon Profile Picture

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

#2
Martin Dráb Profile Picture

Martin Dráb 230,214 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans