Connections to External Databases for Value Lists (2023)

You can set M-Files to update value liststo and from an external database.

This section tells you how to define value lists to use anapplication connection to an external database. To use the legacy databaseconnection, see the section "Using the Legacy Database Connection for Value Lists".

Note: It is not possible to include the configuration for External Object Type Connector to replication packages.

If you use replication and application connection to an external database service,you must configure the connection separately for each vault in the replicationscheme. Make sure that the necessary configuration changes are also made to eachvault.

Before you set up the application connection to an external database, make surethat:

  • You have an external object type connector installed and enabled. If you use the Ground Link service, the connectormust be enabled on the Ground Link proxy. If youuse a local service, the connector must be enabled in the vault.
    • By default, M-FilesOLE DB External Object TypeConnector is installed but disabled. To use M-FilesOLE DB External Object TypeConnector, a license is not necessary.
    • For instructions on adding connectors and managing vaultapplications, see Adding a Connector and Installing and Managing Vault Applications.
  • The connector supports the application connection.

To use a connection to external database and to open the service configuration:

  1. Open M-Files Admin and go to a vault.
    1. Open M-Files Admin.
    2. In the left-side tree view, expand an M-Files server connection.
    3. Expand Document Vaults.
    4. Expand a vault.
  2. Expand Metadata Structure (Flat View).
  3. Click Value Lists.

    Result:The value list listing is opened in theright pane.

  4. In the right-pane listing, double-click the value list.

    Result:The Value List Properties dialog isopened.

    (Video) Get ALL External Links with This SIMPLE Excel TRICK (as NEVER seen before!)

  5. Go to the Connection to External Database tab and enablethe option Use a connection to an external database to import andmodify objects that reside in the external database.
  6. Select Application connection.
  7. In Service, select the service.
    M-FilesOLE DB at vault <name of thevault>Select this option to use the local service.
    M-FilesOLE DB from Ground Link proxy <name ofthe Ground Linkproxy>Select this option to establish the connection with a remote service through Ground Link.

    The services that have the (OK) suffix, have aconfiguration for the value list.

    Result:Information of the service configuration with possible errors is shown.

  8. Click Configure.

    Result:The External Object Type Connectordialog is opened.

To specify the connection settings and to get the sourcecolumns:

  1. Expand Service-Specific Settings> Connection to External Database.
  2. In Provider, select the provider for theexternal database connection.

    Example:Microsoft OLE DB Driver for SQLServer.

    Note: The list of providers shows all the available providers on the servermachine that runs the external object type connector. Thus, it can includeproviders that cannot be used in external database connections.

    The syntax of the connection string is different for each Object Linking and Embedding Database (OLE DB)supplier used for establishing the connection to the external database. If Open Database Connectivity (ODBC) is necessary tocreate the connection, the data store must be accessed over OLE DB and ODBC. For a list ofrecommended providers, see Provider Recommendations for External Database Connections.

  3. Optional: If you selected Custom provider (manualconfiguration), in Customprovider, specify the provider.
  4. Under Connection to External Database,complete one of these steps:
    Specify the other settings.

    The correct values are different for each provider and externaldatabase.

    For more information, select a setting and see the Info tab.

    In Advanced Options, enter theconnection string.Use this option if it is not possible to use the other settings withthe selected provider. When you enter the connection string, make surethat all values are correctly enclosed and the connection string has thenecessary formatting.
    (Video) No API, No Problem: A recipe for connecting to external databases
  5. Optional: Under Optional SELECT Statements, definethe settings.

    Note: The application connection does not support the Connections toExternal Databases settings in the Advanced Vault Settings section in M-Files Admin. To use these settings, define themhere.

  6. Under Service-Specific Settings, inSELECT Statement, write the SELECT statement for getting source columns from theexternal database.

    Examples of SELECTstatements:

    SELECT CustomerNumber, CustomerName FROMCustomer

    SELECT ID, Name + ' ' +Department FROM Company

    SELECT ID, Name,CustomerID FROM Contacts

    SELECT * FROMCustomer

    Tip: You can write a multi-line statement.

    (Video) Visio: Using the Database Wizard to Connect Excel Data to Visio Shapes

  7. Click Save.
  8. Optional: To configure a remote service:
    1. Click Apply.

      Result:The Enter Password dialog is opened.

    2. Enter the password that is defined in the Ground Link proxy configuration.
    3. Click OK.
  9. To authenticate the common user, open the Dashboard tab and click Authenticate.

    Result:The Log In dialog isopened.

  10. Enter the common user credentials and click LogIn.

    Note: The ODBC driver doesnot support all special characters. If your connection uses ODBC, the values that contain special charactersmust be enclosed in curly brackets. The correct format is{username or password}.

  11. In the dialog that is opened, click OK.
  12. Click Save to get the sourcecolumns.
    1. Optional: To configure a remote service, repeat step 16.

    Note: You cannot save the configuration if there are no changes.

    Result:The dialog closes and the configuration refreshes. On the Configuration tab, the Column Mappings section shows the source columns that your SELECT statement returned from the externaldatabase.

To map the source columns with M-Filesproperties:

  1. On the Configuration tab, go to Column Mappings and expand a source columnnode.
  2. 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.

    (Video) SharePoint 2013 External List from Oracle database - part II
  3. 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 = ?

  4. Repeat the steps from 21to 23 for all the necessary source columns.

To take the configuration into use:

  1. Under General Settings, set Enabled to Yes.

    Tip: You can enable and disable the external database connectionalso with the Disabled check box on theConnection to External Database tab of theValue List Properties dialog.

    If the connection is disabled, informationbetween the vault and the external database is notsynchronized.

  2. Optional: To configure a remote service:
    1. Click Apply.

      Result:The Enter Password dialog is opened.

    2. Enter the password that is defined in the Ground Link proxy configuration.
    3. Click OK.
  3. Click OK to close the Value List Properties dialog.
The value list is now updated to and from the externaldatabase.


1. How to connect with an external MS SQL database in OutSystems?
(OutSystems How-Tos)
2. Create SMART Drop Down Lists in Excel (with Data Validation)
(Leila Gharani)
3. Connect Power BI to SharePoint List, SharePoint Folder, SharePoint Excel File 🔌
(Avi Singh - PowerBIPro)
4. Connecting to external databases, Active Directory and LDAP
5. How to connect external Database with Thingworx #Thingworx #MSSQL #Postgres #IoT
(Learn IoT)
6. How to display SQL/Database content in pages built with Elementor | WordPress Tutorial
(Programming Tutorials)
Top Articles
Latest Posts
Article information

Author: Arline Emard IV

Last Updated: 04/28/2023

Views: 6065

Rating: 4.1 / 5 (72 voted)

Reviews: 95% of readers found this page helpful

Author information

Name: Arline Emard IV

Birthday: 1996-07-10

Address: 8912 Hintz Shore, West Louie, AZ 69363-0747

Phone: +13454700762376

Job: Administration Technician

Hobby: Paintball, Horseback riding, Cycling, Running, Macrame, Playing musical instruments, Soapmaking

Introduction: My name is Arline Emard IV, I am a cheerful, gorgeous, colorful, joyous, excited, super, inquisitive person who loves writing and wants to share my knowledge and understanding with you.