Script Token - Sorting Opportunities

[Robert_Whited]

I am new to using script tokens in Marketo so please excuse my ignorance...

I am trying to create a script token that pulls a field value on the Opportunity Object. This field represents a customer's Line of Credit balance remaining, LOC__c. The screenshot below shows the script when I drag.

A person will have one or more Opportunities, but only one in the list will have a Line of Credit balance remaining. The others will not have a value for LOC__c.

When I attempt to sort the list of Opportunities by LOC__c by the highest value first, the script only works when a person has 1 Opportunity:

#if( $OpportunityList && $OpportunityList.size() > 0 )

#set( $OpportunityListHighestFirst = $sorter.sort($OpportunityList,["LOC__c:desc"]) )

#end

${OpportunityListHighestFirst[0].LOC__c}

Any assistance would be welcomed!

[SanfordWhiteman]

Can you please highlight the code using the Advanced Editor's syntax highlighter? Then we'll continue.

[Robert_Whited]

#if( $OpportunityList && $OpportunityList.size() > 0 )
#set( $OpportunityListHighestFirst = $sorter.sort($OpportunityList,["LOC__c:desc"]) )
#end
${OpportunityListHighestFirst[0].LOC__c}‍‍‍‍

[SanfordWhiteman]

OK, you're running into a problem that encompasses far more than just Marketo, Velocity, or even the underlying Java runtime: null values have no sort order.

The correct way to sort nulls (the way that is both professional and foolproof) is to use what's called a NullComparator -- a special comparison wrapper that gives null a forced order within a list, either first (NullsFirstComparator) or last (NullsLastComparator). (In your case you would would want NullsFirst because the no-value is supposed to be less than any value.)

But though I could tell you how to use a NullComparator in Velocity, you wouldn't be able to use the code after June 14th because of upcoming changes to Marketo.

So, though I'm not happy about it, I have to recommend the not-very-professional method, and that is to change every null value to a value that, within your domain, is the lowest possible value. For the moment let's assume that's -1. (Maybe it's -Infinity, not sure if there are negative values in your case.)

#if( !$OpportunityList.isEmpty() )
#foreach( $opportunity in $OpportunityList )
#set( $opportunity.LOC__c = $display.alt($opportunity.LOC__c, -1) )
#end
#set( $OpportunityListHighestFirst = $sorter.sort($OpportunityList,["LOC__c:desc"]) )
${OpportunityListHighestFirst[0].LOC__c}#end‍‍‍‍‍‍‍‍‍‍‍‍

The other thing to note, though it doesn't apply here, is that you can't sort on properties that have different datatypes across the objects list.  That's different matter from null but another thing that can make sorting surprising.

[Robert_Whited · Accepted solution]

That did it.

Thank you sir!

[SanfordWhiteman]

Mark my answer as Correct? Thanks.

[SanfordWhiteman]

Ha, was thinkin' about my answer.