Report filter with three fields.

zulq

Im trying to finalize a report which shows employees who've not contributed their monthly contributions.
The employee table contains all employees and the contributions table contain all employees who've contributed at least once.
I want to be able to list all employees who've not contributed for a specific range. The contributions table contains from date and to date fields. Now I want to use the virtual date table with period start and period end to list all employees who are not in the contributions table for the specified date range by comparing form date,to date and period start, period end respectively.
I've tried the method below and similar filtering:

Cont.SETRANGE("Employee No.",Employee."No.");
Cont.SETFILTER(Cont."From Date", '>=%1', DtVir."Period Start");
Cont.SETFILTER(Cont."To Date",'<=%1', DtVir."Period End");

IF Cont.FIND('-')
THEN
CurrReport.SKIP;

to no avail.
In sql programming this what I want

Select * from employee where employee.No. != [Subquery select Contributions.'Employee No.' where Date.'Period Start' >=Contributions.'From date' and Date.'Period End' <= Contributions.'To Date']

Any ideas please!

lubost

.. and what is the problem?

Mbad

What is the point in using the virtual table when all you do is setting filters?(you did set filter on the virtual table, right?)
And i pressume this is all done on employee dataitem onaftegerrecord?

zulq

I did set the filters but they ain't working. If they were I wouldn't post it and also I've to use the virtual table where the user will choose the dates from as they are not static. I knw its also possible to use the contributions table.
Once again the filters I apply aren't working! ](*,)

lubost

You didn't write anything about it how you are finding and getting appropriate record from DtVir table. As Mbad wrote, you have to get the record from this table, because "Period Start" and "Period End" fields are empty.

zulq

I don't any one of you understands the way filters are working properly. What the filter should is to check if an employee exists in the contributions table within the specified date range then skip it...
Here when I only filter by employee no all employees in the contributions will be skip...An example would be if an employee paid for january and february then if the date range is january 1st to 31st the employee should appear once i.e for february.
The filters here I think is working by or while it should work with and. A record should only be skipped if all conditions are true...
Better ideas please!
[/i]

lubost

1. Check if some of fields Cont."From Date" or Cont."To Date" is not empty
2. Try use
Cont.SETRANGE("From Date", DtVir."Period Start", DtVir."Period End");
Cont.SETRANGE("To Date", DtVir."Period Start", DtVir."Period End");

kine

1) Filters are working, if not, the whole Nav will be unusable... :-)
2) What is it "DtVir"? You didn't wrote how the variable is filled and meaning of this variable...

zulq

I am using two data items and they are: Date virtual table and employee table. There's also Cont which is a global variableName=Cont,Data Type=record and SubType=Contributions.
And below are the filters am trying to use, I updated the code:

Cont.SETRANGE("From Date", Date."Period Start");
Cont.SETRANGE("To Date", Date."Period End");
Cont.SETRANGE("Employee No.",Employee."No.");
IF Cont.FIND('-')
THEN
CurrReport.SKIP;

The user has to enter Period Start and Period End when previewing the report.

kine

Yes, but now it means that just records with exactly Start Date = entered start date and End Date = entered end date will be filtered. Nothing else. Not something between...

zulq

Now you'll understand why I posted this problem. Your response is as exactly I expected the filter to work but that's not happening. I got 4 records with exactly From Date=19/03/07 and End Date=20/03/07 but they are still shown on the report.
I've also used the following to pick dates between to no avail:


Cont.SETRANGE("Employee No.",Employee."No.");
Cont.SETFILTER("From Date",'>=%1', Date."Period Start");
Cont.SETFILTER("To Date",'<=%1',Date."Period End");


IF Cont.FIND('-')
THEN
CurrReport.SKIP;

May be am missing something...
Ideas welcomed please!

kine

1) If I understand correctly, you want to show (not skip) just employees which have record in Cont which is fully in the period (started and ended in the period)? Or you want any record starting or ending (or both) in the period? I assumed first possibility...

2) Do not forget to use .RESET before setting first filter to be sure that there is no other filters set in another part of code

3) In which trigger you have this code? In which DataItem?

zulq

1) What I actually want is to skip any employees that exist in the Cont between the period started and ended.
2) There's no other code in the report. Only two dataitems are in the report and they are employee and date as mentioned in earlier post.
3) The code is in the Employee dataitem and in the trigger OnAfterGetRecord.

kine

Ok, it seems that problem is not there. Shall you describe what is wrong now and post the data related to this "wrongly reported" employee? I mean something like:

Employee xxx is on the report in period XXX..YYY but in table Cont is record Employee No.=xxx, start date=XXX, end date=YYY...

zulq

Employee No. Full Name From Date To Date Amount Date Received Contribution Type Receipt No. Comment
ZS0006 Some Name 19/03/2007 20/03/2007 1,500 20/03/2007 Sample RC004 No
NA2001 Another name 19/03/2007 20/03/2007 1,500 20/03/2007 Sample2 RC004 No
AM0013 Similar One 19/03/2007 20/03/2007 1,500 20/03/2007 Sample3 RC004 No
DC0084 Example 19/03/2007 20/03/2007 1,500 20/03/2007 Sample4 RC004 No
ZS0006 Some Name 21/03/2007 21/03/2007 5,200 21/03/2007 Another RC0008 No

The above list is taken from the contributions table and you can clearly see the From Date and To Date to match Period Start and Period End from the date table respectively. And now when I enter Period Start=19/03/07 and Period End=20/03/07 four of the above records are shown which should not happen as they are within the filter.

kine

Yes, but... which records from table data will be in your filter

Period Start=19/03/07 and Period End=20/03/07

There is no such a record in Date table. May be that this is your problem... take these dates as two variables on request form and it will be much better...

zulq

And how do I do that?
Also I was thinking by the user choosing period start and period end dates before previewing the report the filter would work...
Any way how do I implement the request form?

kine

Go into the report design, add two global variables StartDate and EndDate of type Date, click View - Request form, add two edit boxes wit labels on the form, set SourceExpr of these edit boxes to StartDate and EndDate. Use these variables in the code for filtering and you are done (you can add some testing e.g. that Start date is <= End date, that both dates are entered etc. - add this into OnPreReport trigger)...

zulq

Thanks kine...Please be patient with me...I just started Navision and not taken any course on it...
Can you please specifiy how to implement these functions on the OnPreReport trigger.
Also I've added the request form and the date ranges are working now...Except that it filters the employee out completely even if he/she exists in another date different from the one on the request form.
Below is my updated filter:

Cont.SETFILTER("From Date",'>=%1',DateFrom);
Cont.SETFILTER("To Date",'<=%1',DateTo);
Cont.SETFILTER("Employee No.",Employee."No.");


IF Cont.FIND('-')
THEN
CurrReport.SKIP;

kine

1) The code in PreReport will look like:

  if (DateFrom = 0D) or (DateTo = 0D) then
    Error(Text000);
  if (DateFrom > DateTo) then
    Error(Text001);

where Text000 is text constant with text like "You need to enter both date" and Text001 is "Starting date must be before End Date".

2) I recommend to use

Cont.SETRANGE("Employee No.",Employee."No."); 

instead SETFILTER when you are filtering for one value.

3) And again, have you example of such an employee with the data which are there?

zulq

Yes there's such and employee check the previous post "ZS0006".
Everything except that now is the problem. I implemented setrange but it is still skipping the employee outside the date range specified on the request form.
I also try the code below but still it didn't work on the OnAfterGetRecord as well on OnPreReport:

IF (Cont."From Date" >= DateFrom) AND (Cont."To Date" <= DateTo)
THEN
Cont.SETRANGE("Employee No.",Employee."No.");

IF Cont.FIND('-')
THEN
CurrReport.SKIP;
](*,)

zulq

Thanks all of you guys who help in solving this problem...It's finally solved...The report is working perfectly and I can list all employees who've not paid for a specific date range specified.

Once again thanks a lot!

kine

I am glad that you were sucessfull.

And what was the problem?

zulq

Funny enough the complexity was confusing during the test but I think it was working since I added the code below:


Cont.SETRANGE("Employee No.",Employee."No.");
Cont.SETFILTER("From Date",'>=%1',DateFrom);
Cont.SETFILTER("To Date",'<=%1',DateTo);

IF Cont.FIND('-')
THEN
CurrReport.SKIP;

Now the report only prints employees that are not found on the contributions within the specified date range....!
Thanks.....!

DenSter

quicker:
IF NOT Cont.ISEMPTY