Connections to External Databases for Value Lists

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

This section tells you how to define value lists to use an application connection to an external database. To use the legacy database connection, 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 replication scheme. Make sure that the necessary configuration changes are also made to each vault.

Before you set up the application connection to an external database, make sure that:
  • You have an external object type connector installed and enabled. If you use the Ground Link service, the connector must be enabled on the Ground Link proxy. If you use a local service, the connector must be enabled in the vault.
    • By default, M-Files OLE DB External Object Type Connector is installed but disabled. To use M-Files OLE DB External Object Type Connector, a license is not necessary.
    • For instructions on adding connectors and managing vault applications, see Adding a Connector and Installing and Managing Vault Applications.
  • The connector supports the application connection.

To use a connection to external database:

  1. Open M-Files Admin and go to a vault.
    1. Open M-Files Admin.
    2. In the left-side tree view, expand a connection to M-Files Server.
    3. In the left-side tree view, expand Document Vaults, and then expand a vault.
  2. In the left-side tree view, expand Metadata Structure (Flat View), and select Value Lists.
    Result:The value list listing is opened in the right pane.
  3. In the right-pane listing, double-click the value list.
    Result:The Value List Properties dialog is opened.
  4. Go to the Connection to External Database tab and enable the option Use a connection to an external database to import and modify objects that reside in the external database.
  5. Select Application connection.
  6. In Service, select the service.
    OptionDescription
    M-Files OLE DB at vault <name of the vault> Select this option to use the local service.
    M-Files OLE DB from Ground Link proxy <name of the Ground Link proxy> Select this option to establish the connection using a remote service via Ground Link.

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

    Result:Information of the service configuration with possible errors is shown.
  7. Click Configure.
    Result:The External Object Type Connector dialog is opened.
  8. Expand Service-Specific Settings > Connection to External Database.
  9. In Provider, select the provider for the external database connection.
    Example:Microsoft OLE DB Driver for SQL Server.
    Note: The list of providers shows all the available providers on the server machine that runs the external object type connector. Thus, it can include providers 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 to create the connection, the data store must be accessed over OLE DB and ODBC. For a list of recommended providers, see Provider Recommendations for External Database Connections.
  10. Optional: If you selected Custom provider (manual configuration), in Custom provider, specify the provider.
  11. Under Connection to External Database, complete one of these steps:
    OptionDescription
    Specify the other settings.

    The correct values are different for each provider and external database.

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

    In Advanced Options, enter the connection string. Use this option if it is not possible to use the other settings with the selected provider. When you enter the connection string, make sure that all values are correctly enclosed and the connection string has the necessary formatting.
  12. Optional: Under Optional SELECT Statements, define the settings.
    Note: The application connection does not support the Connections to External Databases settings in the Advanced Vault Settings section in M-Files Admin. To use these settings, define them here.
  13. Under Service-Specific Settings, in SELECT Statement, write the SELECT statement for getting source columns from the external database.
    Examples of SELECT statements:

    SELECT CustomerNumber, CustomerName FROM Customer

    SELECT ID, Name + ' ' + Department FROM Company

    SELECT ID, Name, CustomerID FROM Contacts

    SELECT * FROM Customer

    Tip: You can write a multi-line statement.
  14. Click Save to get the source columns.
  15. 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.
  16. In the dialog that is opened, click OK.
    Result:The dialog closes and the configuration refreshes. The Column Mappings section shows the source columns that your SELECT statement returned from the external database.
  17. Go to Column Mappings and expand a source column node.
  18. 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.
  19. 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
    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

  20. Repeat the steps from 17 to 19 for all the necessary source columns.
  21. Under General Settings, set Enabled to Yes.
    Tip: You can enable and disable the external database connection also with the Disabled check box on the Connection to External Database tab of the Value List Properties dialog.

    If the connection is disabled, information between the vault and the external database is not synchronized.

  22. 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.
  23. Click OK to close the Value List Properties dialog.
The value list is now updated to and from the external database.