Newbie Help Required! Inserting Fields into an email subject (Textbox)

[sbphoto01]

:))Hello All, I'm not sure this is the right place to post but here goes...

I am trying to find a way of inserting text from a fields using a field or variable marker like this <> (this doesnt work when placed in a textbox.

Basically I have a main table with a relationship to an email table, where I can record my own ongoing email correspondence with a client. Within this I want to be able to create email templates that pre fill with the field info of the current client record.

Is there a way of displaying a field placeholder in a text box or am I doing it the wrong way completely??

Please help!

Many thanks in advance, Steve

:

[bcooney]

If you are on a client record, and click a New Email form, do you create a new email record with the Client's ID as a foreign key? If so, then you can pull the client's data into the email record.

Create a calc text field in Emails that is a concatenated body. Something like:

"Dear" & ClientFullName & ", " & CR & "We notice that you last ordered on " & DateLastOrder & "."

Then, in your new email script, set the body to this calc field.

Consider using an email type table (for templates). Then have boilerplate text for email types. By selecting an email typeID in an email record, you can set the body to different template text.

[sbphoto01]

Hi, thanks for your reply, that's a useful answer and one which I could use personally as I understand concatenation of fields! I do use an FK ID for creating new emails for each client.

I would like to know more about the last paragraph you left,... boiler text?? template tables!? this all sounds interesting but I'm not sure what you mean. Would you be able to explain a bit more for me?

Ideally I need to allow users of my solution to create templates themselves by pasting in the field names.

Any ideas?

Many thanks, steve

Edited August 23, 2010 by Guest

[bcooney]

I didn't want to throw a lot at you at once. The technique to achieve using <> is not novice level, and you sound like you need basics first.

For example, in order for a client to see the emails that belong to him in a portal of emails, you need the clientID in each email record.

Once you understand foreign keys, adding a foreign key in each email record for the template to use will make sense. Give the user a popup of templateIDs (show the name), and with a script trigger onModify, you can prompt the change of the body text.

[sbphoto01]

Hi Bcooney, thanks for your reply, I have been using FM Pro for about 9 months, I have built a large relational database solution and do understand the PK_ID=FK_ID concept and have created portals to view all my invoices / contracts and email correspondence records for each client using a PK=FK relationship to stor multiple 'child records' for one parent record which I believe is called a 'One to many' relationship.

In my email creation layout I currently have some scripted buttons which copy subject and message fields from a number of global 'email template' fields'

So I am halfway there... but users of my solution need to be able to use the field placeholders to create their own templates.

I hope this makes things clearer for you to explain things to me!

Many thanks again!

Steve

[bcooney]

Alrighty, then.

Two helpful links. Core has the ability to insert placeholders. They basically sub the placeholder with the appropriate field data. Hard-coding this is unavoidable.

http://www.core2crm.com/

Also, the cf may help:

http://www.briandunning.com/cf/438

Let the user build a field with merges, and then use the resultant calc as the email body.

[Tim Rosencrans]

hey sb the key is that you need to put the items into the template in a way that filemaker can understand and evaluate. that is

"some text "& table::fieldname & " some more text"

But the user doesn't understand Filemaker so you want them to be able to write.

some text <> some more text

What you have to do is make that conversion for them behind the scenes. The custom function mentioned before will do that by replacing (<< ) with ( " &) and (>>) with (& "). It also adds to the beginning and end to make it a valid expression. It then uses the evaluate function to well, evaluate it, returning the full text.

Learn the evaluate function. It is often overlooked but it a powerful tool for solving tough problems.

[sbphoto01]

Thanks So much Barbara & Tim for your replies, it looks like I can solve my problem with a custom function and the evaluate function.

Would you be kind enough to tell me how to create the custom function!?

Many thanks!!

Steve

Edited August 23, 2010 by Guest

[bcooney]

Look for Manage>Custom Functions. Just use the function I referenced to input the user's text field and output the calc that you'll use for the email body.

[sbphoto01]

Hi Barbara, when I paste the calc into a new custom function it says it cannot find the specified paramenter and highlights the word text in the calc?

This is what my custom function box looks like -

Any ideas?

Steve

Edited August 24, 2010 by Guest

[Raybaudi]

The custom function has a parameter: "text" and you didn't set it.

[sbphoto01]

Hi Danielle, do I just put the word text in the function paramenters box??

Steve

[Raybaudi]

...and push add ( the plus symbol )

[sbphoto01]

Hi Danielle, That worked! So how would I get the custom function to do it's job?

eg. I have a global text field, and this is where my users create their templates using ordinary text and the <> placeholders, when I import the text from the global field, how do I script the custom function to translate what's in the global field into the message field body of a new email for the current client?

I very much appreciate your help!

Steve.

[Raybaudi]

Create a calcualtion global field with calc:

Mail Merge ( YourOriginalTextField )

and use that field for mail purpose.

[bcooney]

All this is fine, but isn't the goal to have an email record in the system? Therefore, the final merged email text must not be a global, but stored in a text field in the email record.

So, rather than a global calc, a simple calc field for the merged text. That is the text field that is used as the body in Send Mail, and is displayed when a user navigates to an email record after it is sent.

-B

[Raybaudi]

I said "global" only because he said to have a global field.

[sbphoto01]

I am currently using a number of global fields called Template 1, template 2 etc... This is where the user can input text and field placeholders <> and there for create a template which can be seen by any record. (That is my thinking anyway! it works) Eventually I will replace this with a templates table, for creation of an unlimited no. of templates.

But sticking with the simple global text field idea, am currently doing a simple set variable, set field to copy the text in the global template field into the message field of the new email for the current client. (Which is stored)

I need to add in the custom function or similar script steps within my existing set variable & set field script to translate the field placeholders into the actual field record info.

Tim has mention to me in a PT message that the Custom function is not required as the whole thing can be scripted like this -

Evaluate(

""" & Substitute ( templateField ; [""" ; """] ; ["<<" ; "" & "] ; [">>" ; " & ""] ; ["¶" ; "¶"]) & """

)

I'm just a bit stuck with how to add this into my set variable/set field script.

Any help is much appreciated!! thanks to all who have contributed so far!