To create a working report, you must:
• set up a layout in list view, based on the table that has the desired granularity (probably your join table)
• create sub-summary parts to group by specific entities (e.g. employees and reviewSource)
• create summary fields (e.g Count of, primary key)
• place data fields & summary field(s) into the parts where you want to to see aggregated values; this includes native fields (from the join table) and related fields (e.g. employee name)
Then write a script to:
• go to the layout
• find the desired records (e.g. n-starred reviews, within a dateRange, etc.)
• sort by any fields that are the break fields for the sub-summary parts you want to see (employees and reviewSource)
Which should give you something like
Jane (sub-summary by employee; related name field from Employees)
Yelp: 1 (sub-summary by reviewSource; related field from review, native summary field)
Angie's List: 2
There's no body part here because you're probably not interested in the individual records (review/employee combination).
It seems a little bizarre that creating useful reports with a relational, multiple-table database would be such a carefully guarded secret.
It seems a little bizarre that you should think of this as a secret. By whom should it be guarded, and why carefully?
You can find a similar description in any number of books, and also in the product documentation (which I guess you have temporarily mislaid …), as well as in quite (I think) a number of posts in this and another forum that shall remain unnamed. Google comes to mind …
Thanks so much! This looks like it'll help me find the trail.
I don't literally think of this as a secret, of course. I have a subscription to Lynda.com, I have the Missing Manual for FMP, there are the help files... But as you may remember from your first experience with FMP, or any database design, it's one thing to have questions, but another thing to find/understand the language within the context of the thing in order to ASK the questions. It's especially frustrating when you know that the answers must be simple, but you only know how to ask questions in Tagalog...