Use SQL expressions in Send Email action via the Workflow Manager

It is documented that SQL Expressions can be used for custom formula fields and searches. However, it is not documented that it can be used in sending emails. For workflows, instead of indicating the SQL expression or formula within the message body, an alternate solution is to set a field using a formula. Then, refer to the field ID in the message body.

In this sample, instead of using a standard or custom field, an instance field is created to receive the value of the SQL expression.

1. Create a new workflow by navigating to Customization > Workflow > Workflows > New

2. Define the workflow
   a. Name = Send Email with formula
   b. Record Type = Transaction
   c. Sub Types = Sales Order
   d. Release Status = Released
   e. On Update = TRUE
   f. Trigger Type = After Record Submit
   g. Click Save. 

3. Create an Instance custom field. On the workflow definition page, clicking the New Field button on the Fields tab.
   a. Label = Field with Expression
   b. Type = Decimal Number
   c. Store Value = TRUE
   d. Click Save.
* Take note of the instance custom field's Internal ID. You can customize the name or let the workflow assign one for you.

4. To add a workflow state, click New State and set Name = Set Field, then hit Save.

5. To create an action to the state, click on the Set Field state and click New Action.
   a. Select Set Field Value.
   b. Trigger On = Entry
   c. Field = Field with Expression (Workflow) //workflow instance fields have an suffix of (Workflow) at the end of the field name
   e. Formula = DECODE(TO_CHAR(ADD_MONTHS({trandate},6),'YYYY'),TO_CHAR(ADD_MONTHS({today},6),'YYYY'),{total},0)
   f. Click Save.

6. Create another state and set Name = Send Email.

7. Create an action to retrieve the phone call's Internal ID.
   a. Click on New Action button, select Send Email.
   b. Specific Sender = <choose any from your employee record>
   c. Specific Recipient = <choose any from your employee record>
   d. Content
      - Select Custom
      - Body: {custworkflow9} // {custworkflow9} is the instance field's Internal ID
   e. Click Save.

8. Create a transition by clicking the first state (Set Field), click New Transition and select the second state (Send Email).

9. Edit one of your Sales Order and save it. On the Sales Order's Communication tab, click View on one of the messages. The SQL expression must be interpreted and evaluated as necessary.