A ModelLink field references (or "links to") another model. In the Bookstore example, SalesRep is a ModelLink field on Book which references the User model. By configuring a field to be a ModelLink, the database table has a column whose foreign key points to the surrogate id of the referenced table.

There are three different ways in which you can configure a ModelLink filter: single-select, multi-select and wild card.
images/download/attachments/144835578/mlTypes-version-1-modificationdate-1645046018000-api-v2.jpg

We will discuss all three filtering techniques below, as well as retrieval.

Single-Select, Exact-Match Filtering

The most common way of filtering using a ModelLink field is to allow the user to provide a single value in the ModelLink auto-complete/pop-up "picker" field. For example, given the following SQL:

and ${filterIfNotNull:VAR_SYS_USER_ID,users.sys_user_id = $VAR_SYS_USER_ID$}

We would provide the following filter definition:

<CustomFilterField>
  <FieldRef levelType="ZBKS.Book" category="PDF">
    <ModelLinkTargetLevelType>User</ModelLinkTargetLevelType>
    <FieldName>Sales Representative</FieldName>
  </FieldRef>
  <Type>MODEL_LINK</Type>
  <ModelLinkMapping surrogateIdSqlName="VAR_SYS_USER_ID" displayFieldSqlName="VAR_SALES_REP">
    <AutoCompleteSql>
SELECT users.sys_user_id, users.user_name
FROM users
WHERE UPPER(users.user_name) LIKE UPPER ($TYPED_VALUE$) || '%'
    </AutoCompleteSql>
  </ModelLinkMapping>
</CustomFilterField>

Please note the following key points:

The FieldRef should contain a ModelLinkTargetLevelType which references the linked Model. In this case: User.
Type is set as MODEL_LINK.
ModelLinkMapping is defined, with mappings for the surrogateIdSqlName and displayFieldSqlName. You may choose to filter by either, but typically the surrogate id will be used, as the display name may not be globally unique.
An AutoCompleteSql may optionally be provided to override the default auto-complete behavior of the field. This is particularly useful when you want to apply additional implicit filtering of the linked model table. You can also override the pop-up report behavior, see the "Picker Report" section for more details.

Multi-Select, Exact-Match Filtering

You can configure your ModelLink filter to support multiple selection. Consider this example:

and ${filterIfNotNull:VAR_SYS_USER_ID,users.sys_user_id in $VAR_SYS_USER_ID$}

...
<ModelLinkMapping multiSelectMode="true" surrogateIdSqlName="VAR_SYS_USER_ID" displayFieldSqlName="VAR_SALES_REP"/>
...

By providing the attribute multiSelectMode="true", it will allow the user to select multiple Users in the ModelLink filter field, and by using the "in" keyword in our query we can ensure we join properly to the USERS table against all the supplied values.

Wildcard Filtering

You can also allow the user to type a non-exact match for the model link field. It will still attempt to auto complete, but if the user wants to match using a pattern like "Test*", they are not required to choose an auto-complete option.

and ${filterIfNotNull:VAR_SALES_REP,users.user_name like $VAR_SALES_REP$ || '%'}  

...
<ModelLinkMapping exactMode="false" surrogateIdSqlName="VAR_SYS_USER_ID" displayFieldSqlName="VAR_SALES_REP"/>
...

By providing the attribute exactMode="false", it will allow the user to provide a wild card expression in the UI. Then we filter using the displayFieldSqlName (VAR_SALES_REP) instead of the surrogate id to allow us to match multiple values.

Retrieval

When displaying a MODEL_LINK field, you should include the surrogate id and the display column in the select clause. By providing both values, it empowers the UI to show a display name which is hyper linked to the detail page for that Model.

You will typically need to join to the referenced table in order to get the display field. In our example, ZBKS_BOOK.SYS_SALES_REP_ID is the surrogate id and USERS.USER_NAME is the display column. We need to join the ZBKS_BOOK table and the USERS table on the surrogate id of the USERS table.

select sys_sales_rep_id SYS_SALES_REP_ID, users.user_name SALES_REP from ZBKS_BOOK outer join USERS on USERS.sys_user_id = sys_sales_rep_id

The retrieval should be defined as follows:

<CustomRetrievalField>
	<FieldRef levelType="ZBKS.Book" category="PDF">
		<ModelLinkTargetLevelType>Users</ModelLinkTargetLevelType>
		<FieldName>Sales Representative</FieldName>
	</FieldRef>
	<Type>MODEL_LINK</Type>
	<ModelLinkMapping surrogateIdSqlName="SYS_SALES_REP_ID" displayFieldSqlName="SALES_REP"/>
</CustomRetrievalField>

Please note the following key points:

FieldRef@levelType points to ZBKS.Book, which is where the model link field is defined.
Type is set as MODEL_LINK
ModelLinkMapping is defined in the CustomRetrievalField
ModelLinkMapping is used to map the surrogate id field to the surrogateIdSqlName attribute and display field to the displayFieldSqlName attribute