Setting Appointments / Availability Calendars - Need Help

corradomatt

I'm having trouble conceptualizing how to work this problem and I could use some input from the community. I'm building an application that will be used to set appointments between customers and staff. I have the following BOs...

1. Customer
1. Staff
2. StaffAvailability (Appointment BO - apart of Appointments BO Group)
3. Appointment (Appointment BO - apart of Appointments BO Group)

What I want to do is create a Query that gives me a list of available staff based upon the following criteria....

1. Staff needs to be "Available" based upon the BO entries of the StaffAvailability BO
1. There cannot be another appointment booked at the same time for the same staff member
2. There needs to be enough time for the appointment to be completed within the staff members StaffAvailability entry (the appointment duration is a shortcut attribute of the appointment which is pulled from the customer attribute)

Determining where a resource is available or busy seems complicated in AwareIM. I have tried to set the query for just the availability aspect of the requirements with no luck. Everything I've tried results in no available staff members being displayed. I'm surprised that AwareIM doesn't have a built-in function for determining free vs. busy time on the appointment BOs.

Any ideas, suggestions or examples of something like this in the wild?

corradomatt

I'm also interested in adding the following functionality to the system after I get the above working...

1. Calculate driving time between appointments using the Google Maps addon that will determine if there is enough time to drive between the appointments and schedule them accordingly.
2.Create a customer portal that allows our customers to schedule/reschedule their appointments online with a few clicks. Obviously this will need to be done with some customer buttons and processes after I get the query working right.

Ultimately I would like to give the option of setting an appointment by clicking on one of the query results - I believe that this is possible with AwareIM but I need to get this query working first.

I plan to post a sample BS here once I get things up and running. Thanks for any and all advice![/list]

corradomatt

Ok, I discovered why my query didn't display any available staff options based on the StaffAvailability BO (which is an Appointment object).

The availability instances were setup as a recurring instance. When I looked at availability based upon the first instance of the recurring appointment (which is the only actual instance in the database) - everything worked perfectly.

The problem lies with pulling a query based upon recurring appointments. If you choose "Support recurrence" and do not choose "Create recurring instances" a query has no idea about the recurring events.

This will cause all kinds of issues for my application in AwareIM. Is this a bug? Is there a work around? Support, do you think that this can be fixed in a later release if this is a bug?

corradomatt

OK, so now I can't get the query to return any results again and I thought I had it solved last night. I may have been wrong about a possible awareim bug with my last post. Here is the query I have....

FIND StaffAvailability WHERE ((((StaffAvailability.StartTime<=?'End Time' AND StaffAvailability.EndTime>=?'End Time') OR StaffAvailability.StartTime<=?'Start Time') AND StaffAvailability.EndTime>=?'Start Time') OR StaffAvailability.StartTime>=?'Start Time') AND StaffAvailability.EndTime<=?'End Time'

What's not right about this? I need to show a list of staff (resources) that are available based upon the calendar entries I have for the staff start times.

chris29

I'd try

FIND StaffAvailability WHERE (StaffAvailability.StartTime<=?'End Time' AND StaffAvailability.EndTime>=?'End Time') OR (StaffAvailability.StartTime<=?'Start Time' AND StaffAvailability.EndTime>=?'Start Time') OR (StaffAvailability.StartTime>=?'Start Time' AND StaffAvailability.EndTime<=?'End Time')

corradomatt

You're awesome fuseon!! Thanks for that...it worked, kind of.

It finds the staff members now....but here's a problem....

Let's say I have 2 staff members. Staff A is available from 8am until 5pm. Staff B is available from 8am until 12 noon.

If I setup the query with a start time of 8am and an end time of 12 noon, both staff members should show up and do...so that part works.

But, if I set the start time at 9am and the end time at 2pm, both staff members still show up when it should only return Staff A.

Also, I was right about the bug. Anytime there is a recurring event (event when instances are created) it seems that my query does not pull up any results for appointment instances other than the first initial appointment in the series.