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:
- Copy and paste the list of registrants and attendees from the emailer report into an excel worksheet.
- Import the excel worksheet into a temporary MS SQL table using SQL Data Import
- Refresh M-Files Contacts class(es) using spMFUpdateTable procedure
- 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)
- Outer Join the temporary table and ContactsView table on email address
- Insert new records in the destination Contact class for all records in temporary table that is not in the ContactsView.
- Include standard required columns: Class_ID,Deleted, Process_ID, Name_or_Title
- Include metadata specific required columns: Last Name, Email Address
- Include additional columns: Comments, First Name, Workflow_ID, State_ID
- Use spmfCreateLookupView to create easy lookup for each required column of a lookup property
- Update existing records in the Contacts Tables with comments. Set process_ID
- 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.
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)
SET [mc].[IncludeInApp] = 1
FROM [dbo].[MFClass] AS [mc]
WHERE id IN (6,8,11)
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.