How to show found records from related table?

[macamaca]

Table1 with Field1 and Field2 uses Layout1.

Related Table2 uses Layout2.

After finding a select number of records in Layout1, how can I show (and use in an email) those found fields in Layout2?

(My database is more complicated, but simplifying it as above will hopefully suffice for this newbie question.)

Thanks in advance.

[Vaughan]

Use the Go to Related Records script step. This can show the related records for either the current record, or the found records. You'd use the found records option.

[comment]

Turning a found set into a related set is not a trivial task.

See:

http://www.nightwing.com.au/FileMaker/demos8/demo804.html

http://fmforums.com/forum/showtopic.php?tid/182509/post/231005/#231005

http://fmforums.com/forum/showtopic.php?tid/197149/

[macamaca]

I'm still stuck with this...

[Vaughan]

Have you tried a script with the GTRR step as was earlier suggested?

[macamaca]

Yes, but without success.

Found records from Layout1 do not show up in "FoundRecords" field in Layout2, no matter what I try.

(I really appreciate your assistance.)

Edited October 13, 2010 by Guest

[LaRetta]

As I mentioned on the other forum, you are not providing enough information and phrases such as 'didn't work' tell us nothing. Please provide detailed information and/or attach your file. Remember that we cannot see through internet waves to your solution. We only know what you type in your words. :wink2:

[Vaughan]

Table1 with Field1 and Field2 uses Layout1. Related Table2 uses Layout2. After finding a select number of records in Layout1...

How are table1 and table2 related?

Found records from Layout1 do not show up in "FoundRecords" field in Layout2, no matter what I try.

Going to the related record in Table2 will not show the records in Table1. It will show records from table2 because you're now on layout 2

It is time to post your database to the forums and explain what you *really* want to do. No more "table1" stuff.

[macamaca]

I am not able to properly describe things, suffice to say its the first table contains over 1500 contacts, the second table has no records yet.

My intentions were to:

1. Find a group of contacts in Table1, then

2. Execute a script from Table1 that would capture the Name, Company and Email fields, then create a new record in Table2 (with a scrolling non-editable text field that simply shows those found contacts - just for the user to see).

The Table2 layout would also have email subject and email body fields as well as a couple of containers for attachments. Then

3. Generate the email (which commits the new record in Table2) and send to those found from Table1

The relationship between the two tables is not yet defined. I'm not even sure they need to be related...?

Apologies, for the non-programmer speak.

Any assistance greatly appreciated.

[Vaughan]

A skill worth nurturing is the ability to describe a need without any implementation detail -- by that I mean by NOT talking about fields or tables.

Stripping all the implementation detail out, your intentions are:

Send an e-mail to a set of contacts.

You talk about capturing the company name: why? Is it used in the e-mail message? If so then the e-mail must be different for each contact, so it needs to be more than one e-mail.

Explain more about *what* you want to do, not *how* you imagine it will be done.

[macamaca]

Thanks.

Single email to all found contacts.

Name and Company info is just for the user to see before sending the email.

[comment]

So why not do this from the Contacts table itself?

http://www.filemaker.com/11help/html/scripts_ref2.37.64.html#1029642

[macamaca]

Thanks.

The purpose of the second table is to keep a record of all the emails sent (with referenced attachments) and to who.

Edited October 14, 2010 by Guest

[comment]

Do you want a separate record for each recipient, or just one record with a list of all recipients (i.e. the current found set of contacts)?

[macamaca]

One record (of the email sent) showing who received it.

[comment]

How many contacts do you estimate will be in the found set?

[macamaca]

Thanks.

Probably ~200 max.

Using FileMaker's built-in email facility.

Also, I've checked with out IT, and the SMTP server can easily handle that many.

[Vaughan]

Also, I've checked with out IT, and the SMTP server can easily handle that many.

The issue is showing all 200 recipients in a useful manner.

No doubt, at some stage the requirement will be to see which e-mails the contacts have received. Putting all the recipients into a text field isn't the optimal way of doing that.

[macamaca]

Sorry.

I should have written that 200 would be the exception.

Probably ~50 for most emails.

[comment]

With only 50-200 records, you could collect the ID's of the found contacts like this:

# After finding the contacts, while still in the contacts table

Go to Record [First]

Loop

Set Variable [ $ids ; List ( $ids ; Contacts::ContactID ) ]

Go to Record [Next, Exit after last]

End Loop

# Now you have a return-separated list of the found contacts' IDs in the $ids variable, so you can:

Go to Layout [eMails]

New Record

Set Field [ eMails::ContactIDs ; $ids ]

...

There are more efficient methods to collect the IDs (see the links in my first post), but this should work find with a small found set.

The relationship between the two tables is not yet defined. I'm not even sure they need to be related...?

Yes, they do. They should related as:

eMails::ContactIDs = Contacts::ContactID

Note that the relationship between Contacts and eMails is many-to-many. Strictly speaking, there should be a join table with a separate record for each e-mail/recipient combination. Basing the relationship on a return-separated list is a short-cut that will work - but some limitations will apply (Vaughan mentioned one of them).

[macamaca]

Thank you very much Mr Comment.

I will give it a whirl, later today and report back.

Cheers.

David.

Edited October 15, 2010 by Guest

[macamaca]

... at some stage the requirement will be to see which e-mails the contacts have received...

You're probably right.

I imagine that could be achieved by post-processing an export (Excel) file.

I like Comment's simpler approach which suits me as a novice.

Edited October 15, 2010 by Guest

[macamaca]

With only 50-200 records, you could collect the ID's of the found contacts like this:

Many thanks Comment.

Worked a treat.

Got all contact names and their organisations showing on the new record, and most importantly all the email addresses (in a hidden field). I can now generate an email, BCC'd to all.

Next problem is sending attachments and/or HTML emails, but I guess that's where an FM plugin would be needed, I suppose.

Edited October 18, 2010 by Guest