PickerReport

You can use the PickerReport element on ModelLinkMapping to specify another report to be used for the popup picker dialog (and autocomplete) on a model link filter field. A picker report implemented in this way can be used for any ModelLinkField (not just a report filter field).

When defining a report which will be used as a picker, you need to meet certain criteria. Specifically, you need to provide an ActionDef which specifies the PrimaryModelLevelType and IdField. For example, let's define a report for the "supplier" ModelLink field from ZBKS.Book. This field is a ModelLink to Organization.

When we define the report, we provide an ActionDef with PrimaryModelLevelType of Organization and IdField of SysOrgId. We also make sure that we have a RetrievialField for SysOrgId, with levelType and FieldName matching. Finally, we add the Name field, which is Natural Key part 0 of Organization. (SysId should be listed before the NK part 0 field in the Retrieval section.)

<Report>
  <Name>OrganizationPickerReport</Name>
  <SqlDef Name="OrgainzationPickerReport" GroupName="GroupSqls">
    SELECT SYS_ORG_ID, ORG_NAME
    FROM ORGANIZATION
    WHERE VC_ID = $MY_VC_ID$
    and ${filterIfNotNull:NAME,upper(org_name) like upper($NAME$)}
    ORDER BY ORGANIZATION.ORG_NAME ASC, ORGANIZATION.SYS_ORG_ID
  </SqlDef>
  <ActionDef>
    <PrimaryModelLevelType>Organization</PrimaryModelLevelType>
    <IdField>SysOrgId</IdField>
  </ActionDef>    
  <Filters bindSqlNulls="true">
    <CustomFilterField>
      <FieldRef category="PDF" levelType="Organization">
        <FieldName>Name</FieldName>
      </FieldRef>
      <Hidden>false</Hidden>
      <Editable>true</Editable>
      <Type>STRING</Type>
      <Optional>true</Optional>
      <SimpleMapping sqlName="NAME"/>
    </CustomFilterField>
  </Filters>
  <Retrieval>
    <RetrievalField levelType="Organization" category="PDF">
      <FieldName>SysOrgId</FieldName>
    </RetrievalField>    
    <RetrievalField levelType="Organization" category="PDF">
      <FieldName>Name</FieldName>
    </RetrievalField>
  </Retrieval>
</Report>

Please make sure you have SurrogateId fieldName and displayValue field name (which is NaturalKey part 0 field) in your retrieval fields. As field names should be same as of model fields, we are using <RetrievalField> instead of <CustomRetrievalField>.

Now, we can reference this PickerReport from our ModelLinkMapping:

<CustomFilterField>
	<FieldRef levelType="ZBKS.Book" category="MODEL_LINK">
		<ModelLinkTargetLevelType>Organization</ModelLinkTargetLevelType>
		<FieldName>Supplier</FieldName>
	</FieldRef>
	<Editable>true</Editable>
	<Type>MODEL_LINK</Type>
	<Optional>true</Optional>
	<ModelLinkMapping surrogateIdSqlName="SYS_ORG_ID" displayFieldSqlName="ORG_NAME" multiSelectMode="false">
		<PickerSize width="350" height="180"/>
		<PickerReport>
			<ReportName>ZBKS.OrganizationPickerReport</ReportName>
		</PickerReport> 
	</ModelLinkMapping>
</CustomFilterField>	

You will note that we did NOT provide an AutoCompleteSql or AutoCompleteSqlDefName. Platform is smart enough to automatically use the filter field for Natural Key part 0 (in this case, Organization.Name) in the picker report for autocomplete. So you don't need to replicate your report as an autocomplete query.

Also, you can customize the picker dialog by providing width and height to PickerSize element.
images/download/attachments/144835592/PickerReport-version-1-modificationdate-1645046795000-api-v2.png

It is possible to change the display value for a model link so that it doesn't match the 0th Natural Key Part.

For example, assume that in model PTA.MLNK0Model the 0th natural key is a model link to Enterprise. You can specifcy a "DisplayValueField" which could be the joined Enterprise name, or any other field retrieved in your SQL:

<ActionDef>    
   <PrimaryModelLevelType>PTA.MLNK0Model</PrimaryModelLevelType>
   <IdField>SysMLNK0ModelId</IdField>
   <DisplayValueField>EntName</DisplayValueField>
</ActionDef>
.....
<Retrieval>
.....
  <CustomRetrievalField>
      <FieldRef levelType="undefined" category="PDF">
         <FieldName>EntName</FieldName>
      </FieldRef>
   <Hidden>true</Hidden>
   <Type>STRING</Type>
   <SimpleMapping sqlName="ENT_NAME"/>
  </CustomRetrievalField>     
</Retrieval>   

In the above illustration, the additional retrieval field EntName will be used as the displayValue for the opening ModelLink field. Also, this field is marked as display value field by adding child element DisplayValueField under ActionDef .