I'm having trouble figuring out how to lay out a report to produce the
results I need. Help would be much appreciated.

I need to create a report that looks like this:

Client info (name, address etc. go here)
Date Certificate No. Certificate details
...
...
...
Client info (name, address etc. go here)
Date Certificate No. Certificate details
...


The report is sorted by date, and within date by certificate number.
Certificate numbers are unique.

Clients can obtain an arbitrary number of certificates, and they can get
then all at one time, or on different days, or at different times within
one day. In other words, for any one client the certificate numbers are
not necessarily in sequence.

I want to print the client info only once for every batch of
certificates obtained together. I know I can put the client info in a
sub-summary part based on ClientName - but then I have to sort by
ClientName before sorting by certificate number, which alphabetizes the
client names and disrupts the certificate number sequence. If I reverse
the sort priority - certificate number then ClientName - the client info
gets printed every time the certificate number changes: that is, with
every single record.

What I need, in other words, is to sort by one attribute and group by
another. I know SQL can handle this; is there a way to make it work in
FileMaker?

I have done something similar, but this was with related tables, not with
sorted sub-summaries. I don't know if this will, help you, but this is what
I have done.

Created a new layout with header, body and footer. Header contains labels
that appear on top. It is not very big.

The body contains the main fields that are used in the relations. In my case
name and adress. It is made so small it contains a single row of fields.

The footer is the biggest part and contains all related information. It is
made so big that all three parts together will fit on one A4.

Keep well, Ursus

"Paul" <this (AT) that (DOT) there> schreef in bericht
news:this-8B7C60.10113103032007 (AT) news (DOT) nmix.net...

In article <this-8B7C60.10113103032007 (AT) news (DOT) nmix.net>, Paul
<this (AT) that (DOT) there> wrote:

"Sorting" and "grouping" are the same thing.

You've said it yourself above - what you're looking for is to sort the
records by ClientName and within that by CertificateNumber:
ie.
ClientName Ascending
CertificateNumber Ascending

You can sort by as many fields as you want, with each lower field being
a sub-sort of the records "grouped" by the fields above it.

You then need a report Layout that looks something like:


Header {if wanted}
-------------------------

Client info (name, address etc. go here)

Date Certificate No. Certificate details

Sub-summary by ClientName (Leading)
-------------------------

[CertDate] [CertificateNumber] [CertifcateDetails]

Body
-------------------------

Footer {if wanted}
-------------------------


With the cloumn headings in the Sub-summary part, you will only get
them when the ClientName changes.

As long as you sort the records in the above two-field order, the
report will print / Preview as you want.

The order of the sort fields must always match the order of the
Sub-summary parts, so in this case ClientName must always be first.

You can also use the same Layout to produce a report sorted by
ClientName and within that by Date simply by changing the second sort
field:
ie.
ClientName Ascending
CertDate Ascending


Helpful Harry
Hopefully helping harassed humans happily handle handiwork hardships ;o)

Ursus,

Thanks for your reply. I'll have to think about this, but I don't think
it will do what I need. Thanks for giving me a direction to investigate
though.

In article <45e9c2fb$0$43776$dbd41001 (AT) news (DOT) wanadoo.nl>,
"Ursus" <ursus.kirk (AT) wanadoo (DOT) nl> wrote:

In article <040320071131500602%helpful_harry (AT) nom (DOT) de.plume.com>,
Helpful Harry <helpful_harry (AT) nom (DOT) de.plume.com> wrote:

Harry,

Thanks for your reply. I don't think I'm understanding you right, or
maybe I didn't explain properly. Here's a concrete example of what it
should look like:

------------------------------------
Jones, Shirley; 123 Main, Anytown NE 11223
3/2/2007 #1 -detail-
3/2/2007 #2 -detail-

Able, Marty; 456 Washington, Anytown NE 11222
3/2/2007 #3 -detail-

Smith, Sol; 12 First Av, Anytown NE 11222
3/2/2007 #4 -detail-
3/2/2007 #5 -detail-
3/2/2007 #6 -detail-

Jones, Shirley; 123 Main, Anytown NE 11223
3/2/2007 #7 (she came back for one more)
------------------------------------

.... cert. numbers in order; client info only as needed.
But if I sort by client name then certificate number, I'll get this:

------------------------------------
Able, Marty; 456 Washington, Anytown NE 11222
3/2/2007 #3 -detail-

Jones, Shirley; 123 Main, Anytown NE 11223
3/2/2007 #1 -detail-
3/2/2007 #2 -detail-
3/2/2007 #7 -detail-

Smith, Sol; 12 First Av, Anytown NE 11222
3/2/2007 #4 -detail-
3/2/2007 #5 -detail-
3/2/2007 #6 -detail-
------------------------------------

.... clients alphabetized within the date; cert. number sequence
scrambled.
If I'm using a sub-summary on client name, but sort by certificate
number before client, then I get this:

------------------------------------
Jones, Shirley; 123 Main, Anytown NE 11223
3/2/2007 #1 -detail-

Jones, Shirley; 123 Main, Anytown NE 11223
3/2/2007 #2 -detail-

Able, Marty; 456 Washington, Anytown NE 11222
3/2/2007 #3 -detail-

etc....
------------------------------------

.... cert. numbers back in order, but client info printed each and every
time.
Does that make the problem any clearer? Thanks again for wrapping your
brain around it....

In article <this-7A975C.23001303032007 (AT) news (DOT) nmix.net>, Paul
<this (AT) that (DOT) there> wrote:


Ahhh ... not quite what I thought you were wanting. You really want one
of those silly "management" reports - so called because management
comes up with a bright idea that can't actually be done without a LOT
of messing about.

Bascially, you can't have a report like that.

Either the entire report has the Names in alphabetical order OR it has
the Certificate Numbers in numerical order ... but not both.



Helpful Harry
Hopefully helping harassed humans happily handle handiwork hardships ;o)