I am using ssjs in Marketing Cloud to detect records that were not sent an email through my Journey due to not finding a matching Contact record in Salesforce.

First I am setting all of the values in my EmailSent column to True, the trying to filter the data extension to isolate the records with nothing in the ContactKey_MC__c field (which means they didn't match an existing Contact record) and set the EmailSent column to False so we can fix those and resend the email without the other records getting duplicate emails.

The first part works perfectly, but the second part does nothing. I've tried a number of ways of filtering the rows with null ContactKey_MC__c values (Property:"ContactKey_MC__c",SimpleOperator:"notContains",Value:"0"} is just the latest, since every ContactKey has a 0), but it's ignored regardless. Any help would be much appreciated!

<script runat="server">

var Shipments = DataExtension.Init("Shipping Confirmations");
var result = Shipments.Rows.Update({EmailSent:"True"}, ["EmailSent"],["False"]);

var ShipmentsEmpty = DataExtension.Init("Shipping Confirmations");
var filter = {Property:"ContactKey_MC__c",SimpleOperator:"notContains",Value:"0"};
var data = ShipmentsEmpty.Rows.Retrieve(filter);

 if(data && data.length > 0) {
      for(var i=0; i<data.length; i++) {
        var result = ShipmentsEmpty.Rows.Update({EmailSent:"False"}, ["EmailSent"],["True"]);


Use the SimpleOperator isNotNull like this:

var de = DataExtension.Init("AAAAAA-BBBB-CCCC-DDDDD-ABCDEF1234");
var filter = {Property:"Your field",SimpleOperator:"isNotNull"};
var data = de.Rows.Retrieve(filter);
Write("count:" + data.length);

SimpleOperator value of isNull is also possible.

Remember to also use the "External Key" in your DE init.

  • Thanks so much! Here's where I'm at now... var Shipments = DataExtension.Init("DEExternalKey"); var filter = {Property:"ContactKey_MC__c",SimpleOperator:"isNotNull"}; var data = Shipments.Rows.Retrieve(filter); Write("count:" + data.length); if(data && data.length > 0) { for(var i=0; i<data.length; i++) { var result = Shipments.Rows.Update({EmailSent:"True"}, ["EmailSent"],["False"]); } } The filter is being ignored and the entire DE is getting updated. Am I calling out the filter correctly? – anyat Sep 3 '19 at 14:01
  • 1
    Check to see if those values are actually null or are empty strings. If empty strings, then isNotNull would pass them and not filter them out. – Gortonington Sep 3 '19 at 20:47

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.