NOTE: I've broken this post up into 2 new posts in order to try to keep things cleaner and to the point as this post ended up taking a couple twists and turns:
TNT: "Related Attachments" Related List
TNT: Improving the Attachments List View
You may want to read them instead of continuing on with this outdated post.
I've been working with a great new client lately and we wanted to show all attachments related to a Requested Item, whether they're on the parent RITM record or on any of the associated Catalog Tasks, all in one place. I wrote about this previously (Showing Requested Item Attachments on the Catalog Task Form), but I thought of a better way to display them.
First, we start by adding two Relationship records (System Definition \ Relationships), one for the Requested Item and the other on the Catalog Tasks table:
Name: All Attachments
Applies to table: Requested Item [sc_req_item]
Queries from table: Attachment [sys_attachment]
Query with:
(function(){
var queryString = "table_nameINsc_req_item,sc_task^table_sys_idIN" + parent.getValue("sys_id");
var gr = new GlideRecord("sc_task");
gr.addQuery("request_item", parent.getValue("sys_id"));
gr.query();
while (gr.next()){
queryString += "," + gr.getValue("sys_id");
}
current.addEncodedQuery(queryString);
})();
Name: All Attachments
Applies to table: Catalog Task [sc_task]
Queries from table: Attachment [sys_attachment]
Query with:
(function(){
var queryString = "table_nameINsc_req_item,sc_task^table_sys_idIN" + parent.getValue("request_item");
var gr = new GlideRecord("sc_task");
gr.addQuery("request_item", parent.getValue("request_item"));
gr.query();
while (gr.next()){
queryString += "," + gr.sys_id.toString()
}
current.addEncodedQuery(queryString);
})();
They both get a list of sys_ids for the Requested Item and any child Catalog Task. If we add the "All Attachments" Related List to the Requested Item and Catalog Task forms, we get the following:
It's nice to have all the attachments in one place, but the "Table name" and "Table sys ID" fields are not very useful. Luckily we can improve on that.
First, we need to create a new field called "Record" of type "Document ID" on the Attachment table. To do this, enter "sys_attachment.list" in the Navigator filter - this will display a list of attachment records. Then right-click on the list header and select Configure \ Dictionary. Create a new record with the following settings:
Table: Attachment [sys_attachment]
Type: Document ID
Column label: Record (or whatever else you prefer)
Use dependent field: checked (you may have to click on the Advanced View Related Link to show these fields)
Dependent on field: Table name
You can also create the new column with the System Definition \ Tables module.
Next, we create a Business Rule on the Attachments table to populate that field:
Name: Custom - Populate Record Field
Table: Attachment [sys_attachment]
Active: checked
Advanced: checked
When: before
Insert: checked
Update: checked
Condition: current.table_sys_id.changes()
Script:
function onBefore(current, previous) {
current.u_record = current.table_sys_id;
}
The Business Rule simply copies the table_sys_id field into the new one. Now we can have a useful Related List on the Requested Item and Catalog Task tables by replacing the fields that are displayed (right-click Configure \ List Layout) by removing the "Table name" and "Table sys ID" fields and adding the new "Record" field:
Now you can see the record the attachment is actually on, and even click on the link to go to that particular record. The new Record field will be populated when any new attachments are added. You can run the the following Background Script after hours to populate it on your existing records:
(function() {
var gr = new GlideRecord('sys_attachment');
gr.autoSysFields(false);
gr.query();
while (gr.next()) {
gr.u_record = gr.table_sys_id;
gr.update();
}
})();
Remember, test any script in your development or sandbox instance first.
This solution can also be modified for use with the SDLC and PPM applications.
25 Comments
