1 Reply Latest reply on Apr 8, 2013 9:20 AM by philmodjunk

    Totals of junction tables for use in filters



      Totals of junction tables for use in filters


           Hi there,

           Here's a sketch of my structure:

           People  (ID - pk , OrgID - fk to Organisations::ID, contact details)

           Organisations  (ID)

           Events (ID - pk)

           Delegates (ID - pk, personID - fk to People::ID, eventID - fk to Events::ID)

           So, technically speaking delegates is a junction of people and events.

           All is pretty straightforward and working quite well for our booking purposes, but now of course we have a marketing person who wants to segment the mailing list. I need to be able to get

           1: the number of delegates an organisation has sent to an event

           select orgID, EventID, count(delegateID)
           group by organisation, event

           We are using FM11 so can't use SQL, and they want the column to be exposed on the delegate entry form for filtering purposes prior to an export.

           Help please, I can't make it work in Filemaker.
           Many thanks

        • 1. Re: Totals of junction tables for use in filters


               1) go to a layout based on Delegates. Perform a find, specifying the Event and the Organization. The number of found records will be the number of Delegates.

               You can also set up a summary report for a given event on the Delegates table sorted to group records by orgainization. Such a summary report can list the individual delegates under a sub heading with data from Organizations or, if you remove the body layout part and just use sub summary layout parts with summary fields, you can list each Organization as a single line in the report with a count of the number of Delegates.

               Here's a link on summary reports you can take look at if interested: Creating Filemaker Pro summary reports--Tutorial