14 Replies Latest reply on Oct 3, 2012 12:32 PM by philmodjunk

    Many tables in one portal?

    TimHorton

      Title

      Many tables in one portal?

      Post

           Good morning,

           I have a file with 20 tables and I would like to use information from many of those and create a report or a portal with that information.

           Is there a way to do this?

           I also thought of creating a new table and a script that would copy the desired fields from many portals in that table. But I cannot figure out how I would do this... For example, one table might have 5 records to copy, while the other table has only one...

           Any suggestions?

           Thanks

        • 1. Re: Many tables in one portal?
          philmodjunk

               Only one table can be referenced directly by any given portal, but fields from tables linked to the portal's table in relationships can be included in the portal row. That may or may not work for what you have in mind.

               Feel free to describe the report that you need in greater detail. Using a portal in a report that you will print or save as PDF is often not the best approach to use and alternatives exist that can, in some situations, work much more flexibly than a portal for such a report.

          • 2. Re: Many tables in one portal?
            TimHorton

                 OK, I'll try to describe as best as I can. English is not my native language...

                 I have many tables that are related to a main one. Basically, I'm using this file to produce itineraries.

                 One table is for transportation, the other one for accomodations, and so on...

                 Each table has date, time and a description for each record it holds.

                 What I wish to do is to get those fields in one report, and create an itinerary. It would need to be possible to sort by date and time.

                 I thought a portal could be a nice solution...

                 Thanks!! 

            • 3. Re: Many tables in one portal?
              philmodjunk

                   And what does one record in the Main table represent?

                   Can you produce a general outline of how the report would look?

                   A screen shot of the relationships involved might also be helpful.

              • 4. Re: Many tables in one portal?
                philmodjunk

                     What I see in your report outline looks like a list of tour "events" in chronological order, grouped by date. Is that correct?

                     If so, then it may be possible to set up a summary report that lists your tour events and groups them by day. But that assumes a single table where each record represents a different tour event.

                     Some Questions about your relationship graph:

                     Each "box" in the above screen is called a "table occurrence". Each is a point of reference for refering to a specific data source table defined on your Tables tab in Manage | Relationships. Which table occurrence represents your "Main" table where one record represents a single tour?

                     Would that be Tours_Details? or one of the other occurrences?

                     WHich occurrences refer to tables that contain data you need to include in this report?

                • 5. Re: Many tables in one portal?
                  TimHorton

                       You are correct as for tour events in chronological order.

                       And yes Tour Details is the main one, where each record is a single tour.

                  "WHich occurrences refer to tables that contain data you need to include in this report?" It,s the ones that start with TOURS. Like Tours_Transport, Tours_Special, Tours_Repas, etc.

                        

                  • 6. Re: Many tables in one portal?
                    philmodjunk

                         What is the purpose of Itineraire? I'd guess that table store's the tours Itinerary?

                         If so, how do the records in this table compare to the "events" you want to list in your report?

                    • 7. Re: Many tables in one portal?
                      TimHorton

                           This is just a table I created to test a solution.

                           I thought that I could copy all the records related to the tour in this table.

                           I could have use this table to create a portal in the Tour_Details...

                           Testing and learning...

                            

                      • 8. Re: Many tables in one portal?
                        philmodjunk

                             I do think a structure change would make your report much easier to do:

                             Tour_Details------<Itinerary>------ItineraryDetailTables

                             Tour_Details::__pkTourID = Itinerary::_fkTourID
                             Transportation::__pkTransportID = Itinerary::_fkTransportID
                             Accomodations::__pkAccomID = Itinerary::_fkAccomID
                             and so forth for any remaining detail tables

                             See this thread if this notation is unfamiliar to you:Common Forum Relationship and Field Notations Explained

                             One record in Itinerary represents one "event" in your report. Itinerary would serve as a "join" table linking a tour to a list of all the other details (accomodations, Transportation, etc.) Thus, a specific record in Transportation would represent a particular transportation company and you would link that record to each Itinerary record (in any tour) where they provide the transportation. Details specific to that specific event--such as the Bus ID or a Flight Number, would be recorded in fields in the Itinerary table.

                              

                        • 9. Re: Many tables in one portal?
                          TimHorton

                               OK give me some time to make some sense of this...

                               Thanks for your help, I'll get back to you soon.

                                

                               S

                          • 10. Re: Many tables in one portal?
                            TimHorton

                                 Is this what you had in mind (see attached file) ?

                                 I'm trying to map it in my head... Do I keep the existing relationships between the Tour_transport, Tour_heberg, etc and the Tour_Details?

                                 If I understand, each time I would create a transport record (tour_transport), then I would create an itin item at the same time in the Itinerary table?

                                 Then from the ItineraryDetailTable, I would create a report?

                                  

                            • 11. Re: Many tables in one portal?
                              philmodjunk

                                   Possibly, but note that the primary and foreign keys are reversed for the relationship between itinerary and the existing "tours" tables. You now use a primary key field from a given Tours_... table to link to a foreign key field in itinerary where you previously had the reverse. I can't tell from here, but this unified itinerary table may be able to take the place of your Tours_... tables. And the Itin Details table may not be needed.

                              • 12. Re: Many tables in one portal?
                                TimHorton

                                     OK I'll test it.

                                     I thank you very much for your help!

                                     S

                                • 13. Re: Many tables in one portal?
                                  TimHorton

                                       It works!

                                       I can see all the records created and with a simple script, get all the details I need in a report.

                                       Now the next question; what are the alternatives to a portal to create that itinerary?

                                       Right now, I can see all the details from a portal in the MAIN table (TOUR DETAILS). And all the records exists in the Itinerary table.

                                       Thanks!

                                        

                                  • 14. Re: Many tables in one portal?
                                    philmodjunk

                                         Base your layout on Itinerary and use a list view to view the data. Add a subsummary part "when sorted by" a date field in your itinerary table if you want to group events by date under a heading displaying that date.

                                         To view your report, you'd go to that layout, perform a find for all records with the specified tourID and then sort the records by the date field specified for the sub summary layout part.--this is a process that can be scripted so that you just click a button from the Tour_Details layout and see your report pop up.