If you’ve been coding for Marketing Cloud long enough, you’ve realized that sometimes AMPscript is just not enough.

There is no way to properly debug it, add Data Extension fields if they don’t exist or look up every row without using any criteria.

In this article, I will show you how to create a Data Extension, fill it up with data and retrieve this data with server-side Javascript.

Create a new Data Extension

Consider a new Data Extension named ListOfOptions with the following structure and data:

Id	Option	Active
1	Quick brown fox	True
2	Big white bear	True
3	Awesome flying hawk	True

Let’s create it by using the following server-side Javascript:

<script runat="server">
    Platform.Load("core","1.1");
    var DE = "ListOfOptions"

    /* Create a new Data Extension */
    try {
        var obj = {
            "CustomerKey" : DE,
            "Name" : DE,
            "Fields" : [
                { "Name" : "Id", "FieldType" : "Number", "IsPrimaryKey" : true, "IsRequired" : true },
                { "Name" : "Option", "FieldType" : "Text", "MaxLength" : 50 },
                { "Name" : "Active", "FieldType" : "Boolean", "DefaultValue" : false }
            ]
        };
        DataExtension.Add(obj);
        Write("(+) Data Extension was created successfully.");
    } catch (err) {
        Write("(!) Data Extension was not created. " + err);
    }     

    /* Deleting old data */     
    try {
		var deleteDE = DataExtension.Init(DE);
		deleteDE.Rows.Remove(["Active"],["True"]);
        Write("(+) Old records were deleted.");
	} catch(err) {
        Write("(!) No old records were deleted: " + err);
	}

    /* Insert new data */
    try {
        Platform.Function.InsertData(DE,["Id","Option","Active"],["1","Quick brown fox","True"]);
        Platform.Function.InsertData(DE,["Id","Option","Active"],["2","Big white bear","True"]);
        Platform.Function.InsertData(DE,["Id","Option","Active"],["3","Awesome flying hawk","True"]);
        Write("(+) New data was inserted successfully.");
    } catch(err) {
        Write("(!) New data was not inserted. " + err);
    }  
</script>

Note that it’s good practice to add some AMPscript and client-side Javascript for debugging purposes.

<script runat="server">
    Platform.Load("core","1.1");
	Variable.SetValue("name","Mister Blue");
</script>
<script runat="client">
	console.log(`%%=v(@name)=%%`);
</script> 

Read data from a Data Extension

Have you ever considered to use Data Extensions as a simple data storage for a long list of items? Data Extensions are databases, there is no reason not to use them as such.

In this example, we store a list of options that we use to populate a select field in a form.

<form>
    <label for="Options">List of options</label>
    <select name="Options">
        <option></option>
        <script runat="server">
            Platform.Load("core","1.1");
			var DE = "ListOfOptions"
            try {

                var ListOfOptionsDE = DataExtension.Init(DE);
                var Rows = ListOfOptionsDE.Rows.Retrieve();

                if (Rows.length > 0) {
                    for(var i in Rows) {
                        Write("<option>" + Rows[i]["Option"] + "</option>");
                    }
                }

                Variable.SetValue("rowMessage","(+) New data was retrieved successfully.");

            } catch (err) {
                Variable.SetValue("rowMessage","(-) No data was retrieved." + err);
            }
        </script>
    </select>
    <button>Submit</button>
</form>

<script runat="client">
	console.log(`%%=v(@rowMessage)=%%`);
</script>

In the end, all of our code will perform the following actions:

• Create a Data Extension
• Delete existing data if the Data Extension already exists
• Insert data
• Read data from the Data Extension

Full code

<!-- STEP 1 -->
<script runat="server">
    Platform.Load("core","1.1");
    var DE = "ListOfOptions"

    /* Create a new Data Extension */
    try {
        var obj = {
            "CustomerKey" : DE,
            "Name" : DE,
            "Fields" : [
                { "Name" : "Id", "FieldType" : "Number", "IsPrimaryKey" : true, "IsRequired" : true },
                { "Name" : "Option", "FieldType" : "Text", "MaxLength" : 50 },
                { "Name" : "Active", "FieldType" : "Boolean", "DefaultValue" : false }
            ]
        };
        DataExtension.Add(obj);
        Variable.SetValue("createMessage","(+) Data Extension was created successfully.");
    } catch (err) {
        Variable.SetValue("createMessage","(!) Data Extension was not created. " + err);
    }     

    /* Deleting old data */     
    try {
		var deleteDE = DataExtension.Init(DE);
		deleteDE.Rows.Remove(["Active"],["True"]);
        Variable.SetValue("deleteMessage","(+) Old records were deleted.");
	} catch(err) {
        Variable.SetValue("deleteMessage","(!) No old records were deleted: " + err);
	}

    /* Insert new data */
    try {
        Platform.Function.InsertData(DE,["Id","Option","Active"],["1","Quick brown fox","True"]);
        Platform.Function.InsertData(DE,["Id","Option","Active"],["2","Big white bear","True"]);
        Platform.Function.InsertData(DE,["Id","Option","Active"],["3","Awesome flying hawk","True"]);
        Variable.SetValue("insertMessage","(+) New data was inserted successfully.");
    } catch(err) {
        Variable.SetValue("insertMessage","(!) New data was not inserted. " + err);
    }  
</script>

<script runat="client">
	console.log(`%%=v(@createMessage)=%%`);
	console.log(`%%=v(@deleteMessage)=%%`);
	console.log(`%%=v(@insertMessage)=%%`);
</script> 

<!-- STEP 2 -->
<form>
    <label for="Options">List of options</label>
    <select name="Options">
        <option></option>
        <script runat="server">
            Platform.Load("core","1.1");
			var DE = "ListOfOptions"
            try {

                var ListOfOptionsDE = DataExtension.Init(DE);
                var Rows = ListOfOptionsDE.Rows.Retrieve();

                if (Rows.length > 0) {
                    for(var i in Rows) {
                        Write("<option>" + Rows[i]["Option"] + "</option>");
                    }
                }

                Variable.SetValue("rowMessage","(+) New data was retrieved successfully.");

            } catch (err) {
                Variable.SetValue("rowMessage","(-) No data was retrieved." + err);
            }
        </script>
    </select>
    <button>Submit</button>
</form>

<script runat="client">
	console.log(`%%=v(@rowMessage)=%%`);
</script>

Have I missed anything?

Please poke me with a sharp comment below or use the contact form.