How to restrict query selection to a collaborator role value

[Ramesh]

Hi,
The requirement is to restrict my projects report to collaborator role "Collaborator". Below is the current query

SELECT DISTINCT <%RESULTS%>

FROM

<%SCHEMA%>.FCI_PRO_PROJECTS T1

LEFT OUTER JOIN 

<%SCHEMA%>.FCI_MAS_VALUE_LIST_VALUE T3 ON T1.STATUS_OBJECT_ID = T3.OBJECTID LEFT OUTER JOIN 

<%SCHEMA%>.FCI_MAS_INTERNAL_CAT T2 ON T1.INTERNAL_CAT_OBJECT_ID = T2.OBJECTID

<%MS_JOIN%> 

WHERE T1.INACTIVE = 0

AND T1.IS_TEMPLATE = 0

AND T1.CONTEXTID=<%CONTEXT(projects.projects)%>

AND  <%SCHEMA%>.FCI_DOC_CHECKCOLLAB(T1.OBJECTID, 1100,<%CURRENT_USER_ID%>,'<%CURRENT_USER_GROUPS%>') = 1 

<%ORDERBY%>

The collaborator role object is FCI_DOC_COLLABORATOR_ROLE (510) and need to restrict it to a collaborator role "Collaborator".

Appreciate any inputs regarding this.

Thanks,
Ramesh

[Former Member · Accepted solution]

Hello Ramesh,

As per my understanding, you wanted to pull all the projects where the current loggedin user is added with the role COLLABORATOR.

Follow the below approach and append this syntax to the query you have written.

*Join FCI_PRO_PROJECTS table with FCI_DOC_COLLABORATOR table.

*In WHERE clause, USER_NAME_OBJECT_ID = <%CURRENT_USER_ID%> and COLLABORATOR_ROLE_OBJECT_NAME LIKE 'Collaborator'

Hope this helps!!

Regards,

Vignesh

[Ramesh · Accepted solution]

Thanks for you response, it worked! Appreciate your time and help.