10 Replies Latest reply on Mar 11, 2014 7:08 AM by philmodjunk

    Go to Related records from Sub Summary Report

    sccardais

      Title

      Go to Related records from Sub Summary Report

      Post

           The screenshot below shows a portion of a sub summary report ranking items in descending order.

           I would like to allow users to click on the name of any issue to switch to a different layout that shows details of only these records. e.g.clicking on Complex would find records marked with Complex. Clicking on OTHER would find only records marked with OTHER, etc.

           Is there a way to do this on a sub summary report?

            

      Linking_Issues_to_Detailed_Listing.jpg

        • 1. Re: Go to Related records from Sub Summary Report
          philmodjunk

               It is possible. But the details can vary. A scripted find could pull up such records. GTRR can do it with the correct relationship. But a key detail is missing from your description of your database. Is this report based on all the records in your table all the time? Or is it a sub set of the total records in your table--at least part of the time.

               A GTRR using a relationship based on the issues field (can be a self join to pull up records in the same table as used for this report) will pull up all records with that issue--both those in this report and records with the same value in the issues field that are not in the found set for your report. A scripted find can have the same issue.

               This can be handled with a bit of extra scripting, so I need to know the answer to the question that I asked.

          • 2. Re: Go to Related records from Sub Summary Report
            sccardais

                 The sub summary report (Ranking of Issues) is based on all of the records in the table: Join_Issues_Assigned.

                 Join_Issues_Assigned is related to another table, Surveys. The details I want to view are in the Comments field in the Surveys table. 

                 At some point, I would like to add a variation of this report that shows trends over time, between dates and by product - subsets of the Surveys table. 

                 When viewing the Ranking of Issues report, I want to allow users to click on one of the issues in the left column, Complex, OTHER, etc., to view fields from records in the Survey table.

                  

            • 3. Re: Go to Related records from Sub Summary Report
              philmodjunk

                   Set Variable [$IssueID ; value: Join_Issues_Assigned::Issue_IDFk ]
                   Enter Find Mode []
                   Set Field [ Join_Issues_Assigned::Issue_IDFk ; $IssueID ]
                   Constrain Found Set []
                   Go To Related Record [Show only related records; Match Found Set; From table: Surveys; Using layout: "Comments" (Surveys) ]

              • 4. Re: Go to Related records from Sub Summary Report
                sccardais

                     Thanks.

                     Worked perfectly.

                • 5. Re: Go to Related records from Sub Summary Report
                  SteveMartino

                       Hey Phil, I was wondering..........(which usually gets me into trouble)

                       I have multiple sub summary reports (6 on different layouts).  Could this script be written dynamically to pull the sub summary field, and the variable, and used in the Set Field script step?  Didn't know if it's possible.  I tried using a Script Parameter and the Get(ScriptParameter), naming the script parameter the same as the sub summary field, but no luck.  Maybe it's not possible for mere mortals, but if anyone could point the way, it would be you.

                       Thanks

                       Steve 

                  • 6. Re: Go to Related records from Sub Summary Report
                    philmodjunk

                         This can be done as long as all of the reports are based on the same table.

                         You could use: GetFieldName ( YourTable::BreakField ) as the script parameter where you refer to the "sorted by" field from your sub summary in place of YourTable::BreakField.

                         Your script then becomes:

                         Set Variable [$FieldRef ; value: Get ( ScriptParameter ) ]
                         Set Variable [$Searchvalue: Value: GetField ( $FieldRef ) ]
                         Enter Find Mode[]
                         Set Field By Name [ $FieldRef ; $SearchValue ]
                         Constrain Found Set []
                         Go To Related Records [....

                    • 7. Re: Go to Related records from Sub Summary Report
                      SteveMartino

                           Thanks Phil, I was so close, getting tripped up by setting the second variable. I probably should've waited for a response before writing all the duplicate scripts.

                           One (probably 2) quick follow ups.  Not important, just for information only.  It works fine, however the field it's based on (which is based on a value list) doesn't always return unique results.  For example:

                           Beckett A  (has 1 records)

                           Beckett AF (has 18 records)

                           Becket AFG (has 90 records)

                           Becket AFII (has 1 record)

                           If I click the first item (1 record), it will show 110 related records (the sum of the first 4 items).

                           If I click the 2nd item (18 records), it will show related 109 records (the sum of the first 3 items).

                           If I click on the 3rd item (90 records), it will show the correct 90 related records.

                           And if I click on the 4th item, it shows the 1 correct related record.

                           So my questions are, is there anyway to correct this within the Find script?

                           Or should I go back into the value list and try to rename these values uniquely or number them?

                           Thanks again for all your help

                           Steve

                            

                      • 8. Re: Go to Related records from Sub Summary Report
                        philmodjunk

                             Use:

                             Set Field By Name [ $FieldRef ; "==" & $SearchValue ]

                        • 9. Re: Go to Related records from Sub Summary Report
                          SteveMartino

                               That was it.  I was trying it independently but didn't know you concatenate it within the calculation.......

                               Thanks again Phil

                               Steve

                          • 10. Re: Go to Related records from Sub Summary Report
                            philmodjunk

                                 Using calculations to produce find criteria in a set field step is one of two key reasons why I don't used stored find criteria.