ModelLink Filtering
Another common requirement is to be able to filter based on a ModelLink value. Let's allow the user to filter on the Supplier field of the Book. You may remember from the "Customizing Permissions for a Model" section of this tutorial that this is a ModelLink to Organization.
Before we start filtering, let's retrieve the organization information into the report. Because only the organization id is stored on the ZBKS_BOOK, we must join to the ORGANIZATION table in our query to get the supplier organization name. Note that we need to add "ORGANIZATION.ORG_NAME SUPPLIER_NAME" in the Select statement in addition to the join.
select SYS_BOOK_ID, TITLE, ORGANIZATION.ORG_NAME SUPPLIER_NAME, ... from ZBKS_BOOK left outer join ORGANIZATION on ORGANIZATION.SYS_ORG_ID = ZBKS_BOOK.SYS_SUPPLIER_ID ...Then let's add a simple STRING retrieval field to show this org name in the UI:
<CustomRetrievalField> <FieldRef levelType="Undefined" category="PDF"> <FieldName>Supplier</FieldName> </FieldRef> <Type>STRING</Type> <SimpleMapping sqlName="SUPPLIER_NAME"/> </CustomRetrievalField>
Now that we can see the supplier information, let's add a ModelLink filter. This will allow the user to auto-complete the ModelLink value, just as they would in any other ModelLink field through the UI.
First, add a portion to the where clause of your query which matches an incoming $SUPPLIER_ORG_ID$ with the SUPPLIER_ID of the Book. If you already have something in your where clause, you should connect them with AND. Note also that we're using the filterIfNotNull macro again so if the user omits the value, it will be omitted from the WHERE clause.
select SYS_BOOK_ID, TITLE, ORGANIZATION.ORG_NAME SUPPLIER_NAME, ... from ZBKS_BOOK left outer join ORGANIZATION on ORGANIZATION.SYS_ORG_ID = ZBKS_BOOK.SYS_SUPPLIER_ID where ${filterIfNotNull:TITLE, UPPER(TITLE) like UPPER($TITLE$) || '%'} and ${filterIfNotNull:SUPPLIER_ID, ORGANIZATION.SYS_ORG_ID = $SUPPLIER_ID$}Next, add a CustomerFilterField of type MODEL_LINK. Make sure the ModelLinkTargetLevelType is Organization, and the ModelLinkMapping references SUPPLIER_ID as the surrogateIdSqlname.
<CustomFilterField> <FieldRef category="PDF" levelType="ZBKS.Book"> <ModelLinkTargetLevelType>Organization</ModelLinkTargetLevelType> <FieldName>Supplier</FieldName> </FieldRef> <Type>MODEL_LINK</Type> <ModelLinkMapping surrogateIdSqlName="SUPPLIER_ID" displayFieldSqlName="SUPPLIER_NAME"/> </CustomFilterField>Once this is in place, the user can type in the org name to the filter, auto-complete, and fine the appropriate matches.
