AMPscript LookUpOrderedRows and Loop: if statement to check all values of a field

Question

I'm a real novice with AMPscript, and I have some that works for listing items on an order for one customer in one-to-many relationship. Now I have another challenge, and I need to add something to the loop logic. I just don't know where in the loop this piece goes.

I have data extension "Order_Data" with example data such as below:

Order Number   Customer Number   Item ID   Price   Qty   Web Enabled
1              101               Shirt     $5      1     Y
1              101               Shoes     $10     1     N
2              201               Pants     $7      1     N
3              201               Shirt     $5      1     Y
4              401               Shirt     $5      1     Y
4              401               Hat       $2      1     Y

Below is my AMPscript that summarizes their order in a really simplified table (sorry if the HTML is messy for reading, for some reason it makes most sense to me this way). So customer 101 receives 1 email with 2 items in the table. Customer 2 receives 2 emails, with one item in each email, etc. This all works fine so far.

  %%[
var @rows, @row, @rowCount, @numRowsToReturn, @OrderNo, @i, @prevOrderNo

set @OrderNo = AttributeValue("Order Number")
set @numRowsToReturn = 0 /* 0 means all */
set @rows = LookupOrderedRows("Order_Data", @numRowsToReturn, "Customer Number", "Order Number", @OrderNo)

set @rowCount = rowcount(@rows)

if @rowCount > 0 then

    set @prevOrderNo = ""

    for @i = 1 to @rowCount do

        var @customerno, @itemid, @price, @qty, @webenabled, @webofferblock
        set @row = row(@rows, @i) /* get row based on loop counter */

        set @customerno = field(@row,"Customer Number")
        set @itemid = field(@row,"Item ID")
        set @price = field(@row,"Price")
        set @qty = field(@row,"Qty")
        set @webenabled = field(@row,"Web Enabled")

      IF @webenabled == "Y" THEN
      SET @webofferblock = CONCAT("You can buy these items online! Save 10% when you use discount code EXAMPLE.")
      ENDIF
      
 if empty(@prevOrderNo) or @prevOrderNo != @OrderNo then 
           outputline(concat("Order # ", @OrderNo))
           outputline(concat("<table>
    <td>QTY</td>
    <td>ITEM</td>
    <td>UNIT PRICE</td></tr>"))
           set @prevOrderNo = @OrderNo
        endif

 outputline(concat("<tr><td>", @qty, "</td>"))
 outputline(concat("<td>", @itemid, "</td>"))
 outputline(concat("<td>$",@price, "</td></tr>"))


 
next @i
 outputline(concat("</table><br>", @webofferblock))

else

    outputline(concat("<br>No transactionsList rows found"))

endif

]%%

Here's the piece I can't figure out. I want to check the value of all the Web Enabled rows per item id, and only if all are Y, display the webofferblock variable (but outside the table and only once). You can see I have an IF statement set up attempting that, but what it's actually doing (I think) is checking only if the last item id in the loop is Web Enabled == Y, and if so, it displays the message in webofferblock.

Is what I want possible? I was thinking something like "If the count of items in the loop with a Web Enabled value of Y == the count of total items in the loop, then display webofferblock." But I'm a newbie to the syntax and can't figure out the loop logic.

Answer 1

I'd suggest to switch to opening / closing ampscript and plain HTML instead of outputline - the structure does get lost a little.

Other than that - the only thing you are missing seems to be a simple reversal of your logic.

From:

"only if all are Y, display the webofferblock variable"

To:

"if one is NOT Y, hide the block."

If that's the case, define a new variable as a kind of "marker". I indicated with comments where I inserted that bit.

After the end of the for loop, simply make the display of your block conditional under that marker condition, see second set of comments.

var @rows, @row, @rowCount, @numRowsToReturn, @OrderNo, @i, @prevOrderNo

SET @OrderNo = AttributeValue("Order Number")
SET @numRowsToReturn = 0 /* 0 means all */
SET @rows = LookupOrderedRows("Order_Data", @numRowsToReturn, "Customer Number", "Order Number", @OrderNo)
SET @rowCount = rowcount(@rows)

IF @rowCount > 0 THEN

  SET @prevOrderNo = ""

  FOR @i = 1 to @rowCount DO

    var @customerno, @itemid, @price, @qty, @webenabled, @webofferblock
    SET @row = row(@rows, @i) /* get row based on loop counter */
    SET @customerno = field(@row,"Customer Number")
    SET @itemid = field(@row,"Item ID")
    SET @price = field(@row,"Price")
    SET @qty = field(@row,"Qty")
    SET @webenabled = field(@row,"Web Enabled")

    IF @webenabled == "Y" THEN
      SET @webofferblock = CONCAT("You can buy these items online! Save 10% when you use discount code EXAMPLE.")
    /* set a marker once at least one loop iteration comes across a "non web enabled" line. */      
       SET @hideBlock = true 

    ENDIF
   
    IF empty(@prevOrderNo) 
    or @prevOrderNo != @OrderNo 
    THEN
      ]%%
      Order # %%=v(@OrderNo)=%%
      <table>
        <tr>
          <td>QTY</td>
          <td>ITEM</td>
          <td>UNIT PRICE</td>
        </tr>
      </table>
      %%[
      SET @prevOrderNo = @OrderNo
    ENDIF 
    ]%%
    <table>
      <tr>
        <td>%%=v(@qty)=%%</td>
        <td>%%=v(@itemid)=%%</td>
        <td>$%%=v(@price)=%%</td>
      </tr>
    </table>
    %%[ 
  NEXT @i
  /* new if check: use the marker to determine show / hide */
  IF @hideBlock != true THEN
    ]%%
    <br>
    %%=v(@webofferblock)=%%
    %%[
  ENDIF
  /* end new if check */
ELSE
  ]%%
  <br>
  No transactionsList rows found
  %%[
ENDIF ]%%