Common report tables and joins

These are the most common table and field layouts and joins that are used in iMIS reports:

Activity Reports

Tables
  • Name
  • Name_Address
  • Activity
  • Name_Fin
  • Org_Control
  • Product
  • Subscriptions

Join Examples
  • Name.ID=Activity.ID
  • Name.ID=Name_Fin.ID
  • Name.ORG_CODE=Org_Control.ORG_CODE
  • Activity.PRODUCT_CODE=Product.PRODUCT_CODE
  • Name.ID=Subscriptions.ID
  • Activity.PRODUCT_CODE=Subscriptions.PRODUCT_CODE

See Common Address Joins

AR/Cash Reports

Tables
  • Name
  • Invoice
  • Trans

Join Examples
  • Name.ID=Invoice.BT_ID
  • Trans.BT_ID=Invoice.BT_ID
  • Trans.BATCH_NUM=Invoice.BATCH_NUM

Committee Reports

Tables
  • Name
  • Activity
  • Committee_Minutes
  • Committee_Position
  • Gen_Tables

Join Examples
  • Name.ID=Activity.ID
  • Activity.PRODUCT_CODE=Committee_Minutes.PRODUCT_CODE
  • Name.TITLE=Committee_Position.TITLE
  • Activity.DESCRIPTION=Gen_Tables.DESCRIPTION

Dues Reports-Invoices (Cash Method)

Tables
  • Name
  • Name_Address
  • Subscriptions

Join Examples
  • Name.ID=Subscriptions.ID
  • Name.BILL_ADDRESS_NUM=Name_Address.ADDRESS_NUM

Dues Reports-Invoices (Accrual Method)

Tables
  • Name
  • Name_Address
  • Invoice
  • Invoice_Lines
  • Trans
  • Subscriptions

Join Examples
  • Name.ID=Trans.BT_ID
  • Name.BILL_ADDRESS_NUM=Name_Address.ADDRESS_NUM
  • Invoice.REFERENCE_NUM=Trans.INVOICE_REF_NUM

Meeting Reports

Consist of Rosters, Badges, Confirmation Letters, and Invoices

Tables
  • Orders
  • Order_Badge
  • Order_Lines
  • Order_Meet
  • Order_Payments
  • Meet_Master

Join Examples
  • Orders.ORDER_NUMBER=Order_Lines.ORDER_NUMBER
  • Orders.ORDER_NUMBER=Order_Badge.ORDER_NUMBER
  • Orders.ORDER_NUMBER=Order_Payments.ORDER_NUMBER
  • Orders.ORDER_NUMBER=Order_Meet.ORDER_NUMBER
  • Order_Meet.MEETING=Meet_Master.MEETING

Membership Reports

Tables
  • Name
  • Name_Address
  • Name_Note
  • Name_Fin
  • Any User-Defined Custom Table

Join Examples
  • Name.ID=Name_Note.ID
  • Name.ID=Name_Fin.ID
  • Name.BT_ID=Name_Fin.BT_ID

See Common Address Joins

Orders Reports

Tables
  • Orders
  • Order_Lines
  • Order_Payments

Join Examples
  • Orders.ORDER_NUMBER=Order_Payments.ORDER_NUMBER
  • Orders.ORDER_NUMBER=Order_Lines.ORDER_NUMBER
  • Orders.PAY_TYPE=Order_Payments.PAY_TYPE
  • Orders.PAY_NUMBER=Order_Payments.PAY_NUMBER
  • Orders.CREDIT_CARD_EXPIRES= Order_Payments.CREDIT_CARD_EXPIRES
  • Orders.AUTHORIZE=Order_Payments.AUTHORIZE
  • Orders.CREDIT_CARD_NAME= Order_Payments.CREDIT_CARD_NAME
  • Orders.CASH_GL_ACCT=Order_Payments.CASH_GL_ACCT