Correspondence Template Child Form HTML

A Child Form can be nested within the body of a Marketing Correspondence Template if you have a Toolkit license.

Below is a correspondence template that is sent through NetForum.

Note especially this control:

{child_form_html^87262700-9531-4c2c-b20b-9776397d1bcd}

or

{child_form_text^87262700-9531-4c2c-b20b-9776397d1bcd}

This message is to confirm the receipt of your recent order. 

Customer:  {cst_name_cp}
Date:      {currentdate}
{?pin_cc_auth==""?Invoice #: {inv_code}?Confirmation #: {pin_cc_auth}}
{?pin_cc_auth==""?Invoice Date: {inv_add_date}?Transaction Date: {pin_add_date}}
Total:      {inv_nettotal}
Payment Amount:      {pin_check_amount}
Balance:      {inv_netbalance} 


Bill To:
---------------
{cxa_mailing_label_html}
Payment Method: {apm_method}
{child_form_html^87262700-9531-4c2c-b20b-9776397d1bcd}
{inv_details_with_customer_html}

The control {inv_details_with_customer_html} is a "virtual" control in the Invoice object that outputs the line items in the invoices, as shown in this example:

Customer	Qty	Item	Sub-Total	Discount	Paid	Balance
Hopkins Darryl	1.00	Office Bags	$20.90	$0.00	$20.90	$0.00
Hopkins Darryl	1.00	Donation	$10.00	$0.00	$10.00	$0.00
Hopkins Darryl	1.00	Winter Kit	$30.00	$0.00	$30.00	$0.00

The control {child_form_html^87262700-9531-4c2c-b20b-9776397d1bcd} outputs like this:

Product Quantity Unit Price Total
Winter Key  1  $30.00  $30.00
Donation    1  $10.00  $10.00

The keyword child_form_text^87262700-9531-4c2c-b20b-9776397d1bcd works the same way except it does not use HTML markup tags and should be used in the text portion of a correspondence template.

What drives this output is a child form with the key "87262700-9531-4c2c-b20b-9776397d1bcd."

Note the WHERE clause in the select sql contains:

WHERE net_inv_key = {inv_key}

This will automatically filter the results of the return statement. Also note that the parent form is a form that belongs to the Invoice object, which enables the control {inv_key} to be parsed. Finally, observe that the columns that the child form returns in its recordset are output directly into the correspondence template.

Both the child form listing (on top, with four columns) and the baseline invoice detail list (generated by the virtual control {inv_details_with_customer_html}) (on the bottom, with seven columns) are shown in the template for comparison.

This feature does not work if the template is used within the Messaging Engine.

Adding a Child Form to a Template

1. Create the SQL Query that will pull the fields your template will need. Start from the table that is being called by the Templates list type you are using.
2. When pulling a column you will want to format it for HTML. Ex.
'<font size = 2pt>' + field_name + '</font>' AS "Field_Name"

Note: The Field_Name will appear on the template no matter what. This will be the header for the column being pulled.

2. Edit the form used by the template list type. Add a Child form to this form.
   1. Name the child form. Common practice is to put the Client's acronym at the front of this so it can be identified as a customization.
   2. Set parent form to the form you are adding the child form to.
   3. Set the destination form to the same as above.
   4. Set the child key column to the Select Column.
   5. Copy over the Query into step 1 into the SQL for the child form.
   6. Set the order for this child form below all the other child forms.
   7. Lock the child form.

{child_form_html^child_form_key}

Beyond Result Rows

For more advanced output beyond a table or rows of data, child forms can be used to call custom SQL stored procedures or evaluate SQL statements. As long as the data is returned as a one row one column result set with the column aliased as an empty space [ ] the final output in the email can be quite complex, but may require calling child_form_text in an html template to get the correct results.

The stored procedure pictured reg_payment_template accepts two parameters, the reg_key and the requested output format, either html or text. After building a complex string depending on the registration the final return statement is select @results as [ ]. Calling {child_form_text^52b835d4-19de-41a6-ba3e-e38e59e403e1} ensures that the results are put directly into the template without adding further html.

Troubleshooting

If using Child Form HTML and not including line breaks, it's possible that the receiving SMTP server is interpreting the HTML as a possible injection attack and refusing the E-Mail. Essentially, some SMTP servers will not send an email that goes on and on for many many lines without carriage returns; this can be a red flag for spam or other nastiness. (For more on this, see Troubleshooting Section in Correspondence Template.) Actually, even without child form HTML this may occur, I think that the inv_details_html field has this problem. Just make sure there are line breaks in the HTML source. Not just < br >s, but actual carriage returns/line feeds in the code behind the HTML.

Note the compliant syntax, including the carriage return/line on line 9:

SELECTivd_key,
Customer AS[Customer],
convert(decimal(8,2),ivd_qty)AS[Qty],
prc_display_name AS[Item],
'$'+ convert(varchar(10), ivd_amount_cp,101)AS[Sub-Total],
'$'+ convert(varchar(10), discountamount,101)AS[Discount],
'$'+ convert(varchar(10), payamount,101)AS[Paid],
-- include char(10) + char(13) !!
'$'+ convert(varchar(10), convert(decimal(8,2),linebalance),101)+ char(10)+ char(13)AS[Balance]
FROM vw_ac_invoice_detail_summary_net (nolock)
WHERE inv_key ={inv_key}

Again, take a look specifically at this line:

-- include char(10) + char(13) !!'$'+ convert(varchar(10), convert(decimal(8,2),linebalance),101)+ char(10)+ char(13)AS[Balance]

The Preview link off of the CommunicationPage.aspx does not appear to render the child_form_html content. If you send a test message to yourself you will be able to see the content.

Dynamic Subject Lines

If you want a template to have a variable subject line, you can do this with a specialized form a Child Form HTML. See Dynamic Variable for more on this technique.

Create a child form on a form of the same object as the template's list type. The child form SQL that will return a single row and a single cell. The alias for the one cell should be a single space as shown in the sample below.

Note that there is a single space character between the brackets at the end of the first line:

SELECT convert(varchar, cst_recno)+' '+ cst_sort_name_dn AS[]FROM co_customer (nolock)WHERE cst_key ={ind_cst_key}

After saving the child form, get its key and then in the template's subject line, reference it as follows:

Lorem Ipsum {child_form_text^child_form_key_goes_here:\W*(.+)\W*~$1} Lorem Ipsum

In place of child_form_key_goes_here paste the actual key of the child form.

Then save the template.

When the template is sent, the subject line will be parsed and the value returned by the SQL in the child form will be put into the subject line. Using this technique, you can put any kind of variable text in the template subject. In the child form SQL, you can parse any values contained in the list type's Object in order to dynamically craft the message.

Do realize that subject lines can parse ordinary values such as an invoice number or other controls, in the same way that these values can be parsed in the message body. Therefore, use this technique only for more advanced text that cannot be done through regular parsed values.