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.