i am having some problems using Date Filter

Business Central/NAV Developers Forum
1

Hi,

all i want is the total balance of inventory before and after the date filter (if there is one provied)Here is my code:

Item - OnAfterGetRecord()
IF DateFilter <> ‘’ THEN BEGIN
SETRANGE(“Date Filter”,0D,GETRANGEMIN(“Date Filter”) - 1);//before datefilter
CALCFIELDS(Inventory);
openBAL := Inventory;
SETRANGE(“Date Filter”,GETRANGEMAX(“Date Filter”) + 1,01019999D);//after datefilter
CALCFIELDS(Inventory);
closeBAL := Inventory;

SETFILTER(“Date Filter”,DateFilter);
END;

All it keeps giving me is the total Inventory for both the openBAL and CloseBAL. I know i am doing something wrong cause ive seen this done in many other places. Can someone please point out my mistake ?

Ziad

2

Why are you stroring a variable DateFilter and then don’t use it? (You use GETRANGEMIN(“Date Filter”) instead of GETRANGEMIN(DateFilter) ?!)

3

Perhaps “Date Filter” is not used bay the calcfield.

Regards.

4

because Datefilter is in text format, it comes from Item.GETFILTER(“Date Filter”), notice the condition if dateflter <> ‘’ then, and GETRANGEMAX / MIN is in format GETRANGEMIN(Field). not GETRANGEMIN(Variable).

5

You should determine “FirstDayInFilter” and “LastDayInFilter” as a date prior doing your setranges:

Item - OnBeforeDataItem()
IF DateFilter <> ‘’ THEN BEGIN
SETFILTER(“Date Filter”, DateFilter);
FirstDayInFilter := GETRANGEMIN(“Date Filter”);
LastDayInFilter := GETRANGEMAX(“Date Filter”);
END;

Item - OnAfterGetRecord()
IF DateFilter <> ‘’ THEN BEGIN
SETRANGE(“Date Filter”,0D,FirstDayInFilter - 1);//before datefilter
CALCFIELDS(Inventory);
openBAL := Inventory;
SETRANGE(“Date Filter”,LastDayInFilter + 1,01019999D);//after datefilter
CALCFIELDS(Inventory);
closeBAL := Inventory;
SETFILTER(“Date Filter”,DateFilter);
CALCFIELDS(Inventory);
END;

The problem is just that GETRANGEMAX(“Date Filter”) in your code returns the original start date of the filter as you have set the line with the “before datefilter” filter to 0D…FirstDayInFilter. If you after that call the GETRANGEMIN it returns 0D and GETRANGEMAX returns FirstDayInFilter.

6

Hi Ziad

The “Date Filter” is not used to calculate the Inventory and that’s why you get the same result independent of the value you set in “Date Filter”.

Regards

Claus

7

I think Claus is right, How can add inventory to the flow field?

8

Use the Field “Net Change” instead of “Inventory”. But anyway, the filtering for Date Filter is also not correct

Never change a standard Flow Field !!!

9

Hi, thanks thomas, got it working !

10

The simple way is :

Create a record variable for item ledger Entry (recItemLedgEntry),

Filter it on what you nedd and do recItemLedgEntry.calcsums(quantity)

11

Hi, thanks thomas, got it working !

12

Great to get response on a result. I have set this topic to resolved.

If you don’t mind, please rate this topic if you found the answers useful.