You'll set up user-defined tables through the iMIS Desktop application, which ultimately creates the tables in the iMIS database. After the writebacks are enabled in Higher Logic Thrive Marketing Professional (Thrive Marketing Professional), database jobs run to move the data appropriately. Data are pushed to iMIS.

User-defined tables leverage Thrive Marketing Professional data by setting you up to combine iMIS data with Thrive Marketing Professional data.

IMPORTANT: User-defined table is required in iMIS in order for writebacks to properly function. If the writeback feature is activated but the required table does not exist or is incorrectly formatted, the writeback will not be sent until the setup requirements are met.

The methodology below shows you how to create these tables. The specific table structures vary based upon the type of writeback. You can pull information from:

• Campaigns
• Engagement
• Leads
• Mailings
• Personas
• Surveys
• Voting
• Web Tracking

NOTE: This article focuses on user-defined tables for writebacks. See Landing Pages and iMIS to learn about user-defined tables for Integrated Preference Management forms.

NOTE: The steps outlined in the sections below apply to iMIS versions 10, 15, and 20.

Clear default iMIS data

When iMIS is installed, it's populated with default tables. However, these must be cleared and recreated to match Thrive Marketing Professional.

WARNING: If the tables don't match, the writebacks will fail.

The SQL below clears the following tables: INFORMZ_MAILINGS, INFORMZ_SURVEYS, and INFORMZ_BALLOTS. You'll need to execute this SQL inside SQL Management Studio via a query connected to your iMIS database.

if exists (select * from sysobjects where name = 'informz_mailings')
BEGIN
drop table informz_mailings
END
GO
if exists (select * from sysobjects where name = 'informz_surveys')
BEGIN
drop table informz_surveys
END
GO
if exists (select * from sysobjects where name = 'informz_ballots')
BEGIN
drop table informz_ballots
END

Create a user-defined table

To create a user-defined table:

1. Click Customers at the top of the screen.
2. Navigate to Set up Module > General on the left.
3. Click Additional Windows.
4. Click Define Tables.
5. Click New at the top.
6. Enter the appropriate table name.
7. For “System,” select Membership.
8. For “Access Method,” select ID.
9. Check the Multiple Instances Allowed box on the right.
10. Use the Insert Line button to add fields to the table. Use the links above to see the table structure for each writeback.
11. Click Save after the fields have been added.
12. Click Create/Update Server Table.
13. Click Auto-Create Window.

WARNING: Name the tables and columns (fields) with the exact names from each table. If the names are not identical, the transfer cannot be made properly.

Auto-create & access keywords

When using auto-create, the tab window data for a user-defined table becomes editable when viewing a customer profile. You should not edit this data because the data in the tables is automatically transferred from Thrive Marketing Professional.

To prevent iMIS users from editing data in the user-defined tab windows, create access keywords for the tables.

1. Click Customers at the top.
2. Navigate to Set up tables > General lookup/validation on the left.
3. Look in the table list for a table called ACCESS_KEYWORDS (this may already be created).
4. If the ACCESS_KEYWORDS table does not exist, select (New Table) from the table list. Enter “ACCESS_KEYWORDS” for the table name and click Save.
5. If the ACCESS_KEYWORDS table already exists, select it from the list.
6. Click New at the top.
7. In the Code box on the right, enter the table name (e.g., “INFORMZ_MAILINGS”). In the Description box, enter the table name (e.g. “Informz Mailings”).
8. Click Save.

NOTE: Repeat steps 4—6 as many times as necessary for each subsequent table.

Display user-defined tables in customer profile

To create access keywords for user-defined tables:

1. Navigate to Set up module > General on the left.
2. Click Additional Windows.
3. Click Define Windows.
4. Select the table name (e.g., INFORMZ_MAILINGS) from the Current Windows list.
5. To the right of the “Access Keywords” box, click Find.
6. On the Available list, double-click the table name (e.g., INFORMZ_MAILINGS). This moves it to the Selected list.
7. Click OK.
8. Click Save.

NOTE: Repeat steps 4—8 to create additional user-defined tables.

Add tabs to display data on customer profile

Perform this step on each iMIS user's iMIS installation to allow that user to view Thrive Marketing Professional data in iMIS.

1. Click Customers at the top.
2. Click Manager Customers on the left.
3. Right-click anywhere in the top-right of the screen and select Edit Tab Preferences.
4. New user-defined tables are listed in the Other Available Detail Tabs list.
5. Select a table and click the left arrow to move the table to the Visible Detail tabs list; repeat as necessary.
6. Click Save.
7. Restart iMIS for the changes to take effect.

Enable ad-hoc remote queries

Ad-hoc remote queries must be enabled in order for Thrive Marketing Professional to transfer data into the user-defined tables.

NOTE: This may already be enabled in your database (these instructions only apply to SQL Server 2005 or older).

Use the SQL Server Surface Area Configuration tool and follow these steps:

1. Click Surface Area Configuration for Features.
2. Click Ad Hoc Remote Queries on the left.
3. On the right, check the Enable OPENROWSET and OPENDATASOURCE Support boxes.
4. Click OK.
5. Close the Surface Area Configuration tool.
6. Use the command line option for setting the above settings:

EXEC sp_configure 'show advanced options', 1
GO
RECONFIGURE
GO
EXEC sp_configure 'Ad Hoc Distributed Queries', 1
GO
RECONFIGURE

Enable the writeback feature

After everything is correctly set up, create a case to activate the writeback feature for your account.