Get Email Address from Joined table

[jesperlowe]

Trying to put together a Donation/Collection Database and i aced the e-mail participants part where we e-mail all participants from one table with names and e-mails.

Now I have created a new table (collection) with a a databse join on the "Person_id" With name as the showing field, a cascadinglookup for the e-mail adresse... but still based on the "person_id". Now i want to add a "list email" plugin on the (collction) list that will email the participants with their collected amount of money..

But i get their "person_id" in email when i try emailing them (because i pointed to the "person_id" field i guess)

So question is

How do i get the email list field to look at the "person_id_email" field and not the "Person_id"

 

[Duke3D]

Having a bit of a challenge understanding the issue. What I think you are describing is:

You have a Person table. The primary key is Person_ID. In that table you have an e-mail field.

You have a Collection table. The primary key is a transaction number. It has details about the transaction including a field called Person_ID that contains the ID of the Person involved in the transaction.

When you are generating emails from the Collection table, you are able to include the Person_ID field, but not their e-mail.

In the Collection table you want to add an element of type databasejoin. Under the Data tab you will identify Collection as the "join from" table and Person as the "join to" table. Select the Person_ID field in the Collection table so it will operate as the foreign key identifying the person to be looked up. Select the Person_ID field in the Person table as the primary key to find the matching record in that table. Down at the Label field, select the email field as the data to lookup and display when viewing the Collection table.

If you need more than one field brought into the Collection table, then create additional elements and use the autofill plugin. Only one database join is needed and allowed. Autofill uses an existing databasejoin element to lookup and fill in more fields from the same joined table. So, as an improved design, the databasejoin element might use the person's name as the label so you could select the person by name using a dropdown. Then use autofill elements to copy in their email and other information so all the data you need for your email is available in the one table - no join code needed to generate the email itself. The autofill elements act like a cache or snapshot of the data from the joined record. Just be aware that therefore the data can get old / out of sync if the Person table gets edited without a join; without the Collection records being updated at the same time.

 

[jesperlowe]

Thank you i used the cascading lookup and it works the same way... my problem was actually that i pointed my e.mail field to the person table and not the collection table, s? problem was solved otherwise.

But i think ill give the autofill a go and see if its better at the job then the cascading lookup.

The "old" data is no issue as we create a new have them sectioned by year.