Fill date filter automatically with another date

catiamatos1991

Hello everyone,

I need to add an extra column in Chart of Accounts page with Net Change of the last year. The idea is to fill the Date filter and then in this extra column see the same amount of Net Change but for the last year.

Example.. If i select a period like 01012018..01042018 i want a second date filter to be filled with 01012017..01042017.

In order to do this I create two new columns called Net Change2 (flow filter like Net Change but with a where clause linked to Date Filter 2) and add a column called Date Filter (a flowfield Date) in G/L account table

Then i add this line in GetBalance function

GLAcc.SETFILTER("Date Filter",'%1..%2',v_StartDate,v_EndDate);
GLAcc.SETFILTER("Date Filter2",'%1..%2',CALCDATE('<C-Y>',v_StartDate));

My problem is how to automatically fill date filter 2 with the Date filter less 1 year? I don't want to be me to need to fill this, but be automatically filled.
For now is the only thing i need to do to make this work

Miklos_Hollender

It would be easier to just use Account Schedules, because what you are doing looks like e.g. a typical Balance Sheet and that is better to do there.

But anyway, what is wrong? It seems only the date formula is wrong, it is simply '-1Y' and you forgot to do the same with the end date. Also it is not really a good idea to hardcode this, because it depends on the language. Why not just create a dateformula field in General Ledger Setup?

catiamatos1991

So what I need is to automatically set a new field with the Date Filter - 1y. I tried to code because i though that could be easy but i'm struggling to found a way that when i fill this filter in nav the other one automatically will be filled understand? and this will reflected in my new column net change 2

I tried to do GLAcc.SETFILTER("Date Filter2",'%1..%2',CALCDATE('<C-1Y>',v_StartDate));

but the total is the same...

Miklos_Hollender

Where is vStartDate coming from? Try this. On various triggers of the page, sch as OnAfterGetCurrentRecord, put something along the lines of MESSAGE('From Trigger XY: ' +FORMAT(rec.GETFILTER("Date Filter"));

This should give you a view that what trigger is executed after you set a date filter and in what trigger can you find that date.

If you have that, comes the hard part, because I don't think you can run CALCDATE on a whole date range. Most likely you will have to verify if a date filter is set at all, split it with GETRANGEMIN / GETRANGEMAX, then CALCDATE, then SETRANGE...

Probably it would be easier if you would choose a different approach. For example, adding two new fields on the page, but not on the table, but just startdate, enddate as date variables behind them, and then you would set both date filters from the onvalidate or onaftervalidate or like that of the page fields.

(I still don't think reinventing Accounting Schedules is a good idea.)

catiamatos1991

I never use Accounting Schedules and im not familiarized with. I create in g/l account table a field call date filter 2.
In the net change 2 flow filter i've set the where clause to date filter 2.

I've tried the above code in get balance function but still don't work,

glAcc.SETFILTER("Date Filter2",'%1..%2',CALCDATE('<-CY-1Y>', v_StartDate), CALCDATE('<CY-1Y>', v_StartDate));

Duikmeester

With GETRANGEMIN and GETRANGEMAX, something along the lines of:

GLAccount.SETRANGE("Date Filter",v_StartDate,v_EndDate);

GLAccount.SETRANGE("Date Filter 2",
  CALCDATE('<-1Y>',GLAccount.GETRANGEMIN("Date Filter")),
  CALCDATE('<-1Y>',GLAccount.GETRANGEMAX("Date Filter"))
);

catiamatos1991

I did that in getBalance function but when I fill the filter Date filter the Net change column change but Net Change 2, linked to Date Filter2 remains the same...

I put in date filter this 01012018..01022018 but the column for Date Filter2 is empty, I know that's a flowfilter but is consuming the value of other column with value so it should be filled right?

catiamatos1991

I've solved..

So the solution was to create a text global var called LastDateFilter and add this code in the OnAfterGetCurrRecord of Page 16.

IF LastDateFilter <> Rec.GETFILTER("Date Filter") THEN BEGIN
Rec.SETFILTER("Date Filter2",'%1..%2',CALCDATE('<-CY-1Y>', Rec.GETRANGEMIN("Date Filter")), CALCDATE('<CY-1Y>', Rec.GETRANGEMIN("Date Filter")));
LastDateFilter := Rec.GETFILTER("Date Filter");
CALCFIELDS("Saldo Periodo2");
END;

kriki

[Topic moved from 'NAV/Navision Classic Client' forum to 'NAV Three Tier' forum]