Fill date filter automatically with another date

(0) Share

Report

Posted on by Community Member

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-1Y>',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 after Date Filter 1. I tried the above code but no changes in Net change 2 when I modify the Date Filter like the image can show.
For now is the only thing i need to do to make this work

*This post is locked for comments

I have the same question (0)

All responses (17)

Answers (1)

Suggested answer

Guillem Padilla 427 on at

Like (0)

Report
Hi,

Try this:

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

If v_StartDate is: 10/04/19 then "Date Filter2" would be 01/01/18..31/12/18

If you want to keep the same day try this:

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

"Date Filter2" would be 10/04/18..31/12/18

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

"Date Filter2" would be 10/04/18..10/04/19

Also check this documentation about how to use CALCDATE:

https://docs.microsoft.com/en-us/dynamics-nav/calcdate-function--date-

I hope it helps!

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

I tried this glAcc.SETFILTER("Date Filter2",'%1..%2',CALCDATE('<-CY-1Y>', v_StartDate), CALCDATE('<CY-1Y>', v_StartDate)); but is the same.. no change in Net Change 2.

same result as my last pic

Was this reply helpful? Yes No
Suggested answer

Guillem Padilla 427 on at

Like (0)

Report

I think you did it in "Date Filter", not "Date Filter2"

Can you check it?

Also check if "Saldo periodo 2" Field is looking for "Date Filter 2" in the CalcFormula

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Yes it is,

Sum("G/L Entry".Amount WHERE (G/L Account No.=FIELD(No.),G/L Account No.=FIELD(FILTER(Totaling)),Business Unit Code=FIELD(Business Unit Filter),Global Dimension 1 Code=FIELD(Global Dimension 1 Filter),Global Dimension 2 Code=FIELD(Global Dimension 2 Filter),Posting Date=FIELD(Date Filter2)))

Was this reply helpful? Yes No
Guillem Padilla 427 on at

Like (0)

Report

Where are you executing the code?

Maybe is in the wrong trigger

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

the filter is in the get balance function in table g/l account

Was this reply helpful? Yes No
Suggested answer

Guillem Padilla 427 on at

Like (0)

Report
GetBalance should be executed every time the user puts the Date Filter

Where do you put/get v_StartingDate value?

Try this:

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

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

The problem is the column net change 2 have always the same value... that code din't work because the date filter stay empty.. in the column date filter 2 i don't have any value.

Was this reply helpful? Yes No
Guillem Padilla 427 on at

Like (0)

Report

Are both lists pointing to the same rec?

What if you put the Date filter on first list?

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

I doidn't understand your last reply sorry

Was this reply helpful? Yes No