Report Filter problem

mkpjsr

Hi all,

I have designed a report with the following data item:-
DataItem Name
Sales Cr.Memo Line
Sales Invoice Header
Sales Invoice Line

Here the value DataItemLink property of Sales Invoice Line is Document No.=FIELD(No.)
but there is no such relation between Sales Cr.Memo Line and Sales Invoice Header data items.

I am using posting date as a filter field from Sales Invoice Line Table and this filter has no affect on Sales Cr.Memo Line table. Report is fetching all data from this table.

What should i do to resolve this problem.

Shedman

How about adding a dataitem Sales Cr.Memo Header?

mkpjsr

Shedman wrote:

How about adding a dataitem Sales Cr.Memo Header?

Actually i want to apply the same filter on Sales Invoice Line,header and sales cr. memo line table. And i have selected "posting Date" of Sales Invoice Line as required filter field.

matttrax

NAV doesn't know what filters you've applied to the request form of a data item until the code actually gets to the OnPreDataItem trigger for it.

You have a few of options:

You can move your data items around.
You can filter using a global variable and the Options Request Form.
You can add a "fake" Sales Invoice Line data item to the start of your report, get the filters before running everything else, and apply them as needed.

Play around with each and see what works best for this scenario.

klavin

matttrax wrote:

NAV doesn't know what filters you've applied to the request form of a data item until the code actually gets to the OnPreDataItem trigger for it.

What about Daily Invoicing Report...:

Report - OnPreReport()
FilterString := COPYSTR("Sales Invoice Header".GETFILTERS,1,MAXSTRLEN(FilterString))

;

You could GETFILTER("Posting Date") and apply that on another dataitem.

The only thing I don't like about doing these type of things is people can add filters on the Req. sheet for the dataitem - for example, sell-to customer no... And then do you take that into account and copy the filters as well.

matttrax

klavin wrote:

What about Daily Invoicing Report...:

I believe you can do a GETFILTERS to get ALL the filters before you get to the data item. That's common to print on the top of reports. But if you try to get the filter for a specific field I'm pretty sure it doesn't work unless you have reached the OnPreDataItem trigger for that data item. I seem to remember trying to do this several time. Hopefully someone will correct me if I am wrong.

Savatage

I believe they were trying to say the Daily Invoicing Report - on the request form already has the ability to add into one report Credits & invoices. So you can use that report as your example, to see what was done.

Or even, Rename it and edit that report since the bones are already there.

mkpjsr

Hi,

I have tried the below code assuming that the date filter is in the form: 12/12/09..31/12/09
globals used

FilterString ->text
FirstDateString->text
SecondDateString->text
FirstDate->Date
SecondDate->Date

Sales Invoice Header - OnPreDataItem()

FilterString := "Sales Invoice Header".GETFILTER("Posting Date");
FirstDateString:=COPYSTR(FilterString,1,8);
SecondDateString:=COPYSTR(FilterString,11,8);

Sales Cr.Memo Line - OnPreDataItem()

EVALUATE(FirstDate,FirstDateString);
EVALUATE(SecondDate,SecondDateString);
"Sales Cr.Memo Line".RESET;
"Sales Cr.Memo Line".SETRANGE("Sales Cr.Memo Line"."Posting Date",FirstDate,SecondDate');

but this filter is not working

klavin

A look at the Daily Invoicing report again, if you have access to is...:
Try:

OnPreDataItem of Sales Cr.Memo Header

  "Sales Invoice Header".COPYFILTER("Posting Date","Sales Cr.Memo Header"."Posting Date");

Lavin

navinb

If you have use Posting Date as Reqfilterfields on Sales Inv. Line ,then onprereport you can use GETRANGEMAX and GETRANGEMIN to find the start date and end date ...

klavin

navinb wrote:

If you have use Posting Date as Reqfilterfields on Sales Inv. Line ,then onprereport you can use GETRANGEMAX and GETRANGEMIN to find the start date and end date ...

If you're just copying the filter, why not use COPYFILTER? I agree, that is how you could get the mins and maxes, but from what he is doing COPYFILTER makes more sense.

mkpjsr: I still recommend taking a look at some of the features in the Application Designers Guide on the product CD and online help just for some references on these functions so you know some of the other functions you can use.

navinb

yes i agree with you Lavin,but looking at the original code user is specifically onto calculating startdate and enddate first ,so i tot he wants that apart from setting filters also:

FilterString := "Sales Invoice Header".GETFILTER("Posting Date");
FirstDateString:=COPYSTR(FilterString,1,8);
SecondDateString:=COPYSTR(FilterString,11,8);

Sales Cr.Memo Line - OnPreDataItem()

EVALUATE(FirstDate,FirstDateString);
EVALUATE(SecondDate,SecondDateString);
"Sales Cr.Memo Line".RESET;
the whole part can be easily handled with GETRANGEMAX and GETRANGEMIN,

if it is just limited to getting the whole range then
COPYFILTER must work
"Sales Cr.Memo Line".SETRANGE("Sales Cr.Memo Line"."Posting Date",FirstDate,SecondDate');


Thanks
Navin

mkpjsr

klavin wrote:

A look at the Daily Invoicing report again, if you have access to is...:
Try:

OnPreDataItem of Sales Cr.Memo Header

  "Sales Invoice Header".COPYFILTER("Posting Date","Sales Cr.Memo Header"."Posting Date");

Lavin

Can u plz tell me the report ID

mkpjsr

If i am writing this code under OnPreDataItem of Sales Invoice Header then i am not able to fetch the values of these two globals "FirstDate" and "SecondDate" under OnPreDataItem of Sales Cr. Memo Line.

FirstDate:="Sales Invoice Header".GETRANGEMAX("Sales Invoice Header"."Posting Date");
SecondDate:="Sales Invoice Header".GETRANGEMIN("Sales Invoice Header"."Posting Date");

mkpjsr

  "Sales Invoice Header".COPYFILTER("Posting Date","Sales Cr.Memo Header"."Posting Date");

Lavin

I have tried this, but its not working, do i need to write some code for filtering after COPYFILTER.
Please guide me.

David_Singleton

mkpjsr wrote:

Hi all,

I have designed a report with the following data item:-
DataItem Name
Sales Cr.Memo Line
Sales Invoice Header
Sales Invoice Line

Here the value DataItemLink property of Sales Invoice Line is Document No.=FIELD(No.)
but there is no such relation between Sales Cr.Memo Line and Sales Invoice Header data items.

I am using posting date as a filter field from Sales Invoice Line Table and this filter has no affect on Sales Cr.Memo Line table. Report is fetching all data from this table.

What should i do to resolve this problem.

You are going about this completely the wrong way.

to answer your question:

mkpjsr wrote:

What should i do to resolve this problem.

Before you start programming in Navision you need to understand how Navision works, and I mean Navision the application, not C/SIDE and C/AL. You need to know the table structure of Navision and how data is posted. Untill you do this you will never be able to use Navision to its fullest. Everyone needs to post at least 100 sales orders, purchase orders, payments receipts, Financial journals etc, before expecting to get everything to work the way they need.

in answer to your report requirement, once you learn't Navision, you would realize that you need to start with the table "Cust. Ledger Entry" and indent the other sections under this.