1

I have an email that retrieves data from a data extension and populates it into a table. I used code from example three on this site and customized it a bit. The AmpScript result is:

    %%[
          var @rows, @row, @rowCount, @numRowsToReturn, @lookupValue, @i, 
          set @lookupValue = AttributeValue("LookUpColumn") /* value from attribute or DE column in send context */
          set @lookupValue = "true" /* or a literal value */
          set @numRowsToReturn = 0 /* 0 means all, max 2000 */
          set @rows = LookupOrderedRows("BG-Web-Specials-123117",@numRowsToReturn,"Item Description, NDC, Compares To, ABC8","LookUpColumn", @lookupValue)
          set @rowCount = rowcount(@rows)
          if @rowCount > 0 then for @i = 1 to @rowCount do
          var @DEColumn1, @DEColumn2, @DEColumn3, @DEColumn4
          set @row = row(@rows,@i) /* get row based on counter */
          set @DEColumn1 = field(@row,"Item Description")
          set @DEColumn2 = field(@row,"NDC")
          set @DEColumn3 = field(@row,"Compares To")
          set @DEColumn4 = field(@row,"ABC8")
    ]%%

The values for DEColumns 1 through 4 then get filled into a table which is ended with the AmpScript 'next i' and 'end if'. Code below:

<table border="0" cellpadding="0" cellspacing="0" role="presentation" style="background:#fafafa;" width="100%">
          <tr>
            <td align="left" style="word-wrap: break-word; font-size: 0px; padding: 10px 25px; vertical-align: top;">
              <table border="0" cellpadding="0" cellspacing="0" style="cellspacing:0px;color:#000;font-family:ubuntu,helvetica,arial,sans-serif;font-size:13px;line-height:22px;table-layout:auto;" width="100%">
                <tr>
                  <td style="padding: 10px; border-width: 1px 1px 1px 4px; border-style: solid; border-color: rgb(222, 222, 222) rgb(222, 222, 222) rgb(222, 222, 222) rgb(0, 83, 155); border-image: initial; font-family: arial; font-size: 12px; color: rgb(0, 83, 155); font-weight: bold; background-color: rgb(255, 255, 255); vertical-align: top; line-height: 1.15; width: 45%; text-align: center;">
                    <span style="font-size:11px;"><span style="color:#636466; font-weight:normal;">%%=Uppercase(v(@DEColumn1))=%% </span></span>
                  </td>
                  <td style="font-family: arial; font-size: 12px; color: rgb(0, 83, 155); font-weight: bold; background-color: rgb(255, 255, 255); padding: 10px; border: 1px solid rgb(222, 222, 222); vertical-align: top; line-height: 1.15; width: 15%; text-align: center;">
                    <span style="font-size:11px;"><span style="color:#636466; font-weight:normal;">%%=v(@DEColumn2)=%% </span> </span>
                  </td>
                  <td style="font-family: arial; font-size: 12px; color: rgb(0, 83, 155); font-weight: bold; background-color: rgb(255, 255, 255); padding: 10px; border: 1px solid rgb(222, 222, 222); vertical-align: top; line-height: 1.15; width: 25%; text-align: center;">
                    <span style="font-size:11px;"><span style="color:#636466; font-weight:normal;">%%=v(@DEColumn3)=%% </span></span>
                  </td>
                  <td style="font-family: arial; font-size: 12px; color: rgb(0, 83, 155); font-weight: bold; background-color: rgb(255, 255, 255); padding: 10px; border: 1px solid rgb(222, 222, 222); vertical-align: top; line-height: 1.15; width: 15%; text-align: center;">
                    <span style="font-size:11px;"><span style="color:#636466; font-weight:normal;">%%=v(@DEColumn4)=%% </span></span>
                  </td>
                </tr>
              </table>
            </td>
          </tr>
        </table>%%[ next @i]%% %%[ endif ]%%

This goes through the data extension and returns each row of data into it's own table. It's working pretty well. The struggle I'm having is how to include a header on the table and have the columns match the width of those below. If I make the header it's own row within the repeated table, the header is repeated each time.

Is there a way to pull the header fields from a Data Extension just like I am pulling the data contained within the extension?

0

First, not using the WYSIWYG editor will help you a lot -- especially because the WYSIWYG editor jacks with AMPScript indention.

I'd move the beginning and ending of the tables outside of your AMPScript loop.

Then inside your loop, just check the value of @i and output the heading row when it @i is 1:

  <table border="0" cellpadding="0" cellspacing="0">
    <tr>
      <td align="left">

        <table border="0" cellpadding="0" cellspacing="0">

          %%[
            var @rows, @row, @rowCount, @numRowsToReturn, @lookupValue, @i, 
            set @lookupValue = "true"
            set @numRowsToReturn = 0 
            set @rows = LookupOrderedRows("BG-Web-Specials-123117",@numRowsToReturn,"Item Description, NDC, Compares To, ABC8","LookUpColumn", @lookupValue)
            set @rowCount = rowcount(@rows)

            if @rowCount > 0 then 

                for @i = 1 to @rowCount do

                  var @DEColumn1, @DEColumn2, @DEColumn3, @DEColumn4
                  set @row = row(@rows,@i)
                  set @DEColumn1 = field(@row,"Item Description")
                  set @DEColumn2 = field(@row,"NDC")
                  set @DEColumn3 = field(@row,"Compares To")
                  set @DEColumn4 = field(@row,"ABC8")

                  if @i == 1 then

                  ]%%
                      <tr>
                        <td>Item Description</td>
                        <td>NDC</td>
                        <td>Compares To</td>
                        <td>ABC8</td>
                      </tr>
                  %%[

                  else 

                  ]%%
                      <tr>
                        <td>%%=Uppercase(@DEColumn1)=%%</td>
                        <td>%%=v(@DEColumn2)=%%</td>
                        <td>%%=v(@DEColumn3)=%%</td>
                        <td>%%=v(@DEColumn4)=%%</td>
                      </tr>
                  %%[ 
                  endif

                next @i


          endif 

        ]%%

        </table>
      </td>
    </tr>
  </table>

While you can retrieve the Data Extension column names with the AMPScript API functions, it's much easier to just hardcode them as I've outlined above.

  • I just noticed, for some reason this is consistently missing one row of data. There at 6 in the Data Extensions, but only 5 are being pulled into the table in the email. Any thoughts? – Eczekner Jan 4 '18 at 15:59
0

The first row of data is actually data, not the header columns. If you use the @i=1 row to insert the headers it is replacing the first row of actual data. Instead, hard code the column headers in the HTML prior to starting your AMPScript loop.

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.