Amp Script to pull content from non sendable data extension

Question

I am trying to bring content from a non sendable data extension where I am matching the first 2 locations from my sendable data extension. Example of data in the sendable data extension: Holland, Belgium,Luxembourg

based on this I am referencing the first two locations in a non sendable data extension where I have location data in two fields: a country & a city field.

I want to be able to pull the content from the non sendable DE for when is a match on my primary location (that is successful), if I don't have a match for my primary location then check the secondary location. If there is a match bring content associated with the secondary location; if no match is found pull a default content area.

The problem I have at the moment is that nothing is shown for the default content piece. I had an issue when the secondary location wasn't picked up for content but I realised that was because of space separator that was coming from BuildRowsetFromString so I had to use Trim and now I can show content for secondary location but nothing is displayed for default content.

Any guidance is much appreciated.

   %%[

SET @Location=AttributeValue('Product: Description')
SET @virgula=','

if IndexOf(@Location,@virgula) > 0 then 

     SET @rs = BuildRowsetFromString(@Location,',')
     SET @PrimaryLocation = Field(Row(@rs,1),1)
     SET @SecondaryLocation = Field(Row(@rs,2),1)
else 
SET @PrimaryLocation=@Location
SET @SecondaryLocation=@Location

endif 

]%%


%%[SET @rows = LookupRows("Blog Post Reference Table","Default","1")
set @rowCount = rowcount(@rows)
if @rowCount > 0 then

  set @match = 0
  set @match2= 0

  for @i = 1 to @rowCount do

    if @match == 0 then
      set @row = row(@rows, @i) /* get row based on counter */
      set @Article = field(@row,"Article Title")
      set @Description = field(@row,"Description")
      set @Image = field(@row,"Image")
      SET @LinkTrip=field(@row,"Permalink")
      set @UrlFinal=RedirectTo(@LinkTrip)
      set @defaultURl='https://www.trafalgar.com'
      set @LocationCity=field(@row,"Location_City")
      set @bara='|'
      set @LocationCountry=field(@row,"Location_Country")


      if IndexOf(@LocationCity,@bara) > 0 then 
        SET @LocationCity2=Substring(@LocationCity,1, Subtract(IndexOf(@LocationCity,@bara),1))
      else 
        SET @LocationCity2=@LocationCity
      endif

       if IndexOf(@LocationCountry,@bara) > 0 then 
        SET @LocationCountry2=Substring(@LocationCountry,1, Subtract(IndexOf(@LocationCountry,@bara),1))
      else 
        SET @LocationCountry2=@LocationCountry
      endif

      if (@PrimaryLocation == @LocationCity2) or (@PrimaryLocation == @LocationCountry2) then
        SET @match = 1
      endif

SET @SecondaryNOspace=trim(@SecondaryLocation)
endif


]%%



%%[if @match==1 and @i == @rowCount then]%%

From the blog piece for primary location: <br>
Article name:%%=v(@Article)=%%<br>

Primary location: %%=v(@PrimaryLocation)=%%<br><br>
Description: %%=v(@Description)=%%
<br>
<a href="%%=RedirectTo(IIF(@nolink,v(@defaultURl),v(@UrlFinal)))=%%"><img src="%%=v(@Image)=%%" width="600" height="400"></a>


%%[elseif  (@SecondaryNOspace == @LocationCity2) or (@SecondaryNOspace == @LocationCountry2) then
        SET @match2 = 1

 if @match2==1 and @i == @rowCount then]%%

From the blog piece for secondary location: <br>
Article name:%%=v(@Article)=%%<br>

Secondary location: %%=v(@SecondaryNOspace)=%%<br>

Description: %%=v(@Description)=%%
<br>
<a href="%%=RedirectTo(IIF(@nolink,v(@defaultURl),v(@UrlFinal)))=%%"><img src="%%=v(@Image)=%%" width="600" height="400"></a>



%%[ else if @i == @rowCount then]%%
DEFAULT CONTENT because there was no match for primary or secondary locations 

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

Giulietta

Answer 1

From my understanding you are actually doing too much inside the for loop.

Essentially, what you want is just a FOR loop which contains a bit of data cleanup and one if / elseif / else condition.

I cleaned the code up a bit to reflect that. The @match variables are completely removed. I think the main issue was that inside your loop you were checking for the unnecessary "match" variables, and test whether @i equals the @rowCount.

@i is the current run of the loop. @rowCount is the last run.

Effect: the code inside the loop is only executed once, namely for the last run. Which makes no sense in your usecase, I believe. You want to check matches for each found location in your non-sendable DE, not just the last.

Also:

• Moved some of the variables out of the for loop, no need to define them in each loop iteration.
• Added indentation to make code more readable and structurally clearer.
• Changed the "IIF logic" in the links to a more basic IF EMPTY logic.

I have not tested this code, but it should provide a good starting point.

%%[

/* global variables */

SET @defaultURL ='https://www.trafalgar.com'
SET @bara= '|'
SET @virgula=','
SET @location = AttributeValue('Product: Description')


IF IndexOf(@Location,@virgula) > 0 THEN 
  SET @rs = BuildRowsetFromString(@Location,@virgula)
  SET @primaryLocation = Field(Row(@rs,1),1)
  SET @secondaryLocation = Field(Row(@rs,2),1)
ELSE
  SET @primaryLocation = @location
  SET @secondaryLocation = @location  
ENDIF 
/*
]%%

Primary location: %%=v(@primaryLocation)=%%
<br>
Secondary location: %%=v(@secondaryLocation)=%%
<br>
%%[
*/

SET @rows = LookupRows("Blog Post Reference Table","Default","1")
SET @rowCount = rowcount(@rows)

IF @rowCount > 0 then
/* entry found in nonsendable DE */

  FOR @i = 1 to @rowCount do
  /* get all references */
    SET @row = row(@rows, @i) /* get row based on counter */
    SET @Article = field(@row,"Article Title")
    SET @Description = field(@row,"Description")
    SET @Image = field(@row,"Image")
    SET @linkTrip = field(@row,"Permalink")
    SET @locationCity = field(@row,"Location_City")
    SET @locationCountry = field(@row,"Location_Country")

    /* fallback */
    IF empty(@linkTrip) THEN
      SET @linkTrip = @defaultURL
    ENDIF

    /* cleanup data */
    if IndexOf(@locationCity,@bara) > 0 THEN 
      SET @locationCity = Substring(@locationCity,1, Subtract(IndexOf(@locationCity,@bara),1))
    endif

    if IndexOf(@locationCountry,@bara) > 0 THEN 
      SET @locationCountry = Substring(@locationCountry,1, Subtract(IndexOf(@locationCountry,@bara),1))
    endif
    /* end cleanup */

    IF (@primaryLocation == @locationCity) 
    or (@primaryLocation == @locationCountry) THEN
    ]%%
      From the blog piece for primary location:<br>
      Article name:%%=v(@Article)=%%<br>
      <br>
      Primary location: %%=v(@primaryLocation)=%%<br>
      <br>
      Description: %%=v(@Description)=%%
      <br>
      <a href="%%=RedirectTo(@linkTrip)=%%"><img src="%%=v(@Image)=%%" width="600" height="400"></a>
      %%[
    ELSEIF (@secondaryLocation == @locationCity) 
    or (@secondaryLocation == @locationCountry) THEN
      ]%%
        From the blog piece for secondary location:<br>
        Article name: %%=v(@Article)=%%<br>
        <br>
        Description: %%=v(@Description)=%%
        <br>
        <a href="%%=RedirectTo(@linkTrip)=%%"><img src="%%=v(@Image)=%%" width="600" height="400"></a>
      %%[ 
    ELSE 
      ]%%
        DEFAULT CONTENT because there was no match for primary and secondary location
      %%[
    ENDIF
  NEXT @i 
ELSE
]%%
  DISPLAY SOME ERROR MESSAGE, no data found.
%%[  
ENDIF
]%%

Hope this helps!