Update / Create Valuelist Items from SQL

spMFSynchronizeValueListItemsToMFiles

The connector allows for creating or updating valuelist items from SQL to M-Files. This is process could apply as part of data take on or data management operations, or be built into the processes when third party data is integrated into M-Files.

Updating creating a valuelist item

Follow these steps to update Valuelist items from SQL into M-Files

1. Determine which valuelist items should be updated:

   1. There are new items to be added, possibly derived from the external data source.

   2. Change to the name of an item as a result of some data analysis in the valuelist item table

   3. Valuelist tems that must be deleted that is no longer required.

2. All updates or inserts take place in MFValuelistItems table

   1. An existing item to be updated: use update procedure to change the name, and set process_id = 1

   2. An existing item to be deleted: use update procedure to set process_id = 2

   3. A new item

      1. Determine the ID of the valuelist in question.

      2. Create the valuelist in M-Files and synchronise valuelists If the valuelist have not yet been created

      3. Use insert statement to set the following columns

         1. Name

         2. MFValueListID (note that this is the ID of the valuelist. Do not use the MFID of the valuelist )

         3. OwnerID: Set to MFID of the owner object. Set to 0 if there is no ownership relationship.

         4. Set process_id = 1

3. Execute spMFSynchronizeValueListItemsToMFiles

Type	Description
Procedure Name	spMFSynchronizeValueListItemsToMFiles
Inputs	Debug: 1 = Debug Mode; 0 = No Debug (default)
Outputs	1 = success

Execute Procedure

EXEC [dbo].[spMFSynchronizeValueListItemsToMFiles]
   @Debug = 0 -- smallint

OR

Execute Procedure

EXEC [dbo].[spMFSynchronizeValueListItemsToMFiles]

Warning

Valuelist settings

• The valuelist must allow users to update it to run this procedure.

• The valuelist must be created in M‑Files Admin or pre‑exist.

• Refresh valuelist metadata synchronization if changes are made in M‑Files (e.g., owner changes).

• M‑Files does not enforce unique names within a valuelist; ensure no duplicates when inserting items via SQL.

• The OwnerID of an item can be added but not changed. Delete the unwanted item and create a new one if ownership must change (migrate existing data as needed).

• When item labels are changed in SQL, M‑Files updates immediately, but SQL class table labels update only after the object version changes and the table is refreshed.