Sub-summary confusion

[skrying]

I'm building a database that tracks companies and their employees. I'd like to create a report that lists all employees by their company.

Here are the relevant relationships:

Company::id = R_Employee_Company::companyId

R_Employee_Company::employeeID = Employee::id

and the other relevant fields:

Company::name

R_Employee_Company::title

Employee::name

Here's how I thought I would do this, i.e. here are the sections on the report, top to bottom:



Sub-summary when sorted by Company::name

- contains Company::name field



Body

- contains Employee::name and R_Employee_Company::title fields



Now, this will list all employees at all companies, but it does so with one company entry per employee, i.e. instead of listing a company followed by all of it's employees, its lists the company name every time.



To make this clearer, it does this:

Apple    Steve Jobs

Apple    Mike Matas

Microsoft    Bill Gates

Microsoft    Steve Ballmer

when what I want is this:

Apple

        Steve Jobs

        Mike Matas

Microsoft

        Bill Gates

        Steve Ballmer

A couple of extra points: This behavior doesn't change if I alter the sub-summary section to sort by a different field, and it doesn't change if I change the default table for the layout.

Not sure whey this isn't working, but any help would be appreciated.

[Ender]

Your parts are correct, so you should check that the fields are completely contained within their relevant parts. The sort order will need to include Company::Name for that sub-summary part to show up, and it will only show in Preview Mode or in printouts.

[skrying]

I don't know why, but this is the only combination that works:

Layout Table type: Employee

Sub-summary when sorted by: Company::name

*shrug*

Edited June 5, 2006 by Guest

[Genx]

The reason thats the only combination that works:

If you attempt to report from company accross the one-to-many relationship, filemaker will only utilize a one-to-one relationship when presenting the data so only the first "employee" will be picked up for each company.

However, if you report from employees, you get every employee, and seeing as each employee is only related to one company, the report presents correctly... the sorting is just an extra element.

[Ender]

Hmm, since this is a many-to-many relationship (Company -< R_Employee_Company >- Employee,) you should base the report layout on the join table (R_Employee_Company.)

[Genx]

I missed have missed something.. There's a join table? Why on earth is there a join table. Oh well, ignore my previous response in that case.

[Ender]

Why on earth is there a join table.

Good question.

[skrying]

There's a join table so that each company can have many employees, and each employee can work for many companies.

[skrying]

I set the main layout table to R_Employee_Company and it seems to work fine.

Not sure why I couldn't get it working earlier. :

[Genx]

Weird. Who's perspective are you building the database from. The employee's / contractors or the company's.

I'm not questioning your structure, i'm just trying to find a real work example where an employee will work for multiple companies.. besides contracting.

[Ender]

I was thinking a Temp agency?

[Genx]

Yeah but still even in the case of a temp agency, wouldn't you either assign the employees specifically from the company side or specifically from the employee side.

I.e. find all your un busy employees and assign them to a company or simply assign your un-busy (sorry) employees directly through a filtered value list from the company.

Still just being curious here.

[skrying]

Its for a conference that runs every couple of years. We have many attendees from certain companies and some attendees own or work for multiple companies.

e.g. Steve Jobs is CEO at Apple Computer, and is on the Board of Directors at Disney.

We also track past employment.

Edited June 7, 2006 by Guest

[Genx]

Hmmmm, fair enough.