SFMC.Quest

HTML Table in Ampscript

Rafał Wolsztyniak

SFMC Developer & Architect | 2023 Marketing Champion

Tabularize all the things!

If you find yourself building internal tools like for non-SFMC users, you might need to display some data from SFMC in an table (either on a CloudPage or sent in an email to your team).

Rather than reinvent the wheel each time the need arises, I decided to build a reusable snippet that does the following:

  • builds a HTML table ready to be placed anywhere on the page with a single variable
  • uses both <th> and <td> HTML elements for improved readability and styling support
  • allows to easily decide what column to display and in which order they should be

Tabularize anything!

Want to grab some data from a data view and display it on a CloudPage or in an Email? Here’s an example that looks for all subscribers with a hard-coded email address.

%%[
    /* Define the columns to display */
    set @columns = ""
    set @columns = Concat(@columns, ",", "SubscriberKey")
    set @columns = Concat(@columns, ",", "Status")
    set @columns = Concat(@columns, ",", "DateUnsubscribed")
    set @columns = Concat(@columns, ",", "DateUndeliverable")
    set @columns = Concat(@columns, ",", "BounceCount")

    set @colSet = BuildRowsetFromString(@columns, ',')

    /* Grab the data */
    set @emailAddress = 'example@example.com'
    set @tableData = LookupRows('ENT._Subscribers', 'EmailAddress', @emailAddress))

    /* Build the HTML Table */
    set @tableHTML = "<table>"

    IF RowCount(@colSet) > 0 
        THEN
            set @tableHTML = Concat(@tableHTML, "<thead><tr>")

            FOR @c = 1 TO RowCount(@colSet) DO
                set @column = Field(Row(@colSet, @c), 1)
                set @tableHTML = Concat(@tableHTML, "<th>", @column, "</th>")
            NEXT @c
            set @tableHTML = Concat(@tableHTML, "</tr></thead>")
            IF RowCount(@tableData) > 0
                THEN
                set @tableHTML = Concat(@tableHTML, "<tbody>")
                FOR @r = 1 TO RowCount(@tableData) DO

                    set @tableHTML = Concat(@tableHTML, "<tr>"))            
                    set @row = Row(@tableData, @r))

                    FOR @d = 1 TO RowCount(@colSet) DO
                        set @column = Field(Row(@colSet, @d), 1)
                        set @tableHTML = Concat(@tableHTML, "<td>", Field(@row, @column), "</td>")
                    NEXT @d

                    set @tableHTML = Concat(@tableHTML, "</tr>"))
                    
                NEXT @r
                set @tableHTML = Concat(@tableHTML, "</tbody>")
        ENDIF
    ENDIF

    set @tableHTML = Concat(@tableHTML, '</table>')
]%%
%%=v(@tableHTML)=%%

How it works

The code requires two things to work: data and column names in the order in which you want to display them.

Columns and their order

For the script to work, we need to provide it the names of the fields that should become table columns. In the end they need to be stored in their own row set (or @colSet…) to allow the script to loop through their names.

set @columns = ""

set @columns = Concat(@columns, ",", "SubscriberKey")
set @columns = Concat(@columns, ",", "Status")
set @columns = Concat(@columns, ",", "DateUnsubscribed")
set @columns = Concat(@columns, ",", "DateUndeliverable")
set @columns = Concat(@columns, ",", "BounceCount")

set @colSet = BuildRowSetFromString(@columns, ',')

Sure, this will result in a string starting with a comma:

",SubscriberKey,Status,DateUnsubscribed,DateUndeliverable,BounceCount"

Don’t worry – the script further down the line is ready for that – it will ignore the first rowSet that would be empty and start with the the second one instead.

NOTE
There's also a different lesson to be learned here:

When testing new scenarios, use new contacts that closely resemble the state in which the recipients would be in that moment:

They might not be in All Subscribers at send time? 
Make sure your contacts are not in that list

They might not be in the synchronized data extension yet? 
Don't rely on it at the time of sending

Data input

The code will work with any ampscript function that returns a set of or rows:

  • LookupRows
  • LookupRowsCS
  • LookupOrderedRows
  • LookupOrderedRowsCS
  • RetrieveSalesforceObjects
  • RetrieveSalesforceJobSources
  • BuildRowSetFromJSON (see the example below)
Note

In most cases the order of defining columns and getting data is interchangable, but it makes sense to define the columns first when using the RetrieveSalesforceObjects function – you can actually use the @colSet variable as parameter to define which columns to retrieve, for example:

RetrieveSalesforceObjects('Orders', @colSet, 'PersonContactId', '=', _subscriberkey)

Example with BuildRowSetFromJSON

Imagine you’re tasked with creating an email with a table displaying details of party 4 characters in an RPG game.

Sample JSON

You get the JSON from an API and it looks like this

{
  "crmSpecialists": [
    {
      "name": "John Smith",
      "nickname": "The Enchanter",
      "function": "Customer Acquisition",
      "department": "Sales",
      "ultimateSkill": "Mind Manipulation"
    },
    {
      "name": "Jane Johnson",
      "nickname": "The Keeper",
      "function": "Customer Retention",
      "department": "Marketing",
      "ultimateSkill": "Eternal Loyalty"
    },
    {
      "name": "Michael Brown",
      "nickname": "The Oracle",
      "function": "Data Analysis",
      "department": "Analytics",
      "ultimateSkill": "Infinite Insight"
    },
    {
      "name": "Emily Davis",
      "nickname": "The Architect",
      "function": "CRM Implementation",
      "department": "Operations",
      "ultimateSkill": "Seamless Integration"
    }
  ]
}

You store the value in the @jsonString variable (normally we don’t hard-code data like this, but this is just an example):

%%[
    set @jsonString = '{
  "crmSpecialists": [
    {
      "name": "John Smith",
      "nickname": "The Enchanter",
      "function": "Customer Acquisition",
      "department": "Sales",
      "ultimateSkill": "Mind Manipulation"
    },
    {
      "name": "Jane Johnson",
      "nickname": "The Keeper",
      "function": "Customer Retention",
      "department": "Marketing",
      "ultimateSkill": "Eternal Loyalty"
    },
    {
      "name": "Michael Brown",
      "nickname": "The Oracle",
      "function": "Data Analysis",
      "department": "Analytics",
      "ultimateSkill": "Infinite Insight"
    },
    {
      "name": "Emily Davis",
      "nickname": "The Architect",
      "function": "CRM Implementation",
      "department": "Operations",
      "ultimateSkill": "Seamless Integration"
    }
  ]
}'

Converting the array into a rowset

You use the new BuilRowsetFromJSON function to get the contents of the crmSpecialists array.

/* Convert the JSON string into a set of rows */
set @tableData = BuildRowSetFromJSON(@jsonString, '$.crmSpecialists[*]', 1)
Note

If you want to learn more about this new feature- go check out those excellent resources:

Defining the columns

Now that the rowset is created we just need to define which of the values we want to have displayed in our table. Here you can easily change the order or ommit some values that you don’t want to see.

/* Specify the columns to display */
set @columns = ""
set @columns = Concat(@columns, ",", "nickname")
set @columns = Concat(@columns, ",", "name")
set @columns = Concat(@columns, ",", "function")
set @columns = Concat(@columns, ",", "department")
set @columns = Concat(@columns, ",", "ultimateSkill")

set @colSet = BuildRowsetFromString(@columns, ',')

HTML generation & output

    /* Build the Table */
    set @tableHTML = "<table>"

    IF RowCount(@colSet) > 0 
        THEN
            set @tableHTML = Concat(@tableHTML, "<thead><tr>")

            FOR @c = 2 TO RowCount(@colSet) DO
                set @column = Field(Row(@colSet, @c), 1)
                set @tableHTML = Concat(@tableHTML, "<th>", @column, "</th>")
            NEXT @c
            set @tableHTML = Concat(@tableHTML, "</tr></thead>")
            IF RowCount(@tableData) > 0
                THEN
                set @tableHTML = Concat(@tableHTML, "<tbody>")
                FOR @r = 1 TO RowCount(@tableData) DO

                    set @tableHTML = Concat(@tableHTML, "<tr>"))            
                    set @row = Row(@tableData, @r))

                    FOR @d = 2 TO RowCount(@colSet) DO
                        set @column = Field(Row(@colSet, @d), 1)
                        set @tableHTML = Concat(@tableHTML, "<td>", Field(@row, @column), "</td>")
                    NEXT @d

                    set @tableHTML = Concat(@tableHTML, "</tr>"))
                    
                NEXT @r
                set @tableHTML = Concat(@tableHTML, "</tbody>")
        ENDIF
    ENDIF

    set @tableHTML = Concat(@tableHTML, '</table>')
]%%
%%=v(@tableHTML)=%%

With the %%=v(@tableHTML)=%% output we would get the following HTML (formatted with white space and line breaks for readability):

<table>
    <thead>
        <tr>
            <th>name</th>
            <th>nickname</th>
            <th>function</th>
            <th>department</th>
            <th>ultimateSkill</th>
        </tr>
    </thead>
    <tbody>
        <tr>
            <td>John Smith</td>
            <td>The Enchanter</td>
            <td>Customer Acquisition</td>
            <td>Sales</td>
            <td>Mind Manipulation</td>
        </tr>
        <tr>
            <td>Jane Johnson</td>
            <td>The Keeper</td>
            <td>Customer Retention</td>
            <td>Marketing</td>
            <td>Eternal Loyalty</td>
        </tr>
        <tr>
            <td>Michael Brown</td>
            <td>The Oracle</td>
            <td>Data Analysis</td>
            <td>Analytics</td>
            <td>Infinite Insight</td>
        </tr>
        <tr>
            <td>Emily Davis</td>
            <td>The Architect</td>
            <td>CRM Implementation</td>
            <td>Operations</td>
            <td>Seamless Integration</td>
        </tr>
    </tbody>
</table>

This would be displayed somewhat like this:

Table Example

when used with the following CSS:

<style>
    *{font-family: monospace}
    td, th{padding: 8px}
</style>

Full Example Code

Here’s just the full code if you want to play around with it.

%%[
    set @jsonString = '{
  "crmSpecialists": [
    {
      "name": "John Smith",
      "nickname": "The Enchanter",
      "function": "Customer Acquisition",
      "department": "Sales",
      "ultimateSkill": "Mind Manipulation"
    },
    {
      "name": "Jane Johnson",
      "nickname": "The Keeper",
      "function": "Customer Retention",
      "department": "Marketing",
      "ultimateSkill": "Eternal Loyalty"
    },
    {
      "name": "Michael Brown",
      "nickname": "The Oracle",
      "function": "Data Analysis",
      "department": "Analytics",
      "ultimateSkill": "Infinite Insight"
    },
    {
      "name": "Emily Davis",
      "nickname": "The Architect",
      "function": "CRM Implementation",
      "department": "Operations",
      "ultimateSkill": "Seamless Integration"
    }
  ]
}'

    /* Convert the JSON string into a set of rows */
    set @tableData = BuildRowSetFromJSON(@jsonString, '$.crmSpecialists[*]', 1)

    /* Specify the columns to display */
    set @columns = ""
    set @columns = Concat(@columns, ",", "nickname")
    set @columns = Concat(@columns, ",", "name")
    set @columns = Concat(@columns, ",", "function")
    set @columns = Concat(@columns, ",", "department")
    set @columns = Concat(@columns, ",", "ultimateSkill")

    set @colSet = BuildRowsetFromString(@columns, ',')

    /* Build the Table */
    set @tableHTML = "<table>"

    IF RowCount(@colSet) > 0 
        THEN
            set @tableHTML = Concat(@tableHTML, "<thead><tr>")

            FOR @c = 2 TO RowCount(@colSet) DO
                set @column = Field(Row(@colSet, @c), 1)
                set @tableHTML = Concat(@tableHTML, "<th>", @column, "</th>")
            NEXT @c
            set @tableHTML = Concat(@tableHTML, "</tr></thead>")
            IF RowCount(@tableData) > 0
                THEN
                set @tableHTML = Concat(@tableHTML, "<tbody>")
                FOR @r = 1 TO RowCount(@tableData) DO

                    set @tableHTML = Concat(@tableHTML, "<tr>"))            
                    set @row = Row(@tableData, @r))

                    FOR @d = 2 TO RowCount(@colSet) DO
                        set @column = Field(Row(@colSet, @d), 1)
                        set @tableHTML = Concat(@tableHTML, "<td>", Field(@row, @column), "</td>")
                    NEXT @d

                    set @tableHTML = Concat(@tableHTML, "</tr>"))
                    
                NEXT @r
                set @tableHTML = Concat(@tableHTML, "</tbody>")
        ENDIF
    ENDIF

    set @tableHTML = Concat(@tableHTML, '</table>')
]%%
%%=v(@tableHTML)=%%

In

, , ,