11 Replies Latest reply on Feb 12, 2014 3:17 PM by markgordie

    Please help with report

    markgordie

      Title

      Please help with report

      Post

           I am trying to create a report (or list) using the tables as displayed in the attached upload.

           I simply wish to show the following:

           Table A as the top tier

           > then sorted by Table B
           > then sorted by a field in Table C
           > then sorted by a field in Table D

           When I do a Layout Setup "Showing Records from Table D" I get all the information of Table B, C and D but nothing for A.

           What am I doing wrong?

           Thanks

      Tables.jpg

        • 1. Re: Please help with report
          philmodjunk

               Sorry, but the "alphabet soup" makes this really hard to work with. Actual table names where we can see the purpose of each table makes things a lot simpler to work with.

               then sorted by a field in Table C

               Does not appear to be supported by your data model given the many to many relationship between tables A & B. Any given record in A may be linked to any number of records in B and Vice Versa.

          • 2. Re: Please help with report
            markgordie

                 Understood about the soup sorry. 

                 Problem: I wish to create a report  using a field from  tableA, listing all it's 'linked' records from tableB. TableC & D hold information for each of the records from TableB that I wish to include. It would almost fall into a hierarchy situation but I need the 'Join' table because of the many to many relationship. I need to get past the join table to include the 'one record information' from TableA  in my report. 

                 I hope that makes sense?

            • 3. Re: Please help with report
              philmodjunk

                   I think that you want to find one record in Table A and list data from tables B, C and D for all B records that link to the specified record in Table A.

                   If so, then this approach may work:

                   Perform a find for the record in table A, or perform a script from the current record in table A, to:

                   Put any data you need from A in global variables.
                   Use Go to Related Records to pull up a found set of Table D records on a layout based on Table D. Put the global variables of Table A data on this layout in the header. Sort your records by the __pk fields from Tables B and C. put fields from these two tables in a pair of sub summary layout parts with the same pk fields specified as the "when sorted by" (Break) fields.

              • 4. Re: Please help with report
                markgordie

                     Thanks again Phil,

                     I understand what you are saying and no doubt it will work.

                     However, I was wondering if you could give me a jump start on the script I would use to execute your theory.

                     I have made a 'Resource' table with no relationships to any other table having a global field.

                     I have attached a photo of the tables I am using and a report I can generate.  Based on our previous conversations (alphabet soup), here are the actual tables.

                     Table A = Programs
                     Table B = Courses
                     Table C = Competency
                     Table D = Outcomes

                     I am trying to get information from the Programs Table to appear at the top of the attached report. There will only be one record from Programs Table.

                     The TESTReport is based on the Table 'Outcomes' and you can see how I have used the sub summary parts in the Layout TESTReport photo.

                     I just want to add the information I need from the Programs Table to the top of that report - Please help, and once again, thank you.

                • 5. Re: Please help with report
                  markgordie

                       Sorry I resized the Layout TESTReport so it would fit on the page and messed up where the fields and tables line up.  

                       The field:

                         
                  •           'Outcomes' should be in the 'Body part' of the layout,
                  •      
                  •           field 'Competency' in Sub-summary by Competency::Competency (Leading),
                  •      
                  •           and all the Course information in the Sub-summary by Courses::Course Name (Leading)

                       Sorry if I have made it confusing. 

                  • 6. Re: Please help with report
                    markgordie
                    /files/7bf2f6a4ed/Layout_TESTReport_2.jpg 318x804
                    • 7. Re: Please help with report
                      philmodjunk

                           Can you produce the report that you need except for the need include data from a record in Programs?

                      • 8. Re: Please help with report
                        markgordie

                             Yes.  The attached report is generated perfect.  I can not seem to be able to get the information I would like from the Programs to appear at the top of that report.  One program could have several reports so I am trying to create that.  In the Programs Layout, I select courses within a portal that would be included in that specific program.  All that works great, no duplications of courses during that process either. 

                             Thanks

                        • 9. Re: Please help with report
                          philmodjunk

                               So while on the programs layout at the start of your script use Set Variable steps to capture data in global variables.

                               Example:

                               Set Variable [$$ProgramName ; value: Program::ProgramName ]

                               Then on your report layout, put the layout text <<$$ProgramName>> on your layout to show the value of the variable. (This is called a merge variable and you can also insert them from the Insert menu while in layout mode.

                          • 10. Re: Please help with report
                            markgordie

                                 One last thing and I (or I should say we, you...) have it licked.  Your 'Set Variable' steps works perfectly, thank you very much for that.  I created a button 'Program Report' on the Program Layout to run the 'Program Report' script as seen in the attached.

                                 The only thing I need to do now to make this perfect is, in the Program layout I can select the courses that would make up the program by using a portal with a Pop Up Menu, using a script 'Select Course' to select the courses that would make up that program.  All that part works great.

                                 But when I select the 'Program Report' button which runs the assigned script and I end up on my Program Report Layout, all the courses are listed in my report.  I need only the ones I selected in the portal to show up.

                                 Lets say I have 10 records in my 'Course Table.'  In my Programs Layout, I select 4 of the records from that Course Table in the portal with the Popup.  When I hit the button to run the Program Report script, all 10 records are listed.  I only wish the 4 to be listed.

                                 Any suggestions sir?

                            • 11. Re: Please help with report
                              markgordie

                                   Here is the Program Layout with the Portal to select the courses.

                                   Thanks again.