13 Replies Latest reply on May 26, 2012 11:44 AM by cabarnes

    Should I Use A Portal?


      I am trying to create a database for our timesheets (please see attached sample).


      There are 16 routes, which I have set up in a Route table. I have also set up a table for the drivers and stops.


      The top section (bus number, name, date, miles, fuel) are the same area on each one, but the bottom part that has to do with the route (route name/number, stops, times, counts) would be different fore each one.


      My first thought is to put the bottom part into a portal. Would this be the logical choice? Or is there a better way to do it?


      I can send the FM file if that would help.




        • 1. Re: Should I Use A Portal?



          To clarify, are you asking about designing the data entry layouts or a printed report/form layout?


          Mike Rauch

          • 2. Re: Should I Use A Portal?

            Sorry, designing the data entry screen(s).



            • 3. Re: Should I Use A Portal?
              Stephen Huston

              It sounds like you need a seaprate parent record for each route number, but could use a portal-child record for each run of that route.


              That would be a pretty straight-forward approach to data entry of runs via the route portal.

              • 4. Re: Should I Use A Portal?

                Attached is the FM file. I think I understand your approach, but would appreciate it if you could let me know how to do it.




                • 5. Re: Should I Use A Portal?

                  I think Stephen is on the right track.  I see the following tables...


                  Your setup tables...


                  Routes (the 16 routes)

                  Stops (any stop on any route)

                  RouteStops (link a route to a stop - basically you set up a template of current stops for that route.  Can be reconfigured as necessary per route.  Probably have a field for the Sched Time here)




                  And your logging tables...


                  TimeSheets (link bus, driver, route, date, set of StopLogs, fields for recording the other stuff relevant to the time sheet)

                  StopLogs (linked to TimeSheet, Stops)


                  So, when a new TimeSheet is created and when the Route is selected, you create initial StopLog records based on the RouteStops connected to the Route.  The StopLogs then have fields for recording your actual times, coupons, etc.  And yes, it makes sense to show the StopLogs in a portal on the TimeSheet based layout for data entry.


                  Hope this helps.


                  Mike Rauch

                  • 6. Re: Should I Use A Portal?
                    Stephen Huston

                    Thanks for the file, and the PDF. The file made it clear that you need something a little more complex than a simple portal for runs -- more like a portal for stops.

                    • Routes would be the parent records
                    • Another table route_Stops would need to be created and pre-populated one record for each combination of stop name and route number, so if route 100 stops at Walmart, and route 101 also stops at Walmart, you need 2 Walmart records, one with each route number in a Route_KeyField. These will provide a related value list for entering stops for each route so each route shows only its own stops as a choice in the portal record.
                    • Then you need a a Child table of Run_Stops, which will be the portal records.

                    Create a filtered Value list to show only the route_Stops for each route, and attach it the to Stop field in the portal on each route's main data entry screen. When entered as a Pop-up Menu, it will show only stop locations on that route.


                    Use the portal to enter/create info on each stop, sorted with the newest at the top of the list.


                    You might also want to look into the method documented in several FM manuals and blogs on how to use a one-row portal for data entry, and a multi-row portal to show entered records in creation-timestamp order.

                    • 7. Re: Should I Use A Portal?
                      Stephen Huston

                      Outputing a timesheet would involve going to related records based on a timestamp range and outputing that group via another layout in list view of the actual stop records.


                      PS - there are many other ways to do all of this, but your question was about if you could use a portal as an appropriate method.

                      • 8. Re: Should I Use A Portal?

                        Thanks Stephen & Mike for your replies, but I think I am tiotally lost now by your suggestions. I'm confused on the Logging Tables as well.


                        To add to this, the total passengers must be figured on a daily basis, then on a weekly basis. The stops also have to have a sum of all of them combined (all of the Walmart passengers, all of the Williams passengers, etc), also on a daily & weekly basis.


                        The timesheet would be improtant, as this is what they are paid from.



                        • 9. Re: Should I Use A Portal?
                          Stephen Huston

                          Reports by any time period summarizing any of the data could be generated via scripting if you are capturing the Creation Timestamp in a field in the portal rows.


                          This is not a simple system because your needs are complex.


                          [You may want to look for an FM training session in your area where you can get some hands-on help with your own project as part of the time. Training can really ramp up your skills faster than trial and error.]

                          • 10. Re: Should I Use A Portal?



                            After looking at your file I see you're almost there.  The difference between what I had laid out and what you already have in your file is...


                            1. You are using fields in Routes for the table I call RouteStops.  It's not the end of the world if you do that, but it's not really the 'correct' architecture.  For example, if you change a stop name down the line you'll have to change in all the routes.  You are also limiting the number of stops to the number of fields you create. I would create the table RouteStops with at least four fields (PK_RouteStop, fk_Route, fk_Stop, time_Schedule).  When you're defining a route you'll have a portal (instead of the fields you have now) where you add related RouteStop records.


                            2. Again, you are using fields in Fort Irvwin (my TimeSheets) for the table I call StopLogs.  Your construction here is going to give you serious problems when it comes time for reporting.  You definitely want a table to record the stop data specific to that TimeSheet.  I think ideally, when you create a new TimeSheet and subsequently select a Route for that TimeSheet, you'll script the creation of the StopLog records based on what you have defined in the RouteStop records related to the Route (think of the RouteStop records as the template for how many and which StopLog records you'll have initially).


                            I've attached a sample file with a quick-and-dirty example of the architecture.  Also an example for how to create the default StopLog records.


                            Good luck.


                            Mike Rauch

                            • 11. Re: Should I Use A Portal?

                              Hi Mike,


                              I spent most of the day looking over your example file and seeing how to intigrate it into mine, and now I am totally confused!


                              I guess I am not understanding the RouteStops and the StopLogs tables.


                              I think basically I just want to create something similar to the previously attached PDF's.


                              Thanks for your help.



                              • 12. Re: Should I Use A Portal?

                                Re: RouteStops


                                In your file you have a table Routes.  You have several fields defined in this table where you're specifying the stops for the route.  I'm suggesting you do not use fields in Routes to do this.  Instead you create a table called RouteStops.  A record in RouteStops represents a specific stop on a specific route.


                                You'll see in my example there is a portal on the Routes table where you create/edit the RouteStop records.  If you look in the relationship map you'll see how these tables are related.  Each RouteStop record has a value for a Route and a Stop.  The relationship between Route and RouteStops is set up to "allow creation of related records" (double-click the relationship line in the graph to see how it is defined).  What that does is give you an open record at the bottom of the portal where you can create a new record by typing into any open field in the portal.  When you do that the fk_Route is automatically populated according to the fields linked in the relationship.


                                Re: StopLogs


                                Similiar to what you have going with Routes, you have defined fields in the Fort Irwin table where you are recording the stop details for that bus.  Again, I'm suggesting you use a seperate table for this information and not fields in Fort Irwin.  After all, this was part of your initial question (should I use a portal?).  The answer is yes, and your portal should be to a StopLogs table.  When it's time for you to do your reporting/analysis you'll see why it's necessary to have this data in its own table as well.



                                It's a matter of how your data is modeled; what tables are required, what fields are in those tables, how are the tables related, etc.  If I were creating a Household database, I wouldn't create fields in the House table for Person1, Person2, Person3, Person4, etc.  What happens where there's a household with 14 kids?  No, the proper model is a House table and a Person table.  I then relate a Person to a House.


                                As a general rule, if you find yourself creating the same field and attaching a number (stop1, stop2, stop3) you have a good candidate for something that should be in its own table.


                                Hope this helps.


                                Mike Rauch

                                • 13. Re: Should I Use A Portal?

                                  Thanks Mike, I think that I am starting to understand what you mean. If I understand correctly, I would add the times and counts in a separate table?


                                  Below is a rought idea of what I would like. The top part I have. I'm guessing that the red part would be the portal. If the Route is selected from a dropdown list, would the portal part with the appropriate stops be automatically displayed?