1. Hi Buddy thanks for this wonderfull article . I have a small issue regarding creating Data externsion

   Can u please help, using ur code i am able to generate DE but when i need to add dynamic de fields that is not working Following is the code

   Platform.Load(“core”,”1.1.1″);
   var birthdayDE = DataExtension.Init(“80c489be-20f3-448b-b683-2b098cae20dc”);
   var fields = birthdayDE.Fields.Retrieve();
   var DE = “MyDataExtensionss”,
   FolderName = “Amit”;
   console.log(fields);
   var FolderAttributes = Folder.Retrieve({ Property: “Name”, SimpleOperator: “equals”, Value: FolderName });
   var FolderID = FolderAttributes[0].ID;

   var obj = {
   “CustomerKey” : Platform.Function.GUID(),
   “Name” : DE,
   “CategoryID”: FolderID,
   “Fields” :fields
   };
   var Status = DataExtension.Add(obj);

2. Hi Karna,

   I can already see that you are trying to use console.log which will break your code because it’s client-side Javascript.

   Please use Write(Stringify(fields)) or pass the variable to AMPscript and render it in server-side JS as follows:

   <script runat="server">
Platform.Load('core','1.1.1');
var birthdayDE = DataExtension.Init('80c489be-20f3-448b-b683-2b098cae20dc');
var fields = birthdayDE.Fields.Retrieve();
Platform.Variable.SetValue('@Fields', Stringify(fields));
<script>
<script runat="client">
console.log('%%=v(@Fields)=%%')
</script>

   Then, make sure that the Data Extension you are trying to create does not already exist with the same name.

   Tell me if it helped 🙂

3. Hi Ivan thanks for the reply but i guess my query stll left, Actually i forgot to remove the console while posting. Just ignore it as i had removed it . My query is that i am not able to form new DE using the Fields i retrieve , IF i hardcode the fields i am able to form the DE but when i assign Fields=fields, i guess this is not working out

   Platform.Load(“core”,”1.1.1″);
   var birthdayDE = DataExtension.Init(“80c489be-20f3-448b-b683-2b098cae20dc”);
   var fields = birthdayDE.Fields.Retrieve();
   var DE = “MyDataExtensionss”,
   FolderName = “Amit”;

   var FolderAttributes = Folder.Retrieve({ Property: “Name”, SimpleOperator: “equals”, Value: FolderName });
   var FolderID = FolderAttributes[0].ID;

   var obj = {
   “CustomerKey” : Platform.Function.GUID(),
   “Name” : DE,
   “CategoryID”: FolderID,
   “Fields” :fields
   };
   var Status = DataExtension.Add(obj);

4. Karna,

   When I tried to do the same I got the message: – “Primary key cannot be nullable”.

   Therefore, you need to add IsRequired = true to your primary key field.

   Please add the following:

   for(var i = 0; i < fields.length; i++) {
       if(fields[i]['IsPrimaryKey'] == true ) fields[i]['IsRequired'] = true;
   }
   

   Let me know if it worked.

5. You could also try this method, it gives better error handling:

   Platform.Load("core", "1.1");
   
   var prox = new Script.Util.WSProxy();
   var birthdayDE = DataExtension.Init('80c489be-20f3-448b-b683-2b098cae20dc');
   var fields = birthdayDE.Fields.Retrieve();
   
   for(var i = 0; i < fields.length; i++) {
       if(fields[i]['IsPrimaryKey'] == true ) fields[i]['IsRequired'] = true;
   }
   
   var FolderAttributes = Folder.Retrieve({ Property: 'Name', SimpleOperator: 'equals', Value: 'Amit' });
   var FolderID = FolderAttributes[0].ID;
   
   var obj = {
       'CustomerKey' : Platform.Function.GUID(),
       'Name' : 'MyDataExtensionss',
       'CategoryID': FolderID,
       'Fields' : fields
   }
   
   try {
       var response = prox.createItem("DataExtension", obj);
       Write(response.Results[0].StatusCode + ": " + response.Results[0].StatusMessage);
   } catch (err) {
       Write(Stringify(err));
   }
   

6. HI ivan sorry to bother u but i am new to sfmc to just want to know where can we see the error part or console part . As of now i am working in automation script activity where i cant see these. Means can u please provide other way around on how to run this script

7. thanks a lot for the first method it worked!!!

8. Karna,

   In the Automation Studio you have the Activities tab. You have to run your automation and there you should see if your script errors.

   Hope this helped.

9. Hi Ivan can you please helpme in this

   I am not able to upsert data using the following code.:I am getting parameters from the URL and using them to update the DE. Now the issue is either i can make excutioncontexttype= get or post not not both together , Can anybody help me out in this. Post is used to for updating

   Country:%%=v(@Country)=%%
   DE:%%=v(@DE)=%%
   Language:%%=v(@Language)=%%

   Platform.Load(“core”,”1.1.1″);
   var DE = Variable.GetValue(“@DE”);
   var ListOfContentDE = DataExtension.Init(DE);
   var fields = ListOfContentDE.Fields.Retrieve();
   var field= [];
   var fieldvalue=[];
   for(var i = 0; i < fields.length; i++) {
   field.push(fields[i].Name);
   fieldvalue.push(Request.GetFormField(fields[i].Name));
   }
   Write("” + field + “:”);
   var rows = Platform.Function.InsertDE(“ContentDE2”,field,fieldvalue);
   Write(“Rows:”); Write(“”);
   Write(“”+ rows + “”);

10. Hi Karna. There are multiple places where your code could fail. To be able to debug more efficiently, please use the following function: try { [put your code here] } catch(err) { Write(Stringify(err)); }

11. Thanks for this Ivan. I am a MC user in one of a Business Unit. You wrote: “Please note that the Data Extension will be created at the root folder level.” How can I run this code using a Script activity and create DE in the BU of the Activity. I believe the root is the top level BU (Global) and I am user in one of the child BU. Also I don’t have access to any other console apart from the Script Activity in Automation Studio. Thanks!

12. Hi Carlito. The root folder is the Contact Builder root folder for the Data Extensions of the BU where you are running the script on.

    So if you are running the script on the parent BU to which you don’t have access, it’s going to be a problem.

    My suggestion is to get the external key of a specific folder of your BU and pass it as the CategoryId parameter to your script.

13. Thanks. I ran the script using Automation Studio Script Activity on the BU that I have access to. I ran it once from the Activity. I thought the DE would appear within the Data Extensions folder of the same BU. Unfortunatelly, there is no log console to see any feedback. I will try with your tip to pass the CategoryId of a new test folder.

14. Try to execute the script from a cloud page or an email. There you will see every error that occurs.

15. Hi Ivan sorry to poke u again . I am having a small issue while creating a new DE from the existing one. The order of fields get changed in the new DE as compared to existing de which cause time trouble. Can u help me in this so that the order in which the Fields are same in both

16. Karna,

    Each field has a hidden attribute called “Ordinal” which defines the order of fields.

    
"Fields" : [
{ "Name" : "Id", "FieldType" : "Number", "IsPrimaryKey" : true, "IsRequired" : true , "Ordinal" : 1},
{ "Name" : "MyData", "FieldType" : "Text", "MaxLength" : 50, "Ordinal" : 2 },
{ "Name" : "Active", "FieldType" : "Boolean", "DefaultValue" : true, "Ordinal" : 3 }
]

17. Hi Evan i was able to replicate the code to create DE from the existing one with the order fields as orignal, but a new issue has arrived. I am not ablt to add that record manually, only import activity is working for addding the record.

18. Karna,

    I’m not sure to understand what you want to do. To add records manually, you need to go into the data extension click on the Records tab and click on add record button.

19. Hi Ivan
    I am not able to add the record manually , it is throwing error to contact sfmc support when i am saving the record but when i use ssjs to insert record in that de it is working fine.

20. actually i have got the point frmo where the issue is arising. I have a default column having “Boolean” type and default value set to “false” in main DE from which i am creating the new one . Now if i remove this column in the DE created from the ssjs, then i am able to store the record manually ,otherwise it throws me an error.
    Following the json of m y fields

    [{“Name”:”Country”,”ObjectID”:”6927e846-2355-428e-95d0-c9a86a1a4fec”,”FieldType”:”Text”,”IsPrimaryKey”:true,”MaxLength”:50,”Ordinal”:0,”DefaultValue”:””,”StorageType”:”Plain”,”IsRequired”:true},
    {“Name”:”Locale”,”ObjectID”:”587a5f80-a379-40bc-a248-ff593cc7c4df”,”FieldType”:”Text”,”IsPrimaryKey”:true,”MaxLength”:50,”Ordinal”:1,”DefaultValue”:””,”StorageType”:”Plain”,”IsRequired”:true},
    {“Name”:”EmailName”,”ObjectID”:”0886b10d-4a00-4a43-b799-2707c1df01c7″,”FieldType”:”Text”,”IsPrimaryKey”:true,”MaxLength”:50,”Ordinal”:2,”DefaultValue”:””,”StorageType”:”Plain”,”IsRequired”:true},
    {“Name”:”Cashier”,”ObjectID”:”afe3956d-cc5b-4124-88d4-5a9a3eabefb2″,”FieldType”:”Text”,”IsPrimaryKey”:false,”MaxLength”:4000,”Ordinal”:35,”DefaultValue”:””,”StorageType”:”Plain”},
    {“Name”:”Default”,”ObjectID”:”cd33a99e-0228-46e3-bcea-02f05f526804″,”FieldType”:”Boolean”,”IsPrimaryKey”:false,”MaxLength”:0,”Ordinal”:36,”DefaultValue”:”False”,”StorageType”:”Plain”},
    {“Name”:”Reg”,”ObjectID”:”689148bb-79eb-439b-a10e-5a1ed3101345″,”FieldType”:”Text”,”IsPrimaryKey”:false,”MaxLength”:4000,”Ordinal”:45,”DefaultValue”:””,”StorageType”:”Plain”},
    {“Name”:”Trans”,”ObjectID”:”67315568-2f14-458c-a415-3ab715654890″,”FieldType”:”Text”,”IsPrimaryKey”:false,”MaxLength”:4000,”Ordinal”:46,”DefaultValue”:””,”StorageType”:”Plain”},
    {“Name”:”Date”,”ObjectID”:”a46979b0-fc04-4aba-a41d-1e961cf4412f”,”FieldType”:”Text”,”IsPrimaryKey”:false,”MaxLength”:500,”Ordinal”:47,”DefaultValue”:””,”StorageType”:”Plain”},
    {“Name”:”ReceiptExpires”,”ObjectID”:”6f2e72a8-d57d-41e5-ae27-c774314c9b8c”,”FieldType”:”Text”,”IsPrimaryKey”:false,”MaxLength”:500,”Ordinal”:48,”DefaultValue”:””,”StorageType”:”Plain”},
    {“Name”:”BodyText”,”ObjectID”:”3f61378c-b989-4136-aed0-c4735b253ae1″,”FieldType”:”Text”,”IsPrimaryKey”:false,”MaxLength”:4000,”Ordinal”:49,”DefaultValue”:””,”StorageType”:”Plain”},
    {“Name”:”Title”,”ObjectID”:”aafa8672-16cb-47a1-85c4-c04f2fa8eea5″,”FieldType”:”Text”,”IsPrimaryKey”:false,”MaxLength”:500,”Ordinal”:50,”DefaultValue”:””,”StorageType”:”Plain”},
    {“Name”:”TitleMob”,”ObjectID”:”f3fcbc50-59a9-4384-8848-2adf96ea40a1″,”FieldType”:”Text”,”IsPrimaryKey”:false,”MaxLength”:500,”Ordinal”:51,”DefaultValue”:””,”StorageType”:”Plain”}]

    DO u have any solution to this boolean value issue

21. Karna,

    Let’s try a different approach.

    Could you take a look at this code: https://github.com/ivanrazine/ampscript.xyz/blob/master/cloneDE.js

    To use it, just specify the name of the DE and the name of the folder in this function:

    var resp = cloneDE('Name of DE','Name of my Folder');

22. Hi Ivan
    Thanks for the reply but i was able to rectify the issue . Actually we are setting the length of Boolean object which is incorrect. To Rectify that i removed the length attribute for Boolean values and it worked for me

23. @carlito Did it worked for you? I am running into the same issue as yours. Let me know if you did something which worked. Waiting for your reply 🙂

24. @Ivan I tried using your above code wherein I tried to create a DE within SFMC using cloudpage and it worked on page load. But when I try to put the script under a function and call it onclick event for a button it does nothing. is there anything I’m doing wrong?

    Kindly ignore any typos..

    submit

    Platform.Load(“core”,”1.1″);
    function myFunction(){
    var DE = “MyNewDataExtensiGT”

    try {
    var obj = {
    “CustomerKey” : Platform.Function.GUID(),
    “Name” : DE,
    “Fields” : [
    { “Name” : “Id”, “FieldType” : “Number”, “IsPrimaryKey” : true, “IsRequired” : true },
    { “Name” : “MyData”, “FieldType” : “Text”, “MaxLength” : 50 },
    { “Name” : “Active”, “FieldType” : “Boolean”, “DefaultValue” : true }
    ]
    };
    DataExtension.Add(obj);

    Write(“(+) Data Extension was created successfully.” + “”);
    } catch (err) {
    Write(“(!) Data Extension was not created. Error message: ” + err + “”)

    }

    }

25. Onclick event is client side JS, not server side 😉

26. Hi Ivan,

    Thanks for the snippets… I just tried running it and it works. Awesome awesome.

27. The way you are looking up the folder ID could cause a problem if there is more than one folder with the same name. For instance, if you have 10 folders throughout your directories named “July” then the code, as written, would only pull the first instance of the folder it finds. The call returns an array, whether it only finds one or many. If your folder name is not unique, you may not get the results expected. You can also look up a folder ID by hovering your cursor over the actual folder and getting the CategoryID from the link in the status bar at the bottom of your browser.

28. What’s the format for specifying the Maxlength of a Decimal field? I’ve tried every possibility I can think of, but can’t get it to work.

29. Hi Jeff, for a Decimal field you’ll need MaxLength and Scale. I think you need to specify both in order for this to work.

30. Ivan,
    Can I specify a Data Extension TEMPLATE to utilize when creating? I want to create a Triggered Send Data Extension.

31. Yes Mark, but specifying a template isn’t enough. You can find your answer in this article: https://ampscript.xyz/how-tos/how-to-convert-into-triggeredsend-data-extension-ssjs/

32. Hi Ivan, Quick question regarding the name of the Data Extension. Can you think of a way to programatically name the DE based on a file import name? We have a request to import a file with a specific name, into a specific DE. We are trying to solve for the creating of the DE programmatically based on the file import name. Any ideas are much appreciated. Thank you!

    Joe

33. Haven’t played with file imports yet, so no clue, sorry mate

34. Hi Ivan,
    I am trying create a DE using SSJS in a Automation, with DATE data type field it was not creating DE without DATE data type field it’s creating DE.
    below is the sample code which not creating DE.

    Platform.Load(“core”,”1.1″);

    var DE = “DE_test1”

    try {
    var obj = {
    “CustomerKey” : Platform.Function.GUID(),
    “Name” : DE,
    “Fields” : [
    { “Name” : “ACCT_ACCT_TYPE”, “FieldType” : “Text”, “MaxLength” : 50},
    { “Name” : “ACCT_BP_ROLE”, “FieldType” : “Text”, “MaxLength” : 50},
    { “Name” : “ACCT_GENDER_FEMALE”, “FieldType” : “Text”, “MaxLength” : 50},
    { “Name” : “ACCT_GENDER_MALE”, “FieldType” : “Text”, “MaxLength” : 50},
    { “Name” : “VEH_DELIVERED_DATE”, “FieldType” : “Date”}

    ]
    };
    DataExtension.Add(obj);
    Write(“(+) Data Extension was created successfully.” + “”);
    } catch (err) {
    Write(“(!) Data Extension was not created. Error message: ” + err + “”)
    }

35. Hi Kumar. Try running your code from a CloudPage instead, then you’ll be able to see the errors 😉

36. Hi Ivan, first of all, congratulations for the article.
    I would like to know if it is possible instead of create a Data extension create a list or suppression list using Javascrip inside of marketing cloud, thanks very much, see you!

37. I built it from bits and pieces but here it is:

        Platform.Load("core", "1");
    
        var api = new Script.Util.WSProxy();
    
        try {
    
            var res = api.createItem("List", {
                ListName: "My New List",
                Type: "Public",
                Subscribers: [
                    {
                        SubscriberKey: "0x000000012345",
                        EmailAddress: "jon.snow@crowmail.com",
                        Status: "Active",
                        EmailTypePreference: "HTML",
                        Attributes: [
                            {
                                Name: "FirstName",
                                Value: "Jon"
                            },
                            {
                                Name: "LastName",
                                Value: "Snow"
                            }
                        ]
                    }
                ]
            });
    
            Write(Stringify(res))
    
        } catch(err) {
            Write(Stringify(err))
        }
    
     
    

38. Hi, Ivan, I would like to know if there’s a way to make a Data Extension but using onclick event (client-side), thanks!

39. Murilo, you already asked me that in another article and I replied.

40. Hi Ivan, thanks for the article, it has helped me a lot, I just have a couple of questions:
    1. Do I have to have previously created the folder where I have to save the DataExtension?
    2. How do I find the folder ID and where do I have to place it in the code? If you can help me with an example I would appreciate it, I am new to this. Thank you

41. Hi! You can either use another code to retrieve the folder ID or go to the Contact Builder, then inspect the folder in your browser to find the number in the HTML. Otherwise, if you don’t provide the folder ID, the DE will be created at the root level.

42. Hello Ivan, could you attach an example of where the folder ID should be placed? Thanks!

43. Sure thing God Doctor: https://www.ssjsdocs.xyz/contact-builder/dataextensions/create.html

    Just add CategoryID and assign a value to it 😉

44. Hi. Thanks for the article. It’s a big help. I want to create a data extension with a field with a null length so it is set to the maximum length. I have tried leaving it blank, disregarding it entirely and using NULL and every time I get a NULL value.

45. Hi 👋 That’s not going to work I’m afraid, the only way to assign NULL as a value is in SQL in my experience and even then it’s for the field values, not field properties.

Comments are closed.