3 Replies Latest reply on Oct 9, 2012 10:57 AM by philmodjunk

    Trouble building a Report



      Trouble building a Report



           I have five tables; Projects, Facilitators, Facilitator Line Items, Products, and Products Line Itmes.

           The customer needed to assign Facilitators and Products to each of their Projects.  So I built them two portals on the Projects Table with a relationship to the Line Items Table allowing them to assign facilitators and Products vie the portals.

           Now this works greate, the customer can create as many projects as required along with assigning as many facilitators and Products to each Project as they desire.

           However, the customer would like to see a report that list out all Projects with their assigned Facilitators and Products listed under earch respective projects.

           Since my records live on three different tables, the best I can accomplish is to list one record each, based on the relationship that is build by the portals.  I have not been successful in listing many Faciliators and many Products that have been assigned to each projects.  


           Help, is there a way to do this ??  I've been working with sub summaries but to no avial, I can not get my list of Products and Facilitators under each respect Projects.  Any assistance in this matter would be grateful.


           Thank you.


           Tom :-)

        • 1. Re: Trouble building a Report

               Can you describe the relationships that you have in place in more detail? I'm particularly interested in the relationship between a Project and a Product and then what Product Line Items have to do with ssigning facilitators and products to a project. Knowing which relationships are one to many is crucial.

          • 2. Re: Trouble building a Report


                 The customer must create project first.  Then ....

                 ... depending on what the customer selects in the value list, the customer can select; one Client, many Facilitator and many Products to a project.  

                 Each time the customer selects a Facilitator or Product a script runs and create a record in the Facilitator or Products Line Items Table.

                 See .pic.

                 I need to create a report that list all Projects and under each project I need to list all Facilitars and all Products assigned to each Projects.


            • 3. Re: Trouble building a Report

                   I take it that you have these relationships:


                   It would be very simple to produce a report layout that listed all Projects and Facilitators for a given client. It would be equally straight forward to list all Projects and Products for a given client. In those cases, you can base a report layout on the lineitems table and pull data via relationships from the other tables as needed.

                   The problem lies with listing both in the same report.

                   There is no simple way to do that in FileMaker, but I can suggest several approaches that you can try, starting with the simplest:

                   1) Make it two reports on two report layouts. If desirable, you can use Save as PDF in a script to generate the Faciliatory report then append the Products report to that PDF. A page break will spearate the two reports.

                   2) Base the report on the Project table and place two very large portals with lots of portal rows to list data from the two line items tables with fields included from Facilitator and Products tables. Set the portals to slide up and to resize the enclosing parts. Set any other layout objects alongside and below the portals to also slide/resize. The report will only look correct in preview mode or when printed/PDF'd. Fields inside the portal row cannot slide up so you are limited to a fixed row height within the portal (but sliding shrinks the portal to just the number of records shown in the portal). There can also be problems with page breaks that cross a portal.

                   3) Define a new table used just for this report. Create a script that finds and imports records from the other tables into this report table--which then becomes the basis for your report. This is much more complex than options 1 and 2, can result in some delays while waiting for the report to appear if there's a lot of data to import, but avoids the limitations you can encounter with options 1 and 2.