spMFCreateValueListLookupView

Return
  • 1 = Success

  • -1 = Error

Parameters
@ValueListName NVARCHAR(128)

Name of the valuelist

@ViewName NVARCHAR(128)

Name of view. example ‘vwCountry’

@Schema NVARCHAR(20)

Default = ‘dbo’ We recommend to set it as ‘custom’

@Debug SMALLINT = 0
  • Default = 0

  • 1 = Standard Debug Mode

  • 101 = Advanced Debug Mode

Purpose

To automatically create a view showing all the related columns for the specific valuelist

Additional Info

The view has the following standard columns:
  • Name_ValueListItems : name of the valuelist item

  • MFID_ValueListItems : M-Files internal id of the item

  • DisplayID_ValueListItems : M-files external (visible) id of the item

  • AppRef_ValueListItems : unique reference for item

  • GUID_ValueListItems : GUID if the item

  • OwnerName_ValueListItems : owner valuelist item

  • OwnerMFID_ValueListItems : internal id of owner - default to 0

  • OwnerAppRef_ValueListItems : unique reference for the owner

  • Name_ValueList : name of the valuelist

  • MFID_ValueList : Internal id of valuelist

  • ID_ValueList : SQL ID of the valuelist. This ID joins the valuelist and valuelist item tables

  • OwnerMFID_ValueList : Owner valuelist

  • Deleted : set to 1 if the valuelist item has been deleted

  • Process_ID : default is 0 this is used in processing the valuelist items

Warnings

Deleted items are not being pulled into the table when synchronising the valuelist items for the first time. However, the deleted flag will be updated for items that has been deleted after the initial synchronisation

Examples

EXEC dbo.spMFCreateValueListLookupView @ValueListName = 'Country',
@ViewName = 'vwCountry',
@Schema = 'custom',
@Debug = 0

Changelog

Date

Author

Description

2018-05-20

LC

Add error check that Valuelist exist

2017-12-10

LC

Add Schema

2017-07-25

AC

Update the join statement to fix error with ownership relationship

2017-05-12

LC

Add deleted = 0 as filter

2015-07-20

DEV2

New Logic implemented