Report to show company with list of contacts

[Steven Swallow]

Hi,

 

I'm trying to achieve something I expected to be straightforward, but after banging my head on a brick wall for 24 hours it's time to aask for help.

 

I have a fairly straightforward company table and a contact table. Related one to many as you'd expect. When browsing a layout based on the companies table, I can see all the related contacts via a portal.

 

I've never really done any reporting, but I wanted to create a report layout that gives me the company details at the top, and lists the contacts underneath. I expected this to be relatively simple, but I can't get it to work.

 

I have the folowing report layout based on the companies table.

 

Parts:

Header - cust has freetext field as a title and the date

Sub-summary by txt_Surname (Leading) - This contains all the company address details as merge fields

Body - contains the contact details as merge fields (name, job title, email, telephone and so on)

Footer - page number

 

Now I have tried all manner of different combinations to try and get what I want. Basing it on the contacts table, different sorting and so on. When doing test prints it often shows just one contact under a company when I know there are more, and it some cases it shows too many.

 

This is the first time I've tried reporting of any sort in FileMaker, so I'm really stumped by this so any help you can give me would be appreciated...I do keep thinking about a portal for the contacts, but I can see how this would work if the portal isn't big enough to accomodate a company with a hundred contacts in it, where other companies may only have one contact.

 

Any help you can give me will be greatly appreciated.

[eos]

I've never really done any reporting, but I wanted to create a report layout that gives me the company details at the top, and lists the contacts underneath. I expected this to be relatively simple, but I can't get it to work.

 

I have the folowing report layout based on the companies table.

 

You need to base your report layout on the Contacts table, because that is where each contact is a record that you can use as the body part. Using a list view means that your report is (obviously) as long as it needs to be to display your found set of contacts.

 

In the sub-summary part you can display the related company data.

[milanm]

If it is a One Company to Many Contacts relationship, i would base the report layout on CONTACTS and create a leading Sub-summary by what ever is the reference to the company that a contact belongs to ( this would be the field in contacts that defines/identifies the relation to the particular company, not the merged field ). Then you have to SORT the records by that field. In the Subsumary part on the layout you could put the  txt_Surname or any other field from the related company table (this could be a merged field). In the Body part you should put the data you want to show for contacts (Name, Surname, Phone etc...).

This should give you a list with Company name on the top (in the Sub-summary field) and all the contacts related to that company underneith (in the body part).

[Steven Swallow]

If it is a One Company to Many Contacts relationship, i would base the report layout on CONTACTS and create a leading Sub-summary by what ever is the reference to the company that a contact belongs to ( this would be the field in contacts that defines/identifies the relation to the particular company, not the merged field ). Then you have to SORT the records by that field. In the Subsumary part on the layout you could put the  txt_Surname or any other field from the related company table (this could be a merged field). In the Body part you should put the data you want to show for contacts (Name, Surname, Phone etc...).

This should give you a list with Company name on the top (in the Sub-summary field) and all the contacts related to that company underneith (in the body part).

 

I was close to this, but I think I see where I might have been going wrong. I wasn't creating the sub-summary part by the fields that defined the relationship, just the surname field. I'll try tweaking it as you've specified above and see if that works.

 

Thanks for your help (and eos).

[Steven Swallow]

Yup, that worked. Thanks both.