Attachment count!!

[coolboy]

Hi on kb_knowledge table i have created a field called attachment counter (this field will be counting the number of attachment to particular article.)
Any idea how to do this.I am thinking of making after isert/update b.Rule.

var att = new GlideRecord("sys_attachment");
att.addQuery("table_name","kb_knowledge");


what else :-)....

[gaidem]

Try this out, I haven't tested it, but it should work.
Make a before insert BR on the sys_attachment table.
Condition: current.table_name == 'kb_knowledge'
Script:

var x = new GlideRecord('kb_knowledge');
x.addQuery('sys_id',current.table_sys_id);
x.query();
if(x.next()){
x.u_counter ++;
x.update();
}

[williamsun]

Based on the script and logic from gaidem I did this for the sc_req_item table, adjusted below for the KB table.

I ran it on insert and delete, considering this needs to update when an attachment is removed, also ran it after, as I want it to consider the attachment that was just inserted or deleted.

Business Rule

Name: Update u_attachment_count

Table: Attachment [sys_attachment]

When: after [insert, delete]

Condition: current.table_name == 'kb_knowledge'

Script:

//since we are on the sys_attachment table, we get the KB record into "x"

var x = new GlideRecord('kb_knowledge');

x.addQuery('sys_id', current.table_sys_id);

x.query();

x.next();

//now, we do a second glide record for the attachment table to count all the attachments

var y = new GlideRecord('sys_attachment');

y.addQuery('table_sys_id',x.sys_id);

y.query();

//which we then put back into the "x" record and update.

x.u_attachment_count = y.getRowCount();

x.update();

Worked like a charm in the Requested Item table!

Thanks to both.

[Kevin Chung]

In addition to your script, you also might want to add the followings before x.update():

x.setWorkflow(false); //Do not run business rules

x.autoSysFields(false); //Do not update system fields

These are to prevent business rules to run, and to update Updated and Updated By fields.

Kevin

[williamsun]

Either that or add a comment to log activity for the audit trail of the record (if you're doing this on [task] for example)