2

I need to copy a Data Extension using SSJS and I believe I can do everything I need to do except understanding if I need to set the fields nullable or not.

I use this code to retrieve the info I need from my existing Data Extension for my new Data Extension:

<script runat="server">

Platform.Load('Core','1');
  
var templateDE = DataExtension.Init("AnyDataExtension");
var srcFields = templateDE.Fields.Retrieve();

Write(Stringify(srcFields)+'<br><br>');

So far so good, but in my response I see no info about 'nullability'.

This is the response:

[{"Name":"GroupName","ObjectID":"f7ff7ff7-41ec-41bd-9132-35ec7b97e4b6","FieldType":"Text","IsPrimaryKey":false,"MaxLength":50,"Ordinal":4,"DefaultValue":"","StorageType":"Plain"}, {"Name":"ClaimedDate","ObjectID":"f7ff7ff7-dfd1-427c-9cef-496c73aa96aa","FieldType":"Date","IsPrimaryKey":false,"MaxLength":0,"Ordinal":3,"DefaultValue":"","StorageType":"Plain"}, {"Name":"ClaimedBy","ObjectID":"f7ff7ff7-e181-4ff3-9daf-82346113a1bb","FieldType":"Text","IsPrimaryKey":false,"MaxLength":254,"Ordinal":2,"DefaultValue":"","StorageType":"Plain"}, {"Name":"CouponCode","ObjectID":"f7ff7ff7-1166-44f7-824d-995e886c6fa2","FieldType":"Text","IsPrimaryKey":false,"MaxLength":50,"Ordinal":0,"DefaultValue":"","StorageType":"Plain"}, {"Name":"IsClaimed","ObjectID":"f7ff7ff7-f502-4488-b278-dde1b04ccf90","FieldType":"Boolean","IsPrimaryKey":false,"MaxLength":0,"Ordinal":1,"DefaultValue":"false","StorageType":"Plain"}]

In the following I know that I can actually set a primarykey field nullable or not when preparing the array for my new Data Extension.

I just need to set field.IsRequired = false or true:

var fieldsArray = [];

for (var i = 0; i < srcFields.length; i++) {
 var field = {};

 field.Name = srcFields[i].Name;
 field.FieldType = srcFields[i].FieldType;

 if (srcFields[i].FieldType == 'Text') {
  field.MaxLength = srcFields[i].MaxLength;
 }

 if (srcFields[i].IsPrimaryKey) {
  field.IsPrimaryKey = true;
  field.IsRequired = true;
 }

 if (srcFields[i].DefaultValue) {
  field.DefaultValue = srcFields[i].DefaultValue;
 }
  

 fieldsArray.push(field);

}

Write(Stringify(fieldsArray)+'<br>');

But as I have no primarykeys in the Data Extension I'm copying, I need to find another way of finding if the original fields in the Data Extension I'm making a copy of were actually nullable

Are there ways in SSJS to retrieve info about fields nullable?

Thank you

2 Answers 2

1

If you add "field.IsRequired = false;", then the field should become nullable.

1
  • Hi @AndreasDS - you're right. To determine if any of the fields in the new Data Extension can be nullable or not, I can use IsRequired, which is also nicely documented here: link My bigger headache is still, that I don't know how to retrieve the nullable status of the fields in the original Data Extension, as I want to be able to create an exact copy Commented Jan 17 at 7:11
0

Actually, if using WSProxy with SSJS, I'm able to get tell if my Data Extension fields are nullable or not.

First of all, SSJS and WSProxy is described nicely and in depth here, and from this page I extended my original demo code from this:

<script runat="server">

Platform.Load('Core','1');

var templateDE = DataExtension.Init("AnyDataExtension");
var srcFields = templateDE.Fields.Retrieve();

Write(Stringify(srcFields)+'<br><br>');


var fieldsArray = [];

for (var i = 0; i < srcFields.length; i++) {
 var field = {};

 field.Name = srcFields[i].Name;
 field.FieldType = srcFields[i].FieldType;

 if (srcFields[i].FieldType == 'Text') {
  field.MaxLength = srcFields[i].MaxLength;
 }

 if (srcFields[i].IsPrimaryKey) {
  field.IsPrimaryKey = true;
  field.IsRequired = true;
 }

 if (srcFields[i].DefaultValue) {
  field.DefaultValue = srcFields[i].DefaultValue;
 }

 fieldsArray.push(field);

}

Write('Stringified fieldsArray: '+Stringify(fieldsArray)+'<br>');
</script>

to this:

<script runat="server">

Platform.Load('Core','1');

var templateDE = DataExtension.Init("AnyDataExtension");
var srcFields = templateDE.Fields.Retrieve();

Write(Stringify(srcFields)+'<br><br>');


var fieldsArray = [];

for (var i = 0; i < srcFields.length; i++) {
 var field = {};

 field.Name = srcFields[i].Name;
 field.FieldType = srcFields[i].FieldType;

 if (srcFields[i].FieldType == 'Text') {
  field.MaxLength = srcFields[i].MaxLength;
 }

 if (srcFields[i].IsPrimaryKey) {
  field.IsPrimaryKey = true;
  field.IsRequired = true;
 }

 if (srcFields[i].DefaultValue) {
  field.DefaultValue = srcFields[i].DefaultValue;
 }

 fieldsArray.push(field);

}

Write('Stringified fieldsArray: '+Stringify(fieldsArray)+'<br>');




var api = new Script.Util.WSProxy();

try {

 var dataExtensionName = "AnyDataExtension";

 var req = api.retrieve("DataExtension", ["CustomerKey"], {
  Property: "Name",
  SimpleOperator: "equals",
  Value: dataExtensionName
 });

 var customerKey = req.Results[0].CustomerKey;

 var cols = [
  "Name", 
  "IsRequired"
 ];

 var result = api.retrieve("DataExtensionField", cols, {
  Property: "DataExtension.CustomerKey",
  SimpleOperator: "equals",
  Value: customerKey
 });

 Write('<br>WSProxy, stringified is: '+Stringify(result)+'<br><br>');

} catch(error) {
 Write(Stringify(error));
} 
</script>

In the screenshot you can tell, how I now am able to see if ie. the Field named 'IsClaimed' actually is required. With this info I'm now able to make an exact copy of my original Data Extension :)

Code example SSJS and WSProxy

You must log in to answer this question.

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