First and Last Date from Daterange in a filter

Business Central/NAV Developers Forum
1

I am writing a report and I need to be able to get the first and last date (Min and Max?) from “G/L Entry”.GETFILTER(“Posting Date”)

I need to subtract 2 months from the lowest date and ADD 2 months to the highest date.

Other than putting the dates in the Option tab, I am not sure if this is possible, anyone know?

2

This worked:

FirstDate := CALCDATE(’<-2M>’,“G/L Entry”.GETRANGEMIN(“Posting Date”));
LastDate := CALCDATE(’<+2M>’,“G/L Entry”.GETRANGEMAX(“Posting Date”));

3

Hi Colin,

I think that You must embed the GETRANGEMIN and GETRANGEMAX inside some IF’s.

Something like…
IF “G/L Entry”.GETRANGEMIN(“Posting Date”) THEN
FirstDate := CALCDATE(’<-2M>-,“G/L Entry”.GETRANGEMIN(“Posting Date”))
ELSE
FirstDate := 0D;

If the filter that is actually set on “Posting Date” has no lower limit (e.g. “…31.12.2007”), then the GETRANGEMIN will give a runtime-error.

4

Don’t think that it will work - return value of the function is min. range value.

I think you can only use IF “G/L Entry”.GETFILTER(“Posting Date”) <> ‘’ THEN, but if filter will be set like …01/01/01, then error will occur anyway.

5

[:$] ouups…
You’re right.

I remembered having done it like this in an earlier version, but maybe i’m mistaking.

So i we don’t want any runtime-errors, we have to copy the filter into a string, and search for specific signs, to determine the structure of the filter…
Or what?

6

I don’t think that it is good idea to search in the string - there are lot of posibilities to make complex filters.

I think that if you really don’t want to get such error (which is not very understandable for user), you have to add Starting Date and Ending Date fields in to request form and test if user have entered them.

Or you can apply the filter to Date table “Starting date” field and try to find first entry. If it’s not 01/01/0000 then min. range exists.

7

I did not want to use the StartDate EndDate in the Options as it seems a bit crude. I am sure that it must be possible to find if the filter is “…311207” and if so, enter a MinDate that is say -3M the MaxDate.

8

That’s how I normally do it, if I want to make sure the user enters a date-range.

Maybe, if this can apply to the report, You can make a lookup to the Accounting Periods in the options-tab.
And then calculate the StartDate & EndDate in OnPreReport.