0

Script to confirm date-time availability

I'm an "intermediate" MacOS app user, with a database to track short-term house-rentals. I need a script to confirm availability or flag date-time conflicts as I enter new bookings.

On the table "Stays", bookings are defined with "Arrive" (date), "ArrvTime" and "Depart" (date), "DepTime". Ideally, a 'Trigger after update' script, or a button script would confirm "OK" if there is no overlap with prior records, or if date-time fields overlap, "Conflict".

This is just beyond my capability, and I thank you in advance for suggestions.

Bob

2 replies

null
    • Paul_Chappell
    • 2 yrs ago
    • Reported - view

    bflack  Assuming that your "Stays" table is linked to a table containing the property details - I'll call it "Property". 

    In your Stays table you should use a combined 'Date+Time' field for "Arrive" and another 'Date+Time' field for "Depart" rather than separate fields for date and time.

    On the "Stays" table create a Formula field called "Conflict"

    Add the formula:

    let tempProp := Property.Id;
    let tempFrom := Arrive;
    let tempTo := Depart;
    let conflict := count(select Stays where tempFrom <= Depart and tempTo >= Arrive and Property = tempProp);
    if conflict > 1 then "Conflict" else "No Conflict" end

    I would also add a GANTT view to your Stays table showing the Property, Arrive and Depart fields so you have a visual view of rental arrive/depart dates & times.

    • bflack
    • 2 yrs ago
    • Reported - view

    Works like a charm. Much easier than I imagined. Thanks!

    As suggested, I made datetime fields: ArrvDT & DepDT and simplified since there's only one property, no reason for a property ID.

    let tempFrom := ArrvDT;
    let tempTo := DepDT;
    let conflict := count(select Stays where tempFrom <= DepDT and tempTo >= ArrvDT);
    if conflict > 1 then "Conflict" else "No Conflict" end

Login to reply

Content aside

  • Status Answered
  • 2 yrs agoLast active
  • 2Replies
  • 95Views
  • 2 Following