3 Replies Latest reply on Oct 16, 2016 1:19 PM by philmodjunk

    get all data field from all records from related table to send mail

    franknamal

      I have 2 tables. In one are my adresses and the other one I use to make a simple bookings. (Volunteer schippers on a small canalboat in Amsterdam ;-) They are related and that just works fine.

       

      Now I would like to send all schippers from the adress-table an email with the booking to see if they are available.

      First I just used the send mail function, but then it just finds the emailadresses I used in older booking-records, not all mailadresses from the adresstable.

       

      Then I found this script:

      Set Field [MainTable::Combine; List (RelatedTable::email)]

      But now I just get one emailadress (the one related to the ID)

       

      I want to get all mailadresses from the adress-table inserted in a field and then use this to send mails to all my schippers to see if they have time to steer the boat.

       

      Thank you for helping.

      Frank

        • 1. Re: get all data field from all records from related table to send mail
          philmodjunk

          That list function should give you email addresses from the records linked to the current main table record. However, the addresses will be separated by returns which is not a format that works with send mail if you refer to it in the TO box of Send Mail.

           

          You can use the substitute function to replace the returns with semicolons to get a list in a workable format.

           

          If you really are getting only one email address, then there is only one related record and you'll need to take a closer look at your data and relationships.

          • 2. Re: get all data field from all records from related table to send mail
            franknamal

            This is what I have:

            A file with 2 tables:

            1. adres-table with 2 fields: ID (with auto enter serial number), one field with 'email' (text)

            I created 3 records .

            2. booking-table with 2 fields: IDadresfk (number, no auto enter) , field Combine (text) (In layout I added multiple rows)

             

            I created relation from ID (from acres-table) to IDadesfk with the = sign. in the Manage database/Relationships

            I created the script: Set Field [booking::Combine; List (adres::email)]

            What happens: I have to enter a number in IDadresfk, then run the script gives the just one, the corresponding emailadres, not a combination of all.

             

            Thanks!

            • 3. Re: get all data field from all records from related table to send mail
              philmodjunk

              It is doing exactly what you designed it to do instead of doing what you want it to do. From any record in booking, there is one and only one matching record in adres and so your function only returns a single email address.

               

              If you are using Send Mail, take a look at the options you have. There are two approaches that provide you with a way to send an email to all records in your found set, so you can do a find on the Bookings layout and send to an address from Adres, but across all the bookings records in your found set. Either option should, if I understand you, do what you need here.

               

              Option 1: Chose the option to send "multiple emails (one for each record in found set)"

               

              Option 2: Or leave the single email option selected and check the check box that reads "Collect addresses across found set".

               

              With either option, you can refer to the email address field in adres as the email address to use in the TO box.