1

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 Answer 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>
1
  • 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! Jan 12, 2016 at 16:27

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .