Date comparison

[Ian Wheeler]

Hi Folks,

I am trying to check via a script whether a Date field contains a date that is earlier than today. If it is then I want to delete the record. Ideally this would be a manually run script. I am very new to TF (and loving it, and am in the process of migrating over from Filemaker) and Javascript. Below is the script that I have which just doesn’t work. I suspect it is my Date comparison element that is the problem as the Console.Log isn’t detecting any errors, but I don’t know how to fix it. Any help would be greatly appreciated

function Delete_Expired_Docs() {

	var records = form.getRecords();
var field_id = 'fld-46f16c00d13344e79478a8b629022ee7';
	
for (var index = 0, count = records.length; index < count; index++){
    var aRecord = records[index];
    var field_value = aRecord.getFieldValue(field_id);
    if (field_value < Date()) {
        form.deleteRecord(aRecord);
    }
}

form.saveAllChanges();

console.log(field_value);
console.log(count);
console.log(Date());

}

Delete_Expired_Docs();

• This topic was modified 2 years ago by Brendan.
• This topic was modified 2 years ago by Brendan. Reason: formatted code

[Daniel Leu]

As an alternative, you could search for the records with an old date using advanced search:

Attachments:

You must be logged in to view attached files.

[Daniel Leu]

To get the comparison to work, you need to get the numerical representation of the date. Additionally, the date object needs to set to the beginning of the day to properly detect a prior date.

function Delete_Expired_Docs() {

	var records = form.getRecords();
	var field_id = 'fld-xxx';
	
	// Get time in ms at beginning of today
	let now = new Date();
	now.setSeconds(0);
	now.setMinutes(0);
	now.setHours(0);
	now.setMilliseconds(0);
	const today = now.getTime();
		
for (var index = 0, count = records.length; index < count; index++){
    var aRecord = records[index];
    var field_value = aRecord.getFieldValue(field_id);

    if (field_value.getTime() < today) {
        //form.deleteRecord(aRecord);
        console.log("found record");
    } else {
    	console.log("record doesn't match");
    }
}

form.saveAllChanges();

}

Delete_Expired_Docs();

BTW, I don’t like deleting records… I would just mark a record as ‘expired’. But that depends really on your data model.

[Ian Wheeler]

Thanks Gents, I really appreciate your help with this, as a complete JS novice I would never have worked that out. I will implement your code when I get chance Daniel.
(P.S. I don’t usually delete database records but in this instance the database is a digital filing cabinet of home files. I am only deleting files that have either expired or have been superseded / renewed etc. Doing this reduces my file size which I am syncing across all of my devices.)

[Brendan]

Hi Ian,

Of course another way to delete these records is to just create a Saved Search that finds all records with the date field earlier than today using the is before today comparison operator. Then you can just select all the records and delete them with the Delete key. No programming needed.

Thanks,

Brendan

[Brendan]

Oh, also when you delete, your database file won’t necessarily get smaller. But you can shrink it by running the Compact Database function. Close and re-open your document after that to see the true new size of the database.

[Author]

Posts