8 Replies Latest reply on Oct 24, 2011 1:37 PM by ClayHarrison

    Trouble with Print Layouts using Table Relationships

    ClayHarrison

      Title

      Trouble with Print Layouts using Table Relationships

      Post

      I had setup some table relationships to use with portals to create a filtered set of records.  I tried to use these same relationships to print that same filtered set of records.  When I setup the print layout to use that same table relationship, it displays the complete record set fromt he original table and not the filtered set from the secondard table in the relationship.  I am using the same secondary table as I have used in the portals.  Why does it not limit the record set to those matching the relationship? 

        • 1. Re: Trouble with Print Layouts using Table Relationships
          philmodjunk

          Did you use a portal filter? or is the filtering done at the relationship level?

          Does the print layout show the same exact text in "Show records from" as found in the layout where you have this working?

          Does the portal show the exact same text in "show related records from" as in portal setup of the layout where you have this working?

          • 2. Re: Trouble with Print Layouts using Table Relationships
            ClayHarrison

             I am doing the filtering at the relationship leve.  The "Show records" for both the print layout and the portal match.

            • 3. Re: Trouble with Print Layouts using Table Relationships
              philmodjunk

              Then you should see the same records on both layouts. Since that isn't the case I must not have a sufficiently detailed understanding of what you are trying to do.

              Please describe your original data entry layout where you created these records and your print layout where you want to print them in more detail. Describe both the relationship used and what exactly you want to see on the print layout once we get it working for you.

              • 4. Re: Trouble with Print Layouts using Table Relationships
                ClayHarrison

                 I have created a database for tracking construction project leads.  The main table (Leads) contains information on each project and in particular a field called "status" that we use to classify the leads as "Hot Lead, Active Lead, Awarded, etc." 

                I also have a field called "Hot List Key" that is set as a calculation type and is defined as ="Hot Lead".   I then created a duplicate table from "Leads" and named it "Hot List".  The relationship is defined as Leads:Hot List Key = Hot List:Lead Status.  This relationship is also sorted by the field "Sector" and then "Project Name".

                I have a form view layout that displays all of the info on a project and then I have a tab setup below with one of the tabs displaying a portal to show all of the projects that have a "Status" of "Hot Lead".  The portal is setup to Show related records from "Hot List", the related table I setup.  This portal displays info on each project meeting the relationship and it works exactly like I wanted it to.  The records are filtered to only show those with a value of "Hot List" in the "Status" field and are sorted by Sector and then Project Name.

                I have also created a print layout where under Layout Setup I have set it to Show records from "Hot List".  The parts of the layout are Header, Sub-Summary when sorted by "Sector", Body and Footer.  The fields used on the report are all taken from the table "Hot List".  The report works and looks like I want with the exception that is displays and prints all records.

                Most of my limited experience in FM was with FM5 and these relationships are all new to me.  Previously I would create filters within the scripts to print layouts and I can still do that.  It just seems like if you base a print layout on these relationships that it should work in the same manor as the portals and just show the records that meet the relationships.

                Any help would be greatly appreciated.  Thanks in advance.

                • 5. Re: Trouble with Print Layouts using Table Relationships
                  philmodjunk

                  All you need to do is perform a find for just the records you want to see in your report.

                  Here's an example that lists all Leads with "hot lead" in the lead status field:

                  Enter Find mode [] // clear the pause check box
                  Set field [Hot List::Lead Status ; "hot lead" ]
                  Set Error capture [on] // keeps dialog box from interrupting script if no records are found
                  Perform Find[]
                  Sort Records [restore ; no dialog] //sort the found records by "Sector" so that your sub summary part is visible.

                  A script that uses Go To Related Records, performed from your original layout where you have the portal to Hot List could also be used instead of performing the find.

                  This may not make sense at first, but your print layout could actually be based on any one of the three table occurrences and it would work the same, provided you use a script with the Find or Go To Related Records to pull up just the records you want to see in your report.

                  • 6. Re: Trouble with Print Layouts using Table Relationships
                    ClayHarrison

                     How would you modify the above script is you wanted the report to show all of the leads that met one of several options under the field Lead Status?  such as all records that were either Hot List:Lead Status = "Hot Lead" or "Active Lead" or "Project on Hold"

                    • 7. Re: Trouble with Print Layouts using Table Relationships
                      philmodjunk

                      Enter Find mode [] // clear the pause check box
                      Set field [Hot List::Lead Status ; "hot lead" ]
                      new Record/Request
                      Set field [Hot List::Lead Status ; "Active lead" ]
                      new Record/Request
                      Set field [Hot List::Lead Status ; "Project on Hold" ]
                      Set Error capture [on] // keeps dialog box from interrupting script if no records are found
                      Perform Find[]
                      Sort Records [restore ; no dialog] //sort the found records by "Sector" so that your sub summary part is visible.

                      This is the equivalent of a manual search where you enter find mode. Enter some criteria, then select New Request from the Requests menu before entering some more criteria.

                      It's also possible to set up a global field formatted with check boxes and then have the script loop through the selected check boxes generating a find request entry for each value selected in the check box formatted field.