I have a need to send a weekly email which will include 5 'weekly special' products. I'd like to find a way to automate this as much as possible for the team handling the distribution. I set up some AmpScript inside of an email to pull fields from a Data Extension. This is then added to an automation and will send every time the weekly specials CSV is added via ftp. So the workflow looks like this:

CSV File uploaded to FTP > CSV added to data extension > email sent with weekly specials pulled from data extension.

This is all working quite well. What I'm wondering, since I'm somewhat new to AmpScript, is whether or not I'm handling this all correctly. Is the code as efficient as possible or is there a better way to go about this?
My goal is that the team handling this never has to be in the actual email, all they need to do is drop the csv file into the ftp and let the automation do the rest.

There are 6 columns in the csv:

  1. The Date being sent (today)
  2. Product 1
  3. Product 2
  4. Product 3
  5. Product 4
  6. Product 5

The AmpScript looks for today's date and loads the corresponding products into the correct places. Below is the code:

var @today, @p1, @p2, @p3, @p4, @p5
set @today =  Format(Now(), "M/dd/yy")
set @p1 = Lookup("Pull-Field-Test", "Product-1", "Date", @today) 
set @p2 = Lookup("Pull-Field-Test", "Product-2", "Date", @today) 
set @p3 = Lookup("Pull-Field-Test", "Product-3", "Date", @today) 
set @p4 = Lookup("Pull-Field-Test", "Product-4", "Date", @today) 
set @p5 = Lookup("Pull-Field-Test", "Product-5", "Date", @today) 


I know AmpScript doesn't have native array functionality, but I'm wondering if there's a better way to write this code or handle this process in general.


You are performing too many lookup queries. Please try the following code

SET @rows = LookupRows("Pull-Field-Test","Date", @today)

IF RowCount(@rows) > 0 THEN

    SET @row = Row(@rows,1)

    SET @p1 = Field(@row,"Product-1")
    SET @p2 = Field(@row,"Product-2")
    SET @p3 = Field(@row,"Product-3")
    SET @p4 = Field(@row,"Product-4")
    SET @p5 = Field(@row,"Product-5")


Note I have not tested this code, please refer to the LookupRows() AMPScript documentation.

  • This is a good direction, thank you. Quick question, is there an extra '=' in the SET @p1 declarations before the word "Field"? I received an error about them and once removed things seem to function really well. I wanted to make sure I wasn't misunderstanding something. – Eczekner Nov 20 '17 at 0:49
  • No that was a typo – Fayyaz A Mian Nov 20 '17 at 11:02

Your Answer

