Reports and Queries

iMIS provides several SQL scripts that can can add or change data in the tables. The SQL scripts are stored as standard iMIS reports that you can clone and customize.

System Reports and Queries window

From the menu bar, select Utilities > Reports and Queries

Note: Run the System Setup routines only after the prerequisite conditions are met.

The following Data Import, Purge, System Setup, and Table Maintenance report types display with a brief description.

Data Import

Import Data into the Prospect Table: Imports prospect records from a tab-delimited file into iMIS.

Purge

Membership Purge for MS SQL: Deletes customer records with a status of “D.”

Purge Old Invoices: Deletes records in the Invoice and Invoice_Lines tables which have a zero balance, are dated before the retention months and in which there are no linked and unposted transactions remaining.

Purge Old Transactions: Deletes records in the Trans table where TRANSACTION_DATEis less than Trans retention months and are no longer associated with its corresponding invoice record.

Purge Old Activity attachments: Deletes activity attachments with a timestamp before a specified date.

System Setup

Co. to Member Addr. Flowdown: Populates a member’s first Name_Address record (the required one where PURPOSE = Membership Address1 Prompt) from the corresponding Name_Address record of the company ID (CO_ID). Only member records where ADDRESS_1 is blank are updated.

Prerequisite: This procedure requires that Name_Address.ADDRESS_NUM be populated and Name_Address.PURPOSE be populated to match the address prompts in Customers > Set up module > General > Address and Notes button.

Note: This script updates only the Name_Address record; it does not update user-defined fields within Advanced Flow Down.

Rebuild Chapter Subscriptions: (for optional Chapter Management) Inserts Chapter Subscription records based on Name.CHAPTER.

Prerequisite: Requires that Name.CHAPTER be populated.

Rebuild Member_Type Info: Populates Name.MEMBER_RECORD, COMPANY_RECORD, and CO_MEMBER_TYPE.

Prerequisite: Requires that you first complete Member_Type setup and create the Company_Index view.

Rebuild Name Fields: Rebuilds the index fields Name.LAST_FIRST, COMPANY_SORT, and FULL_NAME. To speed processing, select Drop Indexes first.

Prerequisites: (1) Requires that you first upload the Name table, (2) populate elements of member name (at least LAST and FIRST), and (3) populate Name.COMPANY.

Rebuild Name.DUP_MATCH_KEY: Rebuilds the Name.DUP_MATCH_KEY according to the following iMIS default formula. This formula checks for duplicates: the first five digits of the ZIP (postal code) field, the first four characters of the last name, and the first initial.

con (mid(Name.ZIP,1,5),mid(Name.LAST,1,4),
mid(Name.FIRST,1,1))

The standard formula is used unless a custom formula has been entered in the Duplicate Record Check Formula field on the Customer Setup – Advanced window (from Customers, select Set up module > General, and click Advanced). If the Duplicate Record Check Formula field is blank, the default formula will be used.

Prerequisite: Requires that you first run Rebuild Name_Address Pointers (to populate ZIP in Name) and populate any other fields used in the dupmatch formula.

Note: If you customize your dupmatch formula, you must populate the parameters of the report specification (select Utilities > Report Specs).

Rebuild Name_Address Pointers: Populates Name_Address.FULL_ADDRESS and Name’s address fields, including MAIL_ADDRESS_NUM, BILL_ADDRESS_NUM, ADDRESS_NUM_1, ADDRESS_NUM_2, ADDRESS_NUM_3, and FULL_ADDRESS.

Prerequisite: Requires that you first populate the Name table with unique ID’s, populate the Name_Address table with unique ID’s that correspond to the Name table and populate the Name_Address.ADDRESS_NUM field with unique sequential numbers. From Customers, select Set up module > General and click Address and Notes, populate the Address Usage fields (Name_Address.PURPOSE) to match your setup, and make sure that you have at least one Name_Address record for each member, with a PURPOSE matching the first address prompt.

Post Processing: (1) Select Utilities > Table Analysis (2) Select Name table record, and (3) Click Make Indexes.

Rebuild Name_Indexes: Name_Indexes can be used to define auxiliary indexes on the Name table that will appear in the Name Find window’s list of find criteria. These additional indexes are set up on the Customer Setup – Indexes window (from Customers, select Set up module > General, and click Indexes). This example script shows how to populate the Name_Indexes table when Name.STATE_PROVINCE is set up as additional index 1.

Prerequisites: (1) A column or Omnis expression using columns from the Name table must be defined as an additional index on the Customer Setup – Indexes window. In this example, Name.STATE_PROVINCE is defined as additional index 1. (2) To modify this script, clone it and substitute a SQL expression equivalent to the Omnis expression used in System Setup and the corresponding index number (1, 2, 3, or 4) where this script has Name.STATE_PROVINCE and 1, respectively. (3) This script truncates and rebuilds the whole Name_Indexes table, so if you have several additional indexes you should put all the updates in one script.

Remove Name.LAST_FIRST apostrophes: Removes apostrophes from the Name.LAST_FIRST field. It will run faster if it is done before Name indexes are added.

Prerequisites: (1) Name table is uploaded (2) Elements of member name are populated: at least LAST and FIRST (3) Rebuild Name script has been run to populate LAST_FIRST.

Table Maintenance

Remove Ref_Provided NULLs: Replaces NULLs in the Ref_Provider table with either a blank (‘ ‘) or a zero (0) depending on the datatype of the field being updated.

Set Activity type donor club: Updates Activity.ACTIVITY_TYPE = ‘DONORCLUB’.

Set Activity.ORIGINATING_TRANS_NUM: Updatse Activity.ORIGINATING_TRANS_NUM with its matching Trans.TRANS_NUMBER.

Set Product type donor club: Updates Product.PROD_TYPE = ‘DONORCLUB’.

Set Product FR type donor club: Updates Product_FR.FR_TYPE = ‘DONORCLUB’.

Set Product_Type type donor club: Updates Product_Type.PROD_TYPE = ‘DONORCLUB’.

More:
Customizing system reports
Adding SQL queries to custom reports
Run iMIS Procedure Utility
Open Application Utility