Valuelist and Workflow Management

Scope

Controlled creation, update, lookup optimization and structural evolution of value lists, workflow states and their aliases.

Core Procedures

spMFSynchronizeValueListItemsToMFiles spMFInsertValueListItems spMFCreateValueListLookupView spMFCreateWorkflowStateLookupView spMFAliasesUpsert

Primary Tables

MFValueList / MFValueListItems MFWorkflow / MFWorkflowState

Developer Accelerators

Lookup View Generation (value list):

EXEC spMFCreateValueListLookupView @ValueListName='Country', @ViewName='vwCountry', @Schema='custom';

Lookup View Generation (workflow):

EXEC spMFCreateWorkflowStateLookupView @WorkflowName='Contract Approval Workflow', @ViewName='vwContractApproval', @Schema='custom';

Valuelist Item Upsert Flow

  1. Stage item changes in MFValueListItems (Name, MFValueListID, OwnerID, process_id=1 for insert, 2 delete).

  2. Execute synchronization

    EXEC spMFSynchronizeValueListItemsToMFiles;

  3. On rename: update Name in table + process_id=1.

  4. Validate in class tables after next object version update.

Ownership & Elevation

Use MFvwMetadataStructure columns IsObjectType + Owner_* to detect when a valuelist should be elevated or when cascaded deletions would break referential integrity.

Alias Strategy

Run spMFAliasesUpsert idempotently after adding new lists / workflows to stabilize integration scripts.

Troubleshooting

Duplicate names introduced via SQL

  • Pre-check uniqueness per (MFValueListID, Name) before staging inserts.

OwnerID change needed

  • M-Files restriction: delete + recreate; plan migration script to remap dependent objects.

Gaps / Planned

  • Add complex ownership relationship examples (multi-level) (GAP in functional mapping).

  • Provide automated diff script template for valuelist state drift.

Value List Item Synchronization (Migrated)

This section consolidates and refines the prior standalone page “Update / Create Valuelist Items from SQL”.

Procedure: spMFSynchronizeValueListItemsToMFiles

Use Cases

  • Initial data take-on (bulk seed of controlled vocabularies).

  • Ongoing master data maintenance sourced from an external system.

  • Periodic curation (rename / retire obsolete items).

Staging Rules

All changes are first expressed in MFValueListItems:

  • Insert new item: set Name, MFValueListID (value list ID, not MFID), OwnerID (or 0), process_id = 1.

  • Rename existing item: update Name, set process_id = 1.

  • Delete item: set process_id = 2 (logical removal in vault on sync).

Execution

Synchronize after staging

EXEC dbo.spMFSynchronizeValueListItemsToMFiles @Debug = 0;

Return: 1 on success.

Validation Checklist

  • Confirm value list exists (create in M-Files Admin if new, then refresh metadata sync).

  • Ensure user permissions allow list modification.

  • Pre-validate uniqueness: no duplicate Name within same MFValueListID.

Constraints & Nuances

  • OwnerID can be set on insert but not changed later (must delete + recreate and remap usages).

  • M-Files does not enforce unique names—SQL process must.

  • Renamed labels propagate to vault immediately; class table label columns reflect change only after object version update + table sync.

Quick Reference Table

Operation mapping

Intent

Table Action

process_id

Insert

Insert row with Name, MFValueListID, OwnerID

1

Rename

Update Name

1

Delete

Update row (mark for deletion)

2

Example Minimal Sequence

-- Stage new item
INSERT INTO dbo.MFValueListItems (Name, MFValueListID, OwnerID, Process_ID)
VALUES (N'Zeta Region', 42, 0, 1);

-- Rename existing item
UPDATE dbo.MFValueListItems
  SET Name = N'North-East', Process_ID = 1
  WHERE MFID = 355;

-- Retire obsolete item
UPDATE dbo.MFValueListItems
  SET Process_ID = 2
  WHERE MFID = 201;

-- Apply
EXEC dbo.spMFSynchronizeValueListItemsToMFiles;

Operational Tips

  • Batch large change-sets; commit in manageable chunks (e.g. 5k items) before sync to reduce lock duration.

  • Capture a diff snapshot (pre/post item counts and hash of concatenated names) for audit.

  • Consider a wrapper proc to enforce naming conventions (no leading/trailing spaces, canonical casing).

Warnings

Note

Ensure value list metadata refresh (spMFInsertValueListItems or standard metadata sync) after structural edits like owner changes before staging further item operations.

Lookup Views (Consolidated from ‘Lookup views’)

Special procedures assist with creating focused lookup views for value list items or workflow states, replacing repeated joins to MFValueList + MFValueListItems in downstream queries.

Long form join example:

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';

Short form using generated lookup view:

SELECT vc.MFID_ValuelistItems, mc.Country
FROM Stagingtable AS mc
INNER JOIN custom.vwCountry vc ON mc.Country = vc.Name_ValueListItems;

Create value list lookup view:

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

Create workflow state lookup view:

EXEC dbo.spMFCreateWorkflowStateLookupView
   @WorkflowName = 'Contract Approval Workflow',
   @ViewName     = 'vwContractApproval',
   @Schema       = 'custom',
   @Debug        = 0;

Guidance

  • Prefix custom schema (e.g. custom) to isolate generated artifacts.

  • Regenerate views after state or item renames to avoid stale label columns.

  • Consider adding an index on the Name column in staging tables to speed join resolution.