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!

[ShannonKPerkuto]

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. 

[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

[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?

[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.

[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.