flter date range in portal

[mount]

We have several houses, apartments, etc for hire.

I'm preparing a dB to control the reservations. I have two files: reservations has the records of every reservation (datein, dateout, house, etc). The second file is the parent one, in which I have a portal. the relation is one to many by the field house: then I can see the reservations for a single house.

I would like to filter this portal in order to see only the reservations for a period of time (actual month, three months, or from one date to another).

I've been thinking in a calculated field like:

housename & datein & PM & housename & month(datein)

But whan if the date range doesn't match with datein?

Perhaps I should limit the range to months...

I'll thank any idea

FileMaker Version: 3

Platform: Windows 95/98

[Ender]

Filtering a portal by a date range in FM6 or lower is pretty tricky to set up. An easy alternative is to filter by Month and Year. You need to make a new key in both files:

parent file:

gMonth(Global text)

gYear(Global text)

key(calc) = HouseID & " " & gMonth & " " & gYear

reservation file:

key(calc) = HouseID & " " & MonthName(Date) & " " & Year(Date)

Change the relationship and put the globals on the layout with some value lists attached, and your off. (Are there Globals in FM3? Is there a MonthName() function in FM3? Can't remember.)

[mount]

Thanks Ender

That's a good way.

I've found a sample in another topic by NightWing that filters by date throw a Script method. It can help me.

There are Globals and there is MonthName() function in FM3. Hope the boss gets a newer version.

[mount]

Hi again

I've been thinking about this issue and realized that its easier to use your method, Ender. And I don't need such a precision in the date ranges: months and quarters will be ok.

But this calculated key doesn't work when a reservation begins in the previous month and ends in the current month: it doesn't appare in the portal. If I introduce the MonthName(Dateout) in the key, then the portal shows only the reservations that begin and end in the selected month.

But that's not what I'm looking for: I want to see the days in a month that one house is ocupied.

What would be the calculation key?

Thanks

[Ender]

So, is this what you're trying to show?

If gMonth is "March", show all portal rows whose datein is in or before March and whose dateout is in or after March.

[mount]

No. Sorry: my english isn't very good.

If gMonth is "March", show all portal rows that have one or more days of March reserved. This can occur when datein and dateout is in March, but also when datein is April/28 and dateout is March/4 (there are 4 days of March reserved).

I want to see in what days of March the selected house is reserved.

So, I've tryed with:

key(calc) = HouseID & " " & MonthName(Datein) & " " & Year(Datein) & PM &

HouseID & " " & MonthName(Dateout) & " " & Year(Dateout)

but then the portal shows also a reservation that begins and finishes in March. So it doesn't run.

[mount]

Sorry guys. It works. I did a mistake when I wrote the key in FM. It's better to read two times what you write, first to press OK.

Thanks for your time.