D2 Doclist with Dynamic DQL

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.

Best Answer

  • edited May 26, 2016 Accepted 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

  • edited May 26, 2016

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

  • edited May 26, 2016

    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 .

  • edited May 27, 2016

    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

  • edited May 27, 2016

    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.

  • Thanks

Sign In or Register to comment.