SGT Posted December 30, 2008 Share Posted December 30, 2008 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! Link to comment Share on other sites More sharing options...
comment Posted December 30, 2008 Share Posted December 30, 2008 Your report needs to be done from a layout of the Contracts table - with a sub-summary by Company. Link to comment Share on other sites More sharing options...
SGT Posted December 31, 2008 Author Share Posted December 31, 2008 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! Link to comment Share on other sites More sharing options...
comment Posted December 31, 2008 Share Posted December 31, 2008 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 Link to comment Share on other sites More sharing options...
SGT Posted January 2, 2009 Author Share Posted January 2, 2009 (edited) 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 Link to comment Share on other sites More sharing options...
IdealData Posted January 2, 2009 Share Posted January 2, 2009 (edited) 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 Link to comment Share on other sites More sharing options...
SGT Posted January 2, 2009 Author Share Posted January 2, 2009 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. Link to comment Share on other sites More sharing options...
comment Posted January 2, 2009 Share Posted January 2, 2009 Why don't you post your file. Link to comment Share on other sites More sharing options...
SGT Posted January 2, 2009 Author Share Posted January 2, 2009 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 Link to comment Share on other sites More sharing options...
comment Posted January 2, 2009 Share Posted January 2, 2009 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. Link to comment Share on other sites More sharing options...
SGT Posted January 2, 2009 Author Share Posted January 2, 2009 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 Link to comment Share on other sites More sharing options...
comment Posted January 2, 2009 Share Posted January 2, 2009 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. Link to comment Share on other sites More sharing options...
SGT Posted January 2, 2009 Author Share Posted January 2, 2009 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. Link to comment Share on other sites More sharing options...
comment Posted January 2, 2009 Share Posted January 2, 2009 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. Link to comment Share on other sites More sharing options...
SGT Posted January 2, 2009 Author Share Posted January 2, 2009 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? Link to comment Share on other sites More sharing options...
comment Posted January 2, 2009 Share Posted January 2, 2009 (edited) 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 Link to comment Share on other sites More sharing options...
SGT Posted January 2, 2009 Author Share Posted January 2, 2009 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!! Link to comment Share on other sites More sharing options...
Recommended Posts
This topic is 5589 days old. Please don't post here. Open a new topic instead.
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now