[SalesForce] Ampscript: combining IF with Lookup or LookupRows

We send emails to our dealers, and for each brand a dealer sells we have a record in a 'Brands' synchronized data extension. So if one dealer sells multiple brands, there will be multiple records for his id in that data extension. I'm trying to come up with the simplest way to only show a piece of content/HTML to a dealer selling a certain brand.

Because I'm only getting started with Ampscript, first I put a simple lookup as content in the email. This is what I started with:

%%=Lookup("ent.Brands","Brand","DealerId", AccountId)=%%

This returns a brand when I go through the various dealers in my email preview. But, only one. So I should probably be using LookupRows instead of Lookup if I want all of the values returned, is that correct?

Then, I hoped I could use the Lookup as condition in an IF statement. So I made an HTML block which is basically like this:

%%[IF Lookup("ent.Brands","Brand","DealerId", AccountId) = "Brandname" THEN]%%
--HTML--
%%[ENDIF]%%

But the preview screen now returns the following error:

There is an error in your email. Please contact your customer service
representative. Error 1: Script IF Statement Invalid The script
condition contains an invalid comparison operator.

I've seen examples here where a variable is declared, filled using a lookup, and then used in a way like IF @variable = "value",

I also came across this page on ampscript.com, but I don't fully understand it and I think that's maybe more than what I'm looking for.

Basically, what I'm looking for is Ampscript that compares all returned rows with a certain value (brand name) and if one of the rows contains that value, show some HTML. Can anyone help find the easiest way to do this?

Thanks in advance!

Best Answer

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 */
]%%

Related Solutions

[SalesForce] AMPscript LookUpRows error

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))=%%

[SalesForce] Can you execute Ampscript stored in a Data Extension

Having done a fair bit more research and using the information listed above I can confirm that it is possible to store an IF statement within a DataExtension. However there are important things to consider:

Both the open and the close of the IF statement must be processed at the same time when using the TreatAsContent function
Your stored Ampscript within your DE should use single quotes rather than double quotes
You can use the Concat function to combine Ampscript with HTML content

Here is an example:

%%[
/* Set the sample content - this could also come from a lookup          */
SET @CA_URL = "Image hyperlink goes here"
SET @CA_IMAGE = "Image path goes here"
SET @CA_ALT = "Alt text goes here"

/* For the purposes of this example assume the following IF statements  */
/* AMPSCRIPT_PRE  = "%%[IF (CELL_ID=='1234' OR CELL_ID=='4321') THEN]%% */
/* AMPSCRIPT_POST = "%%[ENDIF]%%"                                       */

/* Now concat your Pre and Post ampscript either side of some HTML      */
SET @CA = concat(lookup(@AMPSCRIPT_DE,"AMPSCRIPT_PRE","ROW","CA"),"<a href='%%=v(@CA_URL)=%%'><img src='%%=v(@CA_IMAGE)=%%' alt='%%=v(@CA_ALT)=%%' border='0' width='600'></a>",lookup(@CAMPAIGN_AMP_SCRIPT,"AMPSCRIPT_POST","ROW","CA"))

/* Now return using TreatAsContent */
]%%

%%=TreatAsContent(@CA)=%%

Best Answer

Related Solutions

[SalesForce] AMPscript LookUpRows error

[SalesForce] Can you execute Ampscript stored in a Data Extension

Related Topic