6 Replies Latest reply on Aug 25, 2011 3:52 PM by PeterDowns

    Reports - Multiple tables



      Reports - Multiple tables



      I have 2 related tables one called 'project' and one called 'tasks'. 'Tasks' contains multiple tasks related to indvidual projects in the 'projects' table.

      My problem is
      I want to generate a report of the current project on the screen at the time. Not all the reports just the current one.

      Also In this report I want to show all the tasks related to this project. These tasks are also currently on the screen in a portal setup. 

      What is the best option to achieve this?



        • 1. Re: Reports - Multiple tables

          I want to generate a report of the current project on the screen at the time. Not all the reports just the current one.

          I assume you mean 'not all of the reportsprojects just the current one'?

          Reports which list tasks as the body should be generated from the tasks table; it doesn't matter what is showing in the portal.  You can place fields from Project up in a leading part.  You can use this script:

          Set Error Capture [ On ]
          Go To Related Records [ show only related records from tasks table  ; result options: matching current record only ]
          If [ Get ( LastError ) ]
          Show Custom Dialog [ OK ; "no tasks for this project" ]
          # records found continue with report
          Sort by any break field specified
          Print report etc.
          End If
          Go To Layout [ original layout ]

          • 2. Re: Reports - Multiple tables

            Sorry I did mean projects not reports.

            I have the report popping up in a new window and I am having issues where to run the code. I have tried running a script that is connected to a button on the layout containing the portal. It just brings up all the records.

            I have also tried it as a script trigger on the new layout opening on the new window. but it only bring up the no records message box.

            Where would you be placing the code.?



            • 3. Re: Reports - Multiple tables

              Run LaRetta's script from the Projects layout. A button on that layout should do the trick. Make sure that the Go TO Related records steps specifies the correct table and layout.

              • 4. Re: Reports - Multiple tables

                Okay, still having issues.

                More detail for your perusal.

                Here is my script:(attached to a button on the parent layout)

                 Set Error Capture [On]
                Go to RELATED record [Show ONLY Related Records; From Table: "Projects_ContactRef"; Using Layout: <Current Layout>]
                If [Get (LastError)]
                   Show Custom Dialog ["no Record"; "Apparently there isn't anything worth showing at this stage - there is no record..."]
                   New Window[]
                   Go to Layout ["rpt_StaffProjects" (Projects_ContactRef)]
                End If 

                The Parent layout is linked to a table called Contacts_ProjectsRef which has a portal linked to Projects_ContactRef. The parent layout displays the project Officer and the portal shows the linked projects attached to this officer. So everything is working correctly in this situation.

                I then made up a report layout called rpt_StaffProjects which I have linked to Projects_ContactRef and I only want to show the list of projects related to the officer displayed on the screen at this time.

                At the moment the custom dialog pops up indicating no records and then the rpt_StaffProjects window pops up with all records of all officers.



                • 5. Re: Reports - Multiple tables

                  Using Layout: <Current Layout>

                  should then be Using Layout: "rpt_StaffProjects" ( Projects_contactRef )

                  It's often useful to leave out/disable Set Error capture when first testing a new script so that you can see any error messages that appear as that can help you figure out what went wrong. You can then add/enable the step once you are satisfied that the script works correctly.

                  • 6. Re: Reports - Multiple tables

                    Thanks works a treat