Hi Community,
I have a requirement where I need to count records in a table based on a custom date range selected from a form.
Here’s the setup on the table form:
table_name (Reference field)
last_updated (Choice field with values like Today, Last Week, Custom Date)
start_date (Date/Time field)
end_date (Date/Time field)
When the user selects "custom date" from the last_updated dropdown, the start_date and end_date fields are shown. Once both dates are selected, I want to display a message showing like this
I created a Script Include (getUpdatedRecordStats)
var getUpdatedRecordStats = Class.create();
getUpdatedRecordStats.prototype = Object.extendsObject(global.AbstractAjaxProcessor, {
getCountForDateRange: function() {
var table = this.getParameter('sysparm_table');
var start = this.getParameter('sysparm_start');
var end = this.getParameter('sysparm_end');
if (!table || !start || !end) {
return JSON.stringify({ purge_count: 0, remaining_count: 0 });
}
try {
var startDate = new GlideDateTime(start + ' 00:00:00');
var endDate = new GlideDateTime(end + ' 23:59:59');
var purgeGR = new GlideRecord(table);
purgeGR.addQuery('sys_updated_on', '>=', startDate);
purgeGR.addQuery('sys_updated_on', '<=', endDate);
purgeGR.query();
var purgeCount = purgeGR.getRowCount();
var totalGR = new GlideAggregate(table);
totalGR.addAggregate('COUNT');
totalGR.query();
var total = totalGR.next() ? parseInt(totalGR.getAggregate('COUNT')) : 0;
return JSON.stringify({
purge_count: purgeCount,
remaining_count: total - purgeCount
});
} catch (error) {
gs.error('Error: ' + error);
return JSON.stringify({ purge_count: 0, remaining_count: 0 });
}
},
type: 'getUpdatedRecordStats'
});
Here is the onChange client script on the end_date field that calls the Script Include:
function onChange(control, oldValue, newValue, isLoading) {
if (isLoading) return;
var start = g_form.getValue('start_date');
var end = g_form.getValue('end_date');
var tableName = g_form.getValue('table_name');
var lastUpdated = g_form.getValue('last_updated');
if (lastUpdated === 'custom date' && start && end && tableName) {
var ga = new GlideAjax('getUpdatedRecordStats');
ga.addParam('sysparm_name', 'getCountForDateRange');
ga.addParam('sysparm_table', tableName);
ga.addParam('sysparm_start', start);
ga.addParam('sysparm_end', end);
ga.getXML(function(response) {
var answerRaw = response.responseXML.documentElement.getAttribute("answer");
var answer = JSON.parse(answerRaw);
var msg = 'Selecting this date will purge ' + answer.purge_count +
' records and leave behind ' + answer.remaining_count + ' records.';
g_form.showFieldMsg('end_date', msg, 'info');
});
} else {
g_form.hideFieldMsg('end_date');
}
}
The script works fine, and in my test case on the incident table, I used this encoded query: and i am getting 1500 updated record and this date range
My question is
when i give same start date and end date in the form i am getting as (Selecting this date will purge 0 records and leave behind 0 records.)
Can i get help to get my requirement complete
Thanks in advance
