0

I know this can be done via SOAP or an SDK, but I have a specific need to have this done in Marketing Cloud SSJS interacting with Web API (on a landing page, not in an email).

The aim is to create a retrieve object that pulls only the column names of a data extension and none of the values. The result should be a JSON file that only contains the column name - eg ["Email","FirstName","LastName"].

I constantly receive an error or a blank payload on each RetrieveRequest I put in. I feel like it is something small or silly that I am doing wrong.

See below for SSJS snippet I am using:

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

var DE = dataextensionIdentifier

var retrieveObject = Platform.Function.CreateObject("RetrieveRequest")

//Set the request type
Platform.Function.SetObjectProperty(retrieveObject,"ObjectType", "DataExtensionField");

//Set the columns
     Platform.Function.AddObjectArrayItem(retrieveObject,"Properties", "DataExtensionField.CustomerKey");
     Platform.Function.AddObjectArrayItem(retrieveObject, "Properties", "DataExtensionField.Name");

//Create a filter
var RetrieveFilter = Platform.Function.CreateObject("SimpleFilterPart");

//By CustomerKey
    Platform.Function.SetObjectProperty(RetrieveFilter, "Property", "CustomerKey")
    Platform.Function.SetObjectProperty(RetrieveFilter, "SimpleOperator","Equals")
    Platform.Function.AddObjectArrayItem(RetrieveFilter, "Value", DE )

//add filter to the retrieve
Platform.Function.SetObjectProperty(retrieveObject, "Filter", RetrieveFilter);

//Do the retrieve
var StatusAndRequestID = [0,0];

var Result = Platform.Function.InvokeRetrieve(retrieveObject,StatusAndRequestID);

Below is the final SSJS that worked:

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

var DE = dataextensionIdentifier

var retrieveObject = Platform.Function.CreateObject("RetrieveRequest");

//Set the request type
Platform.Function.SetObjectProperty(retrieveObject,"ObjectType", "DataExtensionField");

//Set the columns
     Platform.Function.AddObjectArrayItem(retrieveObject,"Properties", "DataExtension.CustomerKey");
     Platform.Function.AddObjectArrayItem(retrieveObject, "Properties", "DataExtensionField.Name");

//Create a filter
var RetrieveFilter = Platform.Function.CreateObject("SimpleFilterPart");

//By CustomerKey
    Platform.Function.SetObjectProperty(RetrieveFilter, "Property", "DataExtension.CustomerKey")
    Platform.Function.SetObjectProperty(RetrieveFilter, "SimpleOperator","Equals")
    Platform.Function.AddObjectArrayItem(RetrieveFilter, "Value", DE )

//add filter to the retrieve
Platform.Function.SetObjectProperty(retrieveObject, "Filter", RetrieveFilter);

//Do the retrieve
var StatusAndRequestID = [0,0];

var Result = Stringify(Platform.Function.InvokeRetrieve(retrieveObject,StatusAndRequestID));

//Check Status
var status = StatusAndRequestID[0];
var requestID = StatusAndRequestID[1];
1

I believe you have to filter on the DataExtension.CustomerKey Property, not just CustomerKey.

Here's a sample SOAP envelope that works:

<?xml version="1.0" encoding="UTF-8"?>
<s:Envelope xmlns:s="http://www.w3.org/2003/05/soap-envelope" xmlns:a="http://schemas.xmlsoap.org/ws/2004/08/addressing" xmlns:u="http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-wssecurity-utility-1.0.xsd">
  <s:Header>
   <!-- SNIPPED -->
  </s:Header>
  <s:Body xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
    <RetrieveRequestMsg xmlns="http://exacttarget.com/wsdl/partnerAPI">
      <RetrieveRequest>
        <ClientIDs>
          <ID><!-- MID GOES HERE --></ID>
        </ClientIDs>
        <ObjectType>DataExtensionField</ObjectType>
        <Properties>Client.ID</Properties>
        <Properties>CreatedDate</Properties>
        <Properties>CustomerKey</Properties>
        <Properties>DataExtension.CustomerKey</Properties>
        <Properties>DefaultValue</Properties>
        <Properties>FieldType</Properties>
        <Properties>IsPrimaryKey</Properties>
        <Properties>IsRequired</Properties>
        <Properties>MaxLength</Properties>
        <Properties>ModifiedDate</Properties>
        <Properties>Name</Properties>
        <Properties>ObjectID</Properties>
        <Properties>Ordinal</Properties>
        <Properties>Scale</Properties>
        <Filter xsi:type="SimpleFilterPart">
          <Property>DataExtension.CustomerKey</Property>
          <SimpleOperator>equals</SimpleOperator>
          <Value><!-- CUSTOMER KEY GOES HERE --></Value>
        </Filter>
        <QueryAllAccounts>true</QueryAllAccounts>
        <Retrieves />
      </RetrieveRequest>
    </RetrieveRequestMsg>
  </s:Body>
</s:Envelope>
  • That did it. It is now working, but it provides the full array. I should be able to just run some for loops or something through it and filter what I need. Thanks Adam! – Gortonington Jan 12 '16 at 16:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.