Get Amazing Stories

Get great contents delivered straight to your inbox everyday, just a click away, Sign Up Now.
Name
Email

Use Cases for MFSQL Connector

The use cases are a growing number of practical examples of how the MFSQL Connector can be used. Consult the MFSQL Documentation to see more use cases or further guidance on the use of specific functions and procedures.

MFSQL Access Campaign Results in M-Files

Accelerate Access to Campaign Results in M-Files

Last Modified: 12 June 2016

In this use case the list of people that have registered for the MSQL webinar was import to include the results into M-Files which is the based CRM system.

The issue is that the email application produces the information as a CSV and the information does not align with the properties and data in M-Files. The only common data is the email address.

The objective is to get the list of registrations into M-Files by creating a new object for those that is already in the contact list, and to add information to the existing contacts to show that they have attended the webinar.

This use case assumes that MFSQL Connector is already installed for the vault.

The following steps were followed:

  1. Copy and paste the list of registrants and attendees from the emailer report into an excel worksheet.
  2. Import the excel worksheet into a temporary MS SQL table using SQL Data Import
  3. Refresh M-Files Contacts class(es) using spMFUpdateTable procedure
  4. Create view of all the MF Class tables for Contacts on email, first name, last name class_ID using ‘union all’ (in this case there are 4 different classes of contacts in M-Files and the webinar registrant can be in any one of these classes)
  5. Outer Join the temporary table and ContactsView table on email address
  6. Insert new records in the destination Contact class for all records in temporary table that is not in the ContactsView.
  7. Include standard required columns: Class_ID,Deleted, Process_ID, Name_or_Title
  8. Include metadata specific required columns: Last Name, Email Address
  9. Include additional columns: Comments, First Name, Workflow_ID, State_ID
  10. Use spmfCreateLookupView to create easy lookup for each required column of a lookup property
  11. Update existing records in the Contacts Tables with comments. Set process_ID
  12. Execute spMDUpdateTable with update method 0 to create new objects in M-Files

The new contacts were added to M-Files and the existing contacts where updated and ready for use in the next campaign.

MFSQL Create Class Tables

Create Multiple Class Tables Simultaneously

Last Modified: 12 June 2016

A class table, such as customers, contacts, marketing documents, must be created to synchronize objects with M-Files. Each application will use different class tables. Only the classes used are deployed which reduces clutter and unnecessary traffic.

In this use case, we highlight the different methods of creating class tables using the MFSQL Connector.

To create a single class table:

EXEC [dbo].[spMFCreateTable] @ClassName = N'yournameclass' -- nvarchar(128)

To get a class name

SELECT * FROM MFClass

In many cases the developer may want to create multiple class tables at the same time.

Use the following update statement to set IncludeInApp in MFClass to 1. (replace the id’s in the WHERE clause with your own)

UPDATE mc
SET [mc].[IncludeInApp] = 1
FROM [dbo].[MFClass] AS [mc]
WHERE id IN (6,8,11)

Then use:

EXEC [dbo].[spMFCreateAllMFTables] @Debug = 0 -- smallint

This will automatically create all the class tables.

If you want to drop all the class tables to start again, just use:

EXEC [dbo].[spMFDropAllClassTables] @IncludeInApp = 0, -- int
@Debug = 0 -- smallint

Note that all the actions in this illustration can be applied by using the MFSQL Manager Web Application also.

 

MFSQL Connector ManagerNewClass

JOIN OUR MAILING LIST

We send out information about webinars, software updates, and news related to Laminin Solutions USA.  We never share your data, so you can be confident to receive emails only from us.
Name
Email