Velocity: Programming custom list data

[Travis_Schwartz]

Please let me know if what I'm wanting to do is possible.

I have a custom list that comes over every day and I have the following fields coming over

Date1: this gives the date that the person was added to the list

List ID: Referring Members

Text3: First name in all caps

What I want to do is have a token that converts the first name (Text3) to proper case for those who are on the list with list ID = "Referring Members" with the date in the date field = today.

Alternatively, if we can't do the date, just pull the Text3 information from the Custom List ID "Referring Members"

[SanfordWhiteman · Accepted solution]

---

It can't pull information from a custom object?  This feels very limiting.

---

Velocity absolutely, positively, completely* can pull information from Custom Objects.

What’s confusing people is that your Custom Object appears to be called “List” which sends people down a very different road, since they’re thinking about Static or Smart Lists.

So let’s rename your Custom Object to something else: “Party Balloon Rental”.

Your Party Balloon Rental CO has 2 fields:

• Rental ID
• Text3

So you want iterate over the list of Party Balloon Rentals to output the value of Text3 when the Rental ID is “Roy-Wambsgans-1234”.

#if( !$PartyBalloonRentals__cList.isEmpty() )
#foreach( $rental in $PartyBalloonRentals__cList )
#if( $rental.RentalID.equals("Roy-Wambsgans-1234") )
${rental.Text3}
#break
#end
#end
#end

* Well, 1st-level objects, at least.

[Jo_Pitts1]

Travis,

if the lists is coming in EVERY DAY, then why not use the API to load the data via a script and automate as much as the process as possible.  

The Script (which could by python, .net, or any language really) could do all your data cleansing for you.  That being the nature of ETL (Extract, Transform, Load) processes.

If you convert the name everytime you want to use it, then at some point - someone will forget to use the magic token, and you'll go out saying things like...

Dear TRAVIS

Regards

Jo

[Phillip_Wild]

Yes, this is the best way. Given it's coming across daily, any time that you or your development team spends actually automating this will save you 10 minutes a day, forever.

As others have noted, Velocity can't do anything in this situation - it's only used for email templating (running code at time of email send to personalise elements). Nothing that it can do with a custom object import.

[Travis_Schwartz]

It can't pull information from a custom object?  This feels very limiting.

I don't want it to do anything to the import. What I want is for it to:

1.  See a member has this custom object

2. The object is a custom list (I'm not sure if this is universal to Marketo or just our instance)

3. That list ID is "Referring Members"

Then take the name that is in the field called "Text3", convert it to proper case, and display it in the email to personalize the email so that the email can say something like:

Hey, (name)!

Thanks for joining, we're glad (new token) referred you.

[SanfordWhiteman · Accepted solution]

---

It can't pull information from a custom object?  This feels very limiting.

---

Velocity absolutely, positively, completely* can pull information from Custom Objects.

What’s confusing people is that your Custom Object appears to be called “List” which sends people down a very different road, since they’re thinking about Static or Smart Lists.

So let’s rename your Custom Object to something else: “Party Balloon Rental”.

Your Party Balloon Rental CO has 2 fields:

• Rental ID
• Text3

So you want iterate over the list of Party Balloon Rentals to output the value of Text3 when the Rental ID is “Roy-Wambsgans-1234”.

#if( !$PartyBalloonRentals__cList.isEmpty() )
#foreach( $rental in $PartyBalloonRentals__cList )
#if( $rental.RentalID.equals("Roy-Wambsgans-1234") )
${rental.Text3}
#break
#end
#end
#end

* Well, 1st-level objects, at least.

[Travis_Schwartz]

Thank you so much.

Yes... we have custom objects that are called custom lists (not sure why... that was the naming convention when I got here, and I came from a different system, so I assumed that was standard.) 

by default, our system capitalizes everything. How would I take

${rental.Text3}

and convert that to proper case?

[SanfordWhiteman]

Assuming by “proper” you mean first letter uppercase, the rest lowercase:

#if( !$rental.Text3.isEmpty() )
#set( $reCased = $rental.Text3.substring(0,1).toUpperCase() + $rental.Text3.substring(1).toLowerCase() )
#end

[Travis_Schwartz]

So I was able to follow and substitute much of the token values, but I'm stuck on one part

#if( !$customList__cList.isEmpty() )
#foreach( $rental in $customList__cList )
#if( $rental.listID.equals("Referred Members") )
${rental.Text3}
#break
#end
#end
#end

#if( !$rental.Text3.isEmpty() )
#set( $reCased = $rental.Text3.substring(0,1).toUpperCase() + $rental.Text3.substring(1).toLowerCase() )
#end

What do I replace the

$rental

with?

Other than that, did I miss anything?

[SanfordWhiteman · Accepted solution]

This won’t work, you need to set the displayable item inside the loop.

#if( !$customList__cList.isEmpty() )
#foreach( $rental in $customList__cList )
#if( $rental.listID.equals("Referred Members") && !$rental.Text3.isEmpty() )
#set( $reCased = $rental.Text3.substring(0,1).toUpperCase() + $rental.Text3.substring(1).toLowerCase() )
#break
#end
#end
#end
${reCased}

You can rename the loop variable $rental to anything you want. $customList for example.

[Travis_Schwartz]

Just curious about something on this... let's use the balloon rental example from before:

#if( !$PartyBalloonRentals__cList.isEmpty() )
#foreach( $rental in $PartyBalloonRentals__cList )
#if( $rental.RentalID.equals("Roy-Wambsgans-1234") )
${rental.Text3}
#break
#end
#end
#end

If there was another field (date1), would you be able to differentiate between custom objects with the same ID?

Using the balloon example, could someone have multiple orders that you could identify with something like

#if( $rental.date1.equals(Todays_date) )

[Travis_Schwartz]

I can do it as a constraint in my smart list... was just curious if velocity could do it as well.

[Jo_Pitts1]

@Travis_Schwartz,

Yes you can do that, and there are different use cases for both the Velocity approach, and the Smart List approach.

Smart List

You'd use this if you (e.g.) only wanted people who'd rented balloons today (using your example)

Velocity

You'd use this if you wanted to have a different criteria in your smart list (maybe dollarsSpent > $x), but wanted to highlight today's order (maybe bold it, or display it separately or something).

Cheers

[Phillip_Wild]

Hi @Travis_Schwartz ,

Yes, that would be possible, but it's certainly not simple Velocity scripting. 

In these situations, you effectively have multiple conditions - I would build out a script that tackles each of them separately, get comfortable with the logic and how Velocity works, and then combine them once they are all working.

There is a lot on the Community already around checking for today's date - while working with dates is complicated, you could definitely find some code to work off on that point. 

To make a name title case, that would be something like 

$s_attribute.name.substring(0,1).toUpperCase() $s_attribute.name.substring(1).toLowerCase()

I think?

Overall this is relatively complex, I would recommend biting off smaller chunks if you aren't already comfortable in Velocity. That will help to build your understanding.

[Phillip_Wild]

Ah, just read @SanfordWhiteman 's response: I assumed you meant as email output, which is the only place Velocity can operate. If you mean changing the actual field values in Marketo, no, that can't be done. You'd have to do it in Excel / Google Sheets before import.

[SanfordWhiteman]

To start off, when you say "convert" do you mean permanently altering the field value? Because Velocity can't do that.

Also, Velocity cannot itself check Static List membership, nor Smart List membership.* You can ensure that only members of a specific list are sent an email in the first place, which gives the same result. But once someone qualifies for a send and they’re in Velocity-land, Velocity doesn’t care/know how they got there.

* You can, btw, check Segmentation membership from within Velocity —

and a Segmentation can be based on List membership, so again you get the desired result.

But Velocity isn’t actually checking the List directly.

[Travis_Schwartz]

Sorry I was not clear. I do not want to change the field values. I was just providing what I had to work with to create the script. I'm not concerned with qualifying for the communication (that is going to be "was added to list today").

I am working on a communication to essentially say "Thank you for referring (name)" the name is in the Text3 field on that custom list. What I was hoping would be that Velocity could also look at the date field as a way of differentiating between multiple (in the instance it happens) referrals.

So the script would need to look at someone added to the custom list (List ID = Referring Members) and hopefully be able to look at the date they qualified (Date1) and output a name (Text3) converted to proper case.

I hope that is more clear. I'm just looking to get the name from the list for the email so we can personalize the email based on the text3 field of that custom list.

Thanks

[SanfordWhiteman]

What is this "custom list"? A Custom Object import?

[Travis_Schwartz]

Correct.