Skip to Content
author's profile photo Former Member
Former Member

Dynamic join with extension collection

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

Add a comment
10|10000 characters needed characters exceeded

Related questions

2 Answers

  • Posted on Aug 26, 2015 at 12:21 PM

    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

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Apr 07, 2016 at 10:01 PM

    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.

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.