5 Replies Latest reply on Jan 17, 2011 9:03 AM by philmodjunk

    A bit of guidance on an e-mail issue



      A bit of guidance on an e-mail issue


      Thanks in advance for looking at this.  I've just been developing my first FileMaker app, and have it mostly done.  The app creates a field trip report, and needs to save the report as a PDF and e-mail it to a list of people.  I have the main table, then an e-mail table, with 1 record per possible recipient, with their name and e-mail address.  For each trip report screen, I have a portal that captures the e-mail addresses that the report should go to. 

      The main table is unique by report#.  The e-mail table (used for lookups) has 1 record per e-mail address.  There is a table in the middle, related to the main table and also to the e-mail table.  I call this table "EmailReportList".  The fields in it are report # and e-mail address.  So this table could have, say, 5 records for report # 1003.  Some of the same e-mail addresses could be in report 1004, 1005, etc.

      If the user is in, say, report 1003, and there are 5 records in "EmailReportList" that have report 1003 in them, when the user clicks on "Send report", I want it to create an e-mail, with all 5 addresses in the "To" field, create the .PDF document, and then send the e-mail out. 

      Another quick issue:

      I am populating the "EmailReportList" table by doing a lookup into the masterfile that has 1 record per e-mail address, and they can select the addresses to send to this way.  Works fine, but it is too easy to accidently select the same address multiple times for a given report.  I am trying to figure out how to prevent this.  Of course the same e-mail address can be in the "EmailReportList" table many times, but only once per report#.

      Thanks again.

        • 1. Re: A bit of guidance on an e-mail issue

          In your report table, define this calculation field: Substitute ( list ( EmailReportList::emailAddress ) ; ¶ ; "; " ) This will produce your list of addresses separated by semi-colons. If your email program needs commas, use them instead.

          Best way I can see to manage your PDF's is to use a variable to specify the name and file path to where you will save your PDF, then you can refer to the variable name in the Send mail step to attach the PDF to the email.

          Something like this:

          Set Variable [$FilePath ; Value:  "file:" & Get ( TemporaryPath ) & "ReportFileName.PDF"]
          Send Mail [//click the check box for the file attachment and type $FilePath into the dialog that opens up.]

          To keep duplicate addresses out of EmailReportList, define a text field in it with this Auto-Enter calculation:

          ReportNumber & EmailAddress //use your field names here

          Set a validation rule on this field that this field's value must be unique.

          • 2. Re: A bit of guidance on an e-mail issue

            I will try these, and I certainly thank you for the information!

            • 3. Re: A bit of guidance on an e-mail issue

              PhilModJunk - the first solution, for getting the e-mail addresses assembled, was perfect.  Thanks.

              On the last issue, preventing duplicate e-mail addresses for a given report, I did what you said, and it makes sense, but it isn't working.

              The file that I am trying to avoid duplicates in is the EmailReportList file, which is accessed through a portal on the main report table layout.  The field that I added is to EmailReportList, and the field is UniqueAddrCheck, set up as you specified.  When I am in the main report file layout screen, and I add e-mail addresses in the portal, it still accepts the same e-mail address multiple times for the report.  However, when I try to move the record pointer to another record, this message pops up:

              "UniqueAddrCheck" is defined to require a value, but it is not available on this layout.  Use another layout to assign a value to this field.

              There is only one layout in use in this little application, the main report file layout.  The other tables are accessed via the portal.

              Do I need to add the field "UniqueAddrCheck" to the portal layout?  I don't want the end user to see or access it, though.  Or does the field need to be in the main report file table? 

              • 4. Re: A bit of guidance on an e-mail issue

                I think that it is kind of working after all, but not the way that I expected.  I did add temporarily the field UniqueAddrCheck to the portal lines, but I don't want this to remain.  The program still allows me to enter duplicate e-mail addresses, but when I move the record pointer I then get a message that the file contains duplicate records and it forces me to revert the changes.  I was looking to try to stop the duplicate from being created as soon as the portal line has a duplicate entry made, instaed of when I save the report file record.  Is this feasible?

                • 5. Re: A bit of guidance on an e-mail issue

                  There are ways to use UniqueAddrCheck in a self join that checks for duplicates, but you'll still run into the fact that you can't tell if it is a duplicate entry until you have complete values entered into both fields. You don't in fact, have to add the UniqueAddrCheck field to your layout, you can just create a custom validation message that says something like: "This email address has already been selected for this report." FileMaker validation rules can be a bit clunky, so if you can't get results you can work with, you might use a self join relationship like this:

                  EmailReportList::UniqueAddrCheck = EmailReportList 2::UniqueAddrCheck

                  Where EmailReportList 2 is a new table occurrence of EmailReportList created by selecting EmailReportList and clicking the button with two green plus signs.

                  Then you can write a script that uses Count ( EmailReportList 2::UniqueAddrCheck ) (count will be 2 or greater if you have a duplicate entry for the same report.) to check for duplicates. You can set this to be performed with the OnObjectExit or OnObjectSave script trigger for the EmailReportList::EmailAddress field.