14 Replies Latest reply on Jul 30, 2014 2:01 AM by ChrisJones_1

    Table Help

    ChrisJones_1

      Title

      Table Help

      Post

           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.png

        • 1. Re: Table Help
          ChrisJones_1

               Sorry Forgot screenshots

          • 2. Re: Table Help
            philmodjunk

                 It would seem that what you are doing is selecting a Client and a destination and then creating a record in Journey to log that client being scheduled to travel to that destination. Correct?

            • 3. Re: Table Help
              ChrisJones_1

                   Yes. You select new journey which creates a new record. You select the client and the destination then with the script that you helped me create, you select the driver. The bit I'm now trying to develop is to set up a journey for a minibus / shopping trip that refers to a single outing for the driver, in which he / she may pick up multiple clients. I want to be able use the original journey reference that was created for the trip but maybe switch to another layout to add multiple clients and multiple destinations.

              • 4. Re: Table Help
                philmodjunk
                     

                          but maybe switch to another layout to add multiple clients and multiple destinations.

                     Which would require adding more records to the Journey table. Each time that you add a destination or a client, you add another record to this join table.

                • 5. Re: Table Help
                  FentonJones

                        The "Journey" is the basic "trip". I'll use my own words: person starting location (could be "person's home", in some cases) destination location start time end time driver

                       The "shopping trip", with multiple people, would also use the above. There would need to be a record for each unique person, home, start time*; even though they'd share the destination, end time and driver. *Of maybe you'd just give them all the same start time (as a "general start time")?

                       However, since the "journey" for them is likely created in advance (for the most part), then I think another table is required for these "shopping trips"; I'll call it "group pre-trip". It would have the IDs, and other data which is known.

                       The "shopping trips location" is just a special Destination. The "group pre-trips" is just a data which is created ahead of time. So, when the time comes to run the current "journey" for it, it can get the data needed, while creating a new "journey" record for each person needed, with data needed. [ If someone doesn't show, the record could be deleted, and/or marked ahead of time in the "group pre-trips" record as "skip" (though then turned back on, for next one, or not).] It seems a little much. But I think that is what you have to do, unless you force the data entry of every piece of data needed for each "journey" every time, at the time it's created. A "pre" table would help. The main difficulty with your solution is that you have to do two somewhat different things; enter values live for some entries, while other times bring data in for "group" entries. A different table for pre-entries of the "group" types, and a layout, would help. The above may or may not make sense. I find it hard to explain (nor much experience with exactly your solution) :-|

                  • 6. Re: Table Help
                    philmodjunk
                    Apologies for the complete absence of formatting in this post. RightNow has screwed up yet again. You may want to copy and paste this text into a word processor just so you can insert some of the stripped out returns in order to be able to read what I am posting. :( <> This does not change the fact that by the design of your tables and relationships, you need to create a new record in Journey for each unique combination of Client and destination. I think you need to add in a "trips" table linked like this: Driver----------Clients Trips::__pkTripID = Journey::_fkTripID This allows you to set up multiple trips for the driver with multiple records in Journey listing the stops and clients specified for that trip. You may also want to link a different occurrence of Clients to Trips or some such in order to see all the stops for a specific trip from the Client's perspective.
                    • 7. Re: Table Help
                      philmodjunk
                        Was stripped out of my previous post. :-((
                      • 8. Re: Table Help
                        philmodjunk
                         Sigh.... the software stripped it out again. "There would need to be a record for each unique person, home, start time*; " If you enclose text in greater than, less than brackets, the software interprets the text as an HTML tag and it won't appear....
                        • 9. Re: Table Help
                          FentonJones

                               I think the forum is working again; let's see. This phrase from Chris's post is what I was responding to:

                               "which is usually the same clients most days and most weeks"

                               Which seemed to me that a new table where you could add the Client and Destination would allow you to just choose the Destination, and a script could create the records needed by the Journey.

                          • 10. Re: Table Help
                            philmodjunk

                                 Sorry Fenton, I mistook your post for a reply by Chris Jones.

                                 Here's a reformatted post of what I think is needed here:

                                 I think you need to add in a "trips" table linked like this:

                                 Driver----------<Trips-----<Journey>-----Clients

                                 Clients Trips::__pkTripID = Journey::_fkTripID

                                 This allows you to set up multiple trips for the driver with multiple records in Journey listing the stops and clients specified for that trip. You may also want to link a different occurrence of Clients to Trips or some such in order to see all the stops for a specific trip from the Client's perspective.

                            • 11. Re: Table Help
                              ChrisJones_1

                                   Ok I think I can get the gist of what you were trying to explain after the hiccups with the forum. If I’ve read it correctly you’re say I do need to create another table / layout, (I’m guessing something similar to step 4 in the screenshot) but make it a join table to drivers table and the already existing journey table. That bit I understand but will it work the way the relationships are currently setup, (see screenshot) or would I need a TO for drivers? The other thing I’m not sure of in this scenario is how do I then get a the number of different clients onto the layout if I don’t use a portal? Are you suggesting I use the pick client method I currently use on the main journey screen?

                              • 12. Re: Table Help
                                philmodjunk

                                     The problem I see with your current set of relationships is that no part of the relationships distinguish between journey records for different trips.

                                     So if a driver takes Client A to shopping center 1 and Client B to shopping center 2 on trip 1, but takes clients C & D to shopping center 3 on trip 2, when you look at the related records from Driver, you see all 4 journey records listed with nothing to tell you which journey records were for Trip 1 and which for trip 2. There is also, as Fenton pointed out, no place to record when the trip took place--which could be documented with fields added to the Trips table.

                                     Hmm.... to avoid confusion, I might rename the Journey table to be "client_Destination" or "Client_Stop" as each record in that table links a client to a specific destination.

                                • 13. Re: Table Help
                                  FentonJones

                                        

                                       "There is also, as Fenton pointed out, no place to record when the trip took place--which could be documented with fields added to the Trips table."
                                        
                                       I was actually thinking more about the easiest way to enter "pre" data. It has to do with the "group" of people who would sign up ahead of time, and who would often be part of a group using one of those "minibus / shopping trip" and who matched the "same clients most days and most weeks". It just seems that it would save time (of the user) to have a table where these can be stored; then, when you need them, you can click a button and a script could create a "journey" for each one of them. Any data which would the same for each person should first be choose, a global to enter, or you'd know from the layout you're on (like "Destination" or "Driver").
                                        
                                       It is also possible that the above could be more or less done without another table, by using the Journey table itself. Imagine this; from the Destination table layout (or from Journey itself) you just need to use a Find matching the LAST records the "trip" matched. Then you could Import the data for the "next" trip to gone, and use a script to handle replacing the the date/time. I don't know whether this could be made as user-friendly as another table where you could enter the "usual suspects".
                                        
                                       There is more to this than my brain can handle "up in the air". 
                                        
                                       P.S. There is a small button on each table occurrence (TO) of the Relationship Graph, at the top right of each; looks kind of like [-]. If you click it, the extra space as the bottom of each TO will go away; but leave the fields used for the links between the TOs. This makes it much easier to see and organize the graph (you can still see what you need to see). You can also hide the fields also, but what until you know EVERYTHING about them first :-) [ Just joking; you can flip them on or off {3 levels} with a click.]
                                  • 14. Re: Table Help
                                    ChrisJones_1

                                         That's not a bad shout. There is, as you quite rightly say, 'the bunch of the usual suspects' so I could maybe get a way with creating a table with them on and if any new clients wanted to go just add them to the table via a simple form. I just need to think about the script and how to get it to create the journey. I had started to look at putting  the relationship table together but maybe this is a better idea and simpler. Thanks to both you and Phil for the help, you can see why I was struggling with it.