iMIS User-Defined Tables

You'll set up user-defined tables through the iMIS Desktop application. This ultimately creates the tables in the iMIS database. Once the writebacks are enabled in Informz, database jobs run to move the data appropriately. Data are pushed to iMIS (if a table is set up incorrectly, the writeback data won't appear in iMIS).

User-defined tables leverage Informz data by setting you up to combine iMIS data with Informz data.

The methodology below shows you how to create these tables. Please note that the specific table structures vary based upon the type of writeback. You can pull information from the following items (click to access the article with table-specifics for each writeback):

Please note that this article specifically addresses user-defined tables for writebacks. Click here to learn more about user-defined tables for IPM forms.

The steps outlined in the sections below are the same for all versions of iMIS (10, 15, and 20).

Clearing Data

When iMIS is originally installed, it is already populated with default tables. However, these need to be cleared and recreated to match Informz. If the tables do not match, the writebacks 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

User-Defined Tables

To create a user-defined table, follow these steps:

  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 checkbox 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.

Remember to 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 and Access Keywords

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

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 at the bottom.

Repeat steps 4 through 6 as many times as necessary for each subsequent table.

Display User Defined Tables in Customer Profile

Use the following steps to create the access keywords for the 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. Click OK.
  7. Click Save at the bottom.

Repeat steps 4 through 7 as many times as necessary for each subsequent table.

Add Tabs to Display Data on Customer Profile

This step needs to be performed on each iMIS user’s iMIS installation to allow that user to view Informz 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 (i.e. on the Profile tab) and select Edit Tab Preferences from the resulting menu.
  4. The new user-defined table(s) is listed in the Other Available Detail Tabs list.  Select each table from that list and click the left arrow to move the table over to the Visible Detail tabs list.
  5. Click Save.
  6. Restart iMIS for the changes to take effect.

Enable Ad Hoc Remote Queries

Ad hoc remote queries must be enabled in order for Informz to transfer data into the user-defined tables. Note that 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 checkbox.
  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

Once everything is set up correctly, contact your Advisor to activate the writeback feature for your account.