Velocity script support - Personalisation tokens from Custom Object entries.

[PaddyRR]

Hello - Thank you for reading. Any support would be very much appreciated. 

Background:

We are a housing development business, building and selling plots/houses on housing developments in the UK.

We have a unique, proprietary CRM that is very difficult to influence hence, the seemingly "roundabaout" solution I am about the explain.

Solution summary:

When a customer enquired/expresses interest about a housing development, a unique Custom object entry is created in our Marketo instance, linked to the person record. 

We would like to personalise a range of emails with contextualised information based on these enquiries. 

Our solution is to use velocity script to populate tokens within a high-level folder, that can then be inherited and utilised within the subsequent emails. 

This simple script is essentially, updating the token with the information of that specific development based on the "EnquiryOutletName" field.

Issue summary:

This is working great for us! However, there are two key flaws:

1. A customer will often have multiple enquiries linked to their record. Over time, these enquires can be updated so that the "EnquiryOutletStatus:" field shows a value other than "Open". We would like to velocity, to only select an enquiry IF that field equals "Open".

2. When the above update is made, the custom object is updated and brought to the top of the list. it appears that the velocity script is currently selecting the most recently updated entry based on this  "Updated at" field. We would like to stipulate that the script selects the entry with the most recent "EnquiryDate:" field instead. 

To summarise the required script criteria, I would like
IF "EnquiryOutletName" equals "XXX", SET tokens as "XXX  IF  "EnquiryOutletStatus:" Equals Open AND  "EnquiryDate:" is the most recent from all entries.

The issue is that I do not know, and cannot find, how to write that script correctly. 

I would REALLY appreciate any help to do so!

[Jo_Pitts1]

@PaddyRR ,

Can you check back in and let us know if the solutions proposed are fit for purpose.  There has been quite a lot of effort gone in here.

Cheers

Jo

[Jo_Pitts1 · Accepted solution]

@PaddyRR ,

Here is a consolidated version taking the sorted list approach, and a method of controlling the number of open enquiries to display.

#set ($maxDevelopmentToDisplay = 2)
#set ($displayCounter = 0)

#set( $allDevelopments = {
  "Westley Green, Langdon Hills" : {
    "Development_Name":"Westley Green",
    "Development_Description":"XXX",
    "Development_Address":"XXX", 
    "Development_Brochure":"XXX", 
    "Development_PageURL":"XXX", 
    "Development_BookingURL":"XXX", 
    "Development_HeaderIMG":"XXX"
  },
  "Meadow View, Silver End" : {
    "Development_Name":"Meadow View",
    "Development_Description":"XXX",
    "Development_Address":"XXX", 
    "Development_Brochure":"XXX", 
    "Development_PageURL":"XXX", 
    "Development_BookingURL":"XXX", 
    "Development_HeaderIMG":"XXX"
  },
  "The Mulberries, Witham":{
    "Development_Name":"The Mulberries",
    "Development_Description":"XXX",
    "Development_Address":"XXX", 
    "Development_Brochure":"XXX", 
    "Development_PageURL":"XXX", 
    "Development_BookingURL":"XXX", 
    "Development_HeaderIMG":"XXX"
  }
  }
)

#set($enquiryListSorted = $sorter.sort($enquiry_cList, ["EnquiryDate:desc"]))

#foreach($enquiry in $enquiryListSorted )
  #if($enquiry.EnquiryOutletStatus.equals("Open"))
    #set($aDevelopment = $allDevelopments[$enquiry.enquiryOutletName] )
    <table>
    <tr>
    <td>Welcome to ${aDevelopment.Development_Name}</td>
    </tr>
    <tr>
    <td>${aDevelopment.Development_Description}</td>
    </tr>
    <tr>
    <td>We're located at:<br>${aDevelopment.Development_Address}</td>
    </tr>
    </table>
    #set ($displayCounter = $math.add($displayCounter ,1))
    #if ( $displayCounter.equals($maxDevelopmentToDisplay) )
      #break
    #end
  #end
#end

Let me know if this suits.

Cheers

Jo

[PaddyRR]

@Jo_Pitts1 @SaurabhGoyal_GN @SanfordWhiteman 

Your help is REALLY appreciated with this!!
I am working through this as we speak to test the solution and will report back ASAP.

Thanks again!!

[PaddyRR]

@Jo_Pitts1 @SaurabhGoyal_GN @SanfordWhiteman 

Thanks again for you help with this. 

I am struggling to implement the solution and from talking this through with my team, I am not sure if it will be fit for purpose. 

From what I understand the implement your solution the Script would need to be applied very specifically to emails, referencing the email HTML in the code itself. 

A big benefit with the original solution as in the code below is that the script and tokens can live in a high-level folder meaning the "Development" tokens can be easily referenced by any email assets within that folder. 

I wonder if you know of any way to make this work using something like the below? (P.s. This code still doesn't seem to pull the correct object info into the tokens)

#set($enquiry = $sorter.sort(${enquiry_cList}, ["EnquiryDate:desc"]))
#foreach($enq in $enquiry)

#if($enq.enquiryOutletName == "Westley Green, Langdon Hills" && $enq.enquiryOutletStatus == "Open")
        #set ( $Development_Name = "Westley Green") 
        #set ( $Development_Description = "XX.") 
        #set ( $Development_Address = "XX") 
        #set ( $Development_Brochure = "XX") 
        #set ( $Development_PageURL = "XX") 
        #set ( $Development_BookingURL = "XX") 
        #set ( $Development_HeaderIMG = "XX")
       
#elseif ($enq.enquiryOutletName =="Meadow View, Silver End" && $enq.enquiryOutletStatus == "Open")
        #set ( $Development_Name = "Meadow View") 
        #set ( $Development_Description = "XX") 
        #set ( $Development_Address = "XX") 
        #set ( $Development_Brochure = "XX") 
        #set ( $Development_PageURL = "XX") 
        #set ( $Development_BookingURL = "XX") 
        #set ( $Development_HeaderIMG = "XX")        

#elseif ($enq.enquiryOutletName =="The Mulberries, Witham" && $enq.enquiryOutletStatus == "Open")
        #set ( $Development_Name = "The Mulberries") 
        #set ( $Development_Description = "XX") 
        #set ( $Development_Address = "XX") 
        #set ( $Development_Brochure = "XX") 
        #set ( $Development_PageURL = "XX") 
        #set ( $Development_BookingURL = "XX") 
        #set ( $Development_HeaderIMG = "XX")

#end
#end

[Jo_Pitts1]

@PaddyRR ,

There are many baffling things in your statement most recent reply.  I'm going to try and unpack them all.

---

@PaddyRR wrote:

From what I understand the implement your solution the Script would need to be applied very specifically to emails, referencing the email HTML in the code itself. 

VTL only works with email (in the Marketo context at least).  Do you mean 'would need to be applied to specific emails'?

---

@PaddyRR wrote:

From what I understand the implement your solution the Script would need to be applied very specifically to emails, referencing the email HTML in the code itself. 

---

The output stage is only there as an example.  You can use the code as provided, strip the output stage and then have separate more email specific tokens to do the output.  I am now having to make a presumption that you only want to generate the output for the most recent enquiry.  If that presumption is correct, then something along these lines would work.

#set ($maxDevelopmentToDisplay = 1)
#set ($displayCounter = 0)

#set( $allDevelopments = {
  "Westley Green, Langdon Hills" : {
    "Development_Name":"Westley Green",
    "Development_Description":"XXX",
    "Development_Address":"XXX", 
    "Development_Brochure":"XXX", 
    "Development_PageURL":"XXX", 
    "Development_BookingURL":"XXX", 
    "Development_HeaderIMG":"XXX"
  },
  "Meadow View, Silver End" : {
    "Development_Name":"Meadow View",
    "Development_Description":"XXX",
    "Development_Address":"XXX", 
    "Development_Brochure":"XXX", 
    "Development_PageURL":"XXX", 
    "Development_BookingURL":"XXX", 
    "Development_HeaderIMG":"XXX"
  },
  "The Mulberries, Witham":{
    "Development_Name":"The Mulberries",
    "Development_Description":"XXX",
    "Development_Address":"XXX", 
    "Development_Brochure":"XXX", 
    "Development_PageURL":"XXX", 
    "Development_BookingURL":"XXX", 
    "Development_HeaderIMG":"XXX"
  }
  }
)

#set($enquiryListSorted = $sorter.sort($enquiry_cList, ["EnquiryDate:desc"]))

#foreach($enquiry in $enquiryListSorted )
  #if($enquiry.EnquiryOutletStatus.equals("Open"))
    #set($aDevelopment = $allDevelopments[$enquiry.enquiryOutletName] )
    #set ($displayCounter = $math.add($displayCounter ,1))
    #if ( $displayCounter.equals($maxDevelopmentToDisplay) )
      #break
    #end
  #end
#end

Put that token at the very top of your email (heck, you might even want to embed it in the template).

and as a separate token (or tokens) - create one per variable:

${aDevelopment.Development_Name}

That will output 100% unformatted text.  Insert that token inside any piece of HTML and it'll naturally take on that formatting.

There are further tricks you could do here to allow this to be used for single or multiple development enquiries and still separate the logic for working out what to output and the actual output, but I think we'll leave that for another day.

---

@PaddyRR wrote:

I wonder if you know of any way to make this work using something like the below? (P.s. This code still doesn't seem to pull the correct object info into the tokens)

---

Have you ensured all relevant fields have been ticked in the list on the RHS?  If not, the don't get passed into Velocity.  This is to help memory management.

I am wondering if @SanfordWhiteman's statement is correct.  You may need to seek internal help from someone with more years of dev experience or contract in someone with experience in this space.

Cheers

Jo

[SanfordWhiteman]

---

I am struggling to implement the solution and from talking this through with my team, I am not sure if it will be fit for purpose. 

From what I understand the implement your solution the Script would need to be applied very specifically to emails, referencing the email HTML in the code itself. 

Not at all. It depends on how you deploy the logic. However, most inexperienced Velocity developers struggle with understanding how to have logic and loops implemented in VTL, while HTML output is both inside and outside VTL tokens. I might suggest you get a more experienced dev on your team.

[SaurabhGoyal_GN]

Hi @PaddyRR  - As I understand from your reply, You dont want to have any kind of HTML code in the velocity script token so that you can just populate required value in the email using the token. In that case, you will have to create multiple tokens. A new one for a different field. For e.g. 

For outlet_name, you can use this one - This one will give you the Name of the Outlet, where an recent enquiry was made AND having status as "Open"

{{my.outlet_name}}

#set ($maxDevelopmentToDisplay = 2)
#set ($displayCounter = 0)
#set($enquiryListSorted = $sorter.sort(${enquiry_cList}, ["EnquiryDate:desc"]))
#foreach($enquiry in $enquiryListSorted)
  #if($enquiry.enquiryOutletName.equals("Westley Green, Langdon Hills") && $enquiry.enquiryOutletStatus.equals("Open"))
        #set ( $outlet_Name = "Westley Green")
  #elseif ($enquiry.enquiryOutletName.equals("Meadow View, Silver End") && $enquiry.enquiryOutletStatus.equals("Open"))
        #set ( $outlet_Name = "Meadow View")     
  #elseif ($enquiry.enquiryOutletName.equals("The Mulberries, Witham") && $enquiry.enquiryOutletStatus.equals("Open"))
        #set ( $outlet_Name = "The Mulberries") 
  #end
#set ($displayCounter = $math.add($displayCounter ,1))
  #if ( $displayCounter.equals($maxDevelopmentToDisplay) )
  #break
  #end
#end

$outlet_Name

Similarly, you can create the other tokens for different field values. 

I just wanted to confirm one thing with you, Is this the "Westley Green, Langdon Hills" exact value of the "enquiryOutletName" in CO.

BR,
Saurabh

[SaurabhGoyal_GN]

@Jo_Pitts1 - Thanks for the suggestions, Got to learn new things. 

[SaurabhGoyal_GN]

Hi @PaddyRR - 

Please review the solution provided below:

In order to achieve the desired outcome, we are implementing the following steps:
1. Sorting the custom object records based on enquiryDate in ascending order.
2. Utilizing a for loop to iterate through the records.
3. On the first instance where EnquiryOutletStatus is identified as "open," we will evaluate the conditions specified for enquiryOutletName to determine the desired outcome.

Here is the code for your reference - 

 #set( $alloulets = {
			  "Westley Green, Langdon Hills" : {
				"outlet_Name":"Westley Green",
				"outlet_Description":"XXX",
				"outlet_Address":"XXX", 
				"outlet_Brochure":"XXX", 
				"outlet_PageURL":"XXX", 
				"outlet_BookingURL":"XXX", 
				"outlet_HeaderIMG":"XXX"
			  },
			  "Meadow View, Silver End" : {
				"outlet_Name":"Meadow View",
				"outlet_Description":"XXX",
				"outlet_Address":"XXX", 
				"outlet_Brochure":"XXX", 
				"outlet_PageURL":"XXX", 
				"outlet_BookingURL":"XXX", 
				"outlet_HeaderIMG":"XXX"
			  },
			  "The Mulberries, Witham":{
			     "outlet_Name":"The Mulberries",
				 "outlet_Description":"XXX",
				 "outlet_Address":"XXX", 
				 "outlet_Brochure":"XXX", 
				 "outlet_PageURL":"XXX", 
				 "outlet_BookingURL":"XXX", 
				 "outlet_HeaderIMG":"XXX"
			  }
			  }
             )
         #set($enquiry = $sorter.sort(${enquiry_cList}, ["EnquiryDate:desc"]))
         #foreach($enq in $enquiry)
		  #if($enq.EnquiryOutletStatus.equals("Open"))
					#set($aOutlet = $alloutlets[$enq.enquiryOutletName] )
					<table>
					<tr>
					<td>Name - to $aoutlet.outlet_Name</td>
					</tr>
					<tr>
					<td>Description - $aOutlet.outlet_Description</td>
					</tr>
					<tr>
					<td>Location - $aOutlet.outlet_Address</td>
					</tr>
					<tr>
					<td>Brochure - $aOutlet.outlet_Brochure</td>
					</tr>
					</table>
					#break
		  #end
		 #end

If this solution meets your requirements, please mark it as the solution.

[SanfordWhiteman]

Please use the Syntax Highlighter (“Insert/Edit Code Sample”) when posting code so it’s readable. I edited your post this time.

There are some fundamental errors in your code:

1. using the == operator instead of .equals() (as discussed in the past, == can cause hard-to-find bugs because of the way it coerces its operands)

2. incorrect use of #break (the OP didn’t yet indicate that only one open record should be output)

Check@Jo_Pitts1’s answer above for some hints and the recommended config-first approach.

[SaurabhGoyal_GN]

@SanfordWhiteman - Thanks for the suggestions. Made the required changes. 

@PaddyRR - You can remove #break if you want multiple entries in the output. With break statement it will stop on the first encounter. 

 #set( $alloutlets = {
			  "Westley Green, Langdon Hills" : {
				"outlet_Name":"Westley Green",
				"outlet_Description":"XXX",
				"outlet_Address":"XXX", 
				"outlet_Brochure":"XXX", 
				"outlet_PageURL":"XXX", 
				"outlet_BookingURL":"XXX", 
				"outlet_HeaderIMG":"XXX"
			  },
			  "Meadow View, Silver End" : {
				"outlet_Name":"Meadow View",
				"outlet_Description":"XXX",
				"outlet_Address":"XXX", 
				"outlet_Brochure":"XXX", 
				"outlet_PageURL":"XXX", 
				"outlet_BookingURL":"XXX", 
				"outlet_HeaderIMG":"XXX"
			  },
			  "The Mulberries, Witham":{
			     "outlet_Name":"The Mulberries",
				 "outlet_Description":"XXX",
				 "outlet_Address":"XXX", 
				 "outlet_Brochure":"XXX", 
				 "outlet_PageURL":"XXX", 
				 "outlet_BookingURL":"XXX", 
				 "outlet_HeaderIMG":"XXX"
			  }
			  }
             )
         #set($enquiry = $sorter.sort(${enquiry_cList}, ["EnquiryDate:desc"]))
         #foreach($enq in $enquiry)
		  #if($enq.EnquiryOutletStatus.equals("Open"))
					#set($aOutlet = $alloutlets[$enq.enquiryOutletName] )
					<table>
					<tr>
					<td>Name - to $aoutlet.outlet_Name</td>
					</tr>
					<tr>
					<td>Description - $aOutlet.outlet_Description</td>
					</tr>
					<tr>
					<td>Location - $aOutlet.outlet_Address</td>
					</tr>
					<tr>
					<td>Brochure - $aOutlet.outlet_Brochure</td>
					</tr>
					</table>
					#break
		  #end
		 #end

 
Changed the code in original reply as well. 

[Jo_Pitts1]

@SaurabhGoyal_GN ,

You've got a variable called $alloulets at the top (note there is no 't' in the spelling) and you refer to $alloutlets further down (with a 't').  

I do like that you have sorted the list to put the most recent enquiry first.

That being said, in your sort, you sort $enquiry_clist to $enquiry.  You then iterate your loop with a variable called $enq.  The problem with this is that both $enquiry and $enq are singular and very similar.  You'd do better to sort $enquiry_clist into (for example) $enquiryListSorted.  The variable name for the sorted list clearly states what's gone on and why it exists.

Rather than a hard break, I'd be more inclined to use a counter with a max value and a counter set at the beginning e.g.

## At the top of your code
#set ($maxOutletsToDisplay = 2)
#set ($outletCounter = 0)
....
....
....
## After an outlet has been displayed
#set ($outletCounter = $math.add($outletCounter ,1))
#if ( $outletCounter.equals($maxOutletsToDisplay) )
  #break
#end

Without constantly having to change the more delicate code in the output stage, this gives increased flexibility.  You could even use the burger trick to load in a Marketo variable for how many outlets to display and control it without any code changes at all.

As always, I've not tested my code so there are likely bugs in it.

Cheers

Jo

[SanfordWhiteman]

Please replace that screenshot with actual code, using the Java syntax highlighter so it’s readable.

[PaddyRR]

Sure @SanfordWhiteman - See here:

Thank you for your support

#if ( $enquiry_cList.get(0).enquiryOutletName.equals("Westley Green, Langdon Hills"))
        #set ( $Development_Name = "Westley Green") 
        #set ( $Development_Description = "XXX") 
        #set ( $Development_Address = "XXX") 
        #set ( $Development_Brochure = "XXX") 
        #set ( $Development_PageURL = "XXX") 
        #set ( $Development_BookingURL = "XXX") 
        #set ( $Development_HeaderIMG = "XXX")
        
#elseif ( $enquiry_cList.get(0).enquiryOutletName.equals("Meadow View, Silver End"))
        #set ( $Development_Name = "Meadow View") 
        #set ( $Development_Description = "XXX") 
        #set ( $Development_Description = "XXX") 
        #set ( $Development_Address = "XXX") 
        #set ( $Development_Brochure = "XXX") 
        #set ( $Development_PageURL = "XXX") 
        #set ( $Development_BookingURL = "XXX") 
        #set ( $Development_HeaderIMG = "XXX")
        
#else ( $enquiry_cList.get(0).enquiryOutletName.equals("The Mulberries, Witham"))
        #set ( $Development_Name = "The Mulberries") 
         #set ( $Development_Description = "XXX") 
        #set ( $Development_Description = "XXX") 
        #set ( $Development_Address = "XXX") 
        #set ( $Development_Brochure = "XXX") 
        #set ( $Development_PageURL = "XXX") 
        #set ( $Development_BookingURL = "XXX") 
        #set ( $Development_HeaderIMG = "XXX")

#end

[Jo_Pitts1]

@PaddyRR ,

before we can solve this problem, there is something you've not stated.  Can someone have more than one open enquiry?

Regards

Jo

[PaddyRR]

Yes, there can indeed be multiple open enquiries.

Thanks for your help

[Jo_Pitts1]

@PaddyRR , OK.. so the if construct really isn't ideal.

What you'll want to do is set up the data as a map.  Loop through your custom objects. Test to see if they are open, and which value in the map they relate to.  Output the correct details for that CO, and then move on to the next.

Something like this:

#set( $allDevelopments = {
  "Westley Green, Langdon Hills" : {
    "Development_Name":"Westley Green",
    "Development_Description":"XXX",
    "Development_Address":"XXX", 
    "Development_Brochure":"XXX", 
    "Development_PageURL":"XXX", 
    "Development_BookingURL":"XXX", 
    "Development_HeaderIMG":"XXX"
  },
  "Meadow View, Silver End" : {
    "Development_Name":"Meadow View",
    "Development_Description":"XXX",
    "Development_Address":"XXX", 
    "Development_Brochure":"XXX", 
    "Development_PageURL":"XXX", 
    "Development_BookingURL":"XXX", 
    "Development_HeaderIMG":"XXX"
  }
  }
)

#foreach($enquiry in $enquiry_cList)
  #if($enquiry.EnquiryOutletStatus.equals("Open"))
    #set($aDevelopment = $allDevelopments[$enquiry.enquiryOutletName] )
    <table>
    <tr>
    <td>Welcome to ${aDevelopment.Development_Name}</td>
    </tr>
    <tr>
    <td>${aDevelopment.Development_Description}</td>
    </tr>
    <tr>
    <td>We're located at:<br>${aDevelopment.Development_Address}</td>
    </tr>
    </table>
  #end
#end

I've not tested the code, so it's bound to have bugs. 

Doing it like this, it separates the data set up from your coding logic, which means adding/changing development details is a simpler and less risky job.  It also means you don't have endless if statements.

You can do whatever you want at the output stage.  The three I've put in a table but with zero formatting.  

Let me know how you get on.

[Darshil_Shah1]

@PaddyRR, you should ideally not reference the 0th or the first element of the custom object list without sorting. You should ideally sort the custom object list based on your field of interest, and then pick the custom object record. You should check out Sandy’s blog on sorting custom object here.

Moreover, you should also take care of normalizing/settling the null values for the field you are using to sort the list. This isn’t required if you are using any of the system fields, such as UpdatedAt or CreatedAt, but required for the non-system custom object fields. 

[PaddyRR]

Sorry, @Darshil_Shah1 , Thanks for you help but I am struggling to understand this. 

Are you able to suggest how I would need to update the script here?

[ShannonKelly1]

Hi @PaddyRR , I think Darshil's response here might be helpful to you. He references Sandy's blog post Sorting objects and lists in Velocity.