Retrieve pagination loop in SSJS

I wrote teh following script to get data from a data extension and then process it. I than noticed that it only gets 2500 records. I read somewhere that I need to add a pagination loop, but I couldn’t find any examples how this should look.As I am still learning JavaScript, could I please ask for your assistance in adding this pagination loop.

Thank you

<script runat="server"> 

var DE = DataExtension.Init("894D9624-9313-420D-3400-74655397D00E"); 
var data = DE.Rows.Retrieve();  

//Write(Stringify(data));   

if(data.length >= 1) { 
      for (i = 0; i < data.length; i++) { 

        ...do stuff...

    } 
} 
else { 
Write("no data to retrieve"); 
}  

</script>

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

To my knowledge the built-in Core library functions cannot be paginated, but the API call (which is essentially what the function is anyway) can. And even better, you can transform it into WSProxy making it even easier AND faster.

Below is an example of pagination utilizing WSProxy: (I updated it from the sample on the page to instead aim at a Data Extension.)

var prox = new Script.Util.WSProxy(),
    objectType = "DataExtensionObject[" + deCustKey + "]",
    cols = ["FirstName","LastName",etc...],
    moreData = true,
    reqID = null,
    numItems = 0;

while(moreData) {
    moreData = false;
    var data = reqID == null ?
           prox.retrieve(objectType, cols) :
           prox.getNextBatch(objectType, reqID);

    if(data != null) {
        moreData = data.HasMoreRows;
        reqID = data.RequestID;
        if(data && data.Results) {
            for(var i=0; i< data.Results.length; i++) {
                Platform.Response.Write(data.Results[i].Name);
                numItems++;
            }
        }
    }
}
Platform.Response.Write("<br />" + numItems + " total " + objectType);

One thing different from the Core library function is that you will need to list all of the columns you want retrieved from the call inside the cols array or they will be returned as empty/null.

Another option, if you can utilize AMPscript instead of SSJS, is to use a LookupOrderedRows function using DataExtensionRowCount() as the ‘max records returned’ value.

E.g. SET @Rows = LookupOrderedRows(@YourDE, DataExtensionRowCount(@YourDE),"SortableAttribute asc", "FlagField", "Value")

Now this would require some sort of flag or lookup value to work, which could be a dealbreaker. I usually solve this by having an extra field in my DEs named ‘LU’ which has a default value of 1. This way if I ever need to pull every record, I can still use the LookupOrderedRows function, filtering on ..."LU", 1... and it will return everything.


All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

0 0 votes
Article Rating
Subscribe
Notify of
guest

0 Comments
Inline Feedbacks
View all comments
0
Would love your thoughts, please comment.x
()
x