Custom Library: Reusable code for retrieving Data Extension complete records

Retrieve Data Extension Records

We often use a Server-Side JavaScript (SSJS) script to efficiently fetch data from Data Extensions. Typically, the maximum number of records we can retrieve in a single call is 2,500. If we need more records, we must repeatedly call the retrieve function in a loop until the process is complete.

To streamline this process, I’ve developed a reusable script in Content Builder, which act as a Custom Library. This script not only simplifies data access but also enhances the consistency and reliability of our data retrieval tasks. Whenever you need to fetch all the data from a Data Extension, just use this script. It will retrieve everything for you without any size limitations.

To create this script, follow these steps:

Go to Content Builder.
Select a Code Snippet block.
Add the code (refer to the end of the article for the complete code).
Save the snippet.

This is a one-time setup. Once created, you can reuse the code anytime. (Please try to create the snippet at the root level for easy access.)

Here is an example:

Once you have created the snippet, you need to call it in your custom SSJS code. Follow these steps to integrate the snippet:

Steps:

Step 1 - Create a function in your code block like below

//-- fuction to get data from DE

function GetRetrieveRecordsCode() {

var retriveRecordsCode = Platform.Function.ContentBlockByKey("CS_Retrieve_Records_From_DE");

return eval(retriveRecordsCode);

}

Step 2: Call the above function with required Data Extension details

We need to mention the Data Extension external key, the required columns and the filter criteria if any in a object and pass the same in the function call.

Example:

var config = {

name: <DE external Key>,

cols: <DE column names to retrieve in array format>,

filter:<filter criteria if any>,

opts: {

BatchSize: 500

},

props: {

QueryAllAccounts: false

}

};

var Data = retrieveRecordsCodeObj.RetrieveRecordsFromDataExtension(config);

The complete records will be retrieved from the specific DE and assigned to the ‘Data’ variable. This variable can then be used anywhere in the script.

The Code Snippet : Complete Code

Platform.Load("Core", "1.1.1");

var object = {

RetrieveRecordsFromDataExtension: function (config) {

var api = new Script.Util.WSProxy();

var result = [],

moreData = true,

reqID = req = null;

while (moreData) {

moreData = false;

if (reqID) config.props.ContinueRequest = reqID;

var req = api.retrieve("DataExtensionObject[" + config.name + "]", config.cols, config.filter, config.opts, config.props);

if (req) {

moreData = req.HasMoreRows;

reqID = req.RequestID;

var results = req.Results;

for (var k in results) {

var props = results[k].Properties;

var o = {};

for (var i in props) {

var key = props[i].Name;

var val = props[i].Value

if (key.indexOf("_") != 0) o[key] = val;

}

result.push(o);

}

return result;

}

We can create custom functions like this and reuse them at any time, which effectively reduces the burden of writing code repeatedly. Isn’t this somewhat similar to using a Custom Library?

Back

By Ameen Rahman

Custom Library: Reusable code for retrieving Data Extension complete records

© www.salesforceguide.me