0

I am looking to do a 'Create Filter Definition' (to initially create these filters) and then an iterative 'Update Filter Definition' to mostly automate our constantly changing campaign requirements - each is filtered off of a 'Master Data Extension' (DE).

I decided to use the SSJS FilterDefinition.Add() (and then use FilterDefinition Update to update] as it was super simple and it can easily be run via a Script Activity. This works 100% when filtering on a single property (e.g. simple filter), but if you attempt to do anything more than a simple filter, it does not work and throws an error.

Error (on complex filter):

Jint.JintException: Exception of type 'Jint.Native.JsException' was thrown.
Line: 1576 Char: 12
throw "Error adding FilterDefinition"
Source: Jint

Current Code

<script runat="server">

Platform.Load("core", "1");

// Data Extension Definitions
var i;
var masterDE = 'myMasterDE';
var onDeck = 'onDeckDE';

// Initiate onDeckDE for Lookup
var onDeckDE = DataExtension.Init(onDeck);

// Gather all Rows from OnDeckDE
var rows = onDeckDE.Rows.Lookup(["Status"], ["Active"]);

// For Loop to create Filters from OnDeck

if (rows !== null) {

    for (i in rows) {

// Set Values for Row from OnDeck

        var campaignCode = rows[i].CampaignCode;
        var status = rows[i].Status;
        var macme = rows[i].MACME;
        var projectedDate = rows[i].ProjectedDate;

// Set Filter Object | If complex it fails, but simple works

        var filterObj = {

            LeftOperand: {
                Property: "CampaignKey",
                SimpleOperator: "equals",
                Value: macme
            },
            LogicalOperator: "AND",
            RightOperator: {
                Property: "MailDate",
                SimpleOperator: "equals",
                Value: projectedDate
            }
        };

// Create the Filter

        var newFD = {
            Name : campaignCode,
            CustomerKey : campaignCode,
            Filter : filterObj,
            DataSource : {
                Type : "DataExtension",
                CustomerKey : masterDE
            }

        };

        var status = FilterDefinition.Add(newFD);
        Write(Stringify(status) + '<br />');

    } // End For Loop

} // End If Statement

</script>

My perfect solution would be to continue to utilize the SSJS FilterDefinition.Add() function with complex filters, but I have had no success finding how to do it.

If this is not possible, my next attempt was to create a SSJS SOAP call to accomplish the same thing:

SOAP API Code

<script runat="server">

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

var i;
var user = 'myUserName';
var pass = 'myPassword';
var payload = '';
var endpoint = 'https://webservice.exacttarget.com/Service.asmx'
var result;

// Data Extension Definitions
var masterDE = 'MasterDE';
var onDeck = 'onDeckDE';

// Initiate onDeckDE for Lookup
var onDeckDE = DataExtension.Init(onDeck);

// Gather all Rows from OnDeckDE
var rows = onDeckDE.Rows.Lookup(["Status"], ["Active"]);

// For Loop to create Filters from OnDeck

if (rows !== null) {

    for (i in rows) {

// Set Values for Row from OnDeck

        var campaignCode = rows[i].CampaignCode;
        var status = rows[i].Status;
        var macme = rows[i].MACME;
        var projectedDate = rows[i].ProjectedDate;

// Create SOAP Call

payload += '<?xml version="1.0" encoding="UTF-8"?>';
payload += '<SOAP-ENV:Envelope xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">';
payload += ' <SOAP-ENV:Header>';
payload += '  <wsse:Security xmlns:wsse="http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-wssecurity-secext-1.0.xsd" xmlns:wsu="http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-wssecurity-utility-1.0.xsd">';
payload += '   <wsu:Timestamp>';
payload += '   </wsu:Timestamp>';
payload += '   <wsse:UsernameToken>';
payload += '    <wsse:Username>' + user + '</wsse:Username>';
payload += '    <wsse:Password Type="http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-username-token-profile-1.0#PasswordText">' + pass + '</wsse:Password>';
payload += '   </wsse:UsernameToken>';
payload += '  </wsse:Security>';
payload += ' </SOAP-ENV:Header>';
payload += ' <SOAP-ENV:Body>';
payload += '  <CreateRequest xmlns="http://exacttarget.com/wsdl/partnerAPI">';
payload += '   <Options/>';
payload += '   <ns1:Objects xmlns:ns1="http://exacttarget.com/wsdl/partnerAPI" xsi:type="ns1:FilterDefinition">';
payload += '    <ns1:ModifiedDate xsi:nil="true"/>';
payload += '    <ns1:ObjectID xsi:nil="true"/>';
payload += '    <ns1:Name>' + campaignCode + '</ns1:Name>';
payload += '    <ns1:DataSource xsi:type="ns1:DataExtension">';
payload += '     <ns1:ModifiedDate xsi:nil="true"/>';
payload += '     <ns1:ObjectID>' + masterDE + '</ns1:ObjectID>';
payload += '    </ns1:DataSource>';

payload += '    <ns1:DataFilter xsi:type="par:ComplexFilerPart" xmlns:par="http://exacttarget.com/wsdl/partnerAPI">';
payload += '      <LeftOperand xsi:type="par:SimpleFilterPart">';
payload += '         <Property>CampaignKey</Property>';
payload += '         <SimpleOperator>equals</SimpleOperator>';
payload += '         <Value>' + macme + '</Value>';
payload += '      </LeftOperand>';
payload += '      <LogicalOperator>AND</LogicalOperator>';
payload += '      <RightOperand xsi:type="par:SimpleFilterPart">';
payload += '         <Property>MailDate</Property>';
payload += '         <SimpleOperator>equals</SimpleOperator>';
payload += '         <DateValue>' + projectedDate + '</DateValue>';
payload += '     </RightOperand>';
payload += '    </ns1:DataFilter>';

payload += '   </ns1:Objects>';
payload += '  </CreateRequest>';
payload += ' </SOAP-ENV:Body>';
payload += '</SOAP-ENV:Envelope>';

try {
    result = HTTP.Post(endpoint, "text/xml", payload, ["SOAPAction"], ["Create"])
} catch(e) {
    result = {StatusCode:500, Response:Stringify(e)};
}

if (result.StatusCode != 200) {
    //Bad response
    Write(i + '<br />' + Stringify(result) + '<br />');
} else {
    //Good response
    Write(i + '<br />' + Stringify(result) + '<br />');
}

    } // End For Loop

} // End If Statement
</script>

but this gives me the following Error:

The remote server returned an error: (500) Internal Server Error. - from System

Any help or alternative solutions that can be provided would be greatly appreciated.

Thanks.

2

There are three principal ways in which you could create a Filter Definition using SSJS:

  1. SSJS API Functions
  2. SSJS Core Functions
  3. Hand-rolling SOAP envelopes and using HTTP.Post

All three achieve more or less the same thing. The only key differentiator would be where you hand-roll SOAP envelopes. This method allows addition of ClientID to the API call allowing the creation of objects in a Business Unit other than the one in which your script is running. Core and API functions run in the context of the current Business Unit and cannot create objects in any other Business Unit.

Follows are working examples of all three methods:

SSJS API Functions

<script runat="server">

    Platform.Load("core", "1");

    var result;
    var filter =    {
        name:       "Sample API Filter",
        key:        "SampleAPIFilter123",
        dataSource: {
                    type:       "DataExtension",
                    key:        "Journey"
                    },
        left:       {
                    property:   "JourneyID",
                    operator:   "equals",
                    value:      "123123"
                    },
        right:      {
                    property:   "Version",
                    operator:   "equals",
                    value:      "1"
                    },
        operator:   "AND"
    };

    try {
        // Create LeftOperand SimpleFilterPart
        var left = Platform.Function.CreateObject("SimpleFilterPart");
        Platform.Function.SetObjectProperty(left, "Property", filter.left.property);
        Platform.Function.SetObjectProperty(left, "SimpleOperator", filter.left.operator);
        Platform.Function.AddObjectArrayItem(left, "Value", filter.left.value);

        // Create RightOperand SimpleFilterPart
        var right = Platform.Function.CreateObject("SimpleFilterPart");
        Platform.Function.SetObjectProperty(right, "Property", filter.right.property);
        Platform.Function.SetObjectProperty(right, "SimpleOperator", filter.right.operator);
        Platform.Function.AddObjectArrayItem(right, "Value", filter.right.value);

        //Adding two Simple filters together to create a Complex filter
        var filterObj = Platform.Function.CreateObject("ComplexFilterPart");
        Platform.Function.SetObjectProperty(filterObj, "LeftOperand", left);
        Platform.Function.SetObjectProperty(filterObj, "LogicalOperator", filter.operator);
        Platform.Function.SetObjectProperty(filterObj, "RightOperand", right);

        // Define the DataSource
        var dataSource = Platform.Function.CreateObject(filter.dataSource.type);
        Platform.Function.SetObjectProperty(dataSource, "CustomerKey", filter.dataSource.key);


        // Create the Filter Definition
        var filterDef = Platform.Function.CreateObject("FilterDefinition");
        Platform.Function.SetObjectProperty(filterDef, "Name", filter.name);
        Platform.Function.SetObjectProperty(filterDef, "CustomerKey", filter.key);
        Platform.Function.SetObjectProperty(filterDef, "DataSource", dataSource);
        Platform.Function.SetObjectProperty(filterDef, "DataFilter", filterObj);

        var result = [0,0];
        var status = Platform.Function.InvokeCreate(filterDef, result, null);
        var message = result[0];
        var errorCode = result[1];

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

SSJS Core Functions

<script runat="server">

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

    var result;
    var filter =    {
        name:       "Sample CORE Filter",
        key:        "SampleCoreFilter123",
        dataSource: {
                    type:       "DataExtension",
                    key:        "Journey"
                    },
        left:       {
                    property:   "JourneyID",
                    operator:   "equals",
                    value:      "123123"
                    },
        right:      {
                    property:   "Version",
                    operator:   "equals",
                    value:      "1"
                    },
        operator:   "AND"
    };

    var left =      {
                    Property:           filter.left.property,
                    SimpleOperator:     filter.left.operator,
                    Value:              filter.left.value
                    };

    var right =     {
                    Property:           filter.right.property,
                    SimpleOperator:     filter.right.operator,
                    Value:              filter.right.value
                    };

    var filterObj = {
                    LeftOperand:        left,
                    RightOperand:       right,
                    LogicalOperator:    filter.operator
                    };

    var newFD = {
        Name : filter.name,
        CustomerKey : filter.key,
        Filter : filterObj,
        DataSource : {
            Type : filter.dataSource.type,
            CustomerKey : filter.dataSource.key
        }
    };

    try {
        var result = FilterDefinition.Add(newFD);
        Write(Stringify(result));
    } catch (e) {
        Write(Stringify(e));
    }
</script>

HTTP.Post SOAP Envelope

< script runat = "server" >

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

    var user = 'myUserName';
    var pass = 'password123';
    var payload = '';
    var endpoint = 'https://webservice.exacttarget.com/Service.asmx'
    var result;
    var filter =    {
        name:       "Sample SOAP Filter",
        key:        "SampleSOAPFilter123",
        dataSource: {
                    type:       "DataExtension",
                    key:        "Journey"
                    },
        left:       {
                    property:   "JourneyID",
                    operator:   "equals",
                    value:      "123123"
                    },
        right:      {
                    property:   "Version",
                    operator:   "equals",
                    value:      "1"
                    },
        operator:   "AND"
    };

    // Create SOAP Call

    payload += '<s:Envelope xmlns:s="http://www.w3.org/2003/05/soap-envelope" xmlns:a="http://schemas.xmlsoap.org/ws/2004/08/addressing" xmlns:u="http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-wssecurity-utility-1.0.xsd">';
    payload += '        <s:Header>';
    payload += '            <o:Security s:mustUnderstand="1" xmlns:o="http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-wssecurity-secext-1.0.xsd">';
    payload += '                <o:UsernameToken u:Id="uuid-88b91f91-bac2-489b-90fb-37e7b256e20c-1">';
    payload += '                    <o:Username>' + user + '</o:Username>';
    payload += '                    <o:Password>' + pass + '</o:Password>';
    payload += '                </o:UsernameToken>';
    payload += '            </o:Security>';
    payload += '        </s:Header>';
    payload += '        <s:Body xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">';
    payload += '            <CreateRequest xmlns="http://exacttarget.com/wsdl/partnerAPI">';
    payload += '                <Objects xmlns:ns1="http://exacttarget.com/wsdl/partnerAPI" xsi:type="ns1:FilterDefinition">';
    payload += '                    <Name>' + filter.name + '</Name>';
    payload += '                    <CustomerKey>' + filter.key + '</CustomerKey>';
    payload += '                    <DataSource xsi:type="ns1:' + filter.dataSource.type + '">';
    payload += '                        <CustomerKey>' + filter.dataSource.key + '</CustomerKey>';
    payload += '                    </DataSource>';
    payload += '                    <DataFilter xsi:type="ComplexFilterPart">';
    payload += '                        <LeftOperand xsi:type="SimpleFilterPart">';
    payload += '                            <Property>' + filter.left.property + '</Property>';
    payload += '                            <SimpleOperator>' + filter.left.operator + '</SimpleOperator>';
    payload += '                            <Value>' + filter.left.value + '</Value>';
    payload += '                        </LeftOperand>';
    payload += '                        <LogicalOperator>' + filter.operator + '</LogicalOperator>';
    payload += '                        <RightOperand xsi:type="SimpleFilterPart">';
    payload += '                            <Property>' + filter.right.property + '</Property>';
    payload += '                            <SimpleOperator>' + filter.right.operator + '</SimpleOperator>';
    payload += '                            <Value>' + filter.right.value + '</Value>';
    payload += '                        </RightOperand>';
    payload += '                    </DataFilter>';
    payload += '                </Objects>';
    payload += '            </CreateRequest>';
    payload += '       </s:Body>';
    payload += '</s:Envelope>';

    try {
        result = HTTP.Post(endpoint, "text/xml", payload, ["SOAPAction"], ["Create"])
    } catch (e) {
        Write(Stringify(e));
    }

    Write(Stringify(result));
</script>
  • Thanks Andrew, but still getting the same error :( I have pulled in SFMC support as well, because I have heard that this SHOULD be a capability of SSJS - I will post any info I get from them as well. – Gortonington Jul 11 '17 at 14:44
  • Sorry. I was fixated with the "ComplexFilterPart" aspect. The last part of the code's also far more long-winded. I've added an example of a working API call from SSJS that creates a filter with a ComplexFilterPart. – Macca Jul 11 '17 at 15:53
  • Thanks Andrew - I was hoping to avoid writing out SSJS API call as they tend to go super slow for me. I am going to wait for feedback from support, but if they do not have a solution, I will make sure to mark your answer as correct. – Gortonington Jul 11 '17 at 18:07
  • Although it was not the optimum solution I was hoping for, it does work (which is the most important part), and is much more than anything SFMC support has given me. Thanks @AndrewMcLeod – Gortonington Jul 12 '17 at 17:33
  • Sorry. Didn't realise you were hanging out for a particular method of achieving this. I've added using SOAP and Core Functions for you to take your pick. I've not particularly noticed issues with API functions, but happy to take your word for it. Generally, I'm not hammering the API with these, so a second or two extra processing time generally doesn't make much odds. Anyways. Choose the one that works best for you. – Macca Jul 13 '17 at 2:41

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.