How do we link customized extension collection in a query?

[Private_Member_113126]

Hello experts!

We have created some extension collection members which are not available in the standard query “Search My Master Agreements”. In this case we have to extend this kind of query in order to make all content searchable for the user.

Ø

• Type of Contract

We tried to customize the query but entries are either unfiltered or redundant/wrong as soon as the search functionality is used. The modified SQL is given as follows (relevant entries which we made are marked in red):

SELECT <%RESULTS%>

FROM

<%SCHEMA%>.FCI_MAS_VALUE_LIST_VALUE T4,

<%SCHEMA%>.FCI_DYN_$2147483348 T5,

<%SCHEMA%>.FCI_CONTRACT T1

JOIN

<%COLLABCHECK_TBL(T1.OBJECTID, 1004)%>

LEFT OUTER JOIN (SELECT PARENT_OBJECT_ID, COUNT(*) AS AGR_COUNT FROM <%SCHEMA%>.FCI_AGREEMENT GROUP BY PARENT_OBJECT_ID) T2 ON T1.OBJECTID = T2.PARENT_OBJECT_ID

LEFT OUTER JOIN <%SCHEMA%>.FCI_MAS_INTERNAL_CAT T3 ON T1.INT_CAT_OBJECT_ID = T3.OBJECTID

<%DYN_JOIN%>

WHERE

T1.CONTEXTID =<%CONTEXT(contracts.Contract)%>

AND T1.IS_TEMPLATE =0

AND T1.STATUS_OBJECT_ID = T4.OBJECTID

<%AND_OP("UPPER(T1.DISPLAY_NAME)",<%?(T1.DISPLAY_NAME)%>)%>

<%AND_OP("UPPER(T1.VENDOR_OBJECT_NAME)",<%?(T1.VENDOR_OBJECT_NAME)%>)%>

<%AND_OP("T1.EXPIRATION_DATE_DATE",<%?(T1.EXPIRATION_DATE_DATE)%>)%>

<%AND_OP("T1.TERMIN_COMMU_DATE",<%?(T1.TERMIN_COMMU_DATE)%>)%>

<%AND_OP("T1.INT_CAT_OBJECT_ID",<%?(T1.INT_CAT_OBJECT_ID)%>)%>

<%AND_OP("T1.INACTIVE",<%?(T1.INACTIVE)%>)%>

<%AND_OP("T1.DOC_TYPE_OBJECT_ID",<%?(T1.DOC_TYPE_OBJECT_ID)%>)%>

<%AND_OP("UPPER(T5.ZCLM_TYPO_CONTR_OBJECT_NAME)",<%?(T5.ZCLM_TYPO_CONTR_OBJECT_NAME)%>)%>

AND

(

(<%?(MyOwnership)%> =2)

OR

(T1.DOC_OWNER_USER_OBJECT_ID=<%CURRENT_USER_ID%>)

)

<%DYN_FILTER%>

<%ORDERBY%>

A filter prompt has been made as well:

I guess the relation is wrong according to extension collection member FCI_DYN_$2147483348.

Finally, we get these results by using this modified query:


I hope I can find here some help

Thank you in advance,
Heiko Hinz

[former_member190023 · Accepted solution]

Hello Heiko,

First of all I would recommend you to also get in touch with someone with DB experience. Your query is 'correct' as per syntax but it results in a Cartesian Product because no join condition was provided.

You could try following updated query:

SELECT <%RESULTS%>

FROM

<%SCHEMA%>.FCI_MAS_VALUE_LIST_VALUE T4,

<%EXT_TABLE(contracts.Contract,COLLECTION_NAME)%> T5,

• Use this format to facilitate transport between DEV-QA-PROD (the dynamic table name will be different between systems)
• replace COLLECTION_NAME with the name of your desired extension collection
  

<%SCHEMA%>.FCI_CONTRACT T1

JOIN

<%COLLABCHECK_TBL(T1.OBJECTID, 1004)%>

LEFT OUTER JOIN (SELECT PARENT_OBJECT_ID, COUNT(*) AS AGR_COUNT FROM <%SCHEMA%>.FCI_AGREEMENT GROUP BY PARENT_OBJECT_ID) T2 ON T1.OBJECTID = T2.PARENT_OBJECT_ID

LEFT OUTER JOIN <%SCHEMA%>.FCI_MAS_INTERNAL_CAT T3 ON T1.INT_CAT_OBJECT_ID = T3.OBJECTID

<%DYN_JOIN%>

WHERE

T1.CONTEXTID =<%CONTEXT(contracts.Contract)%>

AND T1.IS_TEMPLATE =0

AND T1.STATUS_OBJECT_ID = T4.OBJECTID

AND T5.PARENT_OBJECT_ID = T1.OBJECTID

• join condition
  

<%AND_OP("UPPER(T1.DISPLAY_NAME)",<%?(T1.DISPLAY_NAME)%>)%>

<%AND_OP("UPPER(T1.VENDOR_OBJECT_NAME)",<%?(T1.VENDOR_OBJECT_NAME)%>)%>

<%AND_OP("T1.EXPIRATION_DATE_DATE",<%?(T1.EXPIRATION_DATE_DATE)%>)%>

<%AND_OP("T1.TERMIN_COMMU_DATE",<%?(T1.TERMIN_COMMU_DATE)%>)%>

<%AND_OP("T1.INT_CAT_OBJECT_ID",<%?(T1.INT_CAT_OBJECT_ID)%>)%>

<%AND_OP("T1.INACTIVE",<%?(T1.INACTIVE)%>)%>

<%AND_OP("T1.DOC_TYPE_OBJECT_ID",<%?(T1.DOC_TYPE_OBJECT_ID)%>)%>

<%AND_OP("UPPER(T5.ZCLM_TYPO_CONTR_OBJECT_NAME)",<%?(T5.ZCLM_TYPO_CONTR_OBJECT_NAME)%>)%>

AND

(

(<%?(MyOwnership)%> =2)

OR

(T1.DOC_OWNER_USER_OBJECT_ID=<%CURRENT_USER_ID%>)

)

<%DYN_FILTER%>

<%ORDERBY%>

Regards,

Bogdan

[Private_Member_113126 · Accepted solution]

Your answer is plausible. I will try it later and let you know if it works.

Thanks Bogdan

[Private_Member_113126 · Accepted solution]

Hello Bogdan.

Thanks for your input it was really helpful Nevertheless, I've got an error message according to the table which I added - invalid table name (see screenshot).

I checked the table name and I'm sure it is right. In this case I modified the query without the <%EXT_TABLE(logical.Name, Collection_Name) %> That is why my query looks like this:

SELECT <%RESULTS%>

FROM

<%SCHEMA%>.FCI_MAS_VALUE_LIST_VALUE T4,

<%SCHEMA%>.FCI_CONTRACT T1

JOIN

<%COLLABCHECK_TBL(T1.OBJECTID, 1004)%>

LEFT OUTER JOIN (SELECT PARENT_OBJECT_ID, COUNT(*) AS AGR_COUNT FROM <%SCHEMA%>.FCI_AGREEMENT GROUP BY PARENT_OBJECT_ID) T2 ON T1.OBJECTID = T2.PARENT_OBJECT_ID

LEFT OUTER JOIN <%SCHEMA%>.FCI_MAS_INTERNAL_CAT T3 ON T1.INT_CAT_OBJECT_ID = T3.OBJECTID

LEFT OUTER JOIN <%SCHEMA%>.FCI_DYN_$2147483348 T5 ON T5.PARENT_OBJECT_ID = T1.OBJECTID

<%DYN_JOIN%>

WHERE

T1.CONTEXTID =<%CONTEXT(contracts.Contract)%>

AND T1.IS_TEMPLATE =0

AND T1.STATUS_OBJECT_ID = T4.OBJECTID

<%AND_OP("UPPER(T1.DISPLAY_NAME)",<%?(T1.DISPLAY_NAME)%>)%>

<%AND_OP("UPPER(T1.VENDOR_OBJECT_NAME)",<%?(T1.VENDOR_OBJECT_NAME)%>)%>

<%AND_OP("T1.EXPIRATION_DATE_DATE",<%?(T1.EXPIRATION_DATE_DATE)%>)%>

<%AND_OP("T1.TERMIN_COMMU_DATE",<%?(T1.TERMIN_COMMU_DATE)%>)%>

<%AND_OP("T1.INT_CAT_OBJECT_ID",<%?(T1.INT_CAT_OBJECT_ID)%>)%>

<%AND_OP("T1.INACTIVE",<%?(T1.INACTIVE)%>)%>

<%AND_OP("T1.DOC_TYPE_OBJECT_ID",<%?(T1.DOC_TYPE_OBJECT_ID)%>)%>

<%AND_OP("UPPER(T5.ZCLM_TYPO_CONTR_OBJECT_NAME)",<%?(T5.ZCLM_TYPO_CONTR_OBJECT_NAME)%>)%>

AND

(

(<%?(MyOwnership)%> =2)

OR

(T1.DOC_OWNER_USER_OBJECT_ID=<%CURRENT_USER_ID%>)

)

<%DYN_FILTER%>

<%ORDERBY%>

The results are fine. But I don't know what to do with EXT_TABLE command...

[former_member190023 · Accepted solution]

That's very strange, EXT_TABLE should work out of the box.

Make sure that both parameters are correct, and try to test with case-sensitive names

You can double-check the values for <%EXT_TABLE(logicalName,CollectionName)%>

• For logicalName -> get the Class ID string from Reference Guide
• for CollectionName -> go to Extension Definition and get the Collection Internal Name displayed there

Bogdan

[Private_Member_113126 · Accepted solution]

Hello Bogdan.

Thanks for your quick answer. I will try again and let you know if it works. Many thanks.

Best,
Heiko

[Private_Member_113126 · Accepted solution]

Hello Bogdan,

sorry for the late answer I was very busy. I don't find any match to the logical Name and the Collection Name which solves my issue

Nevertheless, thank you for your help I will close this thread.

Best,
Heiko

[former_member207877 · Accepted solution]

Hello Heiko,

Logical Name is nothing but the name which is under ID in reference guide in a class.

Please find below image for your reference.

see below example

FROM <%SCHEMA%>.FCI_CONTRACT T1

LEFT OUTER JOIN <%EXT_TABLE(contracts.Contract, collectionInternalName)%> T2

ON T1.OBJECTID = T2.PARENT_OBJECT_ID

Raj

[former_member13619 · Accepted solution]

Bogdan has explained everything very well.

Now my remark is that you should never use ".FCI_DYN_$2147483348" as you do as this is certainly not a best practice to directly refer to a dynamic table.

What do you mean with you cannot find any match with the logical name and the collection name?

As Bogdan has explained:

You can double-check the values for <%EXT_TABLE(logicalName,CollectionName)%>

• For logicalName -> get the Class ID string from Reference Guide
• for CollectionName -> go to Extension Definition and get the Collection Internal Name displayed there

The logical name you can find using the reference guide, look for the class or name:

Now look for the object you want to get the extension tables from:

Where in this example I have chosen Master Agreement, which is class 1004 and the logical name "contracts.Contract" (be sure you have the spelling correct with the capital letters as this is of the outmost importance)

Do also take in account that a contract document has a different class as well a sub-agreement.

Be sure the logical name you enter is indeed the object in which the extension or extension table has been created.

Now there are 2 different methods on getting extensions and the difference is if it is an extension (single field) or an extension table you have created (multiple fields in a table).

To refer to a single field (so not created as an extension collection) you could use this (only logical name):

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

for multiple fields in a table you have created (extension collection, use logical name and collection name)

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

Where MyTable is the Internal Name of your extension collection

If you would have to refer to as well extensions (single fields) and extension collections then you have to combine the statements.

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

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

You could use write it like this:

<.... previous statements ....> AND

<%EXT_TABLE(contracts.Contract)%> T5 AND

LEFT OUTER JOIN <%EXT_TABLE(contracts.Contract,MyTable)%> T6

WHERE

T5.PARENT_OBJECT_ID = T1.OBJECTID AND

T6.PARENT_OBJECT_ID = T1.OBJECTID

Where of course for the single fields (extensions) you use, in this example, T5 and for the extension collections (created as a table) T6.

This should work if you do it correctly and it is a better practice then referring directly to the dynamic tables as this would provide issues when you transport your query to another system as Bogdan has correctly mentioned.

Hope this answer helps you even more on the way.

Regards,

Erik