ssjs.guide SFMC

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
Retrieve([filter]) object[] Retrieve rows, optionally filtered
Add(columnValues) number Insert a new row
Update(columnValues, keyColumns, keyValues) number Update existing rows
Remove(filterColumn, filterValue) number Delete rows matching filter

Method: Retrieve

de.Rows.Retrieve([filter])

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

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"
    }
};

Retrieve 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.

Method: Add

de.Rows.Add(columnValues)

Inserts a new row into the Data Extension. Returns 1 on success.

Parameters

Name Type Required Description
columnValues object Yes Plain object mapping column names to values

Examples

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

var rowCount = de.Rows.Add({
    Email: "jane@example.com",
    FirstName: "Jane",
    Score: 95,
    SubmittedAt: Platform.Function.Now()
});
// rowCount === 1 on success

Method: Update

de.Rows.Update(columnValues, keyColumnNames, keyValues)

Updates existing rows that match the key. Returns the number of rows updated.

Parameters

Name Type Required Description
columnValues object Yes Columns to update with new values
keyColumnNames string[] Yes Array of key column names to match
keyValues string[] Yes Array of corresponding key values

Examples

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

var updated = de.Rows.Update(
    { Status: "converted", UpdatedAt: Platform.Function.Now() },
    ["SubscriberKey"],
    ["sub_12345"]
);
// updated === 1 if found

Method: Remove

de.Rows.Remove(filterColumn, filterValue)

Deletes all rows where filterColumn equals filterValue. Returns the number of rows deleted.

Parameters

Name Type Required Description
filterColumn string Yes Column name to filter by
filterValue string Yes Value to match for deletion

Examples

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

var removed = de.Rows.Remove("SessionToken", expiredToken);
// removed === number of deleted rows

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