Return to Question

added 142 characters in body

Source Link

edited Jan 3, 2019 at 20:54

151.3k
38
247
431

    <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>

<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>

    <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>

<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>

Source Link

asked Jan 3, 2019 at 20:51

Carri

LookupOrderedRows() Sort by Date

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>

marketing-cloud ampscript