Trying to use functions to create email address on submit

[joeman]

I have an interesting problem.

I have a form with a Main (submit) and a bunch of secondary (retrieve) data
connections. I'm creating a submit (email) data connection.

On the form, I have a field - @InfopathField - bound to TableA.Field1 - this
is an ID field. It's a drop-down, and the display is ViewA.Field2. ViewA is
a concatenation of (TableA.Field4) + ", " + (TableA.Field2), and this work is
all done on the SQL side in the view itself.

So - the actual value of @InfopathField is going to be the ID number pulled
from ViewA.Field1, and then inserted (on submit) to TableA.Field1.

What I want is a function in my EmailSubmit data connection that will get me
the following, if I were writing it in SQL:

SELECT LEFT(TableA.Field2,1) + (TableA.Field4) AS Alias1
WHERE @InfopathField = TableA.Field1

And I want the value of Alias1 to be the email address that pops into the
data connection's field.

Is this even possible?

 

[S.Y.M. Wong-A-Ton]

Try using substring(...) for the LEFT(...), concat() to join the 2 fields,
and "Filter Data" when selecting the fields for the substring() and contact()
functions to simulate your WHERE clause.

 

[joeman]

I'm not certain that I understand how I'm going to get the actual data in
there. It's not on the form, technically, but it IS in the datasource.

 

[S.Y.M. Wong-A-Ton]

Use the "Insert Formula" button behind the "To:" field on the "Data
Connection Wizard" dialog box.