I have a large single table of closed REALTOR transactions in my area, with Name, PropertyAddress, CloseDate, ClosePrice, ListingAgent, SellingAgent, ListingBrokerage, SellingBrokerage fields.
I need to
- pull reports on each agent, filtered by CloseDate as a date range, and summarizing by
- total closed transaction $,
- $ closed as Listing Agent,
- $ closed as Selling Agent,
- Sort by highest to lowest in each of those.
- Group by Brokerages, so we can draw connections (ie agent our company is doing business with is at X company with Y top producer - get an introduction)
In my previous Coldfusion life, I would have
- Queried the whole table for WHERE Person is ListingAgent OR SellingAgent and held it in memory
- Then, created sub-queries against that query to get the result of each of the questions above.
What's the way to do this in Filmaker?
(Still struggling with fm's SQL implementation)