Trying to total by 'region'

[charlesoutcalt]

Hello All. I'm sure some of you will laugh at how easy this question is to answer, but I've been having a hard time with it.

Rather than go into the intricacies of my particular data, I'll use generic terms that should be comparable.

Let's say I have two tables:

Table 1 contains only employee ID numbers. There is one and only one record per employee.

Table 2 contains one record for each sale. These records contain the employee id number, the amount of the sale, and the region. Because employees make many sales, there are lots of records per employee.

What I need to do is create a field for each region for each employee, then total the sales in that region for each employee. In other words, I would like to have a field for each employee showing his/her sales in Region 1, Region 2, Region 3, etc. I want these totals in their own fields, rather than in a portal or summary report, because I want to be able to search on them--for example, to create a found set showing employees with over $10K of sales in Region 1, but under $5K in region 2.

I understand what I want to do, but not how to do it. How can I 'collect' sales and total them by employee ID, then by region within each employee ID?

I would greatly appreciate any help you all could give. As you can probably tell, I'm no FileMaker expert, and so don't be afraid to speak slowly, and to assume I know nothing.

THANKS! :

[Ender]

The best way to do this type of thing is to use a columnar report with sub-summary parts, defined based on the Sale table. The part setup could look something like this:

Header

--------------------

Sub-Summary by Employee ID (leading)

--------------------

Sub-Summary by Region (leading)

--------------------

Footer

Place a summary (Total of Sales) field in both Sub-Summary parts, and place the Employee Name and Employee ID fields on the Sub-Summary by Employee ID part. Then place the Region field on the Sub-Summary by Region part. With this set up, you can create a script that narrows the found set to date range or employee or whatever, goes to this columnar report layout, sorts the records by Employee::Employee Name, Employee ID, and Region, and enters Preview Mode or Prints.

[charlesoutcalt]

Ender, you're a genius. This worked just great. I now have a report showing each ID, followed by 'sales,' arranged by 'region.'

Here's another, related question: How can I search for values, or ranges of values, in each 'region'? For example, how would I look for sales above $10K in the Northeast? In Browse mode, I get each ID's sales by region in turn (if an employee has 10 regions, I have 10 records, and need to click from one to the other). In Preview mode, I can see all the sales, arranged by region, but I can't search via Preview mode.

[charlesoutcalt]

And here's a follow-up: How could I produce, say, an average sales amount by another employee characteristic, such as gender? In other words, how could I come up with a table that listed, for example, sales by region and gender?

It's all these calculations on the sales by region numbers that make me think that it might be easier to just calculate a fixed value for each region for each employee? If I could do that, then I would know how to go about all these other tasks.

BTW, the data in my database won't change. They're historical, and so I don't need to worry about adding any new records.

Thanks again!!!!!

[Ender]

It's all these calculations on the sales by region numbers that make me think that it might be easier to just calculate a fixed value for each region for each employee? If I could do that, then I would know how to go about all these other tasks.

While it's possible to store summary values, this type of thing should be reserved for special situations where the sub-summaries don't work or are too slow. At this point, I'd recommend sticking with sub-summary parts for these breakdowns. For a breakdown by Gender, simply add a Sub-Summary by Employee::Gender part.

Adding the ability to search for a summary total (or range) is considerably more complex. You can't search on a summary field, so you have to look at other techniques.

One technique is to run the search and show the results in a layout based on the Employee table, using an aggregate calc on a relationship to the Sale records, like Sum(Sale::Sale Total). There are two considerations, however: 1. If you wish to only sum() a subset of the related Sales, the relationship must be filtered by adding additional criteria. 2. A search on such an aggregate calc can be slow, depending on the number of records, the number of additional Find criteria, and the version of FMP. A variation on this is to run the Find in the Employee table for whatever subset of Employees you wish, without the sum() criteria, then on the resulting found set have the script loop through the records Omitting those that are outside the desired range.

Another similar algorithm to that last variation, takes the results of the original sub-summary parts process, and loops through the results, omitting blocks of Sale records that don't match the desired range.