Connection to External Database (Value Lists)

Video: Importing from Databases

You can set M-Files to update any object type or a value list to and from an external database.

The example below describes how to define an object type to use an external database connection to SQL Server. The steps for specifying an external database connection for a value list are very similar.

Steps

  1. Open M-Files Admin.
  2. In the left-side tree view, expand the desired connection to M-Files Server.
  3. In the left-side tree view, expand the document vault of your choice.
  4. Still in the left-side tree view, expand the Metadata Structure (Flat View) node.
  5. Select the Object Types node (or the Value Lists node if you are defining a value list to use an external database connection).
    The object type (or value list) listing is opened in the right pane.
  6. In the right-pane listing, right-click the object type (or value list) of your choice and select Properties from the context menu.
  7. Select the Connection to External Database tab.
    The Connection to External Database tab is opened.
  8. Enable the option Use a connection to an external database to import and modify objects that reside in the external database.
  9. Click the Define... button next to the OLE DB connection string (from server) field.
    The syntax of the connection string depends on the OLE DB (Object Linking and Embedding Database) supplier used for establishing the connection to the external database. If Open Database Connectivity (ODBC) is required to establish a connection, the data store has to be accessed over OLE DB and ODBC. For a list of recommended providers, see Provider Recommendations for External Database Connections.
    Note: M-Files Admin only displays OLE DB providers that are available on the computer running M-Files Admin. In case your M-Files Server resides on a different host, ensure that the selected OLE DB connection string works from the computer running M-Files Server as well.
    The Data Link Properties dialog is opened.
  10. On the Provider tab, select Microsoft OLE DB Provider for SQL Server from the list and click Next >>.
    The other providers may have slighlty different options on the Connection and Advanced tabs. The All tab contains all the available connection properties as a name–value table.
    The Connection tab of the Data Link Properties dialog is opened.
  11. To the Select or enter a server name field, input the name of your SQL Server.
  12. For the Enter information to log on to the server section, either:
    1. Select the Use Windows NT Integrated security.
      or
    2. Enter your credentials to the User name and Password fields, and check the Allow saving password check box.
  13. For the Select the database on the server section, either:
    1. Use the drop-down menu to select the database on the server you defined above.
      or
    2. Enter a database name to the Attach a database file as a database name field and use the ... button to select a Microsoft SQL Server Database (MDF) file.
  14. Optional: Click Test Connection to ensure that your database connection is working properly.
  15. Optional: On the Advanced tab, define a timeout period for the database connection.
  16. Click OK to close the Data Link Properties dialog.
    The dialog is closed, the Connection to External Database tab of the Objecty Type Properties dialog is active, and your newly defined connection string is added to the OLE DB connection string (from server) field (not included in the screenshot).
  17. Back on the Connection to External Database tab, enter the SELECT statement for retrieving properties from the database to the SELECT statement field.
    Examples of SELECT statements:

    SELECT CustomerNumber, CustomerName FROM Customer

    SELECT ID, Name + ' ' + Department FROM Company

    SELECT ID, Name, CustomerID FROM Contacts

    SELECT * FROM Customer

  18. Click the Refresh Columns button to fetch the data defined in your SELECT statement to the Columns listing.
    The Columns listing displays correspondences between columns fetched from an external database (Source Column) and document vault property definitions (Target Property).
  19. Map the Source Column properties with properties in your M-Files vault (listed in the Target Property column).
  20. Check the check boxes in the Update and Insert columns and define the four statements below the Columns listing according to the following table:
    If you want to... Complete the following steps:
    Allow read-only access Do not check any of the check boxes and leave the statements empty.
    Allow users to update but not create or delete information
    1. Check the check boxes in the Update column for the properties of your choice.
    2. Click the Default button next to the UPDATE statement field – or enter your own statements to the field.
    Allow users to update, create, and delete information
    1. Check the check boxes in the Update and Insert columns for the properties of your choice.
    2. Click the Default button next to the UPDATE, INSERT INTO, SELECT, and DELETE statement fields – or enter your own statements to these four fields.

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

    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 object 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 SQL Server databases, set the type of the ID column as identity. In Access databases, use an AutoNumber type column for IDs. Because Excel cannot produce new ID values, the INSERT INTO statement cannot be used with Excel.

    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.

    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 = ?
  21. Optional: Check the Disabled check box if you would like to temporarily disable the external database connection.
    If the connection is disabled, any information between the vault and the external database is not synchronized. The synchronization can be re-enabled at any time by unchecking this check box.
  22. Once you are done, click OK to close the dialog.

Results

The value list or objects of the object type are now updated to and from an external database according to your connection definition.
© M-Files Corporation 2016
Last updated 10/2016