The simplest is to set up a summary report where a single summary field can compute a sub total for each category. You perform a find for the records you want for your report--such as all transactions for a given time period, sort your records by Category and then you can place that summary field in a sub summary layout part "When sorted by Category" to calculate the sub total for that category. Put that same field in a footer or grand summary layout part and you'll get a grand total from the same field.
This uses a List View layout and computes values from a found set for that layout. Other methods, that are a bit more complex can also be used in situations where you can't use such a layout design to get the subtotals.
But if it performs a find then it's not possible to show more expenditure from different seasons at the same time. In order to see the difference, and always have your totals on the fly..?
Why couldn't the find include more than one season? (You can sort the records by season, then by category or by category, then by season....)
Can you describe what you want to see in more detail?
And please note that there are other options, I've started with the approach that is simplest and easy for me to describe and for you to implement. Other options are also possible.
But a more detailed description of what you want to see on the screen would be needed in order to select a different approach that works for what you want to see.
I have a small sorts club.
There's 2 different seasons a year. Spring 2013, Autumn 2013, Spring 2014, Autumn 2014... etc
All receipts will have a season attach to them.
Then there's a set of different categories: Travel, Equipment, Food, medical...etc All receipts will have a category attached to them.
Sometimes an expense has been paid/given by external people and not paid by the sports club. So all receipts has a payer attached to them (such as 'paid by club' or 'donation')
I have in a list view layout with summary fields at the bottom and various pull down lists and radio buttons, to sort my receipts into whatever needed. But if i have to hold 'travel expenses' from Spring 2014, against 'travel expenses' from Spring 2013, Autumn 2013, Spring 2014, Autumn 2014. I have to sort my receipts 4 times, every time make sure I'm sorting the right categories etc.
So I thought I would try to make a layout where there will be row for the seasons and rows for categories. So i'll always be able to go that layout and immideiatly be able see what the current 'travel expense' and also see what the past season's travel expenses are, without physically sorting anything. and also see how much money have been donated each season aswell. So I guess I'm looking for making an overview over all my receipts.
I have to sort my receipts 4 times, every time make sure I'm sorting the right categories etc.
I don't see why you need more than one sort order for this layout.
I'm not sure which layout design you want to see. Both of the following examples are possible. Only one would use the summary report method that I described originally:
Spring 2014: $2,000.00
Autumn 2014: 800.00
and so forth... (This is the easiest to set up)
Season Travel Equipment Food Medical... and so forth
Spring 2014 $45.00 $900.00 $45.00 $300.00
Autumn 2014 ---> and so forth
This is not a summary report and you have a built in limit a to how many categories you can fit on the screen or page, but it also makes it easy to compare as many different season subtotals in the same report.
Let me know which you prefer and I'll describe that result in more detail. If you don't want to wait for that, the second option is called a "cross tab" report that you can research to learn more and here is tutorial on summary reports if the first option is one you want to try: Creating Filemaker Pro summary reports--Tutorial
it seems like I need the "cross tab" report. As far as I can understand on the internet is I need a Calculation field and a summary off the calculation field. I think, I just can't really get my calculation to work out.
On my main receipt template I got drop down list for season called 'season' and it has a drop down list with 'summer 12', 'Winter 12', 'Summer 13'... 'Summer 14'
And then I got a field called 'Category', it has a drop down with 'overheads', 'equipment'...'travel'
I guess my problem is to get the calculation to figure out how to sort out what receipts are grouped together based on season and category..?
There are several ways to produce a Cross Tab report. Not all use the calculationfield/summary field pairs that you mention.
In the following example, I'm assuming that you have a single table, called Transactions, with these fields:
Season (Text, populated by value list to be Season name and 2 digits for year)
Category ( Text, populated by value list: Travel, Equipment, etc )
Amount ( The dollar amount of a single expenditure)
There can be any number of records used to log the expenditures for a particular category and season.
Here's the filtered portal method for producing this cross tab report:
Make a duplicate table occurrence of Transactions called Transactions|SameSeason and link it like this in a relationship:
Transactions::Season = Transactions|SameSeason::Season
Define a summary field, sTotalAmount to compute the total of Amount.
Set up a new layout based on Transactions to be viewed in List view. While in Layout Mode, change the Body layout part into a sub summary layout part "When sorted by Season". This will reduce your report to a single row for each season so long as you sort your records by the season field.
Put the Season field in this layout part to serve as the first column of data. Then add a one row portal to Transactions|SameSeason next to it to serve as the second column. Specify this portal filter for it:
Transactions|SameSeason::Category = "Travel"
Put sTotalAmount in this single row portal as the only field.
Now make copies of this one row portal and place each copy next to the previous copy, but edit each new portal's filter expression to specify a different expense category.
Now you can perform a find for the season's that you want and sort the resulting found set by Season to get your report.
Note: The resulting rows may not sort into the order that you want as Autumn 14 will sort before Spring 14, but do it this way to see if you can get things to work. Then we can set up a calculation field that rearranges the text in your season field and use it for sorting (and for your "when sorted by sub summary specification) to get the results that you want.
I can't get it 100% to work.
I've made a Table occurrences of my Transactions named it Transaction|SameSeason
I linked the season field in both occurrences together
I made a summary field called sTotalAmount having it make a total of "net amount"
Then I made a new Layout based on transactions, making change the body into a subsummary sorting by season (from transaction table) - and set it to 'print below'
Then I made a Portal, showing related records from Transactions|SameSeason. It is set to filter portal records: Transactions|SameSeason::CATEGORY = "Travel"
the one field that's in the portal goes to Transactions|SameSeason::sTotalAmount.
Then I switch to browse mode, but nothing comes up..?
Are your records sorted by a sort order that includes the season field?
I have another layout in a list view where I can sort all the reciepts, but I've tried show all records, but doesn't seem to help. It kind of worked when I didn't switch the body into a subsummary - except it was showing all my records...
I did not suggest Show All Records. I am asking if you used Sort Records to sort your records by the Season field. If you do not sort your records by the "sorted by" field specified for the sub summary, the sub summary will not be visible.
That did the trick. Is there a way to build in every time i go to that layout it'll automatically sort my records by season instead of date, which I use on another layout..?
And then so the seasons are sorted by 'seasons' and not the alphabetical.
The On Layout Enter trigger can sort your records by season each time you enter this layout, but there's a good chance that you really need a find script that both pulls up the desired records and that also sorts them by season. See this thread for examples of such scripted finds: Scripted Find Examples
To get your seasons to sort in this typical order:
Define a pair of calculation fields: cSeasonName, cSeasonYear.
cSeasonYear : GetAsNumber ( Season ) // select number as the result type
cSeasonName : LeftWords ( Season ; 1 ) // select text as the result type
Sort your records like this:
cSeasonName ( Descending )
Season (ascending or descending works here)
Perfect it works, just made a button to 'show' summary and it sorts... Thank you so much