[SalesForce] AMPscript LookUpRows error

I'm having an issue when I try to load dynamically content into an email using LookupRows function from AMPscript in ExactTarget (Salesforce). Maybe you can help. Appreciate it.

I have a LookupRows data extension where I have a list of ContentAreaIDs which I try to load into my email HTML using %%=ContentArea(@ContentAreaXX)=%% (XX = a number from 1-13, I have 13 content areas to load into email).

This is what I did step by step:
1. setting up 13 content areas with the content to be pulled in (HTML type content areas)
2. copying the ContentAreaID from each of those 13 and adding to the LookupRows data extension
3. calling the content areas in my HTML code like this %%=ContentArea(@ContentAreaXX)=%%

When I try to preview in ET I receive this error:
Unable to generate preview
A scripting error has occurred.
Invalid value specified for function parameter.
Function Name: ContentArea
Parameter Name: ContentID
Parameter Ordinal: 1
Parameter Type: Numeric
Submitted Value:

These are the data extensions used (one for send, another for the LookupRows):
https://drive.google.com/folderview?id=0B3G2k1oFH3OUa041eGJ1RXZPZ2c&usp=sharing

Can you point me to what I'm doing wrong?

This is the HTML:

%%[
Var @card_type, @Row, @rows, @ac, @au, @ea, @se, @ContentArea1, @ContentArea2, @ContentArea3, @ContentArea4, @ContentArea5, @ContentArea6, @ContentArea7, @ContentArea8, @ContentArea9, @ContentArea10, @ContentArea11, @ContentArea12, @ContentArea13

SET @card_type = [CARD_TYPE]
SET @ac = [AC]
SET @au = [AU]
SET @ea = [EA]
SET @se = [SE]

IF Not Empty(@card_type) THEN
SET @rows = LookupRows("forum-lookup-rows - 1","card_type", [CARD_TYPE])
ENDIF

IF RowCount(@rows) > 0 THEN
SET @rows = Row(@rows,1)
SET @ContentArea1 = Field(@rows,"ContentArea1")
SET @ContentArea2 = Field(@rows,"ContentArea2")
SET @ContentArea3 = Field(@rows,"ContentArea3")
SET @ContentArea4 = Field(@rows,"ContentArea4")
SET @ContentArea5 = Field(@rows,"ContentArea5")
SET @ContentArea6 = Field(@rows,"ContentArea6")
SET @ContentArea11 = Field(@rows,"ContentArea11")

IF @ac == 'n' THEN
SET @ContentArea7 = Field(@row,"ContentArea7")
ELSEIF @ea == 'n' THEN
SET @ContentArea8 = Field(@row,"ContentArea8")
ELSEIF @au == 'n' THEN
SET @ContentArea9 = Field(@row,"ContentArea9")
SET @ContentArea12 = Field(@row,"ContentArea12")
/*ELSEIF @au == 'y' THEN
SET @ContentArea13 = Field(@row,"ContentArea13")*/
ELSEIF @se == 'n' THEN
SET @ContentArea10 = Field(@row,"ContentArea10")
ENDIF
ENDIF

]%%

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
   <head>
      <meta http-equiv="Content-Type" content="text/html; charSET=utf-8" />
      <meta content="initial-scale=1.0" name="viewport" />
      <title></title>
      <style type="text/css">
         #outlook a {padding:0;}
         body{width:100% !important; -webkit-text-size-adjust:100%; -ms-text-size-adjust:100%; margin:0; padding:0;-webkit-font-smoothing: antialiased;font-family: Arial, Helvetica, sans-serif}
         .ExternalClass {width:100%;}
         .ExternalClass, .ExternalClass p, .ExternalClass span, .ExternalClass font, .ExternalClass td, .ExternalClass div {line-height: 100%;}
         img {outline:none; text-decoration:none; -ms-interpolation-mode: bicubic;}
         a img {border:none;}
         @media only screen and (max-width: 629px) {
         body[yahoo] .hide {display:none !important;}
         body[yahoo] table {width:100% !important; background:#ffffff !important;}
         body[yahoo] img[class="fluid"] {max-width:100% !important;width:100% !important;height: auto !important;-ms-interpolation-mode: bicubic !important;}
         body[yahoo] table[class="grey"] {background-color:#f9fafa !important;}
         body[yahoo] table[class="noBorder"] {border:0 !important;}
         body[yahoo] table[class="social"] {width:210px !important;}
         body[yahoo] table[class="centered"] img, body[yahoo] table[class="grey"] img{margin:0 auto !important; text-align:center}
         body[yahoo] a[class="footerLink"] {display:block !important; padding:10px !important;}
         body[yahoo] .title {font-size:15px !important;}
         body[yahoo] img[class="card"] {width:53px !important; height:34px !important;}
         body[yahoo] table[class="green"] {background-color:#19b24b !important;}
         body[yahoo] table[class="borderBottom"] {border-bottom:1px solid #cccccc !important; margin-bottom:20px !important}
         }
      </style>
   </head>
   <body yahoo="fix">
      <table style="border-collapse:collapse; mso-table-lspace:0pt; mso-table-rspace:0pt;" width="630" border="0" cellspacing="0" cellpadding="0" align="center">
         <tr>
            <td style="border-collapse: collapse; font-family:Verdana, Arial, Helvetica, sans-serif; font-size:11px; color:#666666;  margin:0;" align="center">
                 %%=ContentArea(@ContentArea1)=%%  
            </td>
         </tr>
         <tr>
            <td style="border-collapse: collapse; font-family:Verdana, Arial, Helvetica, sans-serif; font-size:11px; color:#666666;  margin:0;" align="center">
                 %%=ContentArea(@ContentArea2)=%%  
            </td>
         </tr>
         <tr>
            <td style="border-collapse: collapse; font-family:Verdana, Arial, Helvetica, sans-serif; font-size:11px; color:#666666;  margin:0;" align="center">
                 %%=ContentArea(@ContentArea3)=%%  
            </td>
         </tr>
         <tr>
            <td style="border-collapse: collapse; font-family:Verdana, Arial, Helvetica, sans-serif; font-size:11px; color:#666666;  margin:0;" align="center">
                 %%=ContentArea(@ContentArea4)=%%  
            </td>
         </tr>
         <tr>
            <td style="border-collapse: collapse; font-family:Verdana, Arial, Helvetica, sans-serif; font-size:11px; color:#666666;  margin:0;" align="center">
                 %%=ContentArea(@ContentArea5)=%%  
            </td>
         </tr>
         <tr>
            <td style="border-collapse: collapse; font-family:Verdana, Arial, Helvetica, sans-serif; font-size:11px; color:#666666;  margin:0;" align="center">
                 %%=ContentArea(@ContentArea6)=%%  
            </td>
         </tr>
         <tr>
            <td style="border-collapse: collapse; font-family:Verdana, Arial, Helvetica, sans-serif; font-size:11px; color:#666666;  margin:0;" align="center">
                 %%=ContentArea(@ContentArea7)=%%  
            </td>
         </tr>
         <tr>
            <td style="border-collapse: collapse; font-family:Verdana, Arial, Helvetica, sans-serif; font-size:11px; color:#666666;  margin:0;" align="center">
                 %%=ContentArea(@ContentArea8)=%%  
            </td>
         </tr>
         <tr>
            <td style="border-collapse: collapse; font-family:Verdana, Arial, Helvetica, sans-serif; font-size:11px; color:#666666;  margin:0;" align="center">
                 %%=ContentArea(@ContentArea9)=%%  
            </td>
         </tr>
         <tr>
            <td style="border-collapse: collapse; font-family:Verdana, Arial, Helvetica, sans-serif; font-size:11px; color:#666666;  margin:0;" align="center">
                 %%=ContentArea(@ContentArea10)=%%  
            </td>
         </tr>
         <tr>
            <td style="border-collapse: collapse; font-family:Verdana, Arial, Helvetica, sans-serif; font-size:11px; color:#666666;  margin:0;" align="center">
                 %%=ContentArea(@ContentArea11)=%%  
            </td>
         </tr>
         <tr>
            <td style="border-collapse: collapse; font-family:Verdana, Arial, Helvetica, sans-serif; font-size:11px; color:#666666;  margin:0;" align="center">
                 %%=ContentArea(@ContentArea12)=%%  
            </td>
         </tr>
         <tr>
            <td style="border-collapse: collapse; font-family:Verdana, Arial, Helvetica, sans-serif; font-size:11px; color:#666666;  margin:0;" align="center">
                 %%=ContentArea(@ContentArea13)=%%  
            </td>
         </tr>
      </table>
   </body>
</html>
<custom name="opencounter" type="tracking">

Best Answer

First, I'd move the retrieval inside your @card_type conditional and output each @contentArea value. Like this:

%%[
Var @card_type, @Row, @rows, @ac, @au, @ea, @se, @ContentArea1, @ContentArea2, @ContentArea3, @ContentArea4, @ContentArea5, @ContentArea6, @ContentArea7, @ContentArea8, @ContentArea9, @ContentArea10, @ContentArea11, @ContentArea12, @ContentArea13

SET @card_type = [CARD_TYPE]
SET @ac = [AC]
SET @au = [AU]
SET @ea = [EA]
SET @se = [SE]

IF Not Empty(@card_type) THEN

    SET @rows = LookupRows("forum-lookup-rows - 1","card_type", [CARD_TYPE])

    IF RowCount(@rows) > 0 THEN

        SET @row = Row(@rows,1)
        SET @ContentArea1 = Field(@row,"ContentArea1")
        SET @ContentArea2 = Field(@row,"ContentArea2")
        SET @ContentArea3 = Field(@row,"ContentArea3")
        SET @ContentArea4 = Field(@row,"ContentArea4")
        SET @ContentArea5 = Field(@row,"ContentArea5")
        SET @ContentArea6 = Field(@row,"ContentArea6")
        SET @ContentArea11 = Field(@row,"ContentArea11")

        IF @ac == 'n' THEN
            SET @ContentArea7 = Field(@row,"ContentArea7")
        ELSEIF @ea == 'n' THEN
            SET @ContentArea8 = Field(@row,"ContentArea8")
        ELSEIF @au == 'n' THEN
            SET @ContentArea9 = Field(@row,"ContentArea9")
            SET @ContentArea12 = Field(@row,"ContentArea12")
        /*ELSEIF @au == 'y' THEN
        SET @ContentArea13 = Field(@row,"ContentArea13")*/
        ELSEIF @se == 'n' THEN
            SET @ContentArea10 = Field(@row,"ContentArea10")
        ENDIF
    ENDIF

ENDIF

]%%

@card_type: %%=v(@card_type)=%%<br>
@ac: %%=v(@ac)=%%<br>
@au: %%=v(@au)=%%<br>
@ea: %%=v(@ea)=%%<br>
@se: %%=v(@se)=%%<br><br>
@ContentArea1: %%=v(@ContentArea1)=%%<br>
@ContentArea2: %%=v(@ContentArea2)=%%<br>
@ContentArea3: %%=v(@ContentArea3)=%%<br>
@ContentArea4: %%=v(@ContentArea4)=%%<br>
@ContentArea5: %%=v(@ContentArea5)=%%<br>
@ContentArea6: %%=v(@ContentArea6)=%%<br>
@ContentArea11: %%=v(@ContentArea11)=%%<br><br>
@ContentArea7: %%=v(@ContentArea7)=%%<br>
@ContentArea8: %%=v(@ContentArea8)=%%<br>
@ContentArea9: %%=v(@ContentArea9)=%%<br>
@ContentArea12: %%=v(@ContentArea12)=%%<br>
@ContentArea10: %%=v(@ContentArea10)=%%<br>

For any @ContentArea* field that's empty the email's going to blow up. You'll either need to have some default content for those or conditionally display alternate content in the body of the email. You might utilize an iif() function -- like this:

%%=iif(empty(@ContentArea1),"not found", ContentArea(@ContentArea1))=%%

Related Solutions

[SalesForce] Ampscript: combining IF with Lookup or LookupRows

What you are going to want to do in order to compare all of the possible brands against that single value is utilize LookupRows instead of Lookup. (as you suspected). From there you will need to do a for loop over the final IF statement to iterate through each value.

See below:

%%[

SET @Rows = LookupRows("ent.Brands","DealerId", AccountId) /*Gets the rowset */

IF Rowcount(@Rows) > 0 THEN /* Verifies there is data in Rowset */

  FOR @i=1 TO Rowcount(@Rows) DO /* Iterates through Rowset */

    SET @Row = Row(@Rows,@i) /* Assigns specific row */

    SET @Brand = Field(@Row,"Brand") /* Assigns Brand Value */

    IF @Brand == "Brandname" THEN /* Compares assigned value to your static value */
]%%
--HTML-- 
%%[ ENDIF /* End comparison */

  Next @i /* End iteration */

ENDIF /* end verification of data */
]%%

[SalesForce] AMPScript Lookup and LookupRows being stored

Workaround:
There are 5 AMPScript Lookup functions that allow you to retrieve data from a Data Extension in Salesforce Marketing Cloud:

Most of these functions only store their own values, however LookupRows appears to overwrite the value returned by Lookup.
The following AMPScript can be run on a Cloud Page to show this in practice:

Create a DE called "TestDE" containing 2 (text) columns with the names "Key" and "A".

%%[
SET @LookupCol = "A"
SET @DE = "TestDE"
SET @KeyFieldValue = "1"
SET @KeyField = "Key"

UpsertData(@DE,1,@KeyField,@KeyFieldValue,@LookupCol,"0") 
SET @Field_Lookup = Lookup(@DE,@LookupCol,@KeyField,@KeyFieldValue) 
UpsertData(@DE,1,@KeyField,@KeyFieldValue,@LookupCol,"1") 
SET @Field_Lookup = Lookup(@DE,@LookupCol,@KeyField,@KeyFieldValue) 
SET @Field_LookupRows = FIELD(ROW(LookupRows(@DE,@KeyField,@KeyFieldValue),1),@LookupCol) 
UpsertData(@DE,1,@KeyField,@KeyFieldValue,@LookupCol,"2") 
SET @Field_LookupRowsCS = FIELD(ROW(LookupRowsCS(@DE,@KeyField,@KeyFieldValue),1),@LookupCol) 
UpsertData(@DE,1,@KeyField,@KeyFieldValue,@LookupCol,"3") 
SET @Field_LookupOrderedRows = FIELD(ROW(LookupOrderedRows(@DE,1,Concat(@KeyField," ASC"),@KeyField,@KeyFieldValue),1),@LookupCol)
UpsertData(@DE,1,@KeyField,@KeyFieldValue,@LookupCol,"4") 
SET @Field_LookupOrderedRowsCS = FIELD(ROW(LookupOrderedRowsCS(@DE,1,Concat(@KeyField," ASC"),@KeyField,@KeyFieldValue),1),@LookupCol)
]%%

<b>First Check:</b><br>
Field_Lookup: %%=v(@Field_Lookup)=%%<br>
Field_LookupRows: %%=v(@Field_LookupRows)=%%<br>
Field_LookupRowsCS: %%=v(@Field_LookupRowsCS)=%%<br>
Field_LookupOrderedRows: %%=v(@Field_LookupOrderedRows)=%%<br>
Field_LookupOrderedRowsCS: %%=v(@Field_LookupOrderedRowsCS)=%%<br>

%%[
SET @Field_Lookup = Lookup(@DE,@LookupCol,@KeyField,@KeyFieldValue) 
SET @Field_LookupRows = FIELD(ROW(LookupRows(@DE,@KeyField,@KeyFieldValue),1),@LookupCol) 
SET @Field_LookupRowsCS = FIELD(ROW(LookupRowsCS(@DE,@KeyField,@KeyFieldValue),1),@LookupCol) 
SET @Field_LookupOrderedRows = FIELD(ROW(LookupOrderedRows(@DE,1,Concat(@KeyField," ASC"),@KeyField,@KeyFieldValue),1),@LookupCol)
SET @Field_LookupOrderedRowsCS = FIELD(ROW(LookupOrderedRowsCS(@DE,1,Concat(@KeyField," ASC"),@KeyField,@KeyFieldValue),1),@LookupCol)
]%%

<b>Second Check:</b><br>
Field_Lookup: %%=v(@Field_Lookup)=%%<br>
Field_LookupRows: %%=v(@Field_LookupRows)=%%<br>
Field_LookupRowsCS: %%=v(@Field_LookupRowsCS)=%%<br>
Field_LookupOrderedRows: %%=v(@Field_LookupOrderedRows)=%%<br>
Field_LookupOrderedRowsCS: %%=v(@Field_LookupOrderedRowsCS)=%%<br>

Output:
First Check:
Field_Lookup: 0
Field_LookupRows: 1
Field_LookupRowsCS: 2
Field_LookupOrderedRows: 3
Field_LookupOrderedRowsCS: 4
Second Check:
Field_Lookup: 1
Field_LookupRows: 1
Field_LookupRowsCS: 2
Field_LookupOrderedRows: 3
Field_LookupOrderedRowsCS: 4

Note that all values should have returned "4", however due to the Lookup Storing, the Second Check has returned the same stored values as the First Check, with the exception of "Lookup" which gets overwritten by LookupRows.

I hope this case study is a useful guide for any SFMC Developers confronted with problems when implementing multiple Lookup functions.

Best Answer

Related Solutions

[SalesForce] Ampscript: combining IF with Lookup or LookupRows

[SalesForce] AMPScript Lookup and LookupRows being stored

Related Topic