Filtering on date

Business Central/NAV User Forum
1

Hi,

I am designing a report using “G/L Entry” table. Some values are shown in the footer part of this table. I have set the “Posting Date” field in the RequiredFilterFields property, but the values in the report at the footer part are not filtering based on the posting date given. What may be the reason for this? Can anyone help me with this…

Thanks in advance…

2

Are you saying that giving a specific filter date on PostingDate field, the report display also entries with different PostingDate ?

It’s sound a problem on C/AL code…but I’m not sure…

3

It totally depends on what the values are. Manually summed up variables? Done using CREATETOTALS? Something else?

4

ya… its not filtering for the date for wat is given in the filter field. Its giving the value as on date for watever date is given.

5

Thanks fa the reply… Ya they are manually summed up variables… Can I pls know how to go on wid it…

6

Have you used G/L Entry dataitem while calculating the total variables or new global variable record of G/L Entry?

7

If you’re manually summing them up then just put the code in the OnAfterGetRecord trigger. Now if you have code later that hides sections instead of skipping records the total will still be off.

8

Hi Mohan,

Ya… I have used G/L Entry dataitem,and in that I have created many instances of G/L Entry record variables as I had filter on the account nos… here is the piece of code that is put in the G/L Entry footer - OnPreSection()

recGLEntry.RESET;
recGLEntry.SETRANGE(recGLEntry.“G/L Account No.”,“G/L Entry”.“G/L Account No.”);
recGLEntry.SETFILTER(recGLEntry.“G/L Account No.”,’%1|%2’,‘3010530’,‘3010540’);
IF recGLEntry.FINDFIRST THEN
REPEAT
GLAmt1 += ABS(recGLEntry.Amount);
UNTIL recGLEntry.NEXT=0;

recGLEntry1.RESET;
recGLEntry1.SETRANGE(recGLEntry1.“G/L Account No.”,“G/L Entry”.“G/L Account No.”);
recGLEntry1.SETFILTER(recGLEntry1.“G/L Account No.”,’%1’,‘3010620’);
IF recGLEntry1.FINDFIRST THEN
REPEAT
GLAmt2 += recGLEntry1.Amount;
UNTIL recGLEntry1.NEXT=0;

recGLEntry2.RESET;
recGLEntry2.SETRANGE(recGLEntry2.“G/L Account No.”,“G/L Entry”.“G/L Account No.”);
recGLEntry2.SETFILTER(recGLEntry2.“G/L Account No.”,’%1’,‘4213030’);
IF recGLEntry2.FINDFIRST THEN
REPEAT
GLAmt3 += recGLEntry2.Amount;
UNTIL recGLEntry2.NEXT=0;

GLAmt4 := GLAmt1 - GLAmt3;

GLAmount := GLAmt1 + GLAmt2;

Thanks…

9

Hi Matt Traxinger,

Thanks fa the reply… I tried with the code in OnAfterGetrecord also… still its not summing up properly…

10

Hi,

The code you have posted does not filter by the posting date - you need to get the filter from the report record and apply it to the record variables recGLEntry and recGLEntry2 using the GETFILTER/SETFILTER or COPYFILTERS to copy all filters.

11

Also, each of those SETRANGE statements isn’t going to do anything. The SETFILTER directly after it will overwrite whatever filter you applied using SETRANGE.

12

My guess is correct [;)]

You have used variable instead of dataitem to calculate the value…

You need apply the same datefilter on those variables also…