Tokens and importing complex data | Community
Skip to main content
Jennifer_Bisho1
Jennifer_Bisho1
Level 4
February 13, 2018
Question

Tokens and importing complex data

  • February 13, 2018
  • 1 reply
  • 7952 views

We have an email send coming up where we need to use tokens to display the contact's renewal contract number and amount.  The contact can have 10+ different contracts/amounts that need to be displayed in one email.  Rather than creating 10-20 separate fields in Marketo to house this information and individually pull into the email layout, is it possible to somehow format a field in Marketo to display information in a column (see the attached screenshot to see something similar to what we want to do).  If we could somehow get 2 Marketo fields to display the data in 2 columns like in the screenshot (one for contract number, the other for contract amount), that would be perfect.

This post is no longer active and is closed to new replies. Need help? Start a new post to ask your question.

1 reply

SanfordWhiteman
SanfordWhiteman
Level 10
February 13, 2018

This is absolutely possible, and I don't think you need even 2 fields, just one.

Use a Textarea and store a string of JSON-formatted data, i.e. a field called Contracts:

[
 {
    "Contract Number" : "912454",
    "Sum of Line Item" : 129.99
 },
 {
    "Contract Number" : "218271",
    "Sum of Line Item" : 999.12
 },
 {
   "Contract Number" : "002328",
   "Sum of Line Item" : 1305.21
 }
]

Then lay out it out using a Velocity (Email Script) token:

#set( $contracts = '#set( $contracts = ' + $lead.contracts + ' )' )
#evaluate( $contracts )
#if( !$contracts.isEmpty() )
<table>
<thead>
<tr>
<td>Contract Number</td>
<td>Sum of Line Item</td>
</tr>
</thead>
<tbody>
#foreach( $contract in $contracts )
<tr>
<td>$contract["Contract Number"]</td>
<td>$number.currency($contract["Sum of Line Item"])</td>
</tr>
#end
</tbody>
</table>
#else
You have no contracts up for renewal.
#end

This token will output the HTML:

<table>
<thead>
<tr>
<td>Contract Number</td>
<td>Sum of Line Item</td>
</tr>
</thead>
<tbody>
<tr>
<td>912454</td>
<td>$129.99</td>
</tr>
<tr>
<td>218271</td>
<td>$999.12</td>
</tr>
<tr>
<td>002328</td>
<td>$1,305.21</td>
</tr>
</tbody>
</table>
    Jennifer_Bisho1
    Jennifer_Bisho1Author
    Level 4
    February 14, 2018

    JSON and Velocity Email Scripts are definitely something I have zero experience with lol.  Where would I store these both and how would I use them?

    This seems like the values would need to be manually added to the above code.  Is there a way to do this if someone provided a spreadsheet to me with a lot of contact information, and separate columns for the contract number/amounts next to their email addresses?

    SanfordWhiteman
    SanfordWhiteman
    Level 10
    February 14, 2018

    Where would I store these both and how would I use them?

    The JSON is a Textarea field right on the lead. You might be confused by the line breaks, but that's just a string (like you would get from a Comments field).

    The Velocity token is at any level of the Marketing Activities hierarchy (depending on whether you want to override it), like other {{my.tokens}}.

    Then you include the {{my.token}} in your email as usual with other tokens.

    This seems like the values would need to be manually added to the above code.

    I wouldn't say they need to be manually added (that would be next-to-impossible to do without messing up).

    You can import into that Textarea field as you would with any field. In Excel, if you have the data in different columns to start, create a formula field to add them together into one string (with the appropriate []{}"" characters surrounding them), then that string column gets mapped to Contracts.

    Terms & ConditionsAccessibility statement

    Loading footer...