2

I'm trying to send customers a receipt via email. I'd like each item in their receipt to be sorted by date (starting with the most recent purchase).

I'm using AMPScript to populate the emails by invoking the LookupOrderedRows() function to grab rows from a Data Extension, where each row represents an item purchased. Unfortunately, simply specifying something like 'Date Desc' in LookupOrderedRows() is insufficient, since this just sorts by characters.

Is there a way I can sort these rows by date? I'm fine with workarounds.

Here's my code:

<table>
    <tr>
        <th>
            Reciept Number
        </th>
        <th>
            Purchase Date
        </th>
        <th>
            Membership Package
        </th>
        <th>
            Total Membership Price
        </th>
    </tr>
    %%[
        var @CustomerID, @rows, @row, @rowCount, @i, @ReceiptNumber, @PurchaseDate, @Package, @Price
        set @CustomerID = _SubscriberKey
        set @rows = LookupOrderedRows('DE1', '0','Date DESC','ID', @CustomerID)
        set @rowCount = rowcount(@rows)
        if @rowCount > 0 then
            for @i = 1 to @rowCount do 
                set @row = row(@rows,@i)
                set @ReceiptNumber = field(@row,'Receipt Number') 
                set @PurchaseDate = field(@row,'Date')
                set @Package = field(@row,'Item') 
                set @Price = field(@row,'Price')
    ]%%
    <tr>
        <td>
            %%=v(@ReceiptNumber)=%%
        </td>
        <td>
            %%=v(@PurchaseDate)=%%
        </td>
        <td>
            %%=v(@Package)=%%
        </td>
        <td>
            %%=v(@Price)=%%
        </td>
    </tr>
    %%[ next @i ]%%
    %%[ else ]%%
    %%[ endif ]%%
</table>
1
  • 3
    Did you verify that your 'Date' field is actually formatted as a Date in your DE? If your dates are stored as strings, then it will sort them as such. – Gortonington Jan 3 '19 at 22:06
4

To make it easier to find the resolution if anyone else has this issue:

The issue with the sorting on LookupOrderedRows() not working was due to the field format inside the DE being 'String' instead of 'Date' - meaning it was not taking into account the full date, but instead just the first number when sorting the dates.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.