10 Replies Latest reply on Mar 5, 2015 3:07 PM by philmodjunk

    Totally customise email body

    dinoapolito

      Title

      Totally customise email body

      Post

      Hi,

      I have a script which loops through records in a found set and sends an email for each. That parts easy.

      And I know how to customise the body of the email message from within the send email dialogue. Again easy.

      And I do this all with the 'perform without dialogue' option which is what I want.

      But ... I'm not the end user. I want the different end users to be able to enter their custom message into a layout somewhere and have the email use that instead. Again that's easy until I want to use replaceable text.

      For example, if I enter the following DIRECTLY into the message box of the Send Email window I get what I need

      "Hello "& subject::name

      which gets me Hello John, Hello Sally etc. Great

      But my customers may want something different, perhaps not even in English. So I created a global field called BODY in a GeMail table and I call that onto the message box with.

      GeMail::Body.

      Now the customer could go to the layout with that field and enter their custom message. They may enter

      "Dear "& subject::firstname

      It no longer works. The emails will just repeat the text verbatim rather than get the firstname for each record.

      I hope this makes sense. Note I can get the result I want by importing a text file into the message box but I can't do that automatically with no dialogue.

      Please help if possible

      Dino

       

       

       

       

       

        • 1. Re: Totally customise email body
          philmodjunk

          You could use the evaluate function in your Body calculation: (Evaluate ( Table::customerEnteredTextField ) )

          But that requires them to learn FileMaker calculation expressions and your field names.

          You can actually set them up with a text field where the text looks like this:

          Dear <CustomerFirstName>,

          Thankyou for buying the <productname>....

          And a substitute function inserts data from your database in place of the <PlaceholderText> that names a specific field but in user friendly terms that need not be exactly the name defined for that field in Manage | Database.

          See this demo file: https://www.dropbox.com/s/6xw8buafjuohncu/MessageTemplateBuilder.fp7

          It's in the older file format, so if you are using FileMaker 12 or newer, use Open from FileMaker's File menu to open this file and produce a copy of the file converted to the newer file format.

          • 2. Re: Totally customise email body
            dinoapolito

            Thank you!

            I tried the evaluate function and it certainly worked but I still need to enter the text in the CustomerEnteredTextField as

            "Hello "& subject::name

            So I looked at your demo file and can see how you are using substitute for field names but how do I avoid having to use correct expression syntax?

            I want the end user to simple enter 

            Hello <<placeholdername>>

            not

            "Hello "& <<placeholdername>>

             

            • 3. Re: Totally customise email body
              philmodjunk

              Take another look at the demo file. You simply edit text in a field. There are buttons that insert the field placeholders for you and you can examine their button settings while in layout mode to see how they insert text at the current location of your cursor, but they are used to make it easier for the user and less likely to be entered incorrectly. It's possible just to click into the field and type in what you want.

              Then the final version of this text, which could be printed or specified as the body of an email, is produced by using the substitute function to replace the placeholder text with values from corresponding fields in a record of the database.

              • 4. Re: Totally customise email body
                dinoapolito

                Yep I can see hoe the placeholders work but it's the fixed text that is the problem.

                I'm using the evaluate function in the email body.

                In my text field if I enter

                Hello <<placeholdername>>

                I get a ? in the body of the emal

                If I enter

                "Hello "& <<placeholdername>>

                it works and I get 

                Hello John 

                • 5. Re: Totally customise email body
                  philmodjunk

                  There is no fixed text. you just type in what you want into the text field. And you would not use evaluate with this approach. As I have stated twice before and as is shown in the demo file, you use SUBSTITUTE, not evaluate.

                  • 6. Re: Totally customise email body
                    dinoapolito

                    My problem is that all the data in this solution is from an external MySQL database and the end user only has read access to those tables.

                    At the local level I can only add calculation fields to those tables and since they are calculation fields the end user can't just type into them as per your solution. I can't have a message field for each record as your solution has.

                    So my solution was to create a new local table with the custom messages in a field called body. It's a global field and the table is not related to any other table.

                    It's in there that I can type

                    "Hello " & shadowtable::fieldname

                    which works if I use the evaluate function

                    but doesn't work if I enter

                    Hello shadowtable::fieldname

                    Hope this helps in understanding the problem. I do think your solution is neat and want to ultimately use substitute but I'm not there yet.

                    • 7. Re: Totally customise email body
                      philmodjunk

                      But you can set this up to use the fields of a local table for typing in your message template. The substitute function can refer to your read only data for the text to insert in place of the placeholders. 

                      • 8. Re: Totally customise email body
                        dinoapolito

                        Thanks I'll have another look at it.

                        • 9. Re: Totally customise email body
                          dinoapolito

                          Thank you I have it working as needed now.

                          I created a new local table for the messages that relates to the read only subjects MySQL table by a SubjectID. And created a R/W MySQL table that holds the email templates and relates to the Emails table by TemplateID.

                          My looping script creates a record in the Email table for each subject in the found set with their subjectID, the templateID and of course the message in a message field which uses the substitute command.

                          Once emails are sent the script then deletes all records from the Email table, ready for the next mail out.

                          On a side note do you know if there is a way to include a clickabe hyperlink in the emails? I think I know the answer but wanted to avoid plug-ins.

                          Thanks again,

                          • 10. Re: Totally customise email body
                            philmodjunk

                            Usually, if you insert text that forms a working hyperlink such as: https://www.dropbox.com/s/jt09b82i0xijbu3/FMP%20Bugs.zip in the body of your email, most email programs that you recipients might use to open the email will automatically turn that text into a clickable link.