Using the Legacy Database Connection for Value Lists

Before you start, take note of this information:

  • In SELECT FROM statements, the columns are selected in order of appearance. There is no relation between column and property names, which is why the order must be the same in the SELECT FROM and INSERT INTO statements, and SELECT FROM and UPDATE statements. Also, columns not used in the INSERT INTO and UPDATE statements must always be listed last in the SELECT FROM statement.
  • In the SELECT statement used to get the ID of the records, WHERE must refer to the first column of the SELECT FROM statement.
  1. Do the steps from 1 to 5 in Connections to External Databases for Object Types.
  2. In M-Files Admin, in the Connection to External Database tab of the Value List Properties dialog, select Legacy database connection.
  3. Click Configure.
    Result:The Configure Connection to External Database dialog is opened.
  4. Click the Define button next to the OLE DB connection string (from server) field.
    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.
    Note: M-Files Admin only displays OLE DB providers that are available on the computer running M-Files Admin. If your M-Files Server resides on a different host, make sure that the selected OLE DB connection string works from the computer running M-Files Server as well.
    Result:The Data Link Properties dialog is opened.
  5. On the Provider tab, select Microsoft OLE DB Driver for SQL Server from the list and click Next >>.
    Other providers can have different options on the Connection and Advanced tabs. The All tab contains all the available connection properties as a name–value table.
    Note: We do not recommend the use of Microsoft Access Database Engine Redistributables to import value lists or object types from an Excel file.
    Result:The Connection tab of the Data Link Properties dialog is opened.
  6. To the Select or enter a server name field, write the name of your Microsoft SQL Server.
  7. In the Enter information to log on to the server section, select:
    • Windows Authentication: Select this option to use a Microsoft Windows account for logging in. In this case, the connection uses the credentials that are used for running the M-Files Server service.
      or
    • SQL Server Authentication: Select this option to use a Microsoft SQL Server login. Enter the credentials in the User name and Password fields, and select the Allow saving password check box.
    Note: The ODBC driver does not support all special characters. If your connection uses ODBC, the values that contain special characters must be enclosed in curly brackets. The correct format is {username or password}.
  8. For the Select the database section, complete one of these steps:
    • Use the drop-down menu to select the database on the specified server.
      or
    • Enter a database name to the Attach a database file as a database name field and click the ... button to select a Microsoft SQL Server Database (MDF) file.
  9. Optional: Click Test Connection to make sure that your database connection operates correctly.
  10. Optional: On the Advanced tab, define a timeout period for the database connection.
  11. Click OK to close the Data Link Properties dialog.
    Result:The connection string is added to the OLE DB connection string (from server) field.
  12. In the Configure Connection to External Database dialog, to the SELECT statement field, enter 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

  13. Click Refresh Columns to get the source columns.
    Result:The Columns listing shows correspondences between columns retrieved from the external database (Source Column) and M-Files properties (Target Property).
  14. 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.

  15. 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.
  16. Click OK to close the Configure Connection to External Database dialog.
  17. Optional: Select the Disabled check box to temporarily disable the external database connection.

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

  18. Click OK to close the Object Type Properties dialog.
The object type is now updated to and from the external database.