I am putting together a script that pulls information from multiple data extensions. I have completed the code for one data extension but I am not sure if I just need to duplicate all the variables and its done (would look really messy though) or I can make it in a nice and neat way.

Here is the code currently

 var @rows, @row, @rowCount, @splitname, @prefix_lang 
 var @lookupValue
 set @studyloc = [Study Where]

 SET @splitname = BUILDROWSETFROMSTRING([emailname_],"-") 
 IF ROWCOUNT(@splitname) > 1 THEN 
 SET @prefix_lang = FIELD(ROW(@splitname,1),"VALUE") 

 set @rows = LookupRows("SFdataCorr_StudyWhere","StudyWhere",@studyloc) 
 set @rowCount = rowcount(@rows)

 if @rowCount > 0 then

 var @studyloccorr 
 set @row = row(@rows,1) /* get row #1 */ 
 set @studyloccorr = field(@row,@prefix_lang)


 SET @studyloccorr = Lookup("SFdataCorr_StudyWhere", @prefix_lang, "StudyWhere", "default")



Now this code is only producing the @studyloccorr (study location correction) value. But I would need 5 other lookups in this which would work in the same way. Meaning there would be a @programinterest, @programinterestcorr (this is 1 out of the 5) variable but I am not sure if I need to create a different variable for the existing @rows,@rowcount,@row.

So is this the most optimal way?

 var @rowsStudyloc, @rowStudyloc, @rowCountStudyloc, @rowsProgramInt,
 @rowProgramInt, @rowCountProgramInt, @splitname, @prefix_lang,
 @studyloc, @programint
 SET @studyloc = [Study Where] 
 SET @programint = [Program Interest]

 SET @splitname = BUILDROWSETFROMSTRING([emailname_],"-") 
 IF ROWCOUNT(@splitname) > 1 THEN 
 SET @prefix_lang = FIELD(ROW(@splitname,1),"VALUE") 

 set @rowsStudyloc = LookupRows("SFdataCorr_StudyWhere","StudyWhere", @studyloc) 
 set @rowCountStudyloc = rowcount(@rowsStudyloc)

 set @rowsProgramInt = LookupRows("SFdataCorr_SFdataCorr_ProgramInterest","ProgramInterest", @programint) 
 set @rowCountProgramInt = rowcount(@rowsProgramInt)

 if @rowCountStudyloc > 0 then

 var @studyloccorr  
 set @rowStudyloc = row(@rowsStudyloc,1)
 set @studyloccorr = field(@rowStudyloc,@prefix_lang)

 SET @studyloccorr = Lookup("SFdataCorr_StudyWhere", @prefix_lang, "StudyWhere", "default") 


 if @rowCountProgramInt > 0 then

 var @programintcorr 
 set @rowProgramInt  = row(@rowsProgramInt ,1)  
 set @programintcorr = field(@rowProgramInt,@prefix_lang)

 SET @programintcorr = Lookup("SFdataCorr_ProgramInterest", @prefix_lang, "ProgramInterest", "default")



The Data Extensions are built up the same way, they are here: https://docs.google.com/spreadsheet/ccc?key=0Aj0ot7lDY1-_dEtaTlpITDJmcTFGRG9HNjlBZXNibEE&usp=sharing

I would need to add 4 other sections to the code for 4 other lookups (though those do not need a default value so I ended up using only Lookup() ). Is there any way to simplify the code? would this so far that I made works?

THank you for any insight you guys can give me.

edit: I actually went ahead and included all the lookups. I hope there is a way to shorten it somehow => http://www.snipsave.com/user/profile/balintsipos#8262


You're hoping to use some kind of method/function within AMPscript it sounds like. And unfortunately that does not exist. So you have a few options:

  1. You can do the messy way you propose. Although messy, its probably the most common way I've seen it done.
  2. You could use SSJS as your ET language of choice. Or a mix of AMPscript and SSJS. That would give you the ability to write functions, and only have this code in there one time.
  3. You could put all the AMPscript you want on its own landing page. Then, you would use AMPscript method HTTPGet to both pass in values and get them back. EDIT: The way to do this is to set the landing page (upon creation) to be of type JSON. Put all the ampscript in a block at the top. Then, after the block, the page would consist of JSON, mixed with AMPscript. Like {"var1" : "%%=v(@var1)=%%", "var2" : "%%=v(@var2)=%%" }

#3 is probably cleanest using what you already have. If I were starting from the beginning I would use SSJS.

Hope this helps!

  • thanks a lot @Timothy. As a marketing person I barely get the concepts of AMPscript (yet). I'm not sure I would venture into SSJS. More so as I actually needed to google this abbreviation to find out what it is :) So for now sticking to the messy AMPscript. I might insert markups to make it more readable. – Balint Jul 8 '14 at 14:10
  • Please have a look at my edits for an example of #3. – Timothy Jul 8 '14 at 14:16

Multiple lookups within an email can cause slowness but for what you are trying to do would be the best option. From experience SSJS would not be recommended for this type of code unless it was at a very small send scale.


A loop may be useful for redudant code

%%[FOR @i=1 TO 5 DO 
NEXT @i]%%

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.