DataExtension.Rows

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

Requires Platform.Load("core", "1.1.5") and DataExtension.Init() before use. Always pass the External Key (CustomerKey) to Init — display Name binding is defective when Name and CustomerKey differ.

Methods

Method	Returns	Description
`<DataExtensionInstance>.Rows.Retrieve([filter])`	object[]	Retrieve rows, optionally filtered
`<DataExtensionInstance>.Rows.Add(rowData)`	number	Insert new row(s)
`<DataExtensionInstance>.Rows.Lookup(searchFieldNames, searchValues[, limit[, orderByFieldName]])`	object[] \| null	Look up rows by column values
`<DataExtensionInstance>.Rows.Update(rowData, whereFieldNames, whereValues)`	number	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"
    }
};

// LogicalOperator: "OR" is honoured too (runtime-verified on a CloudPage)
var orFilter = {
    LeftOperand:  { Property: "Country", SimpleOperator: "equals", Value: "CA" },
    LogicalOperator: "OR",
    RightOperand: { Property: "Country", SimpleOperator: "equals", Value: "US" }
};

Both "AND" and "OR" work in a ComplexFilterPart on CloudPages — an OR filter really returns the union of both operands and is not silently collapsed to AND (runtime-verified: an OR across two mutually-exclusive values returns rows matching either, not zero). Nest ComplexFilterPart objects as operands to combine more than two conditions.

Return value

object[] — row objects with properties matching DE column names. All field values are returned as strings (even Number/Boolean/Date columns) — unlike Lookup, which returns typed values for Number/Boolean/Decimal (Date is an ISO-8601 string exception — see Lookup). On no match, returns an empty array (length === 0), not null — an ergonomic advantage: you can iterate the result directly without a null-guard. If you need typed/native values instead of strings, use Platform.Function.LookupRows or Platform.Function.LookupOrderedRows.

Field type → returned JavaScript type (runtime-verified)

Result of probing a Data Extension containing one column of each valid field type via <DataExtensionInstance>.Rows.Retrieve():

DE field type	Returned type	Notes
Text	`string`
EmailAddress	`string`
Locale	`string`	e.g. `"en-US"`
Phone	`string`
Number	`string`	stringified number (e.g. `"42"`)
Decimal	`string`	stringified number (e.g. `"3.14"`)
Boolean	`string`	stringified boolean (`"True"` / `"False"`, capitalized)
Date	`string`	.NET-formatted date string (e.g. `"1/15/2024 12:00:00 AM"`)

Every field comes back as a string — including Number, Decimal, Boolean, and Date. This is the key difference from the Platform.Function.Lookup* family, which returns typed values. Note the Boolean stringifies to the capitalized "True"/"False" and the Date to a locale-style M/D/YYYY h:mm:ss AM/PM string (not ISO-8601).

Differs from official Salesforce docs

Runtime-verified on a CloudPage: de.Rows.Retrieve() without a filter DOES work on CloudPages and returns all rows — the widely-repeated “returns empty on CloudPages” bug could not be reproduced. Every field value is returned as a string (Number/Decimal as stringified numbers, Boolean as capitalized “True”/”False”, Date as a locale-formatted string, not ISO-8601). The result is a host array (instanceof Array is false, but .length and index access work).

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

Show test script

<script runat="server">

/*
 * Chapter: <DataExtensionInstance>.Rows.Retrieve([filter])
 *
 * CloudPage GET context. Proves:
 *   1. Rows.Retrieve is a function after Platform.Load("core", "1.1.5").
 *   2. DEV Retrieve() without a filter works on CloudPages and returns all
 *      rows (widely-repeated "empty on CloudPages" bug not reproducible).
 *   3. Result is a host array: [object Array], numeric .length, index
 *      access; instanceof Array is false.
 *   4. DEV every field value is a string — Text/EmailAddress/Locale/Phone
 *      as strings; Number/Decimal stringified; Boolean capitalized
 *      "True"/"False"; Date locale-style "M/D/YYYY h:mm:ss AM/PM"
 *      (not ISO-8601). Official docs / Platform.Function.Lookup* return
 *      typed values.
 *   5. SimpleFilter and ComplexFilterPart forms work — including
 *      LogicalOperator "OR", which returns the union and is NOT collapsed
 *      to AND (community cookbook wrongly claims DE WHERE is AND-only).
 *   6. On no match returns an empty host array (length 0), not null.
 *
 * EXPECTED OUTPUT: every line starts with PASS.
 */

function assert(id, actual, expected) {
    Platform.Response.Write((actual === expected ? "PASS " : "FAIL ") + id + " -> [" + actual + "]\n");
}
function outcomeOf(fn) {
    try { return "" + fn(); } catch (ex) { return "THREW: " + ("" + ex.message); }
}
function countDE(key) {
    return DataExtension.Retrieve({ Property: "CustomerKey", SimpleOperator: "equals", Value: key }).length;
}

Platform.Load("core", "1.1.5");

var KEY = "ssjsguide-ts-der-retr";
var NAME = "ssjs-guide-ts-der-retr";

if (countDE(KEY) > 0) { DataExtension.Init(KEY).Remove(); }
assert("precondition: no probe data extension exists", "" + countDE(KEY), "0");
DataExtension.Add({
    CustomerKey: KEY,
    Name: NAME,
    Fields: [
        { Name: "SubKey", FieldType: "Text", IsPrimaryKey: true, MaxLength: 50, IsRequired: true },
        { Name: "F_Text", FieldType: "Text", MaxLength: 50 },
        { Name: "F_Email", FieldType: "EmailAddress", MaxLength: 100 },
        { Name: "F_Locale", FieldType: "Locale", MaxLength: 10 },
        { Name: "F_Phone", FieldType: "Phone", MaxLength: 50 },
        { Name: "F_Number", FieldType: "Number" },
        { Name: "F_Decimal", FieldType: "Decimal", MaxLength: 18, Scale: 2 },
        { Name: "F_Boolean", FieldType: "Boolean" },
        { Name: "F_Date", FieldType: "Date" }
    ],
    SendableInfo: { Field: { Name: "SubKey", FieldType: "Text" }, RelatesOn: "Subscriber Key" }
});
assert("fixture: the probe data extension was created", "" + countDE(KEY), "1");

var de = DataExtension.Init(KEY);
assert("typeof instance.Rows.Retrieve is function", typeof de.Rows.Retrieve, "function");

de.Rows.Add([
    {
        SubKey: "r1", F_Text: "hello", F_Email: "a@example.com", F_Locale: "en-US",
        F_Phone: "5551234567", F_Number: 42, F_Decimal: 3.14, F_Boolean: true,
        F_Date: "2024-01-15T00:00:00.000"
    },
    {
        SubKey: "r2", F_Text: "world", F_Email: "b@example.com", F_Locale: "en-GB",
        F_Phone: "5559876543", F_Number: 7, F_Decimal: 1.5, F_Boolean: false,
        F_Date: "2024-06-01T12:00:00.000"
    }
]);

/* 2 + 3. No-filter Retrieve works; host-array shape. */
var all = de.Rows.Retrieve();
assert("DEV Retrieve() without filter returns rows on CloudPages (rumor: empty)", "" + all.length, "2");
assert("Retrieve() reports as [object Array]", Object.prototype.toString.call(all), "[object Array]");
assert("Retrieve() exposes numeric .length", typeof all.length, "number");
assert("DEV instanceof Array is false (host array; docs imply JS Array)", all instanceof Array ? "true" : "false", "false");

/* 4. All field values are strings with documented formats. */
var row = de.Rows.Retrieve({ Property: "SubKey", SimpleOperator: "equals", Value: "r1" })[0];
assert("DEV Text field typeof string (docs: typed)", typeof row.F_Text, "string");
assert("Text value round-trips", "" + row.F_Text, "hello");
assert("DEV EmailAddress typeof string", typeof row.F_Email, "string");
assert("EmailAddress value", "" + row.F_Email, "a@example.com");
assert("DEV Locale typeof string", typeof row.F_Locale, "string");
assert("Locale value", "" + row.F_Locale, "en-US");
assert("DEV Phone typeof string", typeof row.F_Phone, "string");
assert("Phone value", "" + row.F_Phone, "5551234567");
assert("DEV Number typeof string (docs/Lookup: number)", typeof row.F_Number, "string");
assert("Number stringified", "" + row.F_Number, "42");
assert("DEV Decimal typeof string (docs/Lookup: number)", typeof row.F_Decimal, "string");
assert("Decimal stringified", "" + row.F_Decimal, "3.14");
assert("DEV Boolean typeof string (docs/Lookup: boolean)", typeof row.F_Boolean, "string");
assert("DEV Boolean stringified capitalized True (spec/JS: true)", "" + row.F_Boolean, "True");
assert("DEV Date typeof string (docs/Lookup: Date)", typeof row.F_Date, "string");
assert("DEV Date is locale-style not ISO-8601", "" + row.F_Date, "1/15/2024 12:00:00 AM");

/* 5. Complex filter. */
assert("ComplexFilterPart AND returns the matching row", outcomeOf(function () {
    return de.Rows.Retrieve({
        LeftOperand: { Property: "F_Number", SimpleOperator: "greaterThan", Value: "10" },
        LogicalOperator: "AND",
        RightOperand: { Property: "F_Boolean", SimpleOperator: "equals", Value: "true" }
    }).length;
}), "1");

/* 5b. ComplexFilterPart OR is honoured and NOT collapsed to AND. r1.F_Text=hello,
   r2.F_Text=world; an OR across the two distinct values returns BOTH rows (2). If
   OR were silently ANDed, no single row is both hello and world -> 0. */
assert("DEV ComplexFilterPart OR returns the union (community cookbook: no OR)", outcomeOf(function () {
    return de.Rows.Retrieve({
        LeftOperand:  { Property: "F_Text", SimpleOperator: "equals", Value: "hello" },
        LogicalOperator: "OR",
        RightOperand: { Property: "F_Text", SimpleOperator: "equals", Value: "world" }
    }).length;
}), "2");

/* 6. No match -> empty array, not null. */
var empty = de.Rows.Retrieve({ Property: "SubKey", SimpleOperator: "equals", Value: "no-such-row-zzz" });
assert("no-match result is not null", empty === null ? "true" : "false", "false");
assert("no-match length is 0", "" + empty.length, "0");
assert("workaround: iterate without a null-guard when length is 0", empty.length === 0 ? "true" : "false", "true");

assert("cleanup: the probe data extension is removed", outcomeOf(function () { return DataExtension.Init(KEY).Remove(); }), "OK");
assert("cleanup: no probe data extension is left behind", "" + countDE(KEY), "0");
</script>

<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 \| object	Yes	Array of row objects, or a single row object. Each object’s keys must match data extension field names.

Return value

number — the count of rows that were added.

Differs from official Salesforce docs

Runtime-verified on a CloudPage: Add() returns a number (the count of rows added), not the string "OK". It also accepts a single row object in addition to an array of objects.

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);

Show test script

<script runat="server">
Platform.Load("core", "1.1.5");

/*
 * Chapter: <DataExtensionInstance>.Rows.Add(rowData)
 *
 * CloudPage GET context. Proves:
 *   1. Rows.Add is a function.
 *   2. DEV Add(array) returns a number (count of rows added), not "OK".
 *   3. DEV Add also accepts a single row object (docs: array only).
 *   4. Added rows are readable via Rows.Retrieve / Rows.Lookup.
 *   5. Cleanup Remove leaves zero matching rows (ghost-record check).
 *
 * EXPECTED OUTPUT: every line starts with PASS.
 */

function assert(id, actual, expected) {
    Platform.Response.Write((actual === expected ? "PASS " : "FAIL ") + id + " -> [" + actual + "]\n");
}
function outcomeOf(fn) {
    try { return "" + fn(); } catch (ex) { return "THREW: " + ("" + ex.message); }
}
function countDE(key) {
    return DataExtension.Retrieve({ Property: "CustomerKey", SimpleOperator: "equals", Value: key }).length;
}

var KEY = "ssjsguide-ts-der-add";
var NAME = "ssjs-guide-ts-der-add";

if (countDE(KEY) > 0) { DataExtension.Init(KEY).Remove(); }
assert("precondition: no probe data extension exists", "" + countDE(KEY), "0");
DataExtension.Add({
    CustomerKey: KEY,
    Name: NAME,
    Fields: [
        { Name: "SubKey", FieldType: "Text", IsPrimaryKey: true, MaxLength: 50, IsRequired: true },
        { Name: "FirstName", FieldType: "Text", MaxLength: 50 },
        { Name: "LastName", FieldType: "Text", MaxLength: 50 }
    ],
    SendableInfo: { Field: { Name: "SubKey", FieldType: "Text" }, RelatesOn: "Subscriber Key" }
});
assert("fixture: the probe data extension was created", "" + countDE(KEY), "1");

var de = DataExtension.Init(KEY);
assert("typeof instance.Rows.Add is function", typeof de.Rows.Add, "function");

var nArr = de.Rows.Add([
    { SubKey: "jdoe", FirstName: "John", LastName: "Doe" },
    { SubKey: "aruiz", FirstName: "Angel", LastName: "Ruiz" }
]);
assert("DEV Add(array) returns a number (docs: \"OK\")", typeof nArr, "number");
assert("DEV Add(array of 2) returns 2", "" + nArr, "2");
assert("Add(array) result is not the string OK", nArr === "OK" ? "true" : "false", "false");

var nOne = de.Rows.Add({ SubKey: "solo", FirstName: "Solo", LastName: "Row" });
assert("DEV Add(single object) returns a number (docs: array only)", typeof nOne, "number");
assert("DEV Add(single object) returns 1", "" + nOne, "1");

assert("Retrieve sees all three rows", "" + de.Rows.Retrieve().length, "3");
assert("Lookup finds the single-object row", outcomeOf(function () {
    var r = de.Rows.Lookup(["SubKey"], ["solo"]);
    return r && r.length === 1 ? r[0].FirstName : "missing";
}), "Solo");

assert("cleanup: the probe data extension is removed", outcomeOf(function () { return DataExtension.Init(KEY).Remove(); }), "OK");
assert("cleanup: no probe data extension is left behind", "" + countDE(KEY), "0");
</script>

<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. Prefer External Key on CloudPages (Name binding is defective when Name and CustomerKey differ).

Syntax

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

Parameters

Name	Type	Required	Description
`searchFieldNames`	string[]	Yes	Array of column names to match against
`searchValues`	array	Yes	Array of values to match (one per column, in order). Heterogeneous simple values; Number columns accept a number or a numeric string.
`limit`	number \| string	No	Maximum number of rows to return
`orderByFieldName`	string	No	Field to order results by

Return value

object[] | null — rows with typed values for Number/Decimal/Boolean. Date columns are the exception: they come back as an ISO-8601 string (e.g. "2024-01-15T00:00:00.000"), not a Date object — the same behaviour as Platform.Function.LookupRows. On no match, returns null (not an empty array).

Differs from official Salesforce docs

Runtime-verified on a CloudPage: Lookup() returns typed Number/Decimal/Boolean values (unlike Retrieve, which returns every field as a string). Date columns come back as an ISO-8601 string (e.g. "2024-01-15T00:00:00.000"), NOT a Date object — matching Platform.Function.LookupRows. On no match it returns null. The result is a host array (instanceof Array is false, but .length and index access work).

Examples

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

Show test script

<script runat="server">
Platform.Load("core", "1.1.5");

/*
 * Chapter: <DataExtensionInstance>.Rows.Lookup(searchFieldNames, searchValues[, limit[, orderByFieldName]])
 *
 * CloudPage GET context. Proves:
 *   1. Rows.Lookup is a function.
 *   2. Match returns a host array (instanceof Array false) with typed
 *      Number / Decimal / Boolean values.
 *   3. DEV Date columns are ISO-8601 strings (e.g. "2024-01-15T00:00:00.000"),
 *      NOT Date objects — same as Platform.Function.LookupRows (page used
 *      to claim native Date; official "typed values" wording is incomplete).
 *   4. On no match returns null (not an empty array).
 *   5. Optional limit and orderByFieldName work; limit also accepts a
 *      numeric string (type-acceptance).
 *   6. ARRAY-SHAPE: searchFieldNames accepts string[] (column-name list);
 *      a number[] element list is Rejected (no usable match).
 *   7. ARRAY-SHAPE: searchValues guide type is array (heterogeneous simple
 *      values); runtime accepts string[] (Text + numeric string for Number
 *      columns) and number[] for Number columns.
 *
 * EXPECTED OUTPUT: every line starts with PASS.
 */

function assert(id, actual, expected) {
    Platform.Response.Write((actual === expected ? "PASS " : "FAIL ") + id + " -> [" + actual + "]\n");
}
function outcomeOf(fn) {
    try { return "" + fn(); } catch (ex) { return "THREW: " + ("" + ex.message); }
}
function countDE(key) {
    return DataExtension.Retrieve({ Property: "CustomerKey", SimpleOperator: "equals", Value: key }).length;
}

var KEY = "ssjsguide-ts-der-lk";
var NAME = "ssjs-guide-ts-der-lk";

if (countDE(KEY) > 0) { DataExtension.Init(KEY).Remove(); }
assert("precondition: no probe data extension exists", "" + countDE(KEY), "0");
DataExtension.Add({
    CustomerKey: KEY,
    Name: NAME,
    Fields: [
        { Name: "SubKey", FieldType: "Text", IsPrimaryKey: true, MaxLength: 50, IsRequired: true },
        { Name: "LastName", FieldType: "Text", MaxLength: 50 },
        { Name: "Age", FieldType: "Number" },
        { Name: "Score", FieldType: "Decimal", MaxLength: 18, Scale: 2 },
        { Name: "Active", FieldType: "Boolean" },
        { Name: "Born", FieldType: "Date" }
    ],
    SendableInfo: { Field: { Name: "SubKey", FieldType: "Text" }, RelatesOn: "Subscriber Key" }
});
assert("fixture: the probe data extension was created", "" + countDE(KEY), "1");

var de = DataExtension.Init(KEY);
assert("typeof instance.Rows.Lookup is function", typeof de.Rows.Lookup, "function");

de.Rows.Add([
    { SubKey: "a", LastName: "Alpha", Age: 25, Score: 3.14, Active: true, Born: "2024-01-15T00:00:00.000" },
    { SubKey: "b", LastName: "Beta", Age: 25, Score: 1.5, Active: true, Born: "2024-06-01T12:00:00.000" },
    { SubKey: "c", LastName: "Gamma", Age: 40, Score: 9.9, Active: false, Born: "2023-12-31T00:00:00.000" }
]);

var rows = de.Rows.Lookup(["Age"], [25], 2, "LastName");
assert("Lookup match is not null", rows === null ? "true" : "false", "false");
assert("Lookup reports as [object Array]", Object.prototype.toString.call(rows), "[object Array]");
assert("Lookup length respects limit 2", "" + rows.length, "2");
assert("DEV instanceof Array is false (host array)", rows instanceof Array ? "true" : "false", "false");
assert("orderBy LastName puts Alpha first", "" + rows[0].LastName, "Alpha");
assert("Number column typeof number", typeof rows[0].Age, "number");
assert("Number value", "" + rows[0].Age, "25");
assert("Decimal column typeof number", typeof rows[0].Score, "number");
assert("Boolean column typeof boolean", typeof rows[0].Active, "boolean");
assert("Boolean === true", rows[0].Active === true ? "true" : "false", "true");

/* 3. Date lead — ISO-8601 string, not Date. */
assert("DEV Date column typeof string (page/docs implied Date object)", typeof rows[0].Born, "string");
assert("DEV Date is ISO-8601 (same as Platform.Function.LookupRows)", "" + rows[0].Born, "2024-01-15T00:00:00.000");
assert("DEV Date instanceof Date is false", rows[0].Born instanceof Date ? "true" : "false", "false");
var pf = Platform.Function.LookupRows(NAME, "SubKey", "a");
assert("control: Platform.Function.LookupRows Date is the same ISO string", "" + pf[0].Born, "2024-01-15T00:00:00.000");

/* 4. No match -> null. */
var miss = de.Rows.Lookup(["SubKey"], ["no-such-row-zzz"]);
assert("DEV no-match Lookup === null (docs/Retrieve: empty array)", miss === null ? "true" : "false", "true");

/* 5. limit type-acceptance. */
assert("limit numeric string \"1\" is accepted", outcomeOf(function () {
    return de.Rows.Lookup(["Active"], [true], "1", "LastName").length;
}), "1");

/* 6 + 7. Array-shape type-acceptance for searchFieldNames / searchValues. */
assert("TA searchFieldNames string[] finds by SubKey", outcomeOf(function () {
    var r = de.Rows.Lookup(["SubKey"], ["c"]);
    return r && r.length === 1 ? r[0].LastName : "missing";
}), "Gamma");
assert("TA searchFieldNames number[] is Rejected (throws Invalid Field)", outcomeOf(function () {
    de.Rows.Lookup([25], [25]);
    return "returned";
}).indexOf("Invalid Field") >= 0 ? "true" : "false", "true");
assert("TA searchValues string[] matches Text column", outcomeOf(function () {
    return de.Rows.Lookup(["LastName"], ["Beta"]).length;
}), "1");
assert("TA searchValues number[] matches Number column", outcomeOf(function () {
    return de.Rows.Lookup(["Age"], [40]).length;
}), "1");
assert("TA searchValues numeric-string[] matches Number column", outcomeOf(function () {
    return de.Rows.Lookup(["Age"], ["40"]).length;
}), "1");

assert("cleanup: the probe data extension is removed", outcomeOf(function () { return DataExtension.Init(KEY).Remove(); }), "OK");
assert("cleanup: no probe data extension is left behind", "" + countDE(KEY), "0");
</script>

<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`	string[]	Yes	Array of column names to match against
`whereValues`	array	Yes	Array of values to match (one per column, in order). Heterogeneous simple values; Number columns accept a number or a numeric string.

Return value

number — the count of rows that were updated. Returns 0 (does not throw) when no row matches.

Differs from official Salesforce docs

Runtime-verified on a CloudPage: Update() returns a number (the count of rows updated), not the string "OK". When no row matches the WHERE clause it returns 0 and does NOT throw.

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"]);

Show test script

<script runat="server">
Platform.Load("core", "1.1.5");

/*
 * Chapter: <DataExtensionInstance>.Rows.Update(rowData, whereFieldNames, whereValues)
 *
 * CloudPage GET context. Proves:
 *   1. Rows.Update is a function.
 *   2. DEV Update returns a number (count of rows updated), not "OK".
 *   3. A matching Update changes the row (proven via a varied Retrieve
 *      filter — identical Lookup queries can be request-cached).
 *   4. DEV no-match Update returns 0 and does not throw.
 *   5. ARRAY-SHAPE: whereFieldNames accepts string[] (column-name list).
 *   6. ARRAY-SHAPE: whereValues guide type is array (heterogeneous simple
 *      values); runtime accepts string[] (Text + numeric string for Number)
 *      and number[] for Number columns.
 *
 * EXPECTED OUTPUT: every line starts with PASS.
 */

function assert(id, actual, expected) {
    Platform.Response.Write((actual === expected ? "PASS " : "FAIL ") + id + " -> [" + actual + "]\n");
}
function outcomeOf(fn) {
    try { return "" + fn(); } catch (ex) { return "THREW: " + ("" + ex.message); }
}
function countDE(key) {
    return DataExtension.Retrieve({ Property: "CustomerKey", SimpleOperator: "equals", Value: key }).length;
}

var KEY = "ssjsguide-ts-der-upd";
var NAME = "ssjs-guide-ts-der-upd";

if (countDE(KEY) > 0) { DataExtension.Init(KEY).Remove(); }
assert("precondition: no probe data extension exists", "" + countDE(KEY), "0");
DataExtension.Add({
    CustomerKey: KEY,
    Name: NAME,
    Fields: [
        { Name: "SubKey", FieldType: "Text", IsPrimaryKey: true, MaxLength: 50, IsRequired: true },
        { Name: "MemberId", FieldType: "Number" },
        { Name: "Country", FieldType: "Text", MaxLength: 10 },
        { Name: "StateProvince", FieldType: "Text", MaxLength: 50 },
        { Name: "PreferredActivity", FieldType: "Text", MaxLength: 50 }
    ],
    SendableInfo: { Field: { Name: "SubKey", FieldType: "Text" }, RelatesOn: "Subscriber Key" }
});
assert("fixture: the probe data extension was created", "" + countDE(KEY), "1");

var de = DataExtension.Init(KEY);
assert("typeof instance.Rows.Update is function", typeof de.Rows.Update, "function");

de.Rows.Add({ SubKey: "m1", MemberId: 9868600, Country: "CA", StateProvince: "ON", PreferredActivity: "Hiking" });

var n = de.Rows.Update(
    { StateProvince: "QC", PreferredActivity: "Sailing" },
    ["MemberId", "Country"],
    [9868600, "CA"]
);
assert("DEV Update returns a number (docs: \"OK\")", typeof n, "number");
assert("DEV Update(match) returns 1", "" + n, "1");
assert("Update result is not the string OK", n === "OK" ? "true" : "false", "false");

/* Prove the write with a varied filter (avoid request-scoped Lookup cache). */
var proven = de.Rows.Retrieve({ Property: "StateProvince", SimpleOperator: "equals", Value: "QC" });
assert("varied Retrieve sees the updated StateProvince", "" + proven.length, "1");
assert("PreferredActivity was updated", "" + proven[0].PreferredActivity, "Sailing");

var zero = de.Rows.Update({ StateProvince: "XX" }, ["MemberId", "Country"], [111, "ZZ"]);
assert("DEV Update(no match) returns 0 (docs imply throw)", "" + zero, "0");
assert("DEV Update(no match) does not throw", outcomeOf(function () {
    de.Rows.Update({ StateProvince: "YY" }, ["MemberId"], [999999]);
    return "returned";
}), "returned");

assert("TA whereFieldNames string[] + whereValues number[] updates", outcomeOf(function () {
    return de.Rows.Update({ PreferredActivity: "Skiing" }, ["MemberId"], [9868600]);
}), "1");
var ski = de.Rows.Retrieve({ Property: "PreferredActivity", SimpleOperator: "equals", Value: "Skiing" });
assert("TA number[] WHERE update landed", "" + ski.length, "1");

assert("TA whereValues numeric-string[] matches Number column", outcomeOf(function () {
    return de.Rows.Update({ PreferredActivity: "Cycling" }, ["MemberId", "Country"], ["9868600", "CA"]);
}), "1");
var cyc = de.Rows.Retrieve({ Property: "PreferredActivity", SimpleOperator: "equals", Value: "Cycling" });
assert("TA numeric-string WHERE update landed", "" + cyc.length, "1");

assert("TA whereValues string[] matches Text column", outcomeOf(function () {
    return de.Rows.Update({ PreferredActivity: "Rowing" }, ["Country"], ["CA"]);
}), "1");
var row = de.Rows.Retrieve({ Property: "PreferredActivity", SimpleOperator: "equals", Value: "Rowing" });
assert("TA string[] Text WHERE update landed", "" + row.length, "1");

assert("cleanup: the probe data extension is removed", outcomeOf(function () { return DataExtension.Init(KEY).Remove(); }), "OK");
assert("cleanup: no probe data extension is left behind", "" + countDE(KEY), "0");
</script>

<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`	string[]	Yes	Array of column names to match against
`columnValues`	array	Yes	Array of values to match (one per column, in order). Heterogeneous simple values; Number columns accept a number or a numeric string.

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"]);

Show test script

<script runat="server">
Platform.Load("core", "1.1.5");

/*
 * Chapter: <DataExtensionInstance>.Rows.Remove(columnNames, columnValues)
 *
 * CloudPage GET context. Proves:
 *   1. Rows.Remove is a function.
 *   2. Remove returns a number — the count of deleted rows.
 *   3. Matching rows are gone afterwards (Lookup null + Retrieve length 0).
 *   4. Ghost-record rule: after Remove, a re-count via a distinct filter
 *      confirms zero survivors even if a prior write appeared to fail.
 *   5. ARRAY-SHAPE: columnNames accepts string[] (column-name list).
 *   6. ARRAY-SHAPE: columnValues guide type is array (heterogeneous simple
 *      values); runtime accepts string[] (Text + numeric string for Number)
 *      and number[] for Number columns.
 *
 * NON-ASSERTABLE: timeout on large batched deletes.
 *
 * EXPECTED OUTPUT: every line starts with PASS.
 */

function assert(id, actual, expected) {
    Platform.Response.Write((actual === expected ? "PASS " : "FAIL ") + id + " -> [" + actual + "]\n");
}
function outcomeOf(fn) {
    try { return "" + fn(); } catch (ex) { return "THREW: " + ("" + ex.message); }
}
function countDE(key) {
    return DataExtension.Retrieve({ Property: "CustomerKey", SimpleOperator: "equals", Value: key }).length;
}

var KEY = "ssjsguide-ts-der-rem";
var NAME = "ssjs-guide-ts-der-rem";

if (countDE(KEY) > 0) { DataExtension.Init(KEY).Remove(); }
assert("precondition: no probe data extension exists", "" + countDE(KEY), "0");
DataExtension.Add({
    CustomerKey: KEY,
    Name: NAME,
    Fields: [
        { Name: "SubKey", FieldType: "Text", IsPrimaryKey: true, MaxLength: 50, IsRequired: true },
        { Name: "Area", FieldType: "Text", MaxLength: 50 },
        { Name: "MemberId", FieldType: "Number" }
    ],
    SendableInfo: { Field: { Name: "SubKey", FieldType: "Text" }, RelatesOn: "Subscriber Key" }
});
assert("fixture: the probe data extension was created", "" + countDE(KEY), "1");

var de = DataExtension.Init(KEY);
assert("typeof instance.Rows.Remove is function", typeof de.Rows.Remove, "function");

de.Rows.Add([
    { SubKey: "k1", Area: "Kensington", MemberId: 101 },
    { SubKey: "k2", Area: "Kensington", MemberId: 102 },
    { SubKey: "k3", Area: "Chelsea", MemberId: 103 },
    { SubKey: "k4", Area: "Soho", MemberId: 104 },
    { SubKey: "k5", Area: "Soho", MemberId: 105 }
]);
assert("precondition: five rows present", "" + de.Rows.Retrieve().length, "5");

/* 5 + Text string[] path. */
var n = de.Rows.Remove(["Area"], ["Kensington"]);
assert("Remove returns a number", typeof n, "number");
assert("TA columnNames string[] + columnValues string[] Remove returns 2", "" + n, "2");

var gone = de.Rows.Lookup(["Area"], ["Kensington"]);
assert("Lookup confirms Kensington rows are gone (null)", gone === null ? "true" : "false", "true");
/* Distinct Retrieve filter avoids request-scoped cache on the same shape. */
var left = de.Rows.Retrieve({ Property: "Area", SimpleOperator: "equals", Value: "Chelsea" });
assert("Chelsea row remains", "" + left.length, "1");
assert("ghost-record re-count: Retrieve SubKey k1 length 0", outcomeOf(function () {
    return de.Rows.Retrieve({ Property: "SubKey", SimpleOperator: "equals", Value: "k1" }).length;
}), "0");
assert("ghost-record re-count: Retrieve SubKey k2 length 0", outcomeOf(function () {
    return de.Rows.Retrieve({ Property: "SubKey", SimpleOperator: "equals", Value: "k2" }).length;
}), "0");

/* 6. Number-column array-shape for columnValues. */
assert("TA columnValues number[] removes by MemberId", outcomeOf(function () {
    return de.Rows.Remove(["MemberId"], [103]);
}), "1");
assert("TA number[] Remove: Chelsea gone", outcomeOf(function () {
    return de.Rows.Retrieve({ Property: "Area", SimpleOperator: "equals", Value: "Chelsea" }).length;
}), "0");
assert("TA columnValues numeric-string[] removes by MemberId", outcomeOf(function () {
    return de.Rows.Remove(["MemberId"], ["104"]);
}), "1");
assert("TA numeric-string Remove: SubKey k4 gone", outcomeOf(function () {
    return de.Rows.Retrieve({ Property: "SubKey", SimpleOperator: "equals", Value: "k4" }).length;
}), "0");
assert("Soho k5 still present before final cleanup", outcomeOf(function () {
    return de.Rows.Retrieve({ Property: "SubKey", SimpleOperator: "equals", Value: "k5" }).length;
}), "1");

assert("cleanup: the probe data extension is removed", outcomeOf(function () { return DataExtension.Init(KEY).Remove(); }), "OK");
assert("cleanup: no probe data extension is left behind", "" + countDE(KEY), "0");
</script>

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]
    );
}

DataExtension.Rows

Methods

<DataExtensionInstance>.Rows.Retrieve

Syntax

Parameters

Filter Object

Return value

Field type → returned JavaScript type (runtime-verified)

Examples

<DataExtensionInstance>.Rows.Add

Syntax

Parameters

Return value

Examples

<DataExtensionInstance>.Rows.Lookup

Syntax

Parameters

Return value

Examples

<DataExtensionInstance>.Rows.Update

Syntax

Parameters

Return value

Examples

<DataExtensionInstance>.Rows.Remove

Syntax

Parameters

Return value

Examples

Complete CRUD Pattern

See Also

See Also