Dynamic join with extension collection

[Former Member]

Hi together,

I have to modify a powerful search.

Requirement is that also fields from extension collections of a master agreement need to be searchable and also in result table.

The token <%DYN_JOIN%> makes all fields automatically searchable. It works with direct fields on master agreement, but not with collections in my case.

FROM <%SCHEMA%>.FCI_CONTRACT T1    

<%DYN_JOIN%>

LEFT OUTER JOIN <%EXT_TABLE(contracts.Contract, extension_collection_id )%> X4 ON X4.PARENT_OBJECT_ID = T1.OBJECTID

LEFT OUTER JOIN <%EXT_TABLE(contracts.Contract)%> X5 ON X5.PARENT_OBJECT_ID = T1.OBJECTID

... some more joins

So, the fields from T1 are dynamically added, also extension filds from X5, but not fields auf the extension collection X4 "extension_collection_id".

Am I missing anything?

Thanks in advance and regards,

Özlem

[Former Member]

I know this post is old pretty old, but I think i just found something on this.

From my testing, it seems to only do the DYN_JOIN properly if you are using "INNER JOIN" not "LEFT OUTER JOIN". I tried using the alternative syntax "LEFT JOIN" too but no luck. Unfortunately, there are sometimes good reasons for using LEFT...

Wondering if anyone has run into this nuance or have found a work around.

Thanks in advance.

Mike

EDIT:  PS Now I'm doubting my results because I see other standard queries using it. May be something else causing it.

[Former Member]

Hello Ozlem,

We had a similar requirement few days back. We have also tried with <%DYN_JOIN%> and <%DYN_FILTER%> but no luck. 

I have followed below workaround solution:

I have added collection fields as default filters when we run the query.  Now users are able to see collection fields in filter drop down list along with contract table fields.  Even in result table also we need to display collection fields by default, there is no chance to add by using personalization option.

If above workaround is useful to meet your requirement try otherwise lets see experts can help us. 

Regards,

Lava

[former_member190023]

Hello Lava, Ozlem,

Indeed that is the only possible option for adding collections (either extension or standard).

Bogdan

[Former Member]

Hi Lava, Bogdan,

thanks for your help.

Also good to know that this is the only way to do so. I did it manually also now.

Regards,

özlem