Oracle APEX Email Templates - Conditional Display

In my previous blog, I have explained how you can get started with Oracle APEX Email templates. However, what if you want to show "some part of email" conditionally. Let's consider an example of ticketing system. Maybe you want to display "Service-level Agreement" (SLA) details in email notification for P1 tickets alone, and you want to hide it for all other cases. How can you do it?

Creating multiple templates and using different templates in different scenarios? Yes, it will work, but are there any better ways? Yes, there are.

Email templates are static. You can't write any if-else logic in the templates. You need to manage all your requirements using substitution strings. Luckily, we can implement conditional logic using substitution strings alone.

Let's say you want to display below text conditionally based on ticket priority, i.e. display it for P1 tickets and hide it for all other cases.

<div>Note: SLA for P1 tickets is 4 hours. Please ensure you resolve the ticket within 4 hours.</div>

We can do this in different ways.

1) Send values to substitution string conditionally

Add below text to email template

<div>#SLA_TEXT#</div>

Then you can pass SLA_TEXT text value conditionally while calling APEX_MAIL.SEND

PL/SQL Code to Send Email:

DECLARE
    l_sla_text VARCHAR2(100);
BEGIN
    -- display text only for P1 tickets
    l_sla_text := CASE WHEN :P15_PRIORITY = 'P1' THEN 'Note: SLA for P1 tickets is 4 hours. Please ensure you resolve the ticket within 4 hours.' ELSE NULL END;    
    APEX_MAIL.SEND(
            p_to                 => 'firstname.lastname@xyz.com',
            p_template_static_id => 'SIMPLE_EMAIL_TEMPLATE',
            p_placeholders       => '{' ||
            '   "DESCRIPTION":'    || apex_json.stringify(:P15_DESCRIPTION) ||
            '   ,"PRIORITY":'      || apex_json.stringify(:P15_PRIORITY) ||
            '   ,"TICKET_NUMBER":' || apex_json.stringify(:P15_TICKET_NUMBER) ||
            '   ,"SLA_TEXT":'      || apex_json.stringify(l_sla_text) ||
            '}' 
        );
END;

Simple, correct? This works best for simple cases like discussed here. However, if you have complex cases and you want to show/hide large part of email template conditionally, then this approach is not good. You will end up writing lot of HTML code inside PL/SQL again (pre email template approach)

2) Using Cascading Style Sheets (CSS)

Let's continue with our previous example. To achieve the same thing using CSS, you just need to add an additional substitution string, for e.g. SLA_STYLE and change the template code as below

HTML Code in Email Template: 

<div #SLA_STYLE!RAW#>Note: SLA for P1 tickets is 4 hours. Please ensure you resolve the ticket within 4 hours.</div>

Here, you don't want APEX to escape any values you pass for SLA_STYLE. So use !RAW filter (it's not a filter though).

Now, when sending emails, pass style definition to SLA_STYLE conditionally.

PL/SQL Code to Send Email:
DECLARE
    l_sla_style VARCHAR2(100);
BEGIN
    -- add style attribute only for those cases where you don't want to display SLA note
    l_sla_style := CASE WHEN :P15_PRIORITY != 'P1' THEN 'style="display:none;"' ELSE NULL END;
    APEX_MAIL.SEND(
            p_to                 => 'firstname.lastname@xyz.com',
            p_template_static_id => 'SIMPLE_EMAIL_TEMPLATE',
            p_placeholders       => '{' ||
            '   "DESCRIPTION":'    || apex_json.stringify(:P15_DESCRIPTION) ||
            '   ,"PRIORITY":'      || apex_json.stringify(:P15_PRIORITY) ||
            '   ,"TICKET_NUMBER":' || apex_json.stringify(:P15_TICKET_NUMBER) ||
            '   ,"SLA_STYLE":'     || apex_json.stringify(l_sla_style) ||
            '}'
        );
END;

This works well. However, when users forward/reply the email, then they may see the hidden HTML as-well in some email clients. I have observed this behavior in Microsoft Outlook, Mozilla Thunderbird email clients. However, it is working fine in Gmail web client. So, before you use this approach, test it with your targeted email clients.

3) Using HTML Comments

Other approach is to comment HTML text conditionally. To use this approach, you need to add two substitution strings, one to start the comment and other to end.

HTML Code in Email Template:

#SLA_COMMENT_BEGIN!RAW#
<div>Note: SLA for P1 tickets is 4 hours. Please ensure you resolve the ticket within 4 hours.</div>
#SLA_COMMENT_END!RAW#

PL/SQL Code to Send Email:

DECLARE
    l_sla_comment_begin VARCHAR2(100);
    l_sla_comment_end   VARCHAR2(100);
BEGIN
    -- display HTML comments only for those cases where you don't want to display SLA note
    IF :P15_PRIORITY != 'P1' THEN
      l_sla_comment_begin := '<!--';
      l_sla_comment_end := '-->';
    END IF;
    APEX_MAIL.SEND (
            p_to                 => 'firstname.lastname@xyz.com',
            p_template_static_id => 'SIMPLE_EMAIL_TEMPLATE',
            p_placeholders       => '{' ||
            '   "DESCRIPTION":'        || apex_json.stringify(:P15_DESCRIPTION) ||
            '   ,"PRIORITY":'          || apex_json.stringify(:P15_PRIORITY) ||
            '   ,"TICKET_NUMBER":'     || apex_json.stringify(:P15_TICKET_NUMBER) ||
            '   ,"SLA_COMMENT_BEGIN":' || apex_json.stringify(l_sla_comment_begin) ||
            '   ,"SLA_COMMENT_END":'   || apex_json.stringify(l_sla_comment_end) ||
            '}' 
        );
END;

This approach is working fine with all the three email clients (Microsoft Outlook, Mozilla Thunderbird & Gmail web client) I have tested.

That's it for now and stay tuned for more posts on Email templates topic.

Thank you.

Update on 10-MAY-2020: 

If you are wondering why do you need approach 2 or 3 and where can you use it, then consider below email template.

Here, if the requirement is to show "Previous Dues" section only when previous dues exists, then you can use either approach 2 or 3 to show "Previous Dues" section conditionally. You can't use approach 1 here. Because if you just pass NULL values to "previous dues" substitution strings, then "heading"  (Previous Dues) and "labels" (Due Amount, Penalty etc.) would still be displayed and that will not look great!