I have a velocity script as follows:
#set ($rawList = $CstPolicyList)
#set ($sortedList = $sorter.sort($rawList,"policySaleDate:desc"))
Here is Sorted List:
#if($sortedList.size() > 0)
#foreach($policy in $sortedList)
Sorted list is empty! <br>
Here is Raw List:
#if($rawList.size() > 0)
#foreach($policy in $rawList)
policySaleDate is a Marketo Date field. It may be empty.
When I run it, I get output for the second, "rawList". But nothing for the sorted list (size is 0).
Even weirder, if I change the sort column to use updatedAt:
Then I get Identical output for both the sortedList and the rawList. The "asc" and "desc" do not make a difference.
Why would the sort not return anything if I reference the "policySaleDate" field? I know it is the correct name (and I have the check box checked in the list)
because the data shows up in the email for the $rawList
Message was edited by: Digital Pi fixed some typos
I experimented sorting by the policyId (text field):
#set ($sortedList = $sorter.sort($rawList,"policyId:desc"))
This works fine, it returns a sortedList to $sortedList as expected.
The issue seems to be with the custom object policySalesDate date field. $sorter.sort does not like it for some reason.
It's not really weird! You can't mix types with a generic SortTool.sort, and comparing a null with a String value, or a Boolean with a String, or a Number with a Boolean, are all mixed types. There isn't a comparator function to derive a useful result from such comparisons, so the sort will error out and you will get no result.
In Java at large you can build custom comparators for different types, so you ensure there's a result. But in a locked-down Velocity scenario you can't do this. What you can do is iterate over the list and fill in any null with an empty-like value of the same type. Unfortunately null is the best non-value, and you can't use that or you're back where you started.
Did some more testing. There is another date field on the custom object, ("policyEffectiveDate") which is never empty.
I tried sorting on that and all works fine.
Then, I found some leads that had values for all of the the "policySalesDate" fields. Again, this worked fine.
So my conclusion is the that sorter.sort() function does not work well when sorting custom object lists with date fields that have empty values. This is unfortunate, mysterious behavior.
Did you not read my response?
Thanks Sanford. I Just read your reply. I understand what you are saying, but think that it would be much nicer to handle this the way SQL handles it, with a default sort order for NULL values or allowing you to declare the sort order for NULL. Short of that, to silently fail and return *nothing* seems completely weird to me, especially when considering the near lack of documentation of Velocity scripting in Marketo environments. ( Your blog seems to be the only authoritative source for that!)
Well... for the reason you allude to, SQL is probably not the best example of null sorting, as the order is not defined. A standards-compliant SQL engine could, strange as it would be, apply a random sort order to NULLs so they're interleaved with other values! And some engines don't let you choose between nulls-first and nulls-last so you're at the mercy of their arbitrary implementation. (And technically-technically, in implementations that only use the system order, it's not exactly that NULL is sorted to the top or bottom, it's that the result of a comparison with NULL is sorted to the top or bottom, e.g. the unknown comparison result isn't the same as NULL itself.)
At any rate, yes, this stuff is hard to find documentation for! The main reason is that Velocity is a strongly typed language masquerading as a mere scripting dialect. So it's expected that a person pushing Velocity to the fullest will find it a techie's dream (and I do kind of love it, with reservations) relative to basic scripting languages. But in reality that can be really intimidating, because the documentation seems built for people who are already Java pros. Take the SortTool doc. It says:
Each property which is to be sorted on must return one of the follow[ing]:
- Primitive type: e.g. int, char, long etc
- Standard Object: e.g. String, Integer, Long etc
- Object which implements the Comparable interface.
The sort is performed by calling Collections.sort()...
Even aside from the mention of the Comparable interface (which only is implementable if you control the objects coming in, which we don't in Marketo) this leaves a lot unspoken. What it doesn't specifically say, and which you're supposed to just kinda know, is:
Aaaanyway, if you want to sort on a String property that may have a null value, you can loop over it and set all the nulls to an empty String, then sort:
#foreach( $itm in $myList )
#set( $itm.nullableStringProp = $display.alt($itm.nullableStringProp, "") )
#set( $safeSortedList = $sorter.sort( $myList, "nullableStringProp" ) )
Similarly, if it's an Integer prop, you can set it to the lowest possible Integer:
#foreach( $itm in $myList )
#set( $itm.nullableIntegerProp = $display.alt($itm.nullableIntegerProp, $field.in(0).MIN_VALUE) )
#set( $safeSortedList = $sorter.sort( $myList, "nullableIntegerProp" ) )
Each of these will sort the formerly null values first in ascending order.
This is relatively uncharted territory and I will definitely be adding a blog post on it.