D2 Doclist with Dynamic DQL

dctmadi
edited May 27, 2016 in Documentum #1

I am trying to create a doclist based on a dynamic DQL.

Based on the object select I need to view the set of documents.

Below is the query I am using

select $value(r_object_id),$value(default_attributes) from dm_document where r_object_id in (select child_id from dm_relation where parent_id='$value(r_object_id)' and relation_name='D2 Workflow Document')

It does not resolve the $value(r_object_id) to the selected object's ID.

Tagged:

Best Answer

  • AnuragJain1983
    edited May 26, 2016 #2 Answer ✓

    It does resolver. But why are $value in SELECT clause??

    Just do the following

    1) For the doclist widget make sure SELECT event is subscribed

    2) Choose the columns for the widget and just have query like

    select r_object_id, r_object_type  from dm_document where r_object_id in (select child_id from dm_relation where parent_id='$value(r_object_id)' and relation_name='D2 Workflow Document')

Answers

  • DCTM_Guru
    edited May 26, 2016 #3

    What is the value of r_object_id?  Is this a folder or document?  Did you confirm that your subquery returns a value?

  • AnuragJain1983
    edited May 26, 2016 #4 Answer ✓

    It does resolver. But why are $value in SELECT clause??

    Just do the following

    1) For the doclist widget make sure SELECT event is subscribed

    2) Choose the columns for the widget and just have query like

    select r_object_id, r_object_type  from dm_document where r_object_id in (select child_id from dm_relation where parent_id='$value(r_object_id)' and relation_name='D2 Workflow Document')

  • dctmadi
    edited May 26, 2016 #5

    Thanks Anurag. I can see the document now but it does not show the format of the file, it shows a question mark. Is there any field we should include to get the format icon .

  • AnuragJain1983
    edited May 27, 2016 #6

    Standard attributes r_object_id, r_object_type, a_content_type should solve it ideally. See query generated in logs for normal document widget.

    I know that dm_audittrail has a known issue to show question mqrk but not for dm_document

  • dctmadi
    edited May 27, 2016 #7

    Adding a_content_type shows the format correctly. Thanks for your help on this

  • Hi,
    I am using this query for retrieving all SOP,Policy, Forms, Work Instructions under Gov n Proc for certain applicable_sites.
    The contents are displaying but i am getting **** lot of duplicate rows in the doclist widget.** Any help on this to remove duplicate rows?**

    DQL used is :
    select r_object_id, a_content_type, title, object_name, r_version_label, a_status, r_modify_date, r_modifier, applicable_sites, primary_group from cd_quality_gmp where folder('/Governance and Procedures',descend) and (ANY applicable_sites IN ('Clinical','Global','Investigational Medical Product','Commercial')) and (primary_group IN ('Standard Operating Procedure','Policy','Form','Work Instruction') )

  • Try adding "enable(row_based)" to end of your dql:

    select r_object_id, a_content_type, title, object_name, r_version_label, a_status, r_modify_date, r_modifier, applicable_sites, primary_group from cd_quality_gmp where folder('/Governance and Procedures',descend) and (ANY applicable_sites IN ('Clinical','Global','Investigational Medical Product','Commercial')) and (primary_group IN ('Standard Operating Procedure','Policy','Form','Work Instruction') ) enable(row_based)

    This function is supposed to eliminate unwanted rows that are caused by using ANY function against repeating attributes.

  • Hi All
    After 5 year are anyone success to customize different columns in DocList widget?
    Thanks