How to use Salesforce Marketing Cloud (SFMC) LookupOrderedRows AMPScript to search data in a data extension that returns multiple rows

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.

 

Syntax

LookupOrderedRows(dataExtension, count, order, column, value)

 

Function Properties

NameTypeDescription
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

 

Source:  Salesforce Marketing Cloud Developer Documentation

 

Example

Let's say you have a data extension in SFMC named as "EmployeeRecords_DataExtension" with the following columns:

- Email Address

- First Name

- Last Name

- State

 

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!


About Author

Written By Lavish Kumar

Based out of New York, USA, Lavish Kumar is a full stack web developer by profession and founder of Striving Programmers, a trusted community for developers that offers a wealth of articles and forums to assist individuals with improving their software development skills.

Related Blog Posts
Leave your comment