0

Basically I am trying to write some SSJS that can clone the structure of DEs which is necessary in my role as a consultant and when I'm attempting to pull data out of a synchronized DE.

I've got the SSJS below. I'm currently trying to debug it in a landing page.

I can get all the way to where I set deObj - the JSON's properly formatted that's returned in debug - but when I try to actually add the data extension, I get the message "Error adding data extension."

Where am I going wrong?

Edit: I've made a change to the for loop to account for missing IsRequired per Gortonington's comment. Still getting the "Error adding data extension" issue though.

  <script runat="server">
Platform.Load("Core", "1.1.1");    
    try {      
      var Contact_Salesforce = DataExtension.Init("Contact_Salesforce");
      var jsonArray = Contact_Salesforce.Fields.Retrieve();
      for(var i = 0; i < jsonArray.length; i++) {
          delete jsonArray[i]['ObjectID'];
          jsonArray[i]['IsRequired'] = false;
          if(jsonArray[i]['IsPrimaryKey'] == true)
              jsonArray[i]['IsRequired'] = true;
      };
      var deObj = {"CustomerKey" : "SyncedContactsSalesforce2","Name" : "SyncedContactsSalesforce2","Fields" : jsonArray};
                   // Write(Stringify(deObj));
      var newDE = DataExtension.Add(deObj);
    } catch (ex) {
        Write("An error has occurred: " + Stringify(ex));
        Write("   deObj: " + Stringify(deObj));
        Variable.SetValue("@Result", Stringify(ex));
    }
</script>

Output looks like this:

{"CustomerKey":"SyncedContactsSalesforce2","Name":"SyncedContactsSalesforce2","Fields":
[{"Name":"_ContactKey","IsRequired":false,"FieldType":"Text","IsPrimaryKey":false,"MaxLength":254,"Ordinal":1,"DefaultValue":""}
...
  • I've been doing a bit of testing, and I can replicate your issue. However, if I jsonArray.pop() until there's only 4 fields left, the DataExtension.Add() method works. Does that work on your side too? – Christopher Carswell Jul 10 '18 at 5:08
  • I believe there are some fields that are required in DataExtensionAdd that are not provided inside of the retrieve, for instance, I know that you need "IsRequired" : true along with "IsPrimaryKey" : true when adding - but the retrieve only returns the IsPrimaryKey field. – Gortonington Jul 10 '18 at 12:15
  • @Gortonington good catch, I made an update to the code to account for it and write IsRequired = True when IsPrimaryKey = True. However, I'm still getting the error on creating the DE. – Sam Jul 10 '18 at 13:35
  • unfortunately I do not know all the fields that are required in the combos. I attempted the same thing in the past and just wound up going with API calls instead - which is much less efficient in both code and processing time, but a lot more customizable. – Gortonington Jul 10 '18 at 14:05
2

So I've figured it out.

The synced DEs for Lead and Contact have a field called "_ContactKey". MC doesn't like it when a user attempts to add a field with an underscore at the beginning.

I also noticed that Fields.Retrieve doesn't pull back the right info for decimal fields (they get created as "18,undefined"), so for right now I am just converting them to text fields. If anyone has better advice on how to handle that let me know.

Also, I'm a JS newbie so there's probably more efficient ways to write this code, but this worked for me.

Final code for landing page:

%%[ 
    VAR @Result
]%%
<script runat="server">
Platform.Load("Core", "1.1.1");    
    try {      
      var DEtoCopy = DataExtension.Init("Contact_Salesforce");
      var jsonArray = DEtoCopy.Fields.Retrieve();

      for(var i = 0; i < jsonArray.length; i++) {
          delete jsonArray[i]['ObjectID'];
          jsonArray[i]['IsRequired'] = false;
          if(jsonArray[i]['IsPrimaryKey'] == true)
              jsonArray[i]['IsRequired'] = true;
          if(jsonArray[i]['Name'] == '_ContactKey')
              jsonArray[i]['Name'] = 'ContactKey';
          if(jsonArray[i]['FieldType'] == 'Decimal') {
             jsonArray[i]['FieldType'] = 'Text';
             jsonArray[i]['MaxLength'] = 50;
          };
      };
      var deObj = {"CustomerKey" : "SyncedContactsSalesforce","Name" : "SyncedContactsSalesforce","Fields" : jsonArray};
                   Write(Stringify(deObj));
      var newDE = DataExtension.Add(deObj);
    } catch (ex) {
        Write("An error has occurred: " + Stringify(ex));
        Write("   deObj: " + Stringify(deObj));
        Variable.SetValue("@Result", Stringify(ex));
    }
</script>
0

this for creating Data extension with fields

<script runat="server" language="javascript">
  Platform.Load("Core","1");
     var deObj = {
    "CustomerKey" : "SyncedContactsSalesforce2",
    "Name" : "SyncedContactsSalesforce2",
    "Fields" : [
   { "Name" : "jsonArray", "FieldType" : "Text", "MaxLength" : 50 },
    ]
    };

       var myDE = DataExtension.Add(deObj);
    Write(Stringify(myDE));
      </script>
  • This doesn't help, this is just what's in the documentation. I'm trying to clone a DE over SSJS and feed in the JSON from Fields.Retrieve. – Sam Jul 10 '18 at 13:34

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.