First issue: Since the exact dates that correspond to easter holidays will change with each year and the apartment management might choose to change what dates mark the beginning and end of summer holidays, how will you record these date ranges so that they can be maintained as easily edited data in your system? Once you have that set up, you can implement a change in your billing process to break up a given reservation entry into multiple line items that total the complete reservation, but use different rates based on which rate category a given reservation date falls.
I'd use a "rate table" for you different portions of the calendar:
RateID ( Serial Number Field )
StartDate, EndDate (date fields)
Monthly, Weekly (Number )
UnitID (ID number of apartment unit or group if you have differing rates for differing units)
This can be set up for uniform rates or rates that differ for each apartment unit ( or group of units) as needed by your business model. You can put records for each holiday period where the rates are to be modified and leave non-holiday a default value, or you can include the non-holiday intervals a just another rate period in this table--I prefer the latter here.
You'll then need to check for any reservation start and end dates that cross from one such interval into another. I'd use the following relationships to such a rate table to check for this:
Reservations::StartDate > RatePeriod 1::StartDate AND
Reservations::StartDate < RatePeriod 1::EndDate
Reservations::EndDate > RatePeriod 2::StartDate AND
Reservations::EndDate < RatePeriod 2::EndDate
RatePeriod 1 and RatePeriod 2 would be two table occurrences of the same RatePeriod data source table.
If RatePeriod 1::RateID ≠ RatePeriod 2::RateID then you know that the reservation spans two or more rate periods and you can use a loop to divide the reservation date interval into two or more intervals (A 30 day rental could start before easter and end after easter resulting in 3 different intervals)--each with a different rate looked up from this rate table.
Note: if you have different rates for different units, then the above two relationships would use one more pair of fields to match rates by UnitID in addition to the date range.
Thank you for the detailed answer Phil..
i also thought that i need resolve it over a table relation. One thing is not so clear for me, (i work only from time to time with filemaker) that is this thing with the loop and how to get the different prices from the rate table records together. Each apartment has its own monthly and weekly price, so also the high season. The easter holidays i will manage manually. Setup the dates for the next 5 years for example with the option to continue.
Thanks y saludos, Michael
As I said in my last post, you can support different rates for different apartments by adding a pair of fields to your rates table.
ReservationLineItems::StartDate > RatePeriod 1::StartDate AND
ReservationLineItems::StartDate < RatePeriod 1::EndDate AND
ReservationLineItems::UnitID = RatePeriod 1::UnitID
ReservationLineItems::EndDate > RatePeriod 2::StartDate AND
ReservationLineItems::EndDate < RatePeriod 2::EndDate AND
ReservationLineItems::UnitID = RatePeriod 2::UnitID
The exact details of your loop will vary with your database structure. Are you set up to bill with an Invoice-LineItem type structure where a single reservation record can link to multiple date intervals--each with a different rate?
This script assumes this relationship:
Reservations::ResID = ReservationLineItems::ResID
If ( not IsEmpty ( ReservationLineItems::ResID ) /* Confirm there is a reservation line item record */ ]
Go To Related Records [ Show only related records ; From table: "ReservationLineItems" ; Using Layout: "ReservationLineItems" ( ReservationLineItems)]
Exit Loop If [ RatePeriod 1::RateID = RatePeriod 2::RateID /* No more splitting needed */]
Set Variable [$EndDate ; Value: ReservationLineItems::EndDate ]
Set Field [ReservationLineItems::EndDate ; RatePeriod 1::EndDate ]
Set Variable [$StartDate ; Value: RatePeriod 1::EndDate + 1 ]
Set Variable [$UnitID ; Value: ReservationLineItems::UnitID ]
Set Variable [$ResID ; Value: ReservationLineItems::ResID ]
Set Field [ReservationLineItems::ResID ; $ResID ]
Set Field [ReservationLineItems::UnitID ; $UnitID ]
Set Field [ReservationLineItems::StartDate ; $StartDate ]
Set Field [ReservationLineItems::EndDate ; $EndDate ]
Go To Layout [Original Layout]
I am in the same genre with similar situation.
I have got the two portals with the related priceseasons to work. Is there a way to create a calculated field that somehow uses a Let function or similar that counts weekdays and weekenddays within the rateperiod? I have weekprices, weekendprices, and day prices.