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:
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.
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>>
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.
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
I get a ? in the body of the emal
If I enter
"Hello "& <<placeholdername>>
it works and I get
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.
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
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.
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.
Thanks I'll have another look at it.
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.
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.