2 Replies Latest reply on Jun 19, 2013 12:02 AM by ChrisJones_1

    Roster?

    ChrisJones_1

      Title

      Roster?

      Post

            

           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.

            

      Screen_Shot_2013-05-23_at_21.43.15.png

        • 1. Re: Roster?
          philmodjunk

               Can this be done with FileMaker? Yes, it can be done and I can even conceive of at different basic approaches that could be used.

               Can YOU do it with FileMaker? That is a question I can't answer as this is a pretty challenging issue to work out.

               I can leave you with two general outlines, but I'm really not available to take you by the hand and lead you step by step to a final solution:

               1) you may be able to set up conditional value lists where the conditions you describe (Capabilities of the car, driver availability, ... ) automatically filter the list of available drivers.

               2) you could also set up a scripted find where you specify criteria, (time, location, special needs (wheelchair), etc) in some global fields and then a script performs a find to pull up a list of avialable drivers. Clicking a driver in this list would then select it for the current passenger.

               3) Similar to 2, a filtered search portal might be used to list the available drivers and clicking a row in the portal selects that driver.

          • 2. Re: Roster?
            ChrisJones_1

                 Hi

                 Thanks for prompt reply and the useful suggestions. I like the idea of number three and I think I could develop something long these lines which I think may work.