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

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

Season of Sharing Community Challenge Winners!

Congratulations to our community stars!

Women in Power Builds Momentum

Expanding mentorship, skilling, and AI innovation

Congratulations to the August Top 10 Community Leaders

These are the community rock stars!

Leaderboard > 🔒一 Microsoft Dynamics NAV (Archived)

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans