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