Tech Tip Thursday: Variables in E-Mail Message Query Strings

Using Variables in an E-Mail Message Query String is an advanced way to build custom email messages. Since you can pull any data from any item within Aras and utilize it as part of a mail merge, building these custom queries is as simple as knowing what data you want to pull, the standard text you'd like to use around the data, and some AML to get your messages on the move.

Example messages might be for purchase requests tied to the BOM including a list of items with cost and current status or an engineering change review that you want to send with an attached problem report. Really the possibilities are endless. If it's in Innovator, you can make it happen.

Since so many developers want to be able to build these kinds of custom email messages with data that lives in Aras Innovator; here's the example for you:

Using variables in an E-Mail Message Query String

This example shows how to use parameters in an E-Mail Message Query String to populate variables in both the body of the message as well as the subject line when the message is generated.

For this example, an ECR is created from the PE Solution database and a PR (Problem Report) is attached to the ECR. The body of the letter when sent from a workflow notification will indicate the attached PR number and title.

To use the parameters in the Query String of the E-Mail Message requires some knowledge of XPath as well as the XSLT language which are assumed in this example. You should also have a working knowledge of the Aras AML language to retrieve the information shown.

In order to access the item_number and title properties of the PR item requires retrieving the ECR PR relationship for the current ECR. This can be done using the following Query String statement:

${Item/@id}

Notice that the source_id value for the relationship lookup has been specified using an XSLT parameter. Substitution parameters must be enclosed in $ { } . Since this E-Mail Message will be associated with an ECR, the expression Item/@id will be substituted with the id of the current ECR being processed.

The AML statement will locate the relationship associated with the current ECR and then retrieve the related PR item.
To access properties of the PR item in the email message requires locating the values in the retrieved XML. The following example shows how to display the item_number and title of the PR in the E-mail Message body:
Please review ECR with the following PR attached:
PR # : ${Item[@type="ECR PR"]/related_id/Item[@type="PR"]/item_number}
Title: ${Item[@type="ECR PR"]/related_id/Item[@type="PR"]/title}

The Query String also supports multiple queries that are performed in sequence when the e-mail message is generated. The following query retrieves the current ECR and the item_number from the database:

select="item_number"/>

To access the item number in the e-mail message (or subject line) use a variable:

Please Review ECR ${Item[@type="ECR"]/item_number}

So the complete e-mail message Query String will appear as:

select="item_number"/>

${Item/@id}

And the message body will appear as:

Please Review ECR ${Item[@type="ECR"]/item_number}

Attached PR is:

PR # : ${Item[@type="ECR PR"]/related_id/Item[@type="PR"]/item_number}
Title: ${Item[@type="ECR PR"]/related_id/Item[@type="PR"]/title}

For other examples of using XLST parameters in an E-mail message see the CM Activity Notification item in the solutions database.

Have an idea for a future Tech Tip? Let us know info@aras.com.

Source: http://www.aras.com