query to get the records created in current month

[Priti18]

below is my script which is copying the data from attachment table to data source and if data source already contains some file then first delete it and then attach it and it should only take those attachments from sys_email to sys_attachment which are received on first of current month.

(function runAction( /*GlideRecord*/ current, /*GlideRecord*/ event, /*EmailWrapper*/ email, /*ScopedEmailLogger*/ logger, /*EmailClassifier*/ classifier) {

deleteattachment('123'); // SYS_ID of the Data Source

var gr = new GlideRecord('sys_attachment');
gr.addEncodedQuery('file_name=test.csv^table_name=sys_email^table_name=sys_email^

sys_created_onONToday@javascript:gs.beginningOfThisMonth()@javascript:gs.endOfThisMonth()

');

gr.query();
while (gr.next()) {
GlideSysAttachment.copy('sys_email', gr.table_sys_id, 'sys_data_source', '123'); // SYS_ID of the Data Source

}

})(current, event, email, logger, classifier);

function deleteattachment(id) {
var docattach = new GlideRecord('sys_attachment');
docattach.addEncodedQuery('table_name=sys_data_source^table_sys_id=' + id);
docattach.query();
while (docattach.next()) {
docattach.deleteRecord();
}
}

[DUGGI]

@Priti18 

 

Your script seems almost correct. However, there is an issue in your encoded query where you are using the sys_created_on field. It appears that you are trying to filter attachments created on the first day of the current month. To achieve that, you need to modify the encoded query slightly. Here's the updated script:

javascriptCopy code
(function runAction(/*GlideRecord*/ current, /*GlideRecord*/ event, /*EmailWrapper*/ email, /*ScopedEmailLogger*/ logger, /*EmailClassifier*/ classifier) {

    deleteattachment('123'); // SYS_ID of the Data Source

    var gr = new GlideRecord('sys_attachment');
    gr.addEncodedQuery('file_name=test.csv^table_name=sys_email^sys_created_on>=javascript&colon;gs.beginningOfThisMonth()^sys_created_on<=javascript&colon;gs.endOfThisDay()');
    gr.query();
    while (gr.next()) {
        GlideSysAttachment.copy('sys_email', gr.table_sys_id, 'sys_data_source', '123'); // SYS_ID of the Data Source
    }

})(current, event, email, logger, classifier);

function deleteattachment(id) {
    var docattach = new GlideRecord('sys_attachment');
    docattach.addEncodedQuery('table_name=sys_data_source^table_sys_id=' + id);
    docattach.query();
    while (docattach.next()) {
        docattach.deleteRecord();
    }
}

I have updated the encoded query to include sys_created_on>=javascript&colon;gs.beginningOfThisMonth()^sys_created_on<=javascript&colon;gs.endOfThisDay() which filters the attachments created on the first day of the current month.

Please replace the '123' with the actual sys_id of the Data Source and test the script. It should now correctly filter and copy the attachments as per your requirement.

[Priti18]

I used above script and its only deleting the file but not attaching the other one.

my requirement is user will send mail with  attachment every first of month and once SN receives it, then it will delete the existing one and attach the new file to the data source.

the code which I gave it adds the attachment but not deleting the existing one and the one which you gave its deleting the existing one but not attaching the new file to data source.

[Priti18]

@BharathChintala please help with the above code

[BharathChintala]

@Priti18  try this

(function runAction( /*GlideRecord*/ current, /*GlideRecord*/ event, /*EmailWrapper*/ email, /*ScopedEmailLogger*/ logger, /*EmailClassifier*/ classifier) {

var id = 'wusuagde89';

var docattach = new GlideRecord('sys_attachment');
docattach.addEncodedQuery('table_name=sys_data_source^table_sys_id=' + id);
docattach.query();
while (docattach.next()) {
docattach.deleteRecord();
}
}

var gr = new GlideRecord('sys_attachment');
gr.addEncodedQuery('file_name=test.csv^table_name=sys_email^table_name=sys_email^

sys_created_onONToday@javascript&colon;gs.beginningOfThisMonth()@javascript&colon;gs.endOfThisMonth()

');

gr.query();
while (gr.next()) {
GlideSysAttachment.copy('sys_email', gr.table_sys_id, 'sys_data_source', id); // SYS_ID of the Data Source

}

})(current, event, email, logger, classifier);