Get Started with AMPscript Development—Lesson 4

This lesson expands on Lesson 3, exploring advanced AMPscript topics.

Using For loops
Dynamically changing the appearance of a message using AMPscript
Filtering message context

In addition to the sendable data extension and ShipTime data extension from the previous lessons, this example refers to a data extension that contains information about a subscriber’s purchase history.

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      @shipDays
16
17  Set @memid = MemberID
18  Set @fname = FirstName
19  Set @lname = LastName
20  Set @prefname = PrefName
21  Set @address = Address
22  Set @postcode = PostalCode
23  Set @mempref = MemPref
24  Set @plat = Plat
25]%%
26
27/* Address the subscriber by their preferred name, if they have one. Otherwise,
28   use their first name. */
29Hello %%= Iif(Empty(@prefname), @fname, @prefname) =%%, here are your account details.
30
31Name
32
33First Name:%%= v(@fname) =%%
34Last Name:%%= v(@lname) =%%
35/* Include the PrefName field if it's populated. */
36%%[ If not Empty(@prefname) then ]%%
37Preferred Name:%%= v(@prefname) =%%
38%%[ EndIf ]%%
39
40Address
41
42Address:%%= v(@address) =%%
43
44%%[
45  /* Check to see if the postal code field is populated. */
46  If not Empty(@postcode) then
47
48    /* Find the postal code in the ShipTimes Data Extension */
49    Set @rows = LookupRows("ShipTimes","postcode",@postcode)
50
51    /* Count the number of rows returned to make sure that there's only one match. */
52    If RowCount(@rows) == 1 then
53
54      /* Expose all columns in the matching row to the @row variable. */
55      Set @row = Row(@rows,1)
56
57      /* Set the @city variable to the value in the city column of the matching row. */
58      Set @city = Field(@row,"city")
59
60      /* Set the @state variable to the value in the state column. */
61      Set @state = Field(@row,"state")
62
63      /* Set the @ship variable to the value in the ShipTime column. */
64      Set @ship = Field(@row,"ShipTime")
65
66    EndIf
67  EndIf
68]%%
69
70City:%%= v(@city) =%%
71State:%%= v(@state) =%%
72Postal Code:%%= v(@postcode) =%%
73
74%%[
75  /* If we found a shipping time, show it. */
76  If not Empty(@ship) then
77    /*
78    Iif(@ship > 1, Concat(@ship, " days"), Concat(@ship, " day"))
79]%%
80
81Shipping Time: %%= v(@shipDays) =%%
82
83%%[ Else ]%%
84/* A shipping estimate wasn't available. */
85
86We couldn't find a shipping preference.
87Update %%= RedirectTo(Concat('http://example.com?s=',_subscriberkey)) =%% update your profile.
88
89%%[ EndIf ]%%
90
91Member Preferences
92
93Shopping Preference: %%= v(@mempref) =%%
94
95/* Check the subscriber's platinum status and show the appropriate message. */
96Platinum Member: %%[ If @plat == "N" then ]%%
97%%= RedirectTo(Concat('http://example.com?s=',_subscriberkey)) =%%
98Sign-Up
99%%[ Else ]%%
100%%= v(@plat) =%%
101%%[ EndIf ]%%
102
103Recent Purchases
104
105%%[
106  /* Declare additional variables for products. */
107  Var @prodID, @prodName, @prodDesc, @price, @indicator, @color,
108      @totalPurchases, @decimalChar
109
110  /* Set the total purchases to zero. */
111  Set @totalPurchases = 0
112
113  /* Set the CSS color indicator to odd. */
114  Set @indicator = "odd"
115
116  /* Find all of the recent purchases for the current subscriber.
117     Set the @rows variable to contain the resulting rowset. */
118  Set @rows = LookupRows("Purchases", "MemberID", @memID)
119
120  /* Make sure that the lookup returned at least one row of products. */
121  If RowCount(@rows) >= 1 then
122
123    /* Use a For loop to step through each row in the rowset. */
124    For @i = 1 to RowCount(@rows) do
125
126      /* Set the @row variable to contain the data in the current row. */
127      Set @row = Row(@rows, @i)
128
129      /* Set the product variables to equal the values in the current row. */
130      Set @prodID = Field(@row, "ProductID")
131      Set @prodName = Field(@row, "ProductName")
132      Set @prodDesc = Field(@row, "Description")
133      Set @price = Field(@row, "Price")
134      Set @totalPurchases = Add(@totalPurchases, Field(@row, "Price"))
135
136      /* If the value of the indicator variable is "odd", use a white background color.
137         After setting the color, change the indicator to "even".*/
138      If @indicator == "odd" then
139        Set @color = "#FFFFFF"
140        Set @indicator = "even"
141
142      /* If the value of the indicator variable is "even", use a gray background color.
143         After setting the color, change the indicator to "odd". */
144      Else
145        Set @color = "#EEEEEE"
146        Set @indicator = "odd"
147      EndIf
148
149/* Bring in a content area to contain the recent purchase list. */
150]%%
151
152%%= TreatAsContent(ContentBlockByName("Content Builder\RecentPurchases")) =%%
153
154%%[
155  /* Go to the next row in the rowset. */
156  Next @i
157
158  /* Determine if there are two characters after the decimal point. */
159  Set @decimalChar = Subtract(Length(@totalPurchases),IndexOf(@totalPurchases,"."))
160
161  /* Handle the case where there's one character after the decimal point. */
162  If @decimalChar == 1 then
163    Set @totalPurchases = Concat(@totalPurchases,"0")
164
165  /* Handle the case where there's no decimal point. */
166  ElseIf @decimalChar == Length(@totalPurchases) then
167    Set @totalPurchases = Concat(@totalPurchases,".00")
168  EndIf
169]%%
170
171Total Purchases:$%%= v(@totalPurchases) =%%
172
173%%[ EndIf ]%%

Create New Variables

The first part of this code example is similar to the example in lesson 3. However, the second part inserts information about the subscriber’s previous purchases and total amount spent into the email.

First, the code creates a set of variables to use in inserting product and price information into the email.

1/* Declare additional variables for products. */
2Var @prodID, @prodName, @prodDesc, @price, @indicator, @color,
3    @totalPurchases, @decimalChar

Next, the code assigns values to the @totalPurchases and @indicator variables. The @totalPurchases variable stores the amount of the subscriber’s total purchases. The @indicator variable alternates the background color for each purchase.

1/* Set the total purchases to zero. */
2Set @totalPurchases = 0
3
4/* Set the CSS color indicator to odd. */
5Set @indicator = "odd"

Look Up Data in a Data Extension

The code consults the Purchases data extension to find all of the subscriber’s previous purchases based on their member ID.

1Set @rows = LookupRows("Purchases", "MemberID", @memID)

The @rows variable is set to the resulting rowset.

Use a For Loop to Iterate through the Rowset

If there aren’t any rows in the rowset, it means that there isn’t any purchase data to display. We want to be sure that the rowset contains at least one row, so we use the RowCount() function.

1If RowCount(@rows) >= 1 then

Next, the code uses a For loop to step through the rowset that we obtained from the data extension. The For loop repeats an equal number of times to the number of rows in the rowset.

1For @i = 1 to RowCount(@rows) do

The row() function obtains the data in all of the columns in the current row.

1Set @row = Row(@rows, @i)

In each row, we use the Field() function to set the values of the product variables. We also use the Add() function to keep a running total of the amount of money the subscriber spent on their purchases.

1Set @prodID = Field(@row, "ProductID")
2Set @prodName = Field(@row, "ProductName")
3Set @prodDesc = Field(@row, "Description")
4Set @price = Field(@row, "Price")
5Set @totalPurchases = Add(@totalPurchases, Field(@row, "Price"))

Change the Appearance of Each Row

The cod sets the background color for the current row to white or gray depending on the background color of the previous entry. Odd-numbered entries have a white background, and even entries have a gray background.

1If @indicator == "odd" then
2  Set @color = "#FFFFFF"
3  Set @indicator = "even"
4
5/* If the value of the indicator variable is "even", use a gray background color.
6    After setting the color, change the indicator to "odd". */
7Else
8  Set @color = "#EEEEEE"
9  Set @indicator = "odd"
10EndIf

Apply the Content to a Contact Area

The code uses the ContentBlockByName() function to retrieve an existing content block. The purchase data is formatted in the same manner as the content block.

1%%= TreatAsContent(ContentBlockByName("Content Builder\RecentPurchases")) =%%

The code then instructs the For loop to proceed to the next row.

1%%[ Next @i ]%%

Format the Currency Value

The final part of the code makes sure that the numerical value in the @totalPurchases variable contains exactly two decimal places. It starts by determining the current number of decimal places by using the IndexOf(), Length(), and Subtract() functions.

The IndexOf(@totalPurchases,".") function returns the position in the value of the @totalPurchases variable where a decimal point (.) character occurs. If the variable doesn’t contain a decimal point, the function returns 0.
The Length(@totalPurchases) function returns the number of characters in the value of the @totalPurchases variable.
The Subtract(...) function returns the result of subtracting the value returned by the IndexOf(...) function from the value returned by the Length(...) function.

1Set @decimalChar = Subtract(Length(@totalPurchases),IndexOf(@totalPurchases,"."))

If the resulting value of this computation is 1, it means that the value of the @totalPurchases contains a decimal point followed by one digit. Because we want to show the subscriber a currency value with two decimal places, we use the Concat() function to add a zero to the end of the value.

1If @decimalChar == 1 then
2  Set @totalPurchases = Concat(@totalPurchases,"0")

If the value of the @decimalChar variable equals the number of characters in the value of the @totalPurchases variable, it means that the @totalPurchases didn’t contain a decimal point. In this case, we can use the Concat() function to add a decimal point followed by two zeros.

1ElseIf @decimalChar == Length(@totalPurchases) then
2  Set @totalPurchases = Concat(@totalPurchases,".00")
3EndIf