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 one need to have the ability to lookup or work with a object across multiple classes. There is various ways to achieve this.

1. a) create a view with a union statement for all the 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 a index for all the records included in the application.

Reset SQL Class Table

In various scenarios it may be necessary to re-align 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 in tact.

Dynamically add additional property to 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 to 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 references the id, and not the mfid in the MFProperty and MFClass table. Set Required = 0 and IsAdditional = 0. RetainIfNull is set to 1 if the update from SQL to MF should add the additional property to all objects in the class.. If this column is set to 0 then the property will only by added to the metadata card of an object if the property has a value.

View of Valuelistitems by Class

Using the following select statement and joins will show valuelistitems  for valuelists per class with the related  Property

This view is useful when analysing the use of valuelists and valuelistitems 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 Valuelistitems by Owner ValuelistItem

Using the following select statement will show valuelistitems by their owner Valuelistitems

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 of property related to a valuelist

After renaming a property in M-Files Admin the metadata need to be refreshed.  Use the MFSQL Manager Web App ‘Opearations/Metadata refresh’ menu item. Select ‘Properties’ in the drop down ans select ‘Refresh meatata’

This will change the name of the property in 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 area 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 was 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].[sp_MFUpdateHistoryShow] @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.

It 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: spMFdropAllClassTables

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