function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
Scott KeithScott Keith 

How can I add field data to data extension retrieve?

In the Marketing Cloud I want to use a SSJS script activity to retrieve records from a Data Extension, manipulate them, and write back to a new DE. I've tried using DATAEXTENSION.Rows.Retrieve(), but am limited to pulling back only 2,500 records. Instead, I am trying to use API calls from inside the platform to take advantage of (results[0] == "MoreDataAvailable"). I can now get the full recordset back; which is verified by OBJECT.Length. How can I access the individual field values? I'm trying to get STRINGIFY() or ROW() to help, but failing so far. Below is script I put into a landing page to work through the retrieve and the results displayed on the screen. "OrderheaderID" is a field name from the source data extension.

CODE:
<script runat=server>
          Platform.Load("Core","1.1.1");
          var rr = Platform.Function.CreateObject("RetrieveRequest");
          Platform.Function.SetObjectProperty(rr, "ObjectType", "DataExtensionObject[test_OrderLineItem]");
          Platform.Function.AddObjectArrayItem(rr, "Properties", "OrderheaderID"); // OrderheaderID is a field name

         do
         {
            var results = [0,0];
            var rows = Platform.Function.InvokeRetrieve(rr, results);
            if(rows != null) {
              Write("<br>results0: " + results[0]); 
              Write("<br>results1: " + results[1]);  
              Write("<br>rows0: " + rows[0]); //returning null
              Write("<br>rows1: " + rows[1]); 
              Write("<br>rowsLength: " + rows.length); 
              Write("<br>rows.orderheaderId: " + rows.OrderheaderID);  //returning undefined
              Write("<br>rows[0].orderheaderId: " + rows[0].OrderheaderID);
              Write("<br>rows[1].orderheaderId: " + rows[1].OrderheaderID);
            }
             rr.ContinueRequest = results[1];
          } while (results[0] == "MoreDataAvailable")
  </script>

RESULTS: 
results0: MoreDataAvailable
results1: 9b2c8d3f-4a63-4573-b122-ecfe29af41e0
rows0: 
rows1: 
rowsLength: 2500
rows.orderheaderId: undefined
rows[0].orderheaderId: undefined
rows[1].orderheaderId: undefined

results0: OK
results1: 9b2c8d3f-4a63-4573-b122-ecfe29af41e0
rows0: 
rows1: 
rowsLength: 1615
rows.orderheaderId: undefined
rows[0].orderheaderId: undefined
rows[1].orderheaderId: undefined
 
Scott KeithScott Keith
I solved this one. The stringified data extension object looks like this in JSON: 
{"Name":null,"Keys":null,"Type":"DataExtensionObject","Properties": [{"Name":"OrderheaderID", "Value":"119218067"}], "Client":null,"PartnerKey":null,"PartnerProperties":null,"CreatedDate":"0001-01-01T00:00:00.000","CreatedDateSpecified":false,"ModifiedDate":null,"ModifiedDateSpecified":false,"ID":0,"IDSpecified":false,"ObjectID":null,"CustomerKey":null,"Owner":null,"CorrelationID":null,"ObjectState":null,"IsPlatformObject":false,"IsPlatformObjectSpecified":false}

I did this by iterating the object in my ROWS variable and stringify each [i] record. The "Properties" array object item I added to the retrieve request is above in bold.
var oneRecord = Stringify(rows[i]);

To access the properties I had to parseJSON() the record
var strRecord = Platform.Function.ParseJSON(oneRecord);

and use dot notation with the NAME and VALUE attributes. 
var myOrderHeaderID = strRecord.Properties[0].Value;

Now I can access the field values stored in myOrderHeaderID on each loop. Additional fields (AddObjectArrayItem) would be accessed by increasing the Properties[X] index. 
var myFoo = strRecord.Properties[1].Value;
var myBar= strRecord.Properties[2].Value;