DateFilter

(0) Share

Report

Posted on by Community Member

Hi experts ,

i want to take a report only for expired due date and remaining amount <>0 value i need to take

i have used this code

SETRANGE("Document Type",custledger."Document Type"::Invoice);
SETAUTOCALCFIELDS("Remaining Amount");
SETFILTER("Due Date",'<%1',WORKDATE,Date2);
SETFILTER("Remaining Amount", '>0');

i got solution (please see below screen shot)

4848.st2.png

but my requirements is i need to take a expired due date with this date to this date(19/04/2018..09/04/2018)

i have used this code

Date2 := 090418D;
EndDate:= WORKDATE;
Date1 :=190418D;
SETRANGE("Document Type",custledger."Document Type"::Invoice);
SETAUTOCALCFIELDS("Remaining Amount");
SETFILTER("Due Date",'<%1',WORKDATE,Date2);
SETFILTER("Remaining Amount", '>0');

SETRANGE("Due Date",Date2,EndDate);

but im getting this results (please see below screen shot)

i want to only red color data with this filter

Date2 := 090418D;
EndDate:= WORKDATE;

SETRANGE("Due Date",Date2,EndDate);

please give any soution.

Thank you

*This post is locked for comments

I have the same question (0)

All responses (7)

Answers (1)

Suggested answer

Stefano Demiliani 37,166 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!

A filter for Due date less than TODAY is:

SETFILTER("Due Date",'<%1',TODAY);

P.S. remember that WORKDATE could be different that TODAY.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report
Copy link

Link copied!

Hi Stefano,

Thank yo so much for your quick reply

i want to set filter

date1 := 09/04/2018;

SETFILTER("Due Date",Date1,'<%1',TODAY); //(09/04/2018..19/04/2018)

but its showing error.

please give any solution.

Thank you

Was this reply helpful? Yes No
Suggested answer

Stefano Demiliani 37,166 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!

Do you want to set a date filter from Date1 to TODAY?

Then:

SETFILTER("Due Date",'%1..%2',Date1,TODAY);

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report
Copy link

Link copied!

Hi Stefano,

I want take data from lesser of today date

if i use like that this is also showing error

SETFILTER("Due Date",'%1..<%2',Date1,TODAY);

please see this and give any solution

Thank you

Was this reply helpful? Yes No
Suggested answer

Stefano Demiliani 37,166 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!

SETFILTER("Due Date",'%1..%2',Date1,CALCDATE('-1D',TODAY));

Was this reply helpful? Yes No
Verified answer

Andrey Baludin 3,943 on at

Like (0)

Report
Copy link

Link copied!

Also

SETRANGE("Due Date",Date1,CALCDATE('-1D',TODAY));

will work

Was this reply helpful? Yes No
alan.butterfield on at

Like (0)

Report
Copy link

Link copied!

The SETFILTER statement is incorrect because the Filter string '<%1' only contains a single substitution character therefore the variable Date2 is ignored.

Secondly, the SETRANGE statement on the "Due Date" field removes the filter set by SETFILTER statement

EndDate := CALCDATE(-1D,WORKDATE) //workdate less 1 day

Date2 := 090418D

SETRANGE("Due Date",Date2,EndDate);

Was this reply helpful? Yes No