Background: I'm new to FM and I'm struggling with a problem generating totals. The DB is very simple with a Customers table and a transactions table. Within the transactions table there are some key fields: Type, status, surcharge amount, and transaction amount. The DB is set up as 1 to many linked by customer ID.
Attempting: I am trying to get daily, weekly, and monthly totals from the transaction per customer with totals on count by status, type, and amount.
I'd like to use these totals to generate monthly statements as well as view these amounts on a form in a chart form.
I'm thinking I have to use subsummary parts and fields but then I'd have a whole bunch of parts. This seems inefficient because in one month I have 30498 transactions.
The second idea I had was to generate a list of unique dates on a third table and run totals from there, but I'm not entirely sure how to generate the totals based on the transactions table
Any ideas are appreciated!!