Utilizing a returned lookup value in an email message body

[Medic1196]

I am using the following to perform a lookup script to get a users full name to include it in an email.

dim Vpersonid as n
Vpersonid = e.dataSubmitted.PersonName
dim args as SQL::Arguments
dim cn as SQL::Connection
IF cn.open("::name::conn") then
args.add("newPers",Vpersonid )
dim sql as C
sql="SELECT * FROM users WHERE personid = :newpers"
IF cn.execute(sql,args) then
email=cn.ResultSet.data("userid")
persname=cn.ResultSet.data("namefull")
end if
end if

How can I use the "persname" returned above, in the body of the email message shown below in red?

DIM messageBodyHTML as C = <<%str%
<p><font color=#ff0000><strong> {persname} </strong></font> has sent an email test <br>
to you, on <font color=#ff0000><strong>{PersonNameTS}</strong></font></p>

[Allen Klimeck]

Re: Utilizing a returned lookup value in an email message body

Use the STRTRAN() function

http://wiki.alphasoftware.com/~alpha...RTRAN+Function

[Medic1196]

Re: Utilizing a returned lookup value in an email message body

Thank you Allen, but I cannot get that to work. I may not have explained it well enough, or maybe I didn't check this correctly, but I just want to use the returned value in the text string for the email message but can't figure out how to make it work. Example: "This would be where the field " & [persname] & " would go."

[GGandhi]

Re: Utilizing a returned lookup value in an email message body

normally if you want to include the derived variable in your message this is how to do.
DIM messageBodyHTML as c = "this is a message that includes the "+ derived_variable + "so that the derived_variable will be incorporated in the string"

[Medic1196]

Re: Utilizing a returned lookup value in an email message body

Thank you Gandhi, it works just like you say. I was trying to implement it in my message I had already been using and it wont, apparently due to the formatting. Here is what I use which has formatting of the text color and bold and so on. Why won't it work with this? Is there a way to make it work with it?

DIM messageBodyHTML as C = <<%str%
<p><font color=#ff0000><strong>This is a test. </strong></font> has "+ persname + " has sent an email test <br>
to you on <font color=#ff0000><strong>{PersonNameTS}</strong></font></p>

<p>Details:<br>
{Comments}</p>

<p>This is an email test. <br>
Order ID: <strong><font color=#ff0000>{TestID}</font></strong>.
<br>If you have any questions, contact <font color=#ff0000><strong> "+ persname + " strong></font>.</p>

<p>
<br>
{PersonNameEmail} <br>
</p>%str%

[Tim Kiebert]

Re: Utilizing a returned lookup value in an email message body

Also have a look at http://wiki.alphasoftware.com/EVALUATE_STRING+Function

[Allen Klimeck]

Re: Utilizing a returned lookup value in an email message body

DIM messageBodyHTML as C = <<%str%
<p><font color=#ff0000><strong> {**fullname**} </strong></font> has sent an email test <br>
to you, on <font color=#ff0000><strong>{PersonNameTS}</strong></font></p>
%str%

[COLOR="#FF0000"]messageBodyHTML=strtran(messageBodyHTML,"**fullname**",persname)[/COLOR]

Here I used **fullname** as a place holder for the data i want to replace. Than the strtran() function will replace the place holder with the data in the variable persname

[Medic1196]

Re: Utilizing a returned lookup value in an email message body

Thank you Allen, here is the email it produced.

has sent an email test
to you, on 08/27/2014 21:26
messageBodyHTML=strtran(messageBodyHTML,"**fullname**",persname) ''''''''''''''''''''''''''''''''''''''''''''''''''' '' DIM messageBodyHTML as C = <

I thought it MAY have something to do with missing an "%str%" (wild guess on my part), at the end, so I added it, but still didn't get a name, only this.

has sent an email test
to you, on 08/27/2014 21:26
Still not getting the name inserted.

[Medic1196]

Re: Utilizing a returned lookup value in an email message body

Allen, I got this to work. Wild guessing again on my part. I included the "{}" and now it works.

DIM messageBodyHTML as C = <<%str%
<p><font color=#ff0000><strong> {**fullname**} </strong></font> has sent an email test <br>
to you, on <font color=#ff0000><strong>{PersonNameEmailSentTS}</strong></font></p>%str%


messageBodyHTML=strtran(messageBodyHTML,"{**fullname**}",persname)

Not to complicate things, but if I had more than one of these to include in a message, is it possible to use more than one?

[Allen Klimeck]

Re: Utilizing a returned lookup value in an email message body

Yes you needed the "%str%" at the end sorry I missed that. You need to check that you are retrieving the data you need in other words that the variable persname is not blank.

than try:
messageBodyHTML=strtran(messageBodyHTML,"**fullname**",alltrim(persname))

[Allen Klimeck]

Re: Utilizing a returned lookup value in an email message body

Glad you got it to work!! You can use as many place holders as you like just add another strtran() to replace it

Edit: I should have realized that the "{}" were not part of the HTML

[Medic1196]

Re: Utilizing a returned lookup value in an email message body

That returns nothing, blank. But if I put the **fullname** inside {} then it will work.
This works: messageBodyHTML=strtran(messageBodyHTML,"{**fullname**}",alltrim(persname))

[Medic1196]

Re: Utilizing a returned lookup value in an email message body

Thank you very much Allen.

[Tim Kiebert]

Re: Utilizing a returned lookup value in an email message body

Not to complicate things, but if I had more than one of these to include in a message, is it possible to use more than one?

Yes, with the evalaute_string() function mentioned earlier. Also check out CompileStringTemplate