Get Started with AMPscript Development—Lesson 3

This lesson builds on Lesson 2 by adding this advanced functionality:

Looking up content in a data extension
Using information from the row to populate multiple fields in the email
Using error checking to handle unexpected data

In addition to the sendable data extension that we used in previous lessons, this lesson refers to a data extension called ShipTime, which contains the columns in this table.

Data Extension Column Name	Description
`PostalCode`	A postal code
`City`	The city associated with the postal code
`State`	The state, province, or other subnational unit associated with the postal code
`ShipTime`	The amount of time required to ship items to the postal code

Our fictitious company, Northern Trail Outfitters (NTO), uses this data extension to look up each subscriber’s city and other location details based on their postal code. The company also uses this data to provide subscribers with an estimated shipping time based on their location.

Message Content

This code example contains the message content for this lesson.

1%%[
2  Var @memid,
3      @fname,
4      @lname,
5      @prefname,
6      @address,
7      @city,
8      @state,
9      @postcode,
10      @mempref,
11      @plat,
12      @rows,
13      @row,
14      @ship
15
16  Set @memid = MemberID
17  Set @fname = FirstName
18  Set @lname = LastName
19  Set @prefname = PrefName
20  Set @address = Address
21  Set @postcode = PostalCode
22  Set @mempref = MemPref
23  Set @plat = Plat
24]%%
25
26%%[ if not empty(@prefname) then ]%%
27  %%= v(@prefname) =%%
28%%[ else ]%%
29  %%= v(@fname) =%%
30%%[ endif ]%%, below are your account details
31
32<table width="100%" cellpadding="0" cellspacing="0" border="0">
33  <tr>
34    <td height="30">First Name: %%= v(@fname) =%%</td>
35    <td height="30">Last Name: %%= v(@lname) =%%</td>
36  </tr>
37  %%[ if not Empty(@prefname) then ]%%
38  <tr>
39    <td height="30">Preferred Name: %%= v(@prefname) =%%</td>
40    <td></td>
41  </tr>
42  %%[ endif ]%%
43  <tr>
44    <td height="30">Address: %%= v(@address) =%%</td>
45
46    /* Use the postal code to look up the city, state, and shipping time. */
47    %%[
48    If not Empty(@postcode) then
49
50      /* Find the postal code in the ShipTimes Data Extension */
51      Set @rows = LookupRows("ShipTimes","PostalCode",@postcode)
52
53      /* Make sure exactly one row of data was found */
54      if RowCount(@rows) == 1 then
55
56        /* Set the row - This now exposes all columns to our variable */
57        Set @row = Row(@rows,1)
58
59        /* Set the City */
60        Set @city = Field(@row,"city")
61
62        /* Set the State */
63        Set @state = Field(@row,"state")
64
65        /* Set the @ship variable to the ShipTime column */
66        Set @ship = Field(@row,"ShipTime")
67
68      EndIf
69    EndIf ]%%
70
71    <td height="30">City: %%= v(@city) =%%</td>
72  </tr>
73  <tr>
74    <td height="30">State: %%= v(@state) =%%</td>
75    <td height="30">Postal Code: %%= v(@postcode) =%%</td>
76  </tr>
77  <tr>
78    <td height="30">
79      %%[ If not Empty(@ship) then ]%%
80      Shipping Time: %%= v(@ship) =%%
81      %%[ If @ship > 1 then ]%%
82      days
83      %%[ Else ]%%
84      day
85      %%[ EndIf ]%%
86      %%[ EndIf ]%%
87    </td>
88    <td height="30">&nbsp;</td>
89  </tr>
90  <tr>
91    <td height="30">Shopping Preference: %%= v(@mempref) =%%</td>
92    <td height="30">Platinum Member:
93      %%[ if @plat == "False" then ]%%
94      <a href="%%= RedirectTo(Concat('http://example.com?s=',_subscriberkey)) =%%">
95        See if you're eligible
96      </a>
97      %%[ Else ]%%
98      %%= v(@plat) =%%
99      %%[ EndIf ]%%
100    </td>
101  </tr>
102</table>

Data Extension Lookup

The block of AMPscript code under the Address field uses the @postcode variable to find the subscriber’s city, state, and estimated shipping time. It performs this lookup using the LookupRows() function to find the row in the ShipTimes data extension, for which the value in the PostalCode column equals the value of the @postcode variable. The LookupRows() function returns all rows that meet these criteria. The code example saves this data in the variable @rows.

1%%[
2  If not Empty(@postcode) then
3
4    /* Find the postal code in the ShipTimes Data Extension */
5    Set @rows = LookupRows("ShipTimes","PostalCode",@postcode)

Count the Number of Matching Rows from the Data Extension

We assume that only one row in the data extension contains the specific postal code value. We use the RowCount() function to make sure that the variable only contains a single row of data.

1/* Make sure exactly one row of data was found */
2    if RowCount(@rows) == 1 then

Parse Data from the Matching Row

If the RowCount() function confirms that the @rows variable contains a single row of data, we set the variable @row to equal that row.

1/* Set the row - This now exposes all columns to our variable */
2      Set @row = Row(@rows,1)

Now that we have a single row of data, we use the Field() function to parse the data fields from that row. We set the value of the @city variable to equal the value found in the city column of the matching row and the @state value to equal the value in the state column. We also set a new variable, @ship, to equal the value found in the ShipTime column.

1/* Set the City */
2      Set @city = Field(@row,"city")
3
4      /* Set the State */
5      Set @state = Field(@row,"state")
6
7      /* Set the @ship variable to the ShipTime column */
8      Set @ship = Field(@row,"ShipTime")
9
10EndIf ]%%