Today, we will talk about Salesforce Marketing Cloud (SFMC) and AMPScript (LookupOrderedRows AMPScript to be specific). Salesforce Marketing Cloud (SFMC) is a customer relationship management (CRM) platform for marketers that allows marketers to create 1-to-1 journeys across the entire Salesforce Customer Success Platform -- journeys that span not only digital marketing, but sales and service as well. This simplifies data management, segmentation and campaign management, making it easy to create compelling digital marketing campaigns, boost customer engagement and ultimately drive more leads into Salesforce. (source: Salesforce.com)
In this tutorial, we will go over the LookupOrderedRows AMPScript that can be used to search data in a data extension that returns multiple rows.
What is LookupOrderedRows AMPScript?
It returns a specified number of rows. You can specify multiple additional field/value pairs as part of an AND clause.
LookupOrderedRows(dataExtension, count, order, column, value)
|dataExtension||string||Required||The name of the data extension from which to return the specified rows|
|count||int||Required||Number of rows to return. A 0 value indicates to return all matching rows.|
|order||string||Required||Order clause in the following format: field [asc | desc] [, ... ]|
|column||string||Required||Field to use to build the where clause|
|value||string||Required||Value to use to build the where clause|
Let's say you have a data extension in SFMC named as "EmployeeRecords_DataExtension" with the following columns:
- Email Address
- First Name
- Last Name
For this example, let's assume that we need to search employee records (in EmployeeRecords_DataExtension) and find out which employees are from "Texas" state.
In the AMPScript, we will use "Texas" as our search term and we will perform a LookupOrderedRows search for "Texas" in State column of the EmployeeRecords_DataExtension.
LookupOrderedRows AMPScript to search data in a data extension that returns multiple rows
%%[ var @searchtermlookupValue set @searchtermlookupValue = "Texas" /* this will be our search term */ var @ReturnRows set @ReturnRows = 0 /* Use 0 to get all results */ var @GetRows set @GetRows = LookupOrderedRows("EmployeeRecords_DataExtension",@ReturnRows,"First_Name asc","State", @searchtermlookupValue) /* syntax of LookupOrderedRows */ var @GetRow, @GetRowCount set @GetRowCount = rowcount(@GetRows) if @GetRowCount > 0 then /* if search term or criteria matches with the data in the data extension then we will get 1 or more rows*/ var @GetRowNumber for @GetRowNumber = 1 to @GetRowCount do /* FOR LOOP for each row */ set @GetRow = row(@GetRows,@GetRowNumber) /* calculate row number */ var @First_Name_Column set @First_Name_Column = field(@GetRow,"First_Name") /* display value of the column from data extension */ var @Last_Name_Column set @Last_Name_Column = field(@GetRow,"Last_Name") /* display value of the column from data extension */ var @Email_Address_Column set @Email_Address_Column = field(@GetRow,"Email_Address") /* display value of the column from data extension */ ]%% <!-- Use this for row number: %%=v(@GetRowNumber)=%% Row --> First Name is %%=v(@First_Name_Column)=%% | Last Name is %%=v(@Last_Name_Column)=%% | Email is %%=v(@Email_Address_Column)=%% <br/> <hr/> %%[ next @i ]%% %%[ else ]%% <!-- If search term does not match with the data for any record in data extension --> ---- No rows found ---- %%[ endif ]%%
Note 1: I have not tested the above AMPScript. The above code / AMPScript is for learning purpose only. Please test it before using it on production.
Note 2: I do not take any credit for the above code / AMPScript. The above code / AMPScript is based on what I have learned from the resources available on Salesforce Marketing Cloud Developer Documentation Website and Salesforce Stackexchange. The purpose of this post is to share the knowledge. I hope the developers who are new to SFMC or AMPScript will find this helpful!