one-to-many and letters

[kskalemba]

I'm new to this, so I apologize if I'm over explaning things.

I have 3 DBs that are relational

Here is the pertinent info about those DBs

DB A: Has a ID field (named AddressID) and and a text field (called Address). The ID is unique.

DB B: Has the aforementioned ID field, another unique ID field (called PackageID), a date field and a unstored calculation of the Address field. So we have a one Address to many Packages.

DB C: Has the previous ID fields (Address, Package) and another unique ID field (called ItemID) and a text field (called ItemName).

Same idea as above - For each Package there are many Items.

Now the problem.

I do a find in DB C on the date field for the packages I did that day. Works fine.

Now I switch to a letter layout.

The Address field is in the header (along with the form letter stuff.)

The ItemName field is in the body.

What happens:

A list of all the Items associated with that date and ONE of the Addresses.

The desired result:

An individual letter for each Address with the ItemNames associated with that Address.

I don't want to use a portal and do this in DB B, because I'll be limited by the amount of ItemNames in the portal (and hence the amount of space left on the letter.

Thanks,

kskalemba

[CobaltSky]

Hello kskalemba,

In FileMaker terms, what you are trying to achieve is described as a sub-summary - ie you want the various items found in DB C to be separated into groups according to the associated address, then printed out with the address above them.

To achieve that, you should go into layout mode and double click on the tab which labels the 'header' part. That will bring up the parts dialog box. In the parts dialog, change the header to a leading sub-summary part to appear when the found set is sorted by the AddressID field.

Once you've done that, return to browse mode, sort the database by the address field, then go have a look at it in Preview mode. I believe that what you see will then be a lot closer to what you're looking for.

[kskalemba]

Ray,

I changed the header to a leading sub-summary by the addressID field. I left the Items in the body.

I performed a find (on the date field - mentioned in my first post).

I then sorted by the addressID and then went to preview mode. it looks OK, but I should be getting 7 different address with their corresponding items, (11, first address has 2 items, second address has only one, etc). However, I'm only see 4 of the address with their items (happens to be 6 items) I went to preview mode and all 11 items are there.

I then checked another layout (table view) and all the correct data is there.

any idea why some of the stuff is not appear in the preview mode?

Thanks for you help,

Keith (kskalemba)

[CobaltSky]

Your message is not very clear. First you seem to be saying that only six items are showing in preview mode, but then you say that "I went to preview mode and all 11 items are there". This seems contradictory?

However if in fact some record do not seem to be showing up in preview mode, I suggest that you check which items are present in the found set (only found items will be previewed). Also check the contents of the field you are sorting by. And do make sure that you have gone through all the pages in preview mode - that there is not a further page with the remaining addresses on it.

[kskalemba]

Ray,

I know what's happening. In layout mode, there is this dotted line and a grayed out section which places the next record in another column. That line was moved off the edge of the page, so that the other records were not visible.

I moved the dotted line to the middle of the page and saw the other records.

I would like to have the dotted line in the body so that the Items field can go in two columns, but the other stuff (address and form letter) in the sub-summary to be one record for each page.

Keith

[CobaltSky]

Hi Keith,

By default, layout columns in FileMaker are applied only to the body part, so the header, sub-summary and footer (if any) should not be affected.

To get the output to group contents appropriately, double-click on the sub-summary part flag and select the option for 'Page break before each occurrence' which appears in the lower part of the dialog. With that in place, when you preview or print (with the database appropriately sorted) each sub-summary should start a new page and should be followed by the columnized body part listing all the items which relate to the sub-summary.

[kskalemba]

Ray,

I got it! In the layout setup I changed the print in 2 columns to across instead of down.

Thanks for all your help.

Keith