ssjs.guide SFMC
⌘K

DataExtension.Rows

Row-level CRUD methods on a DataExtension object. Retrieve, Add, Update, and Remove rows using object-oriented syntax.

DataExtension.Rows is the primary interface for reading and writing Data Extension rows via the Core library. Access it through a DataExtension.Init() object.

Methods

Method Returns Description
<DataExtensionInstance>.Rows.Retrieve([filter]) object[] Retrieve rows, optionally filtered
<DataExtensionInstance>.Rows.Add(rowData) string Insert new row(s)
<DataExtensionInstance>.Rows.Lookup(searchFieldNames, searchValues[, limit[, orderByFieldName]]) object[] Look up rows by column values
<DataExtensionInstance>.Rows.Update(rowData, whereFieldNames, whereValues) string Update existing rows
<DataExtensionInstance>.Rows.Remove(columnNames, columnValues) number Delete rows matching column values

<DataExtensionInstance>.Rows.Retrieve

Returns an array of row objects. Each object has properties matching the DE column names.

Syntax

<DataExtensionInstance>.Rows.Retrieve([filter])

Parameters

Name Type Required Description
filter object No SimpleFilterPart or ComplexFilterPart object

Filter Object

// SimpleFilterPart
var filter = {
    Property: "columnName",
    SimpleOperator: "equals",    // equals, notEquals, greaterThan, lessThan, etc.
    Value: "filterValue"
};

// ComplexFilterPart (AND/OR)
var filter = {
    LeftOperand: {
        Property: "Status",
        SimpleOperator: "equals",
        Value: "active"
    },
    LogicalOperator: "AND",
    RightOperand: {
        Property: "Score",
        SimpleOperator: "greaterThan",
        Value: "50"
    }
};

Return value

object[] — row objects with properties matching DE column names.

Examples

Platform.Load("core", "1.1.5");
var de = DataExtension.Init("Products");

// Retrieve all rows
var all = de.Rows.Retrieve();

// Retrieve with single filter
var active = de.Rows.Retrieve({
    Property: "IsActive",
    SimpleOperator: "equals",
    Value: "true"
});

// Iterate
for (var i = 0; i < active.length; i++) {
    var row = active[i];
    Write(row.ProductName + ": " + row.Price + "<br>");
}

On CloudPages, de.Rows.Retrieve() without a filter may return empty. Always pass a filter on CloudPages or use Platform.Function.LookupRows() instead. See Known Bugs.

<DataExtensionInstance>.Rows.Add

Adds one or more rows to the previously initialized data extension.

Syntax

<DataExtensionInstance>.Rows.Add(rowData)

Parameters

Name Type Required Description
rowData array Yes Array of objects, one per row to add. Each object’s keys must match data extension field names.

Return value

"OK" on success.

Examples

Platform.Load("core", "1.1.5");
var arrContacts = [
    { Email: "jdoe@example.com", FirstName: "John", LastName: "Doe" },
    { Email: "aruiz@example.com", FirstName: "Angel", LastName: "Ruiz" }
];
var birthdayDE = DataExtension.Init("birthdayDE");
birthdayDE.Rows.Add(arrContacts);

<DataExtensionInstance>.Rows.Lookup

Returns rows where the specified columns equal the specified values (AND-joined). Optionally limits results and orders by a field.

When initializing a data extension for Lookup() from an email message, you must use the data extension Name; on landing pages, either Name or external key works — make them identical to be safe.

Syntax

<DataExtensionInstance>.Rows.Lookup(searchFieldNames, searchValues[, limit[, orderByFieldName]])

Parameters

Name Type Required Description
searchFieldNames array Yes Array of column names to match against
searchValues array Yes Array of values to match (one per column, in order)
limit number No Maximum number of rows to return
orderByFieldName string No Field to order results by

Return value

object[]

Examples

Platform.Load("core", "1.1.5");
var testDE = DataExtension.Init("testDE");
var data = testDE.Rows.Lookup(["Age"], [25], 2, "LastName");

<DataExtensionInstance>.Rows.Update

Updates the columns of rows where whereFieldNames equal whereValues (AND-joined).

Syntax

<DataExtensionInstance>.Rows.Update(rowData, whereFieldNames, whereValues)

Parameters

Name Type Required Description
rowData object Yes Object whose keys are columns to update and values are the new values
whereFieldNames array Yes Array of column names to match against
whereValues array Yes Array of values to match (one per column, in order)

Return value

"OK" on success.

Examples

Platform.Load("Core", "1");
var dataExt = DataExtension.Init("NTO Customer List");
var fieldsToUpdate = { StateProvince: "QC", PreferredActivity: "Sailing" };
var result = dataExt.Rows.Update(fieldsToUpdate, ["MemberId", "Country"], [9868600, "CA"]);

<DataExtensionInstance>.Rows.Remove

Deletes rows from the previously initialized data extension where the specified columns equal the specified values (AND-joined). For large deletion requests, batch the work — this method times out on long-running deletes.

Syntax

<DataExtensionInstance>.Rows.Remove(columnNames, columnValues)

Parameters

Name Type Required Description
columnNames array Yes Array of column names to match against
columnValues array Yes Array of values to match (one per column, in order)

Return value

number — count of deleted rows.

Examples

Platform.Load("Core", "1.1.5");
var memberDE = DataExtension.Init("MembershipRewards");
var result = memberDE.Rows.Remove(["Area"], ["Kensington"]);

Complete CRUD Pattern

Platform.Load("core", "1.1.5");
var de = DataExtension.Init("UserPreferences");
var subscriberKey = Platform.Variable.GetValue("@subscriberKey");

// Read
var existing = de.Rows.Retrieve({
    Property: "SubscriberKey",
    SimpleOperator: "equals",
    Value: subscriberKey
});

if (existing.length === 0) {
    // Create
    de.Rows.Add({
        SubscriberKey: subscriberKey,
        Theme: "light",
        Language: "en",
        CreatedAt: Platform.Function.Now()
    });
} else {
    // Update
    de.Rows.Update(
        { Theme: newTheme, UpdatedAt: Platform.Function.Now() },
        ["SubscriberKey"],
        [subscriberKey]
    );
}

See Also

See Also