Portal Help

[Jonah74]

We currently have a FM12 Database that’s used as an appointments system for a local community transport charity and was designed to allocate and book volunteer drivers time and vehicle from a pool of volunteer drivers in order to take residents of the community to medical and private appointments. We also run Minibus Hire, Club Bus And a Shopping Bus Service, which are also entered onto the system in order to record the bookings.

 

In order to enhance the current Shopping Bus Service the system has to be able to allow multiple clients, (passengers) to be booked on a specific Bus Route and Service. These minibuses run on set routes everyday collecting the clients and taking them to either their single destinations, like supermarkets, or multiple destinations similar to a hail and ride service visiting various places. A printout then has to be generated displaying the passengers taken, their fares cost, their destinations and return trips so that drivers have the correct information when carrying out these journeys. Office staff must also have the correct information in order to maintain the service and determine if the correct revenue is received at both passenger level and service level.

 

What I’d like to be able do, is add clients from the existing client table along with their destinations from the existing destinations table and add them to another layout  / table using a portal if possible. (Please see the attached diagram  / screenshots that probably illustrate it better than I can describe it.) Once each route has its quota of passengers (which is usually the same clients most days and most weeks) the passenger list needs to be printed to give to the bus driver so he knows who he is collecting and where they are bound.

 

I’ve got an idea of how I’d like it to look / work (see screenshots) and with a bit of research on portals and TO’s I’ve made some assumptions. As both the client and destination tables are all ready linked to the main journey table I’m guessing I have to create two TO’s one for client and one for destinations and add these to a join table. However there are some things I’m not sure of and need some help with. For instance, should the portal be based on the client table or something else? How do I populate the portal from a search field using a different layout? Can I accommodate the clients and their addresses, plus the destination and addresses, on the Portal? Is it possible?

 

Sorry about the length of this post but there was no other way of explaining what I’m trying to achieve but any help or advice will be very much appreciated.

Diagram.pdf

Screenshots.pdf

[Matthew F]

It sounds like you're off to a good start.   I have some conceptual issues with your relationships though. Not only is your join table linked to Clients and Destinations, its also linked to Drivers.  So this begs the question: Will there be a single Journey record each time a Client is taken to their destination, or does a Journey refer to a single outing for the driver, in which he/she may pick up multiple clients?

 

If there is one "Journey" for each Client-Trip then you might think of it as a "fare".  If there is one "Journey" for each Driver's outing then you might think of it as a "route".  In fact, you could use two join tables: Fares and routes (see attached screen shot).  Connect Clients and Destinations to "fares" (as you have) and connect Fares--Routes--Drivers.   The reason is that a driver will do multiple routes (each consecutive outing) and each route will have multiple fares (clients going to specific destinations).  

 

Regarding your popup fields for Clients and Destinations, you could simplify the process of creating new records by placing them on the "Journey" (Fare) portal.  You would create a new portal row by selecting the client ID.  Format this as a Popup field linked to a value list of Client IDs.  (Set the value list to show Client names rather than IDs to make it more user friendly).  Then specify the destination ID with another field formatted as a popup on the same portal row. The Journeys portal could be displayed from either the Client layout (to see all the Clients scheduled trips) or from the Route layout (to see all the drivers fares for the day).  Use the latter to print the jobs for each driver.   Its not a big problem to put a pop field at the top of your layout, like you show, but it would require some scripting to create the new related record.

Edited July 26, 2014 by Matthew F

[Jonah74]

I think I may not have given you enough information regarding the relationships and you may need to see the whole relationship table which might show why the clients, destinations and drivers are joined. (see screenshot). At the moment the way it works is one client will call the office and want to book a driver for a journey. A new record is created which becomes the ‘journey’ and the serial number becomes the journey reference ID. The client field is populated from the client table. Likewise, the client’s destination. Finally, when the user selects the journey date and time this triggers a script. This is the mechanism behind it all allowing the user to select drivers that are available for that particular day and time. (see screenshot 1).

 

So going back to your question: Will there be a single Journey record each time a Client is taken to their destination, or does a Journey refer to a single outing for the driver, in which he/she may pick up multiple clients? I think you have to say the way it is set up at the moment each journey refers to a single outing for the driver, in which he/she may pick up multiple clients. One of the main reasons behind this is because drivers have to be booked anywhere up to four weeks in advance so the charity knows it has drivers and can carry out the service for the given weeks, hence why minibus, shopping bus trips and outings are booked the same way as single client car journeys even though they have multiple clients.  This is the bit I’m trying to develop. I want to use the original journey reference that was created for the trip but maybe switch to another layout to add multiple clients and multiple destinations. (see screenshot 2). If this is the case would I still use, as you have suggested, two tables, fares and journeys and how would that now tie in with the already existing journey? I‘m also guessing that I would need to create a new layout to accommodate the fares and journeys data and is this what the portal would be based on, one of these tables?

[Matthew F]

Yes, that's pretty much what I expected.  Take a look at the scheme that I suggested. 

 

In my tip, above, the "Fare" table is the same as your "Journey".  However, I still think that you need an intervening "Routes" (or Outing) table that links to Journeys.  If you think in terms of records for each outing this makes sense.  The route can pick up multiple clients.  But each client will also engage multiple Routes (on different days).  So there is a many-to-many relationship between Routes and Clients that is joined by Journeys.    Since a Route can have only one driver, then there is a simple one-to-many relationship here (a driver does multiple routes).  

 

Does that make sense? 

[Jonah74]

Yes this makes sense. I thought I would have to create another table layout, I just wasn't sure where this would fit. I'm going to add this to the journey table as per your tip and hopefully that will take care of table layout. One thought though. As the driver table is already being used on the car side is it OK to use this for the bus trips or would you suggest a TO.? My other question is once I have created the join table as per your suggestion how do I then 'pick' the clients to go on it? Do I use the same method as on the car journey and if so, how would I get a 'listing'? I thought that is where I would have to use a portal. BTW, just wanted to say thanks for your help so far, really appreciated.

[Matthew F]

 

As the driver table is already being used on the car side is it OK to use this for the bus trips or would you suggest a TO.?

I don't understand that question.  What is the car side? A T.O. for what?

 

 

once I have created the join table as per your suggestion how do I then 'pick' the clients to go on it?

There should already be a record for each Client.    Put a portal to the Journey T.O. on your Route layout.  Place the field Journey::fk_ClientID on this portal.  Format the field as a popup that displays a value list based on Client::pk_ClientID. Set this value list to display values from a second field  Client::Name.  In this way the user will pick from a list of names, but will actually be selecting the proper Client ID.  When you create a new portal row in this way it makes a new related Journey record. 

 

To print out the Jouneys for each Route, then you can display them as a portal on a Route layout.  Alternatively you can go the the Journey's table, Find the current Route ID, and then sort and print from there.  

[Jonah74]

Sorry the 'car side' refers to the original use of this database i.e. Social car use and volunteer drivers for the charity as first explained. (see screenshot). That's why I wondered if I could still use the same table or whether I would need to create another TO. Are you suggesting creating a TO for the original table journey and then adding the join tables as per your example to  that? As for pop ups, this may be impractical as there are something like 1800 clients which is why I suggested a type of pick from a search or find field, but I'm open to suggestions for this.

[Matthew F]

 

 

As for pop ups, this may be impractical as there are something like 1800 clients

I agree, a popup showing all destinations would be impractical.  However, you could possibly filter the number down to a reasonable number, using the "show only related records" option on the value list.  I'm thinking that you could have a table of favorite destinations for each client that would used to populate a drop down field.  

 

As far as the "car-side" question goes, I still don't have a good grasp on what that's being used for or how that is substantially different from your minibus service.  You will need to design the database to meet your current needs, and decide yourself whether its best to intermingle records designed for another purpose, or to create a separate table.  While it may be less work to design and maintain fewer tables, you don't want to clutter up things, or confuse or reduce the efficiency of your users.  If you don't see it as a problem to intermingle records, you can add a tag field to designate the kind of trip (e.g. van vs. car formatted as radio buttons) to help filter records, choose the right layout, etc.