Data Repository - Delete based on DateM and another column

OL Connect Professional Workflow
1

I would like to delete records if my Status column has a specific value and the DateM is over 7 days old.
Having difficulties getting it to work.

2

Something like this will get the list of keysets that are over 7 days old. You should be able to take it from there:

var repo = new ActiveXObject("RepositoryLib.WorkflowRepository");
var numberOfDays = 7;
var oneWeekAgo = new Date(Date.now()-(numberOfDays*3600*24*1000))

oneWeekAgoString = oneWeekAgo.toISOString().slice(0,10)

var fields       = ["DateC", "DateM"];
var condition    = 'DateM < "' + oneWeekAgoString + '%"';

var ks = repo.GetKeySets("Group1", JSON.stringify(fields), condition);

Watch.Log(ks,2);
3

This helps a lot, but wondering why some files are not being deleted.

var repo = new ActiveXObject("RepositoryLib.WorkflowRepository");

var numberOfDays  = 7;
var printPeriod   = new Date(Date.now()-(numberOfDays*3600*24*1000))
printString       = printPeriod.toISOString().slice(0,10);

var purgeDays     = 30;
var purgePeriod   = new Date(Date.now()-(purgeDays*3600*24*1000))
purgeString       = purgePeriod.toISOString().slice(0,10);

var orphanDays    = 60;
var orphanPeriod  = new Date(Date.now()-(orphanDays*3600*24*1000))
orphanString      = orphanPeriod.toISOString().slice(0,10);

var fields        = ["DateC", "DateM", "status"];
var condPrint     = 'DateM >= "' + printString + '%" and status = "printed"';
var condPurge     = 'DateM >= "' + purgeString + '%" and status = "critical printer error"';
var condOrphan    = 'DateM >= "' + orphanString + '%" and status = "received"';

Watch.Log(condPrint,2);
Watch.Log(condPurge,2);
Watch.Log(condOrphan,2);

var delPrintCount  = JSON.parse(repo.RemoveKeySets("JobTracking_SF_PRINT", condPrint));
Watch.Log("Deleted Printed Count: " + delPrintCount,2);

var delPurgeCount  = JSON.parse(repo.RemoveKeySets("JobTracking_SF_PRINT", condPurge));
Watch.Log("Deleted Purged Count: " + delPurgeCount,2);

var delOrphanCount = JSON.parse(repo.RemoveKeySets("JobTracking_SF_PRINT", condOrphan));
Watch.Log("Deleted Orphan Count: " + delOrphanCount,2);

After running this script files still remain that I believe should have removed.
Today is 2023-08-29, but “printed” files from 2023-05-22 still remain even though they are older than 7 days old.

image

4

Since you are not looking at the time component of the date strings, you can simply compare the first 10 characters in each Key (and also remove the extra “%”). For instance:

var condPrint     = 'substr(DateM,1,10) >= "' + printString + '" and status = "printed"';

The repository is based on SQLLite, so you can lookup information on the types of WHERE clauses you can use in the Repository API.