Velocity - Sort an arraylist by highest value

[Adam_Mokrzecki1]

Hi, I've set up a velocity script to sort a currency field of a custom object. It works as it should in most cases, however if a there is a lead that has a record with "0" as the value in that field then the script will fail (nothing will display in the email).

Here is slimmed down version of the code I was using which just pulls in (or doesn't) the field values:

#if( $engagementMilestoneDetail_cList && $engagementMilestoneDetail_cList.size() > 0 )
#set( $engagementInitialValue = $number.format("0", ${engagementMilestoneDetail_c.engagementInitialValue}) ) 
#end
#foreach( $opportunity in $sorter.sort($engagementMilestoneDetail_cList, "engagementInitialValue:desc") )
#set($ivFormat = $opportunity.engagementInitialValue )
${ivFormat}<br>
#end‍‍‍‍‍‍‍

I added the first three lines thinking it might be something with the format of the values, but that didn't help. 

Next, looked at the code below, I tried to add the data to a collection (or arraylist), but used the if statement to skip the records with the value "0". This worked! however, I'm unable to sort the arraylist in descending order so that the record with the highest Initial Value displays first. 

#set( $ivToOutput = [] )  
#foreach( $opportunity in $engagementMilestoneDetail_cList )  
           
#set( $showIV = $number.format("0", ${opportunity.engagementInitialValue}) )  
#set( $showBD = $opportunity.billingDate ) 
 
#if( $showIV == "$0" ) 
#else 
#set( $showable = {  
  "InitialValue" : $showIV,  
  "BillingDate" : $showBD  
})  
#set( $tmp = $ivToOutput.add($showable) )  
#end  
#end 

#foreach( $showable in $ivToOutput )  
IV: ${showable.InitialValue}<br>  
BD: ${showable.BillingDate}<br>  

#end‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

I think I read (or skimmed) every velocity post on the community board and I'm sure the answer lies somewhere with a  combination of some of those posts, but I don't do this type of coding often and everything is starting to meld together. Any help would be much appreciated!!

Thank you!!

[SanfordWhiteman · Accepted solution]

You can't sort on a field whose values have different datatypes.

Loop over the field once, coerce the values to the same type, then sort.

#foreach( $opportunity in $engagementMilestoneDetail_cList )
#set( $opportunity.engagementInitialValue = $convert.toDouble($opportunity.engagementInitialValue) )
#end
#foreach( $opportunity in $sorter.sort($engagementMilestoneDetail_cList, "engagementInitialValue:desc") )
#set($ivFormat = $opportunity.engagementInitialValue )
${ivFormat}<br>
#end‍‍‍‍‍‍‍

P.S. Using Currency is almost always a bad idea for reasons I've blogged about before. A scaled Integer (currency * 100) is much better.

[Adam_Mokrzecki1]

This is great! Works like a charm! Thank you so much!! 

I did read your blog about the issue with Currency fields, it was very enlightening! I wish changing the datatype was an option, but unfortunately that decision is out of my hands.  

[SanfordWhiteman]

I hear ya.