Reusable Content for the Value Lists Section

External value lists can be manually refreshed via the metadata card by selecting a property that uses an external value list and clicking the Refresh icon in M-Files Desktop and in the classic M-Files Web.

  1. Optional: If the Mapping Type is Name or Title, specify the setting Use in Insert Operation and define the related statements.
    If you want to...Complete the following steps:
    Allow read-only access Set the Use in Insert Operation setting to No. Do not specify the statements in this table.
    Allow users to create but not update or delete information
    1. Set the Use in Insert Operation to Yes.
    2. Under Service-Specific Settings, in INSERT INTO statement, write the two statements in this table.
    Statement Definition Examples
    UPDATE When you edit an object in M-Files, M-Files Server edits the corresponding record in the external database using an UPDATE statement. Use a question mark (?) to signal columns to be updated.

    UPDATE Customers SET CustomerName = ? WHERE CustomerID = ?

    UPDATE Contact SET Name = ?, CustomerID = ? WHERE ContactID = ?

    INSERT INTO When you create a new value list item in M-Files, M-Files Server adds a corresponding record into the external database using an INSERT INTO statement. Use a question mark (?) to indicate the value of each column.
    Note: The INSERT INTO statement input to M-Files does not define a value for the ID column. The database should be set up to automatically provide an ID for new records. For example in Microsoft SQL Server databases, set the type of the ID column as identity. If the external database cannot produce new ID values, the INSERT INTO statement cannot be used.

    INSERT INTO Customers( CustomerName ) VALUES( ? )

    INSERT INTO ContactPersons( Name, CustomerID ) VALUES( ?, ? )

    Get ID SELECT After a new record has been created with the INSERT INTO statement, M-Files Server gets the ID of the newly created record with this SELECT statement.

    SELECT MAX( CustomerID ) FROM Customer

    DELETE When you delete an object from M-Files, M-Files Server deletes the corresponding record in the external database using a DELETE statement. Use a question mark (?) for the ID of the record to be deleted.

    DELETE FROM Customers WHERE CustomerID = ?

    DELETE FROM Contacts WHERE ContactID = ?

  2. Select the check boxes in the Insert column and define the two statements below the Columns listing.
    If you want to...Complete the following steps:
    Allow read-only access Do not select the check boxes and leave the statements empty.
    Allow users to create but not update or delete information
    1. Select the check boxes in the Insert column for the necessary properties.
    2. Click the Default button next to the INSERT INTO and SELECT statement fields. You can also enter your statements to the fields.

    The table below explains the use of the two statements mentioned above.

    Statement Definition Examples
    INSERT INTO When you create a new value list item in M-Files, M-Files Server adds a corresponding record into the external database using an INSERT INTO statement. Use a question mark (?) to indicate the value of each column.
    Note: The INSERT INTO statement input to M-Files does not define a value for the ID column. The database should be set up to automatically provide an ID for new records. For example in Microsoft SQL Server databases, set the type of the ID column as identity. If the external database cannot produce new ID values, the INSERT INTO statement cannot be used.

    INSERT INTO Customers( CustomerName ) VALUES( ? )

    INSERT INTO ContactPersons( Name, CustomerID ) VALUES( ?, ? )

    SELECT After a new record has been created with the INSERT INTO statement, M-Files Server gets the ID of the newly created record with this SELECT statement. After a new record has been created with the INSERT INTO statement, M-Files Server gets the ID of the newly created record with this SELECT statement.
  3. In Mapping Type, specify how the source column is mapped to M-Files.
    • To map a source column as the external ID, select Object ID.
    • To map the source column as the name of the value list item, select Name or Title.
    • To map the the source column as the ID of the owner value list item, select Owner.
    • To map the the source column as the ID of the value list's parent item, select Parent.
    For more information on value list hierarchy, see Value list hierarchy.
  4. Map the Source Column properties with properties in your M-Files vault (listed in the Target Property column).
    Note: When you want to map multiple values to a property of the Choose from list (multi-select) data type, the values must be recorded on their own rows in the external database. For example, if you want to map multiple values to the Industry property, the values must be recorded like this:
    ID Customer name City Industry Active
    ABC-123 ESTT Corporation New York 100 1
    ABC-123 ESTT Corporation New York 101 1
    ABC-123 ESTT Corporation New York 108 1

    In this case, however, data can only be read from, not recorded to the external database.