Query email templates.

Table Specific Information

Select

The connector will use the Salesforce Marketing Cloud Account Engagement API to process WHERE clause conditions built with the following columns and operators. The rest of the filter is executed client side within the connector.

• Id supports '=', '<', '<=', '>', '>='.
• IsOneToOneEmail supports '='.
• IsDeleted supports '='.
• CreatedAt supports '=', '<', '<=', '>', '>='.
• UpdatedAt supports '=', '<', '<=', '>', '>='.

For example, the following queries are processed server side:

SELECT * FROM EmailTemplates WHERE Id = 50
SELECT * FROM EmailTemplates WHERE UpdatedAt > '01/01/2022'
SELECT * FROM EmailTemplates WHERE CreatedAt > '01/01/2022'

Insert

To insert EmailTemplates the following fields are required:

• CampaignId
• IsAutoResponderEmail
• IsDripEmail
• IsListEmail
• IsOneToOneEmail
• Name
• Subject
• TextMessage
• HtmlMessage
• SenderOptionsAggregate
• TrackerDomainId
• Type

For inserting into this table specifying the SenderOptionsAggregate is required. The input for this field follows CData principles of deep Inserts. In Deep Insert, you simultaneously create a base object and link it to the related field in the main table.

You may either submit JSON data, or you may create a temporary table for the child table(in this case SenderOptions) and then reference the temporary table in the insert to the base table.

Example: Deep Inserts using JSON

To submit JSON data, simply supply the values for the child table in JSON format.

For example, the following JSON adds two sender options in the email template.

[
	{
		"address": "[email protected]",
		"name": "CDataTest",
		"type": "general_user"
	},
	{
		"address": "[email protected]",
		"name": "CDataTest2",
		"type": "general_user"
	}
]

In order to execute the insert, simply reference or include as string literals the complete JSON. For example:

INSERT INTO EmailTemplates (..., SenderOptionsAggregate) VALUES (..., '[{"address":"[email protected]","name":"CDataTest","type":"general_user"},{"address":"[email protected]","name":"CDataTest2","type":"general_user"}]')

Example: Deep Inserts using Temporary Tables

If using temporary tables, they must be defined and inserted within the same connection. Closing the connection will clear out any temporary tables in memory.

Creating Temporary Tables

Insert the child table fields into temporary table.

INSERT INTO SenderOptions#TEMP (Type, Address) VALUES ('general_user', 'address1')

INSERT INTO SenderOptions#TEMP (Type, Address) VALUES ('general_user', 'address2')

Inserting the email template

In the INSERT statement for the EmailTemplates table, reference the temporary tables in the SenderOptionsAggregate input.

INSERT INTO EmailTemplates (..., SenderOptionsAggregate) VALUES (..., 'SenderOptions#TEMP')

Update

Updating EmailTemplates:

UPDATE EmailTemplates SET TextMessage = 'newMessage' WHERE Id = 24143

Delete

Remove all email templates or a email template by specifying the Id of the template.

DELETE FROM EmailTemplates WHERE Id = 10003

GetDeleted

To retrieve email templates that are in the recycle bin in Pardot the following operation is supported:

GETDELETED FROM EmailTemplates

Columns

Name	Type	ReadOnly	References	Description
Id [KEY]	Int64	True		ID of the email.
Name	String	False		Name of the object for identification in Pardot.
Subject	String	False		Subject line for the email template.
TextMessage	String	False		The text content of the email. This field is retrieved only when filtering with email id.
HtmlMessage	String	False		The HTML content of the email. This field is retrieved only when filtering with email id.
IsOneToOneEmail	Boolean	False		If True, the template is available for one-to-one emails.
IsAutoResponderEmail	Boolean	False		If True, the template is available for autoresponder emails.
IsDripEmail	Boolean	False		If True, the template is available for Engagement Programs.
IsListEmail	Boolean	False		If True, the template is available for list emails.
TrackerDomainId	Int64	False		ID of the tracker domain used for the list email.
FolderId	Int64	False		ID of the folder containing this object.
Type	String	False		'html', 'text', and 'htmlAndText' values.
CampaignId	Int64	False		Pardot campaign related to this object.
IsDeleted	Boolean	True		True if the object is in the recycle bin in Pardot.
CreatedById	Int64	True		ID of the individual who created this object.
UpdatedById	Int64	True		ID of the individual who last updated this object.
CreatedAt	Datetime	True		The timestamp of when this object was created.
UpdatedAt	Datetime	True		The timestamp of when this object was last updated.
SenderOptionsType	String	True		The sending user type(s).
SenderOptionsAddress	String	True		The sending email address(s).
SenderOptionsName	String	True		The name(s) of the sender.
SenderOptionsUserId	String	True		The user ID(s) of the sender.
SenderOptionsProspectCustomFieldId	String	True		The ID of the prospect custom field that contains the sender value. Only available on read and query.
SenderOptionsAccountCustomFieldId	String	True		The ID of the account custom field that contains the sender value. Only available on read and query.
ReplyToOptionsType	String	True		The reply to user type(s).
ReplyToOptionsAddress	String	True		The sending email address(s).
ReplyToOptionsUserId	String	True		The user ID(s) of the sender.
ReplyToOptionsProspectCustomFieldId	String	True		The ID of the prospect custom field that contains the sender value. Only available on read and query.
ReplyToOptionsAccountCustomFieldId	String	True		The ID of the account custom field that contains the sender value. Only available on read and query.

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

Name	Type	Description
SenderOptionsAggregate	String	This input is used for INSERT/UPDATE statements. Set this to SenderOptions#TEMP temporary table, whose rows contain inputs for inserting/updating the sender options. Or simply reference or include as string literals the complete JSON.
ReplyToOptionsAggregate	String	This input is used for INSERT/UPDATE statements. Set this to ReplyToOptions#TEMP temporary table, whose rows contain inputs for inserting/updating the replyTo options. Or simply reference or include as string literals the complete JSON.