AnsweredAssumed Answered

Totals of junction tables for use in filters

Question asked by PaulSmith on Apr 6, 2013
Latest reply on Apr 8, 2013 by philmodjunk


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