Are you familiar with summary reports?
Using sub summary layout parts, you can easily produce a report with this format:
2011 totals: $3,000
2012 totals: $2,225
2011 totals: $3,035
2012 totals: $2,885
And so forth....
You base the layout on Orders and put the Customer fields in a sub summary layout "when sorted by" Customer ID. You then must sort your records by that same field. You can perform a find to pull up orders for any number of different time periods for such a report.
A side by side comparison with the totals in two columns are also possible, but it takes quite a bit more effort to set up so I am suggesting the simpler option first.
Thanks for the comments ..
I already did the simple answer and is working perfectly in my current database .. what I need is the side by side comparison because this is the default format requested as a report.
Presumably, you have this relationship:
Customers::__pkCustomerID = Orders::_fkCustomerID
Though your field names may be different. (See: Common Forum Relationship and Field Notations Explained if this notation is unfamiliar.)
If so, you can make your report on a layout based on Customers. Put a one row portal to Orders on your layout.
Give it this portal filter expression:
Customers::gYear1 = Year ( Orders::OrderDate )
Place your summary field inside the portal row. Define a number field with global storage for use with this expression.
Make a copy of this portal and its summary field to place next to the first portal. Change it's filter expression to be:
Customers::gYear2 = Year ( Orders::OrderDate )
Now you can enter a year into gYear1 and a different year into gYear2 and you can then get yearly totals in two columns.
There will be one possible glitch, however: When you first select values for the global year fields, the portals will not automatically update to reflect the change in these values until you either use Refres Window [ Flush cached Join results] in a script or modify your relationship to be:
Customers::__pkCustomerID = Orders::_fkCustomerID AND
Customers::gYear1 X Orders::anyfield AND
Custoemrs::gYear2 X Orders::anyfield
It does not matter what field you select in Orders for these two added pairs of match fields. This relationship forces the filtered portal to update automatically each time you modify a value in either gYear1 or gYear2.
If you are using FileMaker 12, there are also ways to get these two yearly totals using calculation fields with the ExecuteSQL function.
But this will give me the information I need for each customer seperately (per customer record)
How would it work if I need the final layout to be :
Customer1 Value Value
Customer2 Value Value
Yes. It will give you what you have specified. Use a list view layout so that you can list each customer record in a single row. Where you show "value" is where you'd place the one row filtered portals.
Thanks a lot for your valubale help !! :-) ..
Just one more thing .. Is it possible to compare the years through calculation .. like for example percentage difference between years for each customer ??
Not with a filtered portal and these relationships.
But if you defined these relationships:
Customers::__pkCustomerID = OrdersYear1::_fkCustomerID AND
Customers::gYear1 = OrdersYear1::cYear
Customers::__pkCustomerID = OrdersYear2::_fkCustomerID AND
Customers::gYear2 = OrdersYear2::cYear
OrdersYear1 and OrdersYear2 would be new occurrences of the Orders table.
You can replace the filtered portals with just the Total of TotalAmount from OrdersYear1 and OrdersYear2 to show the totals for each year.
Now this calculation defined in Customers could give you a precent increase from year 1 to year 2:
(OrdersYear2::Total of TotalAmount - Orders::year1::Total of TotalAmount ) / OrdersYear2::Total of TotalAmount
That's great ... Thank you .. I've been struggling with that for a week now ..
I really appreciate your help and time
I've worked up the above solution and it's been working fine. I'm facing another problem now when trying to compare the values from the 2 years on the same chart by month. The chart only relates the values to one table ( either year 1 or year 2 ) while the other one is fixed over the months.
The X-axis is based on Monthname(ordermonth) and the Y-axis has 2 series one for each each year (from different tables)
Can you please help me out with that?