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":""}
...
4
  • 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? Commented Jul 10, 2018 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. Commented Jul 10, 2018 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
    Commented Jul 10, 2018 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. Commented Jul 10, 2018 at 14:05

2 Answers 2

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>
1
  • 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
    Commented Jul 10, 2018 at 13:34

You must log in to answer this question.

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