Complicated relationship ???

[kiwiora]

Hi,

Thanks for any help in advance.. my brain has fried so hope you may be of assistance. My quandry is as follows.

I need to generate letters to be sent to each borrower, at each specified address.

For example, if I have borrower 1 2 & 3 (B1, B2 and b3), and addresses A B C and X (AA, AB & AC and X) then I need to select which borrower can be found and which (mutliple) address with the security address being mandatory (X) i.e

B1 has addresses AA, AB & AC (and X)

B2 has addresses AA & AC (and X)

B3 has addresses AB & AC (and X)

I attach a file which I hope clarifies it a bit more. I have purposely created separate fields (borrower 1, borrower 2) instead of using a portal for borrower's names as this data needs to be exported into a word document independently - thus having to identify the exact field data. However as this letter is so ... arrrccckkk i'm going to generate these from filemaker.

I hope that makes it clear as to what i'm trying to accomplish

help.zip

[aaa]

May be will be better you have one table for Borrowers and one for Addresses?

Is not clear construction of your tables.

[SlimJim]

I agree with aaa. You need the two tables Borrowers and Address and a third table Letters which acts as a join table. So each record in letters contains one borrowerID and one addressID. Using a letter layout, from the letters table, send all the letters by printing out all records.

The thing I am puzzled about is your Borrower table where each record contains a list of borrowers. There must be a reason why you did this and it may well negate the suggestions here.

[kiwiora]

aaa - I do have a separate table for borrowers and one for addresses

SlimJim - Yes there is a reason why I have one record containing all borrowers - as mentioned in my post I need to export this data and use each field referenced in word - something you can only do if you have identified the field and its value (i.e on a form where I want borrower 1's name to appear, insert field borrower1, if it was related it would be the same field name etc etc.

As for agreeing AAA - as I said I do have two tables. My problem is generating the letter to each borrower, at each address selected.

[Fenton]

If you want 1 letter to each address then you would be mailing from the Addresses table. My rule of thumb is to go to the table with the right number of records (or create one if none exists). You can fairly easily reference the letter, via a relationship, if it is a bulk letter, ie., the same letter to everyone.

If it is not, or if you want a "history" of letters sent, then you need a "Sent" table. You'd import the IDs, AddressIDs and LetterIDs, into that table and mail from there. A creation date gives you a history.

In FileMaker 7 you can run text through calculations and formatting is preserved. This makes referenced calculated letters work well. If these letters are "layout-based" then they'd have to be in the table they're printed from. The "field-based" methods above will work for email also.

[kiwiora]

Creating the letter to each address is the easy bit. The thing I'm trying to get my head around is duplicating that address as selected (different each record) to each borrower. So you have five addresses, three borrowers, but the resulting letters might actually be anywhere from 5 to 15 dependent on where each borrower can be found at which address(es).