TNT: "Related Attachments" Related List

[Jim Coyne]

Part of the Tips 'N Tricks" series.

I've seen a lot of requests in the Community to see related attachments on multiple forms.   For instance, people want to see Requested Item attachments on the related Catalog Task records as well.   Most "solutions" that are suggested involve copying the attachments from one record to the other, which you really do not want to do (synching problems, duplicate records for no reason, etc...).   My solution is to create a Defined Related List (Create defined related lists) which can display attachments from multiple records.

We start by adding a new Relationship record (System Definition \ Relationships):

Name:                   Related Attachments
Applies to table:       Global [global]
Queries from table:     Attachment [sys_attachment]
Query with:

(function refineQuery(current, parent) {
  var tableName = parent.getTableName();
  var queryString = "table_name=" + tableName + " ^table_sys_id=" + parent.getValue("sys_id");   //default query

  switch (tableName){
    //add your table-specific blocks from below
  }

  current.addEncodedQuery(queryString);

  function u_getRelatedRecords(table, field, sysId){
    var result = "";
    var gr = new GlideRecord(table);
    gr.addQuery(field, sysId);
    gr.query();
    while (gr.next()){
      result += "," + gr.getValue("sys_id");
    }
    return result;
  }

})(current, parent);

The script checks the table name for the record being displayed and then builds the appropriate query.   As a safety measure, the queryString variable is given a default query to display the attachments for just that one record, otherwise all attachments would appear in the list if the Related List was added to a form that did not have any specific "case" block.   I created the private "u_getRelatedRecords" function to simplify the whole script as we use the same GlideRecord query to retrieve the appropriate sys_ids regardless of the table.

The above script is just the starting block - I'll add table specific examples next.   Each of the next blocks of code should be inserted within the "switch" block at line 6:

Request, Requested Item and Catalog Task Tables

      //===== Requests =====

      case "sc_request":

      queryString = "table_nameINsc_request,sc_req_item,sc_task^table_sys_idIN" + parent.getValue("sys_id");

      //find the related Requested Items
      queryString += u_getRelatedRecords("sc_req_item", "request", parent.getValue("sys_id"));

      //and then the Catalog Tasks
      queryString += u_getRelatedRecords("sc_task", "request_item.request", parent.getValue("sys_id"));

      break;


      //===== Requested Items =====
      case "sc_req_item":
      queryString = "table_nameINsc_request,sc_req_item,sc_task^table_sys_idIN" + parent.getValue("request") + "," + parent.getValue("sys_id");

      //find the related Catalog Tasks
      queryString += u_getRelatedRecords("sc_task", "request_item", parent.getValue("sys_id"));

      break;

 
      //===== Catalog Tasks =====
      case "sc_task":
      queryString = "table_nameINsc_request,sc_req_item,sc_task^table_sys_idIN" + parent.request_item.request.toString() + "," + parent.getValue("request_item");

      //find the related Catalog Tasks
      queryString += u_getRelatedRecords("sc_task", "request_item", parent.getValue("request_item"));

      break;

Incident and Service Desk Call Tables

      //===== Incidents =====
      case "incident":
      queryString = "table_nameINincident,new_call^table_sys_idIN" + parent.getValue("sys_id");

      //find the related New Call
      queryString += u_getRelatedRecords("new_call", "transferred_to", parent.getValue("sys_id"));

      break;


      //===== Service Desk Calls =====
      case "new_call":
      queryString = "table_nameINincident,new_call^table_sys_idIN" + parent.getValue("sys_id") + "," + parent.getValue("transferred_to");

      break;

Idea and Demand Tables

      //===== Idea =====
      case "idea":
      queryString = "table_nameINidea,dmn_demand^table_sys_idIN" + parent.getValue("sys_id") + "," + parent.getValue("demand");

      break;


      //===== Demand =====
      case "dmn_demand":
      queryString = "table_nameINidea,dmn_demand^table_sys_idIN" + parent.getValue("sys_id") + "," + parent.getValue("idea");

      break;

Project and Project Task Tables

  //===== Project =====
  case "pm_project":
  queryString = "table_nameINpm_project,pm_project_task,idea,dmn_demand^table_sys_idIN" + parent.getValue("sys_id");
 
  //find the related Project Tasks
  queryString += u_getRelatedRecords("pm_project_task", "top_task", parent.getValue("top_task"));

  //find the related Idea and Demand
  queryString += u_getRelatedRecords("dmn_demand", "project", parent.getValue("sys_id"));
  queryString += u_getRelatedRecords("idea", "demand.project", parent.getValue("sys_id"));

  break;


  //===== Project Task =====
  case "pm_project_task":
  queryString = "table_nameINpm_project,pm_project_task,idea,dmn_demand^table_sys_idIN" + parent.getValue("top_task");
 
  //find the related Project Tasks
  queryString += u_getRelatedRecords("pm_project_task", "top_task", parent.getValue("top_task"));

  //find the related Idea and Demand
  queryString += u_getRelatedRecords("dmn_demand", "project", parent.getValue("top_task"));
  queryString += u_getRelatedRecords("idea", "demand.project", parent.getValue("top_task"));

  break;

HR Case and HR Task Tables

      //===== HR Case =====
      case "hr_case":
      queryString = "table_nameINhr_case,hr_task^table_sys_idIN" + parent.getValue("sys_id"); 

      //find the related HR Tasks
      queryString += u_getRelatedRecords("hr_task", "parent", parent.getValue("sys_id"));

      break;


      //===== HR Tasks =====
      case "hr_task":
      queryString = "table_nameINhr_case,hr_task^table_sys_idIN" + parent.getValue("sys_id") + "," + parent.getValue("parent");

      break;

Now you can see all the attachments from related records if you add the "Related Attachments" Related List to a form:

The above blocks of code are just examples of what you can do and there are quite a few more that can be added.   I'll add some more as I come across some more ideas or people ask for more.

If you want a better looking and more useful list view, you will want to read this post - TNT: Improving the Attachments List View:

You will be able to see the record the attachment is actually on (instead of a sys_id), and even click on the link to go to that particular record.

NOTE: My earlier blog post, A Better Requested Item Attachments Related List, got a little messy so I split it into 2 different posts so it would be easier to read and update if required.   This post is the first of those 2 new posts.

[Mark Rodriguez]

Thank you for this information, but I have a couple of questions. I am new to scripting and doing my best to pick it up as quickly as possible but how would I write a table specific to copy attachments from the sn_customerservice_case table to the sn_cusotmerservice_task table? Also we use the Agent WorkSpace for most of our daily duties. In a case like this would I end up just adding a Related List tab to the Case Task Form and we would not use the attachmetns shortcut?

[Samyuktha Akara]

How to make this related list available on portal ?

[Community Alums]

Awesome solution, thanks for the help!

I was trying to implement on Quebec and using "attachment" variable types. I have a catalog task where the user is uploading a document using the attachment variable, then that updated document would need to be available in a future catalog task for further manipulation. 

According to this article: ZZ_YY is prepended to attachment variable type attachments - "at the start of the table name this tells the system not to show the file as an attachment on the top of the form."

Anyways, I was having issues where the attachments were not being pulled in.

After adding ZZ_YYsc_request, ZZ_YYsc_req_item, ZZ_YYsc_task to the queryString, like below, the solution now works. So, if you are ever using an attachment type variable check the sys_attachment table and you may need to prepend "ZZ_YY" in order to reference it's table_name.

        case "sc_request":

            queryString = "table_nameIN,ZZ_YYsc_request,sc_request,ZZ_YYsc_req_item,sc_req_item,ZZ_YYsc_task,sc_task^table_sys_idIN" + parent.getValue("sys_id");

            //find the related Requested Items
            queryString += u_getRelatedRecords("sc_req_item", "request", parent.getValue("sys_id"));

            //and then the Catalog Tasks
            queryString += u_getRelatedRecords("sc_task", "request_item.request", parent.getValue("sys_id"));

            break;

It is now working as expected

[Catherine16]

Hello,

I tried this for Request and Requested Items.

It is working but I need it to show all attachments in one Requested Item to another Requested Item with the same Request and vice versa. 

In REQ level, it is showing all attachments from REQ, RITM1  & RITM2.

In RITM level, it is only showing the attachment I attached on it and from the REQ. It does not show the attachment from RITM2 (new RITM with the same REQ).

The goal is to see in RITM level, what we are seeing in REQ level.

This is my code:

(function refineQuery(current, parent) {

// Add your code here, such as current.addQuery(field, value);
var tableName = parent.getTableName();
var queryString = "table_name=" + tableName + " ^table_sys_id=" + parent.getValue("sys_id"); //default query

switch (tableName) {
//===== Requests =====
case "sc_request":

queryString = "table_nameINsc_request,sc_req_item,sc_task^table_sys_idIN" + parent.getValue("sys_id");

//find the related Requested Items
queryString += u_getRelatedRecords("sc_req_item", "request", parent.getValue("sys_id"));

//and then the Catalog Tasks
queryString += u_getRelatedRecords("sc_task", "request_item.request", parent.getValue("sys_id"));
break;

//===== Requested Items =====
case "sc_req_item":
queryString = "table_nameINsc_request,sc_req_item,sc_task^table_sys_idIN" + parent.getValue("request") + "," + parent.getValue("sys_id");

//find the related Catalog Tasks
queryString += u_getRelatedRecords("sc_task", "request_item", parent.getValue("sys_id"));
break;

//===== Catalog Tasks =====
case "sc_task":
queryString = "table_nameINsc_request,sc_req_item,sc_task^table_sys_idIN" + parent.request_item.request.toString() + "," + parent.getValue("request_item");

//find the related Catalog Tasks
queryString += u_getRelatedRecords("sc_task", "request_item", parent.getValue("request_item"));
break;
}

current.addEncodedQuery(queryString);

function u_getRelatedRecords(table, field, sysId) {
var result = "";
var gr = new GlideRecord(table);
gr.addQuery(field, sysId);
gr.query();
while (gr.next()) {
result += "," + gr.getValue("sys_id");
}
return result;
}

})(current, parent);

[jmiskey]

Awesome!  This helps me do exactly what I wanted to do, which was show all the attachment, from both the Attachment fields on a Catalog Item along with any manually added Attachments, and display them on a Catalog Task.  I just needed to take your "queryString" update and apply it to the other two tables (sc_req_item and sc_task).

Thank you!

[Community Alums]

Hi All,

Has anyone used this script to show related attachments on parent/child incidents?

I have tried the below but no attachments are showing -

		      //===== Incidents =====
      case "incident":
      queryString = "table_nameINincident,new_call^table_sys_idIN" + parent.getValue("sys_id");

      //find the related New Call
      queryString += u_getRelatedRecords("new_call", "transferred_to", parent.getValue("sys_id"));

      break;
		  
		  
		  
//===== Incident Tasks =====
		case "incident_task":
		queryString = "table_nameINincident,incident_task^table_sys_idIN" + 
                parent.incident.toString() + "," + parent.getValue("incident");
		
		//find the related Change Tasks
		queryString += u_getRelatedRecords("incident_task", "incident", 
                parent.getValue("incident"));
		
		break;

Thanks!

Alex

[Mustafa Jawad]

how can the sc_task attachment be viewed in the portal under my tickets? 

[Community Alums]

THIS POST IS EXTEREMLY HELPFUL THIS WILL GIVE YOU EXACTLY WHAT YOU NEED FOR YOUR RELATED LISTS!!!

[Edxavier Robert]

Hi all, 

Thanks @Jim Coyne  for this amazing solution. 

I have tried to apply the same logic for other tables but seems that I can't get it work. I have 2 custom table

table1 = x_352504_breach_case

table2 = x_352504_breach_task

The table2 is extended from the table 1. I have a reference field on the table2 to called: u_parent_case to show task releated to the case on table1. 

Now, what I am trying to do is show on table2 all the attachments that are on table1. I saw all your examples and tried to apply the logic but quite not getting the correct results with my custom table. 

With this code I am getting the records from the correct table, but it getting all the attachments for anyrecord. My issue is how to get the correct table_sys_id to filter the data. 

current.addQuery('table_name', 'x_352504_breach_case');
current.addQuery('table_sys_id', 'parent_case'('sys_id'));

[Jim Coyne]

If I'm understanding your layout properly, this should work:

      //===== Breach Tasks =====

      case "x_352504_breach_task"":
      queryString = "table_name=x_352504_breach_case^table_sys_id=" + parent.getValue("u_parent_case") + "^table_sys_idISNOTEMPTY;
      break;

That will show only the attachments on the parent x_352504_breach_case record.

Obviously I don't know the use case for the app nor the layout of everything, but I don't really like that the "task" table is an extension of the "case" table.  They are different things.  You can link them like you have with the "u_parent_case" field on the "task" table, but "task" should not be an extension of "case"

The problem is when someone looks at a list of "table1" records, the records from "table2" will be included because they are in fact a type of "table1" records as well.