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