
1. Re: Calculating sums from selective entries
philmodjunk Nov 9, 2009 10:11 AM (in response to benjibear)Check out Summary fields and summary reports in Filemaker's help system. If you can't get get that to work, report back. Using Summary fields, I've been able to set up Ledger or Check Register type layouts with running totals and or subtotals for each budget category.
There are also ways to use aggregate functions such as Sum() with a relationship to do this.

2. Re: Calculating sums from selective entries
benjibear Nov 9, 2009 4:04 PM (in response to benjibear)Thanks Phil,
I did stumble upon the Summary option as well when I did some further searching,
but my solution is a bit clumsy at present, I'll let you know when I have worked it out.
Cheers,
Benjamin

3. Re: Calculating sums from selective entries
philmodjunk Nov 9, 2009 4:33 PM (in response to benjibear)One further trick that might help:
Define a deposit number field
Define a withdrawel number field
Define a Balance calculation as Deposit  Withdrawel.
Define a sRunningBalance summary field as Total of balance and select the running total option. For multiple accounts and/or budget categories, you can also use the "restart summary for each sorted group" and keep your layout sorted by the specified "sort" field.

4. Re: Calculating sums from selective entries
benjibear Nov 14, 2009 7:58 AM (in response to benjibear)Hi Phil,
Sorry about the delay, here is my solution, even if it isn't optimal:
I created one Calculation Field called 'Total Travel', with : If ( Category="Travel", Sum ( Amount ) )
and a Summary Field that is simply the Total of 'Total Travel', which shows the correct figure.
I don't see why a single field couldn't do the same in one ?
But hey, it works now . . .
One more question:
can I add another condition into the 'Total Travel' calculation field, to limit the search to a specific Date Range ?
With thanks, Ben

5. Re: Calculating sums from selective entries
raybaudi Nov 14, 2009 8:58 AM (in response to benjibear)Hi Ben
So you are using a version ante FMP7.
You'll find that your calculation:
If ( Category="Travel", Sum ( Amount ) )
is exactly the same of:
If ( Category = "Travel" , Amount )
To answer your last question, try something like:
If ( Category = "Travel" and YourDate > Date ( 12 , 31 , 2008 ) , Amount )

6. Re: Calculating sums from selective entries
benjibear Nov 14, 2009 3:36 PM (in response to benjibear)Grazie mille Daniele,
That works !
Now I can build my monthly cashflow form.
Benjamin
PS : I use Version 8.5

7. Re: Calculating sums from selective entries
raybaudi Nov 14, 2009 5:42 PM (in response to benjibear)benjibear wrote:
If ( Category="Travel", Sum ( Amount ) )
The "comma" ( , ) as separator was used till FM6 ( American version )

8. Re: Calculating sums from selective entries
philmodjunk Nov 16, 2009 9:38 AM (in response to benjibear)In addition to using a date in the calculation, you can also simply perform finds to pull up all records from a specific date range. In some situations, this can be a more flexible approach. In others, including the date in your calculation will work better. 
9. Re: Calculating sums from selective entries
benjibear Nov 16, 2009 9:48 AM (in response to benjibear)Thanks Phil,
But if I use Find the results are only stored temporarily, aren't they ?
On the other hand, if I build a monthly cashflow, with about 15 categories,
and multiply that by 12 months, I need 180 fields, even 360 with the method I have been using . . .
Something else you might be able to help with:
in addressing a data entry in a field (eg. If (Category ="Travel" ...
how do I change the expression so that it doesn't have to be precise,
i.e. that it even calls up entries like Travel/Subsistence ?
I tried using == but that didn't work. . .
Cheers,
Ben

10. Re: Calculating sums from selective entries
raybaudi Nov 16, 2009 10:40 AM (in response to benjibear)Case (
PatternCount ( Category ; "Travel" ) and YourDate > Date ( 12 ; 31 ; 2008 ) ; Amount
) 
11. Re: Calculating sums from selective entries
benjibear Nov 16, 2009 11:13 AM (in response to benjibear)Thanks Daniele,
It seems that your suggested calculation only displays the Amount of first entry under Travel, not the sum of all amounts.
Yet if I create a summary field of Total of (...) then I get the sum of all entries with Category of 'Travel' within the date range
Saluti,
Benjamin

12. Re: Calculating sums from selective entries
raybaudi Nov 16, 2009 11:34 AM (in response to benjibear)
benjibear wrote:It seems that your suggested calculation only displays the Amount of first entry under Travel, not the sum of all amounts.
Yes, that was implicit because you said:
benjibear wrote:I created one Calculation Field called 'Total Travel', with : If ( Category="Travel", Sum ( Amount ) )
and a Summary Field that is simply the Total of 'Total Travel'
benjibear wrote:
... then I get the sum of all entries with Category of 'Travel' within the date range
... but also Travel/Subsistence

13. Re: Calculating sums from selective entries
benjibear Nov 16, 2009 2:17 PM (in response to benjibear)Of course,
I misunderstood what you offered to solve with the calculation script you recommended,
thank you, that's great. . .
Saluti,
Benjamin