Sql to nav page

sjen · 2019-04-16

Hi,
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 cant filter properly. What am I missing? I am a beginner btw

Jan87 · 2019-04-17

Hi sjen,

why don't you filter in NAV?

PurchInvLine.SETRANGE(Type,Type::"G/L Account");
PurchInvLine.SETFILTER("No.",'%1*|%2*|%3*',122350,122370,7);
IF PurchInvLine.FINDSET(FALSE,FALSE) THEN BEGIN
   REPEAT
       // Populate Temp Table //
       Rec := PurchInvLine;
       INSERT;
   UNTIL PurchInvLine.NEXT = 0;
END;

RockWithNAV · 2019-05-01

@sjen Between SETRANGE and SETFILTER you should always give priority to SETRANGE.

COPYSTR will anyhow you have to do looping and SETFILTER will save you from this so SETFILTER holds the priority.

wolfskin · 2019-04-17

Hi sjen,

You can use the Linked Object for linking to SQL Server objects.

https://docs.microsoft.com/en-us/dynamics-nav/using-linked-objects

Jan87 · 2019-04-17

Hi sjen,

why don't you filter in NAV?

PurchInvLine.SETRANGE(Type,Type::"G/L Account");
PurchInvLine.SETFILTER("No.",'%1*|%2*|%3*',122350,122370,7);
IF PurchInvLine.FINDSET(FALSE,FALSE) THEN BEGIN
   REPEAT
       // Populate Temp Table //
       Rec := PurchInvLine;
       INSERT;
   UNTIL PurchInvLine.NEXT = 0;
END;

sjen · 2019-04-17

Thank you Jan87,
This should do the trick. I will work on it. Thank you for your time.

RockWithNAV · 2019-05-01

@sjen - Even what you have written will work with certain tweak but it will have an impact over performance , Why to do unnecessary loops if it can be saved by a filter right.

sjen · 2019-05-01

but using a setfilter, doesn't it make run slower than copystr?

RockWithNAV · 2019-05-01

@sjen Between SETRANGE and SETFILTER you should always give priority to SETRANGE.

COPYSTR will anyhow you have to do looping and SETFILTER will save you from this so SETFILTER holds the priority.

sjen · 2019-05-01

Thank you for your time @RockWithNAV,
I started nav 3-4 months ago and I feel like I am learning a lot. I also have the best mentor at work. Thank you again!

Sql to nav page

Best Answers

Answers

Categories