SETFILTER problem

mkpjsr

Hi all,

I want to filter records from Sales Invoice Line table based on the month from Posting Date field with the value comming from request variable, so i have written the code.

Smonth ->Integer [used for receiving month value from request form]

"Sales Invoice Line".SETFILTER(DATE2DMY("Sales Invoice Line"."Posting Date",2),'%1',SMonth);

but its not allowing to use DATE2DMY function inside the SETFILTER function.

how can i achieve this.

kapamarou

mkpjsr wrote:

"Sales Invoice Line".SETFILTER(DATE2DMY("Sales Invoice Line"."Posting Date",2),'%1',SMonth);

You cannot use that.

SETFILTER is followed by the field on which you want to filter.

You will have to use variables and get the Starting date of the month and the ending date of the month.

Then do a SETRANGE("Posting Date",StartMonthDate,EndMonthDate) or the SETFILTER("Posting Date",'%1..%2,'StartMonthDate,EndMonthDate).

Have you read the documentation / help to figure out the syntax of the commands?

vijay_g

kapamarou wrote:

Then do a SETRANGE("Posting Date",StartMonthDate,EndMonthDate) or the SETFILTER("Posting Date",'%1..%2,'StartMonthDate,EndMonthDate).

That will not fullful to requirment.
he wants entries with specific month filter either year would have 2001,2002,2003......so.
I think you(mkpjsr) have to extract each entry for calculate month and then use SETRANGE.
but it will create problem when you want to set filter on ONPREDATAITEM trigger.

let see more reply...

kapamarou

vijay_g wrote:

he wants entries with specific month

If that's the case then it would be better to create a field to store the month and filter on that.

vijay_g

i think so...
in this case it can be best.

stryk

Have in mind you couold use the CALCDATE function; e.g. like this:

SMonth := <VariableInput>;
StartDate := DMY2DATE(1, SMonth, DATE2DMY(WORKDATE, 3));  // first day of SMonth
EndDate := CALCDATE('+1<M>-1<D>', StartDate);                      // last day of SMonth
SalesInvoiceLine.SETRANGE("Posting Date", StartDate, EndDate);

DenSter

There's no need for additional variables, data type conversion or any other fancy programming. CALCDATE has what you need. Use CM in your CALCDATE, which stands for Current Month. For the first of the month use -CM, for the last of the month use +CM.

Create a new blank form, put a button on it, and in the OnPush of that button, paste this code:

MESSAGE(format(CALCDATE('<-CM>',TODAY)) + '\' + format(CALCDATE('<+CM>',TODAY)));

The angle brackets are to keep translation issues out of the picture.

For putting a proper date filter into your SETFILTER, you're going to have to think about that. Think about what a proper date filter looks like, and make sure that the value that you put in there is a proper date filter. DATE2DMY returns an integer, and an integer is NOT a proper value for a date filter.

mkpjsr

DenSter wrote:

There's no need for additional variables, data type conversion or any other fancy programming. CALCDATE has what you need. Use CM in your CALCDATE, which stands for Current Month. For the first of the month use -CM, for the last of the month use +CM.

Create a new blank form, put a button on it, and in the OnPush of that button, paste this code:

MESSAGE(format(CALCDATE('<-CM>',TODAY)) + '\' + format(CALCDATE('<+CM>',TODAY)));

The angle brackets are to keep translation issues out of the picture.

For putting a proper date filter into your SETFILTER, you're going to have to think about that. Think about what a proper date filter looks like, and make sure that the value that you put in there is a proper date filter. DATE2DMY returns an integer, and an integer is NOT a proper value for a date filter.

but in my case month is not always the current month, then how to do

DenSter

Create a test form, with the button, and put the code in the button. Then create a date variable (call it "MyDate"), add a textbox to the form, and set the text box's SourceExpr to MyDate. Change the code that I gave you to use MyDate instead of TODAY. Enter any date in the textbox and see what happens.

Try it, and see.