0

I have an events page that i want to load the event information from a data extension. There will be nothing from a sending DE to match with on the event DE. I want to pull all information from the event table and my primary key in the event DE has 3 options. I have hard coded those in and i am using those to find the rows. The column name is 'audience'

When i go to publish my page it is not publishing, meaning there is an error. I have checked to make sure all field names and DE names are correct so i dont think that is the issue.

Not sure if Lookuporderedrows is only meant to be matched to another DE? see code below.

<table>
%%[
Set @con = con
Set @hcp = hcp
set @both = both

Set @rows = Lookuporderedrows("TEST_events_table",0,"date 
ASC","audience",@con, "audience",@hcp, "audience",@both)
set @rowCount = rowcount(@rows)


for @i = 1 to @rowCount do
SET @CurrentRow = Row(@rows,@i)   
SET @audience = Field(@currentrow,"audience") 
SET @type = Field(@currentrow,"type") 
SET @date = Field(@currentrow,"date") 
SET @title = Field(@currentrow,"title") 
SET @location = Field(@currentrow,"location") 

]%%


%%[if @rowCount > 0 then]%%      


<tr>
    <td style="text-align:center; color:#a846ab; background-color:%%=v(@rowcolor)=%%; font-family:Arial, Helvetica, sans-serif; font-size:15px; line-height:19px;  padding-top:20px;padding-bottom:20px;padding-left:5px;padding-right:5px;word-break: break-word;">%%=v(@audience)=%% </td>
   <td style="text-align:center;  color:#a846ab; background-color:%%=v(@rowcolor)=%%; font-family:Arial, Helvetica, sans-serif; font-size:15px; line-height:19px; padding-top:20px;padding-bottom:20px;padding-left:5px;padding-right:5px;word-break: break-word;">%%=v(@type)=%%</td>
  <td style="text-align:center;  color:#a846ab; background-color:%%=v(@rowcolor)=%%; font-family:Arial, Helvetica, sans-serif; font-size:15px; line-height:19px; padding-top:20px;padding-bottom:20px;padding-left:5px;padding-right:5px;word-break: break-word;">%%=v(@date)=%%</td>
  <td style="text-align:center;  color:#a846ab; background-color:%%=v(@rowcolor)=%%; font-family:Arial, Helvetica, sans-serif; font-size:15px; line-height:19px; padding-top:20px;padding-bottom:20px;padding-left:5px;padding-right:5px;word-break: break-word;">%%=v(@title)=%%</td>
  <td style="text-align:center;  color:#a846ab; background-color:%%=v(@rowcolor)=%%; font-family:Arial, Helvetica, sans-serif; font-size:15px; line-height:19px; padding-top:20px;padding-bottom:20px;padding-left:5px;padding-right:5px;word-break: break-word;">%%=v(@location)=%%</td>
                             </tr>



%%[endif]%%
%%[next @i]%%  

</table>

1 Answer 1

1

First, there should be quotes around your hard-coded values of 'con', 'hcp', and 'both'.

The last 2 properties of the LookupOrderedRows() function are used as an AND clause, so your code is looking for records where audience = 'con' AND audience = 'hcp' AND audience = 'both'.

A field can only specified once in the where clause of LookupOrderedRows().

LookupOrderedRows()

edit:

A downside of using LookupOrderedRows() is that you are limited to returning only 2000 rows. To return all rows in the DE, if there are 2000 or less, you could add a column to your DE and use a SQL query to assign all records with any of those audience values a generic value in the new column. Then you can use that new column in a LookupRows() function, but then you'd lose your ordering by date.

If you already have a column that you can use for your lookup value, such as SubscriberKey (or you can just create field in the DE with a default value for everyone), then the AMPscript below could be used. In this code, we're getting all rows, but only outputting a html table row if the audience value is one of those three.

<table>
  %%[
  set @rows = LookupRows("TEST_events_table","SubscriberKey",@SubscriberKey)
  set @rowCount = rowcount(@rows)
  if @rowCount > 0 then
    for @i = 1 to @rowCount do
      SET @CurrentRow = Row(@rows,@i)   
      SET @audience = Field(@currentrow,"audience") 

      if @audience == 'con' or @audience == 'hcp' or @audience == 'both' then

        SET @type = Field(@currentrow,"type") 
        SET @date = Field(@currentrow,"date") 
        SET @title = Field(@currentrow,"title") 
        SET @location = Field(@currentrow,"location") 

          ]%%      
          <tr>
            <td style="text-align:center; color:#a846ab; background-color:%%=v(@rowcolor)=%%; font-family:Arial, Helvetica, sans-serif; font-size:15px; line-height:19px;  padding-top:20px;padding-bottom:20px;padding-left:5px;padding-right:5px;word-break: break-word;">%%=v(@audience)=%% </td>
            <td style="text-align:center;  color:#a846ab; background-color:%%=v(@rowcolor)=%%; font-family:Arial, Helvetica, sans-serif; font-size:15px; line-height:19px; padding-top:20px;padding-bottom:20px;padding-left:5px;padding-right:5px;word-break: break-word;">%%=v(@type)=%%</td>
            <td style="text-align:center;  color:#a846ab; background-color:%%=v(@rowcolor)=%%; font-family:Arial, Helvetica, sans-serif; font-size:15px; line-height:19px; padding-top:20px;padding-bottom:20px;padding-left:5px;padding-right:5px;word-break: break-word;">%%=v(@date)=%%</td>
            <td style="text-align:center;  color:#a846ab; background-color:%%=v(@rowcolor)=%%; font-family:Arial, Helvetica, sans-serif; font-size:15px; line-height:19px; padding-top:20px;padding-bottom:20px;padding-left:5px;padding-right:5px;word-break: break-word;">%%=v(@title)=%%</td>
            <td style="text-align:center;  color:#a846ab; background-color:%%=v(@rowcolor)=%%; font-family:Arial, Helvetica, sans-serif; font-size:15px; line-height:19px; padding-top:20px;padding-bottom:20px;padding-left:5px;padding-right:5px;word-break: break-word;">%%=v(@location)=%%</td>
          </tr>
          %%[
      endif
    next @i
  endif
  ]%%  
</table>

Edit #2: You could also try the AMPscript below, which will change the audience value every time a new loop starts. The date ordering won't be perfect though.

<table>
  %%[
  for @loop = 1 to 3 do
    if @loop == 1 then
      set @value = "con"
    elseif @loop == 2 then
      set @value = "hcp"
    elseif @loop == 3 then
      set @value = "both"
    endif

    set @rows = Lookuporderedrows("TEST_events_table",0,"date ASC","audience",@value)
    set @rowCount = rowcount(@rows)

    if @rowCount > 0 then
      for @i = 1 to @rowCount do
        SET @CurrentRow = Row(@rows,@i)   
        SET @audience = Field(@currentrow,"audience") 
        SET @type = Field(@currentrow,"type") 
        SET @date = Field(@currentrow,"date") 
        SET @title = Field(@currentrow,"title") 
        SET @location = Field(@currentrow,"location") 
        ]%%      
        <tr>
          <td style="text-align:center; color:#a846ab; background-color:%%=v(@rowcolor)=%%; font-family:Arial, Helvetica, sans-serif; font-size:15px; line-height:19px;  padding-top:20px;padding-bottom:20px;padding-left:5px;padding-right:5px;word-break: break-word;">%%=v(@audience)=%% </td>
          <td style="text-align:center;  color:#a846ab; background-color:%%=v(@rowcolor)=%%; font-family:Arial, Helvetica, sans-serif; font-size:15px; line-height:19px; padding-top:20px;padding-bottom:20px;padding-left:5px;padding-right:5px;word-break: break-word;">%%=v(@type)=%%</td>
          <td style="text-align:center;  color:#a846ab; background-color:%%=v(@rowcolor)=%%; font-family:Arial, Helvetica, sans-serif; font-size:15px; line-height:19px; padding-top:20px;padding-bottom:20px;padding-left:5px;padding-right:5px;word-break: break-word;">%%=v(@date)=%%</td>
          <td style="text-align:center;  color:#a846ab; background-color:%%=v(@rowcolor)=%%; font-family:Arial, Helvetica, sans-serif; font-size:15px; line-height:19px; padding-top:20px;padding-bottom:20px;padding-left:5px;padding-right:5px;word-break: break-word;">%%=v(@title)=%%</td>
          <td style="text-align:center;  color:#a846ab; background-color:%%=v(@rowcolor)=%%; font-family:Arial, Helvetica, sans-serif; font-size:15px; line-height:19px; padding-top:20px;padding-bottom:20px;padding-left:5px;padding-right:5px;word-break: break-word;">%%=v(@location)=%%</td>
        </tr>
        %%[
      next @i
    endif
  next @loop
  ]%%  
</table>
2
  • quotes was the issue, cant believe i over looked that. always nice to have another pair of eyes. Is there anyway to return all of my rows then since it sees them as 'AND'?
    – LD Jewell
    Commented Aug 2, 2019 at 20:43
  • I've updated my answer above. You can also just do multiple lookups/loops, specifying the different audience values each time. All of the AMPscript would exist inside one html table in that case.
    – jake314159
    Commented Aug 2, 2019 at 21:53

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .