Setting Date Range filter

kolaboy

Hi Experts,
I have created a table called Staff attendance table with fields: Emp. No., Name, from date, to date, time in, time out, total time and date.

I also created form called attendance registration where employee attendance are keyed in.

I designed report called attendance registration that displaces Emp. No, Name, time in, time out, total time and date.

The report can filter by all the fields in the staff attendance table, except the from date and to date.

I want to choose a date range say from date=01/01/02 and to date=04/01/02 and if i run the report with this filter( from date and To date) the report does not show anything.

If i choose only from date or To Date, it comes fine.

the problem is only when i choose both to filter( i.e From date and TO date) it does not work.

The idea is i want to get report weekly, Monthly, or yearly attendance.
Such that when i choose a date in the beginning of the week as from date and choose a date for the end of the week as to date( i.e Monday to Friday), it should give me all the employees whose were present on work during that week.

So please can anyone help me to solve this problem.
Thanks.

DenSter

I think you're a bit mixed up in your thinking...

You say you have two fields, a "From Date" and a "To Date". If you set a filter where the "From Date" is 01/01/02 and where the "To Date" is set to 04/01/02, then you are not setting a date range filter, but exact values. All you will find is records where the From Date is exactly 01/01/02, and where the to date is 04/01/02, but both at the same time. So you will miss records where the from date is 01/02/02, or where the to date is 03/15/02 for instance.

If you want to set a date range filter, you have to set the filter on the same field. Or, what you could also do is set the following filters:
SETFILTER("From Date",'>=%1','01/01/02')
SETFILTER("To Date",'<=%1','04/01/02')
See if that gives you what you need.

kolaboy

Hi Denser,
I want the date filter to be set generally. I want it to be able to filter any date range i choose, not the specific date you have set.

Can i see a code that can set it for any date range i choose.
Where will the code be put?

MTC

SETFILTER("From Date",'>=%1',sdate);
SETFILTER("To Date",'<=%1',edate);

DenSter

It appears to me that you have difficulty understanding some basic filtering and programming skills. I would like to suggest that you purchase a few books and study general database design as well as general programming structures.

themave

DenSter wrote:

It appears to me that you have difficulty understanding some basic filtering and programming skills. I would like to suggest that you purchase a few books and study general database design as well as general programming structures.

Have to agree with this completly, I am not a programer and the mistake jumps out to me.

your staff attendance table should have an entry for each date. not a range of dates, you only need one date field. Then when you run your report, your filter will pull each days entry, based on the date range you enter.

kolaboy

Hi MTC,
I have use your code, but its not fltereing the range, instead everything comes.
I put it on preReport() tigger and OnAfterGetRecord() on the report, but it is working. I already have this code their on the OnPreReport() on the report:

EmployeeFilter := Employee.GETFILTERS;

This is allowing me to filter by the fields i have on the staff attendance table.

ara3n, i am a newbies in Navision that is why i am depending on you guys to help me, and in the process i will be learning, Please understand me. i need your helps.
Thanks

kolaboy

Hi themave,

The date range is the one that is not working. The from date and the To date is not working. It is not taking the range. I want to be able to filter by date range, so that the employee that are presently let say from 01/01/02 to 04/01/02 will appear only on the report. but it is not bring any employee when a date range is chosen the range.
Any ideas please.
Thanks

themave

kolaboy wrote:

Hi themave,

The date range is the one that is not working. The from date and the To date is not working. It is not taking the range. I want to be able to filter by date range, so that the employee that are presently let say from 01/01/02 to 04/01/02 will appear only on the report. but it is not bring any employee when a date range is chosen the range.
Any ideas please.
Thanks

yes like I said, your table is set up wrong for what you want to accomplish. You need an entry for each date, so far example

your table for march 1, 2007, with three employees would look something like this

Date EmpNo TimeIn TimeOut TotalTime
03/01/2007 001 8:00 am 4:00 PM 8
03/01/2007 002 9:00 am 4:00 pm 7
03/01/2007 003 7:00 am 5:00 pm 10

so if you run a report filtered on March 1, 2007 for all employees, it would result in 25 hours, if you filtered on employee 002, then it would report 7

now if your table had entries for more days, then it would do what you want.

DenSter

If you want to set a date range filter, you have to set it on the same field. Just because you call one field 'from date' and another field 'to date' does not cause the computer to understand what you want with them. The computer doesn't understand human language, at least NAV doesn't

I really think you need to put some real time into some general studies about database design and programming, so you understand how computers 'think'.

Try to find a book or a website about basic database design, and make sure there is a chapter or a section about normalisation, you can read about how to turn 'real world information' into 'computer data', which is the basis of table design. Once you have the skill to analyze how to create fields for the information you need to store, you will start to understand why you would use certain fields for certain purposes. With that understanding you will also come to understand how to set the right filters.

kolaboy

Hi Denster,
Thank you very much for you advice. I have been trying to look for materials that i can lay hands on to study, but i am not lucky to get one. Al the same i am still searching.

I would be grateful if you could help me with good materials. You can send them to me as attachment or send me the links. I really want to study now.

But in the interim or in the meantime, i am going to depend on you for some assistants.
Thanks.