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.

Find more posts tagged with

Documentum

Note:

If there are Accepted Answers, those will be shown by default. You can switch to 'All Replies' by selecting the tab below.

Accepted answers

AnuragJain1983

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')

All comments

DCTM_Guru

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

AnuragJain1983

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

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

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

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

shubhro_d2

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') )

DCTM_Guru

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.

shubhro_d2

Thanks

GTN

Hi All

After 5 year are anyone success to customize different columns in DocList widget?

Thanks