Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.databases.filemaker > #432 > unrolled thread

FM 11 - Emailing lists

Back to article view | Back to comp.databases.filemaker

Contents

  FM 11 - Emailing lists Doug Anderson <douga@fcstone.com> - 2011-10-12 15:48 -0500
    Re: FM 11 - Emailing lists yourname@yourisp.com (Your Name) - 2011-10-15 12:28 +1300
      Re: FM 11 - Emailing lists Doug Anderson <douga@fcstone.com> - 2011-10-20 16:49 -0500
    Re: FM 11 - Emailing lists cortical <cb@corticaldata.com.au> - 2011-10-15 10:28 +1030

#432 — FM 11 - Emailing lists

I have a database with two tables.

Table one consists of just one field, its an email address.  There are 
no duplicate email addresses, each is unique.

In the second table I have two fields, one is email address which is 
linked to table 1 and the second is a customer name.

In layouts I can create a portal that shows for each email address all 
the customer names.

What I want to do is carry that over to the Send Mail function; but 
I've been unable to figure that piece out. How do I insert a 
Calculation that will pull each record from table 2 where the email 
address matches and in the body of the email list the values in list 
form from the customer names field?

[toc] | [next] | [standalone]

#433

In article <2011101215485852723-douga@fcstonecom>, Doug Anderson
<douga@fcstone.com> wrote:

> I have a database with two tables.
> 
> Table one consists of just one field, its an email address.  There are 
> no duplicate email addresses, each is unique.
> 
> In the second table I have two fields, one is email address which is 
> linked to table 1 and the second is a customer name.
> 
> In layouts I can create a portal that shows for each email address all 
> the customer names.
> 
> What I want to do is carry that over to the Send Mail function; but 
> I've been unable to figure that piece out. How do I insert a 
> Calculation that will pull each record from table 2 where the email 
> address matches and in the body of the email list the values in list 
> form from the customer names field?

You already have a Relationship link from Emails -> Names, so you're half
way there.

Now you can create a new Calculation field in the Emails Table that
retrieves all the Names via that Relationship using the List function (it
depends on what version of FileMaker you're using as to what this function
is actually called).
e.g.
      EmailNames     Calculation, Text Result, Unstored
         = List (Relationship::Name)

This will cause the EmailNames field to be given a copy of all the related
Names, separated by a carriage return character.

For example, if you had data like:

    Email Table:      Flinstones@Bedrock.com
                      Rubbles@Bedrock.com

    Names Table:      Flinstones@Bedrock.com      Fred
                      Flinstones@Bedrock.com      Wilma
                      Rubbles@Bedrock.com         Barney
                      Flinstones@Bedrock.com      Peebles
                      Rubbles@Bedrock.com         Betty
                      Rubbles@Bedrock.com         Bam-Bam
                      Flinstones@Bedrock.com      Dino

Then the new EmailNames field in the Email Table for the two records would
have this data:

    Fred                 
    Wilma
    Peebles
    Dino

and

    Barney
    Betty
    Bam-Bam

The order of the Names will depend on the sorting of the Relationship.

This Field can then be used when emailing - you can of course replace the
carriage return character with commas or whatever else you want using the
Substitute function.

Helpful Harry   :o)

[toc] | [prev] | [next] | [standalone]

#443

On 2011-10-14 23:28:24 +0000, Your Name said:

> In article <2011101215485852723-douga@fcstonecom>, Doug Anderson
> <douga@fcstone.com> wrote:
> 
>> I have a database with two tables.
>> 
>> Table one consists of just one field, its an email address.  There are
>> no duplicate email addresses, each is unique.
>> 
>> In the second table I have two fields, one is email address which is
>> linked to table 1 and the second is a customer name.
>> 
>> In layouts I can create a portal that shows for each email address all
>> the customer names.
>> 
>> What I want to do is carry that over to the Send Mail function; but
>> I've been unable to figure that piece out. How do I insert a
>> Calculation that will pull each record from table 2 where the email
>> address matches and in the body of the email list the values in list
>> form from the customer names field?
> 
> You already have a Relationship link from Emails -> Names, so you're half
> way there.
> 
> Now you can create a new Calculation field in the Emails Table that
> retrieves all the Names via that Relationship using the List function (it
> depends on what version of FileMaker you're using as to what this function
> is actually called).
> e.g.
>       EmailNames     Calculation, Text Result, Unstored
>          = List (Relationship::Name)
> 
> This will cause the EmailNames field to be given a copy of all the related
> Names, separated by a carriage return character.
> 
> For example, if you had data like:
> 
>     Email Table:      Flinstones@Bedrock.com
>                       Rubbles@Bedrock.com
> 
>     Names Table:      Flinstones@Bedrock.com      Fred
>                       Flinstones@Bedrock.com      Wilma
>                       Rubbles@Bedrock.com         Barney
>                       Flinstones@Bedrock.com      Peebles
>                       Rubbles@Bedrock.com         Betty
>                       Rubbles@Bedrock.com         Bam-Bam
>                       Flinstones@Bedrock.com      Dino
> 
> Then the new EmailNames field in the Email Table for the two records would
> have this data:
> 
>     Fred
>     Wilma
>     Peebles
>     Dino
> 
> and
> 
>     Barney
>     Betty
>     Bam-Bam
> 
> The order of the Names will depend on the sorting of the Relationship.
> 
> This Field can then be used when emailing - you can of course replace the
> carriage return character with commas or whatever else you want using the
> Substitute function.
> 
> Helpful Harry   :o)

Thank you for the help, that worked perfectly.

[toc] | [prev] | [next] | [standalone]

#434

On 13/10/11 7:18 AM, Doug Anderson wrote:
> I have a database with two tables.
>
> Table one consists of just one field, its an email address. There are no
> duplicate email addresses, each is unique.
>
> In the second table I have two fields, one is email address which is
> linked to table 1 and the second is a customer name.
>
> In layouts I can create a portal that shows for each email address all
> the customer names.
>
> What I want to do is carry that over to the Send Mail function; but I've
> been unable to figure that piece out. How do I insert a Calculation that
> will pull each record from table 2 where the email address matches and
> in the body of the email list the values in list form from the customer
> names field?
>

t1 = Email
t2 = Customer


Names are NOT a sound way to implement keys; you need a customer_id in 
each table.

So in Customers the primary key is customer_id; defined as an auto 
enter, serial, unique, no modify ( the standard primary key definition)


I Emails, the pk is email_id, also defimed as a as an auto enter, 
serial, unique, no modify ( the standard primary key definition).
Also in Email table, a customer_id 'foreign key', text field


So a REL from Email to Customer will use customer_id to customer_id


Use the List function:  List( Customers::name) to calculate teh list of 
related names, for a given email record

[toc] | [prev] | [standalone]

Back to top | Article view | comp.databases.filemaker

csiph-web