13 Replies Latest reply on Nov 16, 2009 2:17 PM by benjibear

# Calculating sums from selective entries

### Title

Calculating sums from selective entries

### Post

Hi,

I am trying to build a simple accounting database.

I have expenses listed in a table with fields for date, supplier, item, category, amount etc.

I would like to select all entries of a certain category (e.g. Travel) and sum up the corresponding amounts

in a new field, to indicate the total travel expenses.

I am not sure how to formulate the calculation field.

I would greatly appreciate some advice.

Benjamin

• ###### 1. Re: Calculating sums from selective entries

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 sub-totals 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

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

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

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

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

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

benjibear wrote:

If ( Category="Travel", Sum ( Amount ) )

The "comma" ( , )  as separator was used till FM6 ( American version )

• ###### 8. Re: Calculating sums from selective entries
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

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
Case (
PatternCount ( Category ; "Travel" ) and YourDate > Date ( 12 ; 31 ; 2008 ) ; Amount
)
• ###### 11. Re: Calculating sums from selective entries

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

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

Of course,

I misunderstood what you offered to solve with the calculation script you recommended,

thank you, that's great. . .

Saluti,

Benjamin