11 Replies Latest reply on Jan 22, 2015 12:59 PM by JoanneMarcotte

    Inserting layout list within a report

    JoanneMarcotte

      Title

      Inserting layout list within a report

      Post

       

      Hi,

      I am presently migrating a DB Access application to FileMaker. My Database is comprised of 3 tables: Client, People, and Dossiers. In DB Access, it is possible to design a report as such:

      First section: Client information (client name, address, phone number, etc.)

      Section section: A list of people (or contacts) for this client

      Third section: A list of all Dossiers of the client (and therefore People).

      The way to do this in DB Access was to insert within the main report 2 other reports (the first for the list of People) and the second for the list of Dossiers.

      How do I do this with fileMaker?

       

       

        • 1. Re: Inserting layout list within a report
          philmodjunk

          To a limited degree, a FileMaker Portal can server the same function as an MS Access sub report. (It can take the place of sub forms and sub reports in many cases).

          This can be a limited option depending on what data you need to display and how you need to format it. Simple tables of info work fairly well and you can make the portal many rows tall and set the portal to "slide up", "resize enclosing part" to make them shrink to just the required number of rows--but only for Previews and PDF's.

          For a single set of related records for a given parent record, you can also set up a list view layout based on the related table with fields included from the parent table placed in headers, footers, grand summary and sub summary layout parts. So in your case, you might base your layout on either Dossiers or Contacts with a portal to the other of these two tables with field from Clients added directly to the above mentioned layout parts.

          There are other options possible, that get pretty complex so I would try the above options first.

          • 2. Re: Inserting layout list within a report
            JoanneMarcotte

            Thanks. I'll try this.

            • 3. Re: Inserting layout list within a report
              JoanneMarcotte

              Hello again,

              Have to realize that I do not quite understand what you are suggesting. I am inserting an image of the report I am looking to reproduce in FileMaker Pro, considering I have 4 tables in my Database: Client, Contacts, Projects and Activity, all one to many relationships. Really hopeful I can reproduce such a Report in FileMaker Pro. Thanks in advance.

               

              • 4. Re: Inserting layout list within a report
                philmodjunk

                That fits my understanding of your question. You will likely need to use at least one portal for this. You can look up "portal" in FileMaker help or any training resources at your disposal, but it's essentially the same thing as an MS Access SubForm object.

                You are welcome to ask specific follow up questions about the parts of my answer that you do not understand.

                • 5. Re: Inserting layout list within a report
                  JoanneMarcotte

                  OK. I will try to do this one step at a time if you will accompany me. 

                  First, I started the report in the context of the Client table since portals can only be used in the context of a parent (or grandparent) - Do I understand this correctly?

                  I put in the header info on Client, a portal on the Client's contacts and a second portal listing Projects for the Client. See image of what it looks like. Questions:

                  1. Can portals be of variable length? Some Clients have 1 contact, others have up to 5. Same question for Projects.

                  2. For now, nothing is in the body. Where do I go from here to have a list of activities for each project?

                  Thank you. 

                  • 6. Re: Inserting layout list within a report
                    JoanneMarcotte

                    Remember, I am in the context of a client. I cannot wrap my mind around the concept of listing activities and regrouping them under projects, all this in the context of a great grand parent. ???

                    • 7. Re: Inserting layout list within a report
                      philmodjunk

                      That's the wrong context. Given the need for the "list of activities sorted by project" section, I'd base the layout on that table occurrence and start by setting up a summary report of just that part of the report. Once it is working, we can work on setting up the other data using portals where needed in a header and possibly a leading grand summary layout part.

                      There is often more than one option for what table serves as the "parent" and the same table can even be both parent and child by using two Tutorial: What are Table Occurrences? based on the same data source table. (see the link for more on this.)

                      A tutorial on summary reports: Creating Filemaker Pro summary reports--Tutorial

                      • 8. Re: Inserting layout list within a report
                        JoanneMarcotte

                        I will close this post of mine by the following things I have learned.

                        1. The Report I am designing needs to be made in the context of the Activity Table.

                        2. Portals ARE NOT the equivalent of subtables in DB Access. They need to be made in the context of a parent table and data in the portal is therefore data related to children or grandchildren. Furthermore, they cannot be of variable length depending on the number of rows related to the parent table.

                        3. The Report I managed to created makes use of Header, 3 sub summary parts (one for each of the Client, Contacts and Project tables) and a Body part for the activities. That's the best that can be done with FileMaker Pro.

                        There it is. That's it for me. 

                        • 9. Re: Inserting layout list within a report
                          philmodjunk

                          Apologies for forcing open your thread (As community leader, I have the needed access permissions to do that).

                          But you are mistaken

                          2. Portals can be made of variable length - though not in browse mode if that's what you need. They can very in length in preview mode, when printing and when saving as PDF.

                          "They need to be made in the context of a parent table"

                          That's actually true for sub forms as well, as I recall. It's just that in Access, you use a SQL query to establish that context. In FileMaker, you establish that through the correct use of Table Occurrences in Manage Database and you have far more flexibility there than you apparently understand being new to the use of FileMaker.

                          That's the best that can be done with FileMaker Pro.

                          That is not correct. FileMaker has some significant limitations in it's ability to produce a report. But what you have outlined here can definitely be produced in FileMaker.

                          There it is. That's it for me. 

                          How very unfortunate that you give up so easily when learning how to use a different RDBMS!

                          • 10. Re: Inserting layout list within a report
                            JoanneMarcotte

                            I asked how portals could be made of variable length on a report. No response.

                            You say that the equivalent of a table list could be inserted in a report. Show me! How would I go about it with the list of contacts for example? No response.

                            I gave sufficient specifications here so that it would be possible for a FileMaker specialist to outline the solution. Nothing.

                            I do not give up easily. If this forum is of any use, specific answers could be given to specific questions.

                            Thanks anyways for your time. Rest assured that I use Table Occurrences on certain layouts. Works very well even if it is somewhat really twisted solution. If you ever want to detail the solution to inserting the Contact List in the Report above, I would be very happy to follow your suggestions.

                             

                            • 11. Re: Inserting layout list within a report
                              JoanneMarcotte

                              OK. Here is the solution and it works just fine. A certified FileMaker Pro based in Montreal, Quebec, gave me an hour of his time and came up with the solution.

                              1. Since it is an Activity Report, the report (or layout or the report) needs to be made in the context of the Activity Table.

                              2. Using subsummary parts for Client, Contacts and Projects, the first thing to do is to design a layout that will at least take care of the Activity Report part.

                              3. As for the 2 lists (Contact List and Project List) that I needed to integrate at the top of the Report, this is how it goes:

                              4. Since we need to be able to list all contacts and all projects pertaining to a Client, there needs to be in the Activity table (and in all other tables for that matter) a field that is the equivalent to the primary key of the Client table. I did not have this field in my Activity Table since I counted on the fact that it is, after all, a relational database. After inserting this key and initializing it so that the value is the equivalent of the primary key of the Client table, no relationship is necessary between Activity Table and Client Table. I used the Command + "=" function to initialize all the fields in batch mode.

                              5. Create a new occurence of the Contact Table (Duplicate the Contact Table). Let us call it the "ActivityContact" Table. Establish a relationship between the Activity Table and the ActivityContact Table using the ClientNb primary key. (All Tables must have the ClientNb primary key).

                              6. Do the same with the Project Table.

                              7. Return to the Report Layout. Insert in the Client subsummary part (presumably after the information on the Client), a portal for the ActivityContact Table. You will need to fix the number of rows to the maximum number of Contacts possible in your database. Otherwise, you will not see all of them when printing. Do not worry about the length of the portal on the layout. It could take a lot of space but it will shrink eventually.

                              8. Do the same for the ActivityProject Table.

                              9. Finally, select all items pertaining to the 2 portals (including all the fields) and go in the Inspector. Go the the Position - Sliding and Visibility parameters and set them as such: Sliding up based on - Only objects directly above - Also resize enclosing part.

                              10. Done. You will see the result (and the shrinking of the 2 portals) either in Preview or when printing the report.

                              There. Done. Hope this will help.