0

Using the following filter I am retrieving all the rows I need to delete from my Data Extension:

<script runat="server">
Platform.Load("core","1");
var deletedCustomerDE = DataExtension.Init("DeletedCustomers");
var filter = {Property:"Passenger",SimpleOperator:"greaterThan",Value:5};
var data = deletedCustomerDE.Rows.Retrieve(filter);

</script>

How do I now remove all the rows inside the filter from my Data Extension? I have used a function like deletedCustomerDE.Rows.Remove(["PassengerID"], ["56"]); to remove individual rows but not all rows matching my condition.

Also if you recommend an IDE in which I can test my code and not code in the dark like this, it will be highly appreciated.

  • There is no IDE for these. Only postman can help if you do API requests. Normal use case would be printing everything before to make sure it is right and then switch to deleting. – Johannes Schapdick Jul 8 at 8:41
  • @JohannesSchapdick Thank you! Could you also help me with the code? – Disasterkid Jul 8 at 8:43
2

You can use a for loop to go through your filtered records.

<script runat="server">
Platform.Load("core","1");
var de = DataExtension.Init("11111-22222-5555-aaa-bbb-ccc");
var filter = {Property:"FirstName",SimpleOperator:"equals",Value:"John"};
var data = de.Rows.Retrieve(filter);
var total;

for (var i=0; i<data.length; i++) {
  var result = de.Rows.Remove(["ID"], [data[i].ID]);
  total += result;
}
Write("Deleted: " + total);
</script>

You can also use WSProxy deleteBatch also, but your DE must contain a Primary Key to reference in the call, so may not be as useful in this scenario.

<script runat="server">
Platform.Load("core","1");
var deCustKey = "45151BD5-F593-4B2D-8D10-AAAAAAAAAA";
var de = DataExtension.Init(deCustKey);
var filter = {Property:"Passenger",SimpleOperator:"greaterThan",Value:5};
var data = de.Rows.Retrieve(filter);
var objs = [];

for (var i=0; i<data.length; i++) {
  objs.push({
    CustomerKey: deCustKey,
    Keys: [{Name:"ID", Value:data[i].ID}]
  });
}

var prox = new Script.Util.WSProxy();
var res = prox.deleteBatch("DataExtensionObject", objs);

Write(Stringify(res));
</script>
  • Thank you! Let me put them into the test and come back to you! – Disasterkid Jul 9 at 6:09
  • Where does this Write() function output the messages? – Disasterkid Jul 9 at 6:49
  • 1
    Perhaps this also answers your IDE question. Although there is no specific IDE, you can use Cloudpages for testing. Write is only useful in a Cloudpage context. If you are using a Script Activity in an Automation, then you will not see the output. I like to test in a cloudpage first, to know that the script is working as expected, although be aware they have shorter timeouts than script activities. – Josh de Blank Jul 9 at 6:57
  • An old colleague suggested that I use try-catch as much as I can but output the errors into a data extension. Does that sound like a reasonable solution? Do cloud pages not expose your code if they are published on a URL? – Disasterkid Jul 9 at 7:05
  • 1
    Try catch is a good tool also. Cloudpages will only expose the client-side code to the public. Ampscript and SSJS are server-side. You can always delete your page once you are done testing. – Josh de Blank Jul 9 at 10:56

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.