2. Lookup views and metadata search¶
2.1. Overview¶
Use connector-supplied lookup views to simplify joins to value lists and workflow states, and apply SQL-first search patterns to avoid unnecessary round-trips to M‑Files. This page consolidates and supersedes the legacy “Lookup views” and “Metadata search” pages.
2.2. Lookup views¶
Special procedures are included in the connector to assist with the creation of value list item and workflow state lookup views. These procedures take input parameters and automatically create a view that can be used in applications. The benefit of the view is to replace having to use a join between MFValueList and MFValueListItems when applying a specific value list or workflow.
The following illustrates the two methods and their differences. The objective is to map an external source with the value list item table to get the value list item ID when inserting the record into the class table.
The long method:
SELECT vli.MFID, mc.Country FROM Stagingtable AS mc
INNER JOIN MFValueListItems vli
ON mc.Country = vli.name
INNER JOIN MFValueList vl
ON vl.id = vli.MFValueListID
WHERE vl.name = 'Country'
The short method:
SELECT vc.MFID_ValuelistItems, mc.Country FROM Stagingtable AS mc
INNER JOIN custom.vwCountry vc
ON mc.Country = vc.Name_ValueListItems
spMFCreateValueListLookupView is used to create a lookup for a specific value list.
EXEC dbo.spMFCreateValueListLookupView @ValueListName = 'Country',
@ViewName = 'vwCountry',
@Schema = 'custom',
@Debug = 0
spMFCreateWorkflowStateLookupView is used to create a lookup for a specific workflow.
EXEC dbo.spMFCreateWorkflowStateLookupView @WorkflowName = 'Contract Approval Workflow',
@ViewName = 'vwContractApproval',
@Schema = 'custom',
@Debug = 0
2.3. Metadata search¶
Metadata searches can be performed in SQL on the Class Tables or directly into M‑Files. By keeping the metadata in the Class Tables up to date, one does not have to access M‑Files directly for a search.
However, searches can be performed on M‑Files with the Connector procedures.
There are two type of search methods available.