Daniel Shanahan Posted March 25, 2008 Posted March 25, 2008 I have a simple db with three tables: Clients, Janitors, and Accounts. Accounts is a join table. I have a problem in a report. The report is based in the child table (Accounts). I want to organize the report by a calculation field in the Janitors table, so the first break field is from the Janitor table. It counts the status of the Janitor's Insurance standing (Current, Expired, or Upcoming). Underneath that heading I have the Janitor in another sub-summary part and then their clients in the body part. I want to add a field on the first sub-summary line (Current, Expired, and Upcoming) that contains the number of Janitors in the next sub-summary section. However, nothing I've tried works. Summary fields, for instance, count the number of records in the child table; so, in the example attached, there are 8 janitor companies, but one has two clients. I want that section to indicate "8" rather than "9", but I can't figure out how. I've tried many combinations of summary and calculation fields, but no luck. You can see my attempts in the file, under Define Database/Fields in the "--- F A I L ---" section. Any insights? Example_copy.fp7.zip
comment Posted March 26, 2008 Posted March 26, 2008 This is all done from the same table that the report is based upon. However, the solution requires using the GetSummary() function - and GetSummary() cannot use a related field as the breakField. Therefore you need to change your sub-summary part by Janitor to use a "local" field instead (and make sure you sort by it, too). Example2.fp7.zip
Daniel Shanahan Posted March 27, 2008 Author Posted March 27, 2008 Thanks comment. I will have to study this a little more in-depth. I'm not very familiar with the GetSummary() function and had not considered that the solution might require several fields. Thanks. dan
Recommended Posts
This topic is 6077 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