Dynamics 365 CommunityForums Microsoft Dynamics NAV (Archived) Sql to nav page(Micros...

Sql to nav page(Microsoft Dynamics NAV)

(0) Share

Report

Posted on by sjen

I have a sql statement and my client runs this sql monthly basis and he decided that he wants this in nav. I created a temp page and a report for them to enter a date range(request page) then I want to display but I can't display the way I want.

for example here my sql statement

dbo.[Companyname$Purch_ Inv_ Line].No_ LIKE '122350%' OR
dbo.[Companyname$Purch_ Inv_ Line].No_ LIKE '122370%' OR
dbo.[Companyname$Purch_ Inv_ Line].No_ LIKE '7%')

My solution is

IF PurchInvLine.FINDSET THEN
      REPEAT
      IF (Type = Type::"G/L Account") AND 
        ((COPYSTR("No.",1,6) IN ['122350', '122370']) OR // Problem is here I know. If I don't have anything here I can display everything
         (COPYSTR("No.",1,1) = '7'))
      BEGIN
          // Populate Temp Table //
          Rec := PurchInvLine;
          INSERT;
        END;
      UNTIL PurchInvLine.NEXT = 0;
  UNTIL PurchInvHeader.NEXT = 0;

I can do the rest but I can't filter properly. What am I missing? I am a beginner btw

*This post is locked for comments

All responses (1)

Answers (1)

Verified answer

Hannes Holst 5,767 on at

Like (1)

Report
RE: Sql to nav page(Microsoft Dynamics NAV)

Hi,

for what exactly do you want to apply a filter?

Alternativly, you could do something like this also:

PurchInvLine.SETRANE(Type, PurchInvLine.Type::"G/L Account"); PurchInvLine.SETFILTER("No.", '122350*|122370*|7*'); IF PurchInvLine.FINDSET THEN REPEAT // processing of PurchInvLine-records here
Rec := PurchInvLine; INSERT;UNTIL PurchInvLine.NEXT = 0;