Newbie calculation problem

[mabbott]

I set up a database that keeps track of all the work I do and has all the standard fields... job number, client, date, fees, etc... To send out invoices every month, I've set up a button with a simple script to search by a range of dates as well as by the specific client and throw it into a list layout. This works fine. What I'd also like to do is be able to do is total the fees for that date range. When I define a field as a number and then have it auto-calc the sum of the fees, it only spits back the fees of one of the jobs that results from the find and doesn't add anything. I've set up a "Total Fees" field and a "Monthly Fees" field. I'd also like to be able to add up the total fees of my entire database. How can I add up the total fees? Also how can I get the monthly fees to total after I perform the find?... and can I add this monthly addition part to the simple search script that is on my search button?

Thanks!

Mark

[LiveOak]

The most common way of creating totals of groups of records in a report is to use Summary fields (defined as "Total of" some field) and place them in one or more Subsummary parts and/or a Trailing Grand Summary part added to a layout. Each subsummary part is tied to a sort order. A subsummary part might be "Subsummary with sorted by Month". A Summary field placed in a Trailing Grand Summary totals all the records in the found set.

Time to open your FM manual are read up on Summary Field, Subsummary and Trailing Grand Summary Parts.

-bd

[Ugo DI LUCA]

There are several ways to get this result.

Keep the fields you already created.

1. Add a Month(Date) calculation field c_Month = Month(Invoice date*).

*or use another event with a date field (the one you use for the script)

2. Create a Summary field s_GrandTotalMonth = Sum (Total Fees)

First Solution : Using a Summary Report (you can only view the results in Preview Mode.

Won't detail it as it is very well explained in your Help section.

Header

Subsummary when sorted by c_Month - page break before each occurrance

Body (a list of the records and related informaton + your Total fees field)

Subsummary when sorted by c_Month

Place s_GrandTotalMonth here.

Footer -

Second Solution : Using a Self Relationship.

For the Month :

create a self relationship on Month(date) -MainFile:c_Month::MainFile:c_Month - call it selfjoinbymonth

create a calc field c_TotalMonth = Sum(selfjoinbymonth::Total fees)

As the relationship is set to match together all records with same Month (Attention for years), this self relationship and calc will give you the total you're looking for, constant on any record in your file.

For the entire file :

create 2 more fields called c_Hook = indexed calculation set to 1 ang g_Hook (global number field) and set it to 1.

Create another self relationship MainFile:c_Hook::MainFile:g_Hook - call it selfjoinHook

Create a calculation FileTotalFees = Sum (SelfjoinHook::Total fees)

As this is a 1 to 1 relationship (all records have a 1), then all records would be match together to get your Total for entire File.

You could also have a total by Client ID, category,...

I suggest you sometimes read your Books.