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.
