4 Replies Latest reply on Nov 11, 2014 10:52 AM by RebeccaBiggs

    report categorized by a calculated field



      report categorized by a calculated field


      I am using filemaker pro 13.

      I have created a database to organize person data and groups our company works with.

      Group table has a primary key.  Person table has a foreign key for the group. Relationship is groupID to GroupIDfk  with records created and deleted in the group table.

      I set up checkboxes with the groups in the person table. Which works great so I can just click on the groups that the person is associated with.  Generating a report by group works ok but it list all the groups in the heading.  I would rather have them split out by group.

      The goal is to generate a report to categorize by the person and the groups associated with the person would list underneath the person and then vise versa if need be.  I am open to all suggestions at this point.  Thanks in advance.

        • 1. Re: report categorized by a calculated field

          There's no easy way to do that when you select all your groups from a single record in the Person table.

          If you set up a portal where each record in the portal identifies a different group for which that person is a member, you can use a layout based on this portal table to list the person records by group and then list the same person in each group for which they are a member.

          It would be easiest to set up that portal with a drop down list for selecting groups, but it is also possible to set up a portal where all the groups are listed and a control simulates the look and function of a check box.

          • 2. Re: report categorized by a calculated field

            Ok, I tried and like the portal option when I first developed the database but ran into issues when I tried to create the report..  At the time I created the portal I had to create a copy of the occurrence for each group in the relationships tab. This allowed me to be able to choose groups separately instead of each group duplicating  when it was selected for the next portal row.  Truthfully I like this option much better than the checkboxes. Individual records with the portal were beautiful and gave me greater level of detail. I just could not create a report with the information categorized and sorted as stated above.

            Can you advise me on a way to do this?

            • 3. Re: report categorized by a calculated field

              To repeat from my last post, Base your report layout on the portal's table not the people table.

              You can perform a find for the people and groups that you want in your report, sort them by group and use sub summary layout parts and a mix of fields from both tables to produce your report. No additional table occurrences should be necessary.

              Here's a tutorial on summary reports that may help a bit: Creating Filemaker Pro summary reports--Tutorial

              • 4. Re: report categorized by a calculated field

                ok, thank you for the response.  Still new to filemaker so I apologize for the repeat.  I will try this and see where I get.  Again Thank you looking at this.