Roster?

[Jonah74]

Hi

 

I'm not sure exactly where this question should be asked on the forum as it's more of a "Can it be done?" sort of query and if so, how, so I apologise in advance if its in the wrong place. 

 

Let me explain. I have created a social car database for a charity that matches elderly and infirm people that need to go to various appointments such as doctors, dentists, hospitals etc. with volunteer drivers. The drivers themselves use their own cars and give up their time freely to the help the charity on various days and times each week. This is all co-ordinated using an FM Pro 12 DB where clients are held in one table, destinations in another, drivers in another and the actual journey held in yet another. When a  person contacts the charity needing the transport to their appointment an office co-ordinator selects the person, their desired destination, date and time and selects a driver which is all extracted from the various tables via a journey layout, producing a printout for the driver in advance of the appointment. 

 

What I would like to do is, when the user, (who is coordinating the journey), selects the driver, the database picks the driver automatically from only those drivers who are available on that chosen day and who do not already have a job booked for that time on that day. (However, it should also be able to have some sort of a manual override in the event the driver can't take that particular person for any reason. For example, some of the drivers cars aren't capable of fitting folding wheelchairs in the boot of the car so an alternative driver with a bigger car needs to be allocated.) It would also be good if it could make that driver unavailable for the length of the appointment including the journey time to and from the appointment using and linking the date and time fields that are currently on the journey table  / layout somehow. 

 

The driver schedule is currently managed manually using an excel spreadsheet and is very labour intensive as it caters for over thirty drivers. (I've included a screenshot of how this currently looks) but if FM can help me resolve this the graphical schedule could then be scrapped and save admin staff many hours in managing it.

 

I am guessing this is going to be very similar to a roster database but I'm really not sure how to do this or where to start and whether I could amalgamate it into my existing database. I appreciate I may be biting of more than I can chew and it may need to be done by a professional database programmer but any help, advice, pointers or comments on how to to do this would be greatly appreciated.

[KaosMaker]

Calendaring is always tricky, especially when you're not talking straight up hourly units, but variable "length of appointment" blocks. There a few complex ways to do this, but since you only have 30 drivers, the simplest way might just be the following:

 

1) In the Drivers table, make two global fields: gStart_Time and gEnd_Time

2) Make one relationship (Rel1)between Drivers and Journeys, where Drivers::gStart_Time >= Journeys::Start_Time, and Drivers::gStart_Time <= Journeys::End_Time

3) Make a second relationship (Rel2) between Drivers and Journeys, where Drivers::gEnd_Time >= Journeys::Start_Time, and Drivers::gEnd_Time <= Journeys::End_Time

4) In the Drivers table, make a new unstored calculation (cBusy) that basically says: "Count(Rel1) + Count(Rel2)"

5) Make a script that searches for "cBusy < 1", after gStart_Time and gEnd_Time are set.

 

Basically what this will do is, when you enter a prospective start and end time into the aforementioned globals, and run the script, it should return all the Driver records that are available for selection, and let the user choose one. Warning, searching unstored calculations that are in turn calculated off of related inequalities is not the fastest script in the world, but for a mere 30 drivers it should be ok, even if the number of your Journeys is fairly large.

 

This is off the top of my head, so it is possible I made a mistake in my instructions somewhere, or underestimated how long it would take to run, but it is pretty easy to implement, so consider giving it a try, and let me know if you have any questions or problems!

[Jonah74]

Hi

 

Thanks for the prompt reply and great suggestion. I'll try this when I get back from work and let you know how I got on.

[Jonah74]

Hi KaosMaker

 

I started to setup the table and relationships as suggested but then realised there is no where that this will match up the day / date with the time, (or nowhere I could see) so wasn't sure how to proceed. I did try setting up the date field in the way you described but wasn't sure how this would correlate to the time. I've attached a date / time field picture from the database to show you how this is laid out in the db if this is of use to you, as these are the two fields I'm trying to use to utilise the drivers time.

[KaosMaker]

Oh! Sorry, of course that makes sense. What you can do is simply create a Timestamp field that calculates off of Date and Time (using GetAsTimestamp, I believe). Then use THAT, instead of time. For example, gStart_TimeStamp, gEnd_TimeStamp, Journey::Start_TimeStamp, Journey::End_TimeStamp.

[Jonah74]

Hi

 

The Start Date & Time field is a calendar drop down box so the co-ordinator can pick the date and the Time field is another field which is a drop down list drawn from list values so they can select the appropriate time. So how do I replace these fields with the just a timestamp field so the co-ordinater and still select the date and time? Apologies in advance if this seems a daft question but I knew this was going to be hard to try and achieve. 

[KaosMaker]

Not a problem! The trick is to make the timestamp field a hidden, calculated field. The user still picks a date, and sets a time, and behind the scenes, they are concatenated into a Timestamp.

 

[GetAsTimestamp(Date & " " & Time)] should do it, I believe. The relationship is based off the timestamp fields, even though nobody ever actually has to see it.

[Jonah74]

Thanks for your answer although I'm not sure I fully understand everything. I created a field in the drivers table called DateTime and selected Calculation from the "Type" drop down box and then in "Options" typed in GetAsTimestamp (Journey::Start Date & " " & Journey::Start Time), (Journey being the layout and Start Date and Start Time being the fields I need to use). Then I selected 'TimeStamp' on the calculated result. (As picture 1 & 2). However, on setting the field up on the layout, (which I will hide when I know everything is working), it seems to have a static set date and time (see attached picture 3). I'm guessing it needs something else to happen and I'm assuming I have to set this up using your previous instructions (as below) somehow, although I'm not sure where this calculation fits in and if I still need to setup the global Start Date, Start Time and End Date and End Time. :-

 

1) In the Drivers table, make two global fields: gStart_Time and gEnd_Time

2) Make one relationship (Rel1)between Drivers and Journeys, where Drivers::gStart_Time >= Journeys::Start_Time, and Drivers::gStart_Time <= Journeys::End_Time

3) Make a second relationship (Rel2) between Drivers and Journeys, where Drivers::gEnd_Time >= Journeys::Start_Time, and Drivers::gEnd_Time <= Journeys::End_Time

4) In the Drivers table, make a new unstored calculation (cBusy) that basically says: "Count(Rel1) + Count(Rel2)"

5) Make a script that searches for "cBusy < 1", after gStart_Time and gEnd_Time are set.

 

Perhaps you can point me in the right direction and hopefully I'll manage to get this to work and in the meantime I appreciate all your help, time and advice.

[Jonah74]

Hi 

 

Have you any more thoughts on this or can anyone else help?

[beverly]

Is the journey the actual date and time the driver will not be available for selection? Do you need to put in a buffer time so that the driver is not available before or after the appointment in case of delay? Do you use a start time and end time?