I have created a database view (between attachment & incident table)

[utsavjaiswa]

Hello Everyone, I have created a database view between attachment & incident table..with clause as:-

incident_sys_id=attachment_table_sys_id

By this clause it is working as expected.But suppose if a incident record is having 5 attachments then it is showing same 5 records differently..I want to make it a single to reduce the duplication.

Is there any way??

Warm Regards

Utsav

[ifti122]

Hi @utsavjaiswa,

To achieve your goal of showing a single row per incident, you have two main options:

Option-1: Group the Results in the View

• In the "View Fields" related list of your database view definition, find the field for the Incident Number (e.g., number).

• Edit that row and check the "Group by" checkbox.

• Caveat: This will collapse the rows, but the attachment-related fields (like file name) will only show data from one of the attachments, which can be misleading.

Option-2:  Use a Report Instead (Recommended)

A database view is not the best tool for this specific requirement. A better approach is to create a report directly on the Incident table.

• Table: Incident [incident]

• Condition: Add a filter like [Has attachments] [is] [true] or [Attachments] [is not empty].

This will give you a clean list of all incidents that have at least one attachment, without any duplicate rows.

Thanks & Regards,
Muhammad Iftikhar
If my response helped, please mark it as the accepted solution so others can benefit as well.

[Brad Bowman]

A Database View is meant to join two tables on a related field, and show one row for each combination.  You don't need a Database View in this case since the attachment record is already linked back to the incident table. 

[Ankur Bawiskar]

@utsavjaiswa 

why to join both the tables when sys_attachment already stores the INC sysId?

Since your INC has 5 attachments and it has 5 sys_attachment records then logically it will show 5 rows in database view.

that's expected behavior.

If my response helped please mark it correct and close the thread so that it benefits future readers.

[Bhuvan]

@utsavjaiswa 

Why do you need to create a database view for this requirement ?

You can create a report or data visualization on 'sys_attachment' table with condition 'table name' is incident and use a group by function.

Reporting on system table [sys_attachment] is not allowed and needs to be included in the property glide.ui.permitted_tables

If this helped to answer your query, please mark it helpful & accept the solution.

Thanks,

Bhuvan