Listing events chronologically based on one _or_ another field
I am renting out holiday apartments and using Filemaker to keep track of bookings.
I now want to generate the list of changes (moves in and out) for our cleaning lady to see when she is busy.
I have a Booking table with the fields Arrival Date, Departure Date and Arrival Time among others, and relations to 2 other tables.
The list I want looks something like
1 Feb: House1 Departure
3 Feb: House3 Departure
3 Feb: House3 Arrival, 3 persons
4 Feb: House1 Arrival, 2 persons
The list typically covers a month or so.
In other words, I want to make a list of records where //<Arrival Date<//+30 OR //<Departure Date<//+30, and I want to list them together, ascending. A record can occur twice!
Perhaps I need to do an event table, where the event is arrival or departure with date and time?
Any good ideas how I go about this?
A record can occur twice!
That's the key detail here. I think separate arrival and departure records like you describe for your "event" table would make sense here.