Tips and tricks on using the connector

The Connector is designed to be applied in many different ways for a range of different applications. The following list highlights some of these applications.

Multi-class

Sometimes you need to look up or work with an object across multiple classes. There are various ways to achieve this.

1. a) Create a view with a UNION across all class tables that need to be included in the multi-class table.

2. b) Use the procedure spMFObjectTypeUpdateClassIndex to update the table MFObjectTypeToClassObject. This will create an index for all the records included in the application.

Reset SQL class table

In various scenarios it may be necessary to realign the class table with M-Files. For instance, you may have update the incorrect columns or incorrect records in SQL and need to re-instate the records prior to updating it in M-Files. If you are working in batch mode then simply delete the error records in the SQL table and perform an update from M-Files.

However, if you have changed the metadata structure of the object in M-Files, it is good practice to drop the class table and recreate it. This will ensure that the table design stays intact.

Dynamically add an additional property to a class table when inserting/updating records

The connector will automatically add an additional property to the class table when updating the class table from M-Files to SQL with the following in place:

• The property was created in M-Files

• spMFDropAndUpdateMetadata was processed after the property is created and before the update in the Connector is processed

When updating objects from SQL to M-Files, and adding or using an ad hoc property on the object, the following should be taken into account.

• Create the property in M-Files and select spMFDropAndUpdateMetadata to pull through the property into the Connector. The property should update in the MFProperty Table.

• If it is an additional property on a class and not defined on the metadata card, it will not automatically be added to the class table.

• If a test value is added to one object in the class and the property value is not null, and the class table is updated from M-Files to SQL, the property will automatically be added in the class table at the end of the list of columns.

• To add the property in the class table without first refreshing data from M-files with values in this property, care should be taken to:

• Add the property as a column in the class table with the column name and datatype defined in the MFProperty table and datatype as per MFDataType table

• If a lookup property is added then two columns must be added, one for the property and another for the property_id.

• Add an entry in the MFClassProperty table for the property to associate the column with the class, and set the rules for handling of the additional property.

• The class and property id in the MFClassProperty table reference the ID (not the MFID) in the MFProperty and MFClass tables. Set Required = 0 and IsAdditional = 0. Set RetainIfNull = 1 if the update from SQL to M-Files should add the additional property to all objects in the class. If this column is set to 0 then the property will only be added to the metadata card of an object if the property has a value.

View of value list items by class

Use the following SELECT statement and joins to show value list items for value lists per class with the related property.

This view is useful when analysing the use of value lists and value list items across multiple classes, especially documents.

Execute Procedure

SELECT  mvli.ID ,
        [mvli].[Name] ,
        [mvli].[MFID] ,
        [mvli].[AppRef] ,
        mvli.[Owner_AppRef] AS OwnerAppRef ,
        mvl.[Name] AS Valuelist ,
        mvli2.ID AS OwnerID ,
        [mvli2].[Name] OwnerValuelistitem ,
        mvl2.[Name] AS OwnerValuelist ,
        mp.[Name] AS PropertyName ,
        mc.Name AS classname
FROM    [dbo].[MFValueListItems] AS [mvli]
        INNER JOIN [dbo].[MFValueList] AS [mvl] ON [mvl].[ID] = [mvli].[MFValueListID]
        INNER JOIN [dbo].[MFValueListItems] AS [mvli2] ON [mvli2].[AppRef] = [mvli].[Owner_AppRef]
        INNER JOIN [dbo].[MFValueList] AS [mvl2] ON [mvl2].[ID] = [mvli2].[MFValueListID]
        INNER JOIN [dbo].[MFProperty] AS [mp] ON [mp].[MFValueList_ID] = [mvl].[ID]
        INNER JOIN [dbo].[MFClassProperty] AS [mcp] ON [mp].ID = mcp.[MFProperty_ID]
        INNER JOIN [dbo].[MFClass] AS [mc] ON mc.ID = mcp.[MFClass_ID]
WHERE   [mvli].deleted = 0;

View of value list items by owner value list item

Use the following SELECT statement to show value list items by their owner value list items.

This view is useful when analysing the use of valuelistitems where the items are owned by another. For instance each Financial Year has their own financial periods; or Each County/State has their own cities.

Execute Procedure

 SELECT  mvli.ID ,
        [mvli].[Name] ,
        [mvli].[MFID] ,
        [mvli].[AppRef] ,
        mvli.[Owner_AppRef] AS OwnerAppRef ,
        mvl.[Name] AS Valuelist ,
        mvli2.ID AS OwnerID ,
        [mvli2].[Name] OwnerValuelistitem ,
        mvl2.[Name] AS OwnerValuelist
FROM    [dbo].[MFValueListItems] AS [mvli]
        INNER JOIN [dbo].[MFValueList] AS [mvl] ON [mvl].[ID] = [mvli].[MFValueListID]
        INNER JOIN [dbo].[MFValueListItems] AS [mvli2] ON [mvli2].[AppRef] = [mvli].[Owner_AppRef]
        INNER JOIN [dbo].[MFValueList] AS [mvl2] ON [mvl2].[ID] = [mvli2].[MFValueListID]
WHERE   [mvli].[OwnerID] <> 0;

Renaming a property related to a value list

After renaming a property in M-Files Admin the metadata needs to be refreshed. Use the MFSQL Manager Web App Operations > Metadata refresh. Select Properties in the drop-down and select Refresh metadata.

This will change the name of the property in the MFProperty table. It will also change the ColumnName in MFProperty table and change the ColumnsNames of all existing class tables. Note that the change of name of the column in the class table will only come into effect on the next table update with update method 1 or 2.

If you are already using the property in any custom procedures, reports, select statements etc, then you will have to manually adjust your custom procedures to align with the new columnname.

Showing the objects that were updated using the history table

The MFUpdateHistory table has a record of the result of every update. Refer to Utility Tables for more detail on the columns in the MFUpdateHistory table. The column object version details include all the object versions from M-Files at the time of the update transaction in XML format. Use spMFHistoryShow with @updatecolumn = 1 to return a list of the records that is included in the update.

DECLARE @id INT;
SELECT TOP 1 @id = [muh].[Id]
FROM [dbo].[MFUpdateHistory] AS [muh]
ORDER BY [muh].[Id] DESC;

EXEC [dbo].[spMFUpdateHistoryShow]
   @Debug = 0,
   @Update_ID = @id,
   @UpdateColumn = 1;

Getting deleted objects

When spMFUpdateTable is executed with the MFLastModified parameter then this procedure does not check for records that is deleted in M-Files. The same applies when spMFUpdateTableWithLastModifiedDate is executed.

In those cases where it is important to ensure that deleted records in M-Files are updated in SQL and it is important to use the MFLastModified date as a parameter in the update routine then the following steps can be followed to update the deleted records in the class table

This can be achieved by executing the spMFTableAudit procedure.

Helper procedures when iterating through the configuration of the vault and the connector

To reset the metadata: use spMFDropAndUpdateMetadata rather than spMFSynchronizeMetadata.

To delete all the class tables and start over again, use spMFDropAllClassTables.

To recreate all class tables: set IncludeInApp = 1 in MFClass for the desired tables to be created and then use spMFCreateAllMFTables.