Report w/ grouped data

[SGT]

Hi everyone,

I'm working with a database that keeps track of companies and contracts that we have with them. Each record is for a separate company, and within each record I have a list of contracts. I'm trying to create a columnar report that lists each company *and* all of the contracts for each company (plus a few other fields, contact data for each contract).

Essentially I want to have something like this:

CompanyA

////Contract1////Phone#

////Contract2////Phone#

////Contract3////Phone#

CompanyB

////Contract1////Phone#

CompanyC

////Contract1////Phone#

////Contract2////Phone#

(where //// represents a tab)

Within each record, I have a portal showing the related data (the contracts). Whenever I add "contract" to my columnar report, it only shows the first contract, not all of them. I thought "columnar report w/ grouped data" might do the trick, and I have tried using "sub-summary fields," but can get neither to work. They still only show one contract.

Another problem is that there is not a set # of contracts for each company. I would really appreciate whatever suggestions you guys have. Thanks!

[comment]

Your report needs to be done from a layout of the Contracts table - with a sub-summary by Company.

[SGT]

I created a new layout, created a sub-summary sorted by Company, put company in the body, and the contract field in the sub-summary. But obviously that's only going to give me one contract per company.

Can you explain what you mean by "it has to be done from a layout of the contracts table"? Thanks!

[comment]

The layout must be defined (in Layout Setup) to show records from the Contracts table (or rather table occurrence). The (local) fields from Contracts go into the body part, and the (related) fields from Companies go into the sub-summary by Company part. After sorting by Company, you'll get a report like this:

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

Company A (sub-summary)

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

• Contract 1 (body)

• Contract 2

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

Company B

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

• Contract 3

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

Company C

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

• Contract 4

• Contract 5

[SGT]

I'm getting farther, but still not there. I did what you said -- created a sub-summary by company, put the company field there, and put the contract field into the body part. I'm still only getting the first contract though.

However, when I inserted a portal, chose the contract field, and put that into the body part, it shows *all* of the contracts. The only problem with that is that there is a set number of rows in the portal, so either there will be too many or not enough rows to show all the contracts.

What I'm really trying to do is set this up in a columnar report, because I want to be able to print this off. If I can figure out how to set up the portal so that for each record it shows the exact number of contracts for the related record, I should be set.

Edited January 2, 2009 by Guest

[IdealData]

Also bear in mind that the report will only:

1. Produce data based on the CURRENT FOUND SET from CONTRACTS.

2. Only works in PREVIEW mode or when PRINTED.

You must also SORT the data by COMPANY.

I suspect you are falling foul of point 2. As you said you put a portal on the layout and that works fine because portals work in all modes.

For reference most of us consider REPORTS to be PRINTED or PREVIEWED.

Edited January 2, 2009 by Guest

[SGT]

Thanks for the advice. I was looking at the report in preview mode, and they are sorted by company. I'm still only getting the first contract for each company though.

[comment]

Why don't you post your file.

[SGT]

Sure. It may give you an error when opening about not finding related files, but it will open anyway. Note that I didn't create this database but am modifying it. The two fields I'm concerned with are "company," in the contacts table, and "name of contract," in the contracts table. If you notice on the layout view, the contracts portal is listed as "cntc_INTRT"--I wasn't sure how to change it to cntc-contract, but that doesn't really matter.

I appreciate all your help in advance, especially putting up with a FM novice like me.

Company_contracts.fp7.zip

[comment]

Which layout have you been working with? I don't see any that has a sub-summary part. And I don't know why you keep referring to a portal. The idea here is to print a list of contracts directly from the Contracts table - no portals are required for this.

[SGT]

Sorry, the file attached to this post has a layout with sub-summary sorted by company (layout #50). I must be doing something incredibly wrong because when I put company field in the sub-summary section and then contracts in body, companies don't even show up.

Company_contracts.fp7_2.zip

[comment]

The layout must be defined (in Layout Setup) to show records from the Contracts table

Your layout is showing records from CNTCT, which is a TO of Contacts - not Contracts. The field "Name of Contract" is a related field, and the field "Company" is a local field. It must be the other way round.

[SGT]

OK. How do I go about fixing that then? I understand what you're saying but am not sure how to go about doing this.

[comment]

Change the table occurrence in Layout Setup to one of the TO's of Contracts (I cannot advise you on which one, because your graph has so many*). Then double-click each field in turn, and change the TO as well.

---

(*) Perhaps it would be easier for you to start with an new empty file, rather than trying to modify an existing complex one.

[SGT]

Aha! One last problem. I changed it like you said, and now it is listing all of the contracts for each company. However, the company names (company field in sub-summary portion sorted by company) are still not showing up, even in preview mode. Any idea on why it might be doing this?

[comment]

Probably because there's no relationship between the two TO's you have picked (or the relationship is not a direct one - again, there is a price to pay for having such a complex graph). Also make sure that the sub-summary by ... and the field you sort by are the same.

Edited January 2, 2009 by Guest

[SGT]

Ahh, gotcha. OK. Rather than fight with it more, I just have company and contract fields in the body, but since contract is now a local field, it's showing all of them. That's essentially what I wanted anyway. Thank you so much for your help!!