Report using field from primary table and a summary field from a related table
Thanks in advance for your help on this. Am new to Filemaker Pro and have found it very intuitive so far but am stuck on a report I'm looking to generate.
I have two tables TBL_CONTACT, which represents a person and includes a F_CATEGORY field and related TBL_INTERACTION, which represents interactions with a person and includes a F_DATE of interaction field. The relationship is one (TBL_CONTACT) to many (TBL_INTERACTION). The basic table structures are working based on a unique index - portals work for instance.
I'm looking to produce a report which shows number of interactions (TBL_INTERACTION), by F_CATEGORY (TBL_CONTACT), by month. Months as rows, categories as column headings, the number that matches the combination in the body of the report.
What I'm doing currently is creating a calculated field that shows the month and a summary field "count of month" - F_COUNT_MONTH. Then I create a report with the summary field and category. The end result is a report with two columns - i) F_COUNT_MONTH and ii) F_CATEGORY, grouped by category. Aside from the columns/rows not being quite what I'm after - it's hard to work out what the report is actually trying to show.
Could one of you fine folks help shed some light on this? I'm very grateful for the help.