Getting started with IQA queries: Business Objects

The following table goes over the differences between the contact-specific business objects.

Tip! ASI recommends using NetContactData for all queries requiring contact data.

What is a Contact Key?

A Contact Key is a unique identifier for contacts across any business object in iMIS. For example, a contact may have an ID of 1000, however, there may also be a GL Account with a code of 1000, a commerce product with a code of 1000, and so forth. A contact may have a Contact Key of 8B00A23E-9C15-41A2-A1F3-0007DB3AF5B9 that will only ever appear for that particular contact.

Contains name, primary mailing address, and contact information along with membership type, category and status. Contains both individual and organization records. Combo records are returned as one row (organization).

Common joins

Some common joins include:

Properties

Contains all the information stored in traditional iMIS activities. These include calls or emails, meetings attended, dues history, product orders and committee membership among others. It will also contain any user defined activity types you have created. There are likely multiple rows per iMIS ID in this BO.

Activity is multi-purpose, so the fields you want to use in your queries will depend on the type of activity you are using. Some fields are used for certain purposes in one activity but may not be used the same for other types of activities, or not at all. Refer to the activity setup in the Advanced Accounting Console to better understand the setup.

Panel Editor creates a data source for each Activity, with the prompts you use for the fields, as the field names, making it easy to use.

Common joins

Some common joins include:

Properties

Contains all the address information for records in iMIS. There is likely multiple rows per iMIS ID in this BO, making it multi-instance.

Address contains multiple rows per record based on the number of addresses that have been entered. Querying will need to consider which specific address information you are looking for. NetContactData also contains address information which is a copy of the primary mailing address from CsAddress.

Common joins

Some common joins include:

Properties

Contains standard iMIS change log details. There are likely multiple rows per iMIS ID in this BO, one row for each change recorded on the record. Log text contains lengthy change details which require parsing to get individual change details. Use Contains filters to search on this field.

Common joins

Some common joins include:

Properties

Stores relationships between two contacts. There is one row per relationship. This means there is likely multiple rows per iMIS ID. Commonly used to view relationships between two people and/or organizations.

Common joins

Some common joins include:

Properties

Contains specific committee participation history. There are likely multiple rows for this BO, making it multi-instance.

To get full details of a committee you will need to join multiple committee business objects. CsCommittee provides a limited subset of information. To filter who is active in a committee you must use two filters – using two fields together will automatically create an OR situation.

Common joins

Some common joins include:

Properties

Contains details about the configuration of committees including code, title, description, and group. This BO returns one row per committee and is used to return committee setup information.

To get full details of a committee you will need to join multiple committee business objects. CsCommitteeCode provides a limited subset of information.

Common joins

Some common joins include:

Properties

Contains committee member information. There are likely multiple rows per iMIS ID in this BO for each committee membership.

Common joins

Some common joins include:

Properties

Contains the history of dues information. There are likely multiple rows per iMIS ID in this BO, one row for each dues payment. Dues history is only generated after dues payments and batches have been processed.

Common joins

Some common joins include:

Properties

Contains details of subscriptions for each ID including current dues and various other subscription information. There will be one row per subscription item which may result in multiple rows per iMIS ID.

Common joins

Some common joins include:

Properties

Contains the subscriptions for chapters. One row per Chapter subscription which may mean more than one row per iMIS ID depending on the number of Chapter subscriptions.

Common joins

Some common joins include:

Properties

Contains event-level setup details. Returns one row per event. Summary data for Total Cancellations, Total Registrants and Total Revenue is available on this business object.

Common joins

Some common joins include:

Properties

Contains event history information. There are likely multiple rows per iMIS ID in this BO, meaning one row for each event registration.

Common joins

Some common joins include:

Properties

Contains function level details about an event. Summary data for Total Registrants and Total fees are available on this business object. There are likely multiple rows per Event in this BO, one row for each function in an event. Filter based on Event Code to get functions related to a specific event.

Common joins

Some common joins include:

Properties

Contains pricing information for an event function. There are likely multiple rows per function in this BO, one row for each reg class with pricing on a function.

Common joins

Some common joins include:

Properties

Contains registrant classes for events.

Common joins

Some common joins include:

CsRegistrantClasses properties

Contains registration data for events. One row per Registration, with multiple rows per iMIS ID likely.

Common joins

Some common joins include:

Properties

Contains function level registration information. There are likely to be multiple rows per registration, one row per registered function, plus multiple rows per ID representing different event registrations.

Common joins

Some common joins include:

Properties

• FormDefinitionFieldData - Event questions are stored in this business object.
• FormFieldResponseData - Responses to event questions are stored in this business object.

See Reporting on event questions for an example.

Common Joins

Use the following common joins with these two business objects:

FormDefinitionFieldData properties

FormFieldResponseData properties

Contains specific invoice data.

Important! It is recommended to use InvoiceSummary instead of CsInvoice when creating queries needing basic invoice data.

Common Joins

Some common joins include:

Properties

Contains specific invoice data and invoices (such as cash dues) that are not included in the invoice tables.

Common Joins

Some common joins include:

Properties

Details all invoice types. Invoice types include the following:

• Regular
• Membership Join
• Membership Renewal
• Past Due Membership Renewal
• Pledge
• Gift
• AutoPay Pledge
• AutoPay Renewal Billing
• AutoPay Billing
• Subscription Fees
• Installment Pledge
• Membership Adjustment
• AutoPay Subscription Fees

Common Joins

Some common joins include:

Properties

Provides information for invoice lines, such as the item, quantity sold, total amount, payments applied, remaining balance, source and promotion codes used, and the ship-to contact's ID.

Note: It is recommended to use InvoiceLineData as opposed to InvoiceLines.

Common Joins

Some common joins include:

Properties

Contains specific invoice line data. Does not include rows for orders or cash dues invoices.

Note: It is recommended to use InvoiceLineData as opposed to InvoiceLines.

Common Joins

Some common joins include:

Properties

Returns separate rows for each installment of an installment pledge. When joining this business object, it is recommended to join on either the InvoiceNumber or InvoiceReferenceNumber property.

Common Joins

Some common joins include:

Properties

Contains invoice data related to events.

Common Joins

Some common joins include:

In addition to these common joins, EventInvoice is the primary business object in the following queries:

• Event registration has invoice ($/Common/Tasks/Queries/Staff dashboard alerts/Event registration has invoice)
• Registration invoice ($/EventManagement/DefaultSystem/Queries/Registration details/Registration Invoices)

Properties

Lists individual scheduled payments for installment pledge invoices.

Common Joins

Some common joins include:

Properties

Lists all order numbers and their related invoice reference numbers.

Common Joins

Some common joins include:

Properties

Contains product order history information. There are likely multiple rows per iMIS ID in this BO, one row for each order. Order history is only generated after order payments and batches have been processed.

Common joins

Some common joins include:

Properties

Stores data on the line items of open and closed orders. Includes the order number, order line number, item name, quantity sold, amount, discount code, status, and ship to contact ID.

Common joins

Some common joins include:

Properties

Contains order level details. CsOrders is commonly used to create an invoice or receipt, view order details (amounts paid or outstanding balances), and details on promotion codes.

Common joins

Some common joins include:

Properties

Contains information about products in iMIS, including dues, certifications, meetings, chapters, committees, and more.

Product contains varying types of products. ProductCode is a concatenation of the type of product and the individual product code in order to create a unique identifier for each row (e.g. COMMITTEE/BOARD). Use product minor for the specific item’s code (e.g. BOARD) but be aware if you have other products that use that code you may get inaccurate results.

Common joins

Some common joins include:

Properties

Contains the product categories (Settings > Commerce > Product categories) for products created at Commerce > Add product.

Common joins

Some common joins include:

Properties

Contains Product Types for items in the Product table.

Common joins

Some common joins include:

Properties

Stores data on commerce-specific products. Includes fair market value, shipping and handling charges, weight, standard price, discounted price, and other commerce data.

Properties

The following table lists the recommended business objects to use for various fundraising purposes:

The Gift business object uses the GiftReport table to create a list of gifts received along with their details. The list includes pledge payments but not unpaid pledges. Split gifts (gifts to more than one product or item) and financial adjustments appear in separate rows. The list reports the fiscal month and a less granular gift type than the GiftsReceived business object.

Common joins

Some common joins include:

Properties

The GiftsReceived business object uses the GiftReport table to create a detailed list of all gifts received, including pledge payments, but not unpaid pledges. Separate rows appear on the list for financial adjustments and split gifts (gifts to more than one product or item). The GiftType property differentiates between split gifts and charitable event gifts.

Common joins

Some common joins include:

Properties

This business object contains information from the GiftTransaction and Receipting tables and lists all gifts received that are eligible for receipting. The list includes one row per receipt. If multiple receipts exist for the same gift, each receipt will have a separate row. If applicable, rows will include the receipt status, receipt number, and any financial adjustments made to the gift.

Common joins

Some common joins include:

Properties

Used to report on specific gift information, such as amount, fair market value, tribute type, and more. This business object uses data from the GiftReport table to generate a list with one row per gift item. The list includes pledge payments but not unpaid pledges. Financial adjustments to gifts are incorporated into the same row, but split gifts (gifts to more than one product or item) appear in separate rows.

Common joins

Some common joins include:

Properties

This business object returns total gift counts and sums. It contains a listing of gifts with their basic details: the donor ID, gift amount, gift date, and original transaction number. The list combines financial adjustments and split gifts into one row per gift.

Common joins

Some common joins include:

Properties

This business object retrieves data from the GiftTransaction table to generate a transaction-focused listing of gifts. This list is the basis for receipting. It includes pledge payments but not unpaid pledges. Financial adjustments to gifts and split gifts (gifts to more than one product or item) appear in separate rows.

Common joins

Some common joins include:

Properties

The CsPledges business object is based on the PledgeReport table and contains a list of pledges and their details. Some details include the payment amount, balance, and adjustments amount. Each pledge installment appears as a new row on the list.

Common joins

Some common joins include:

Properties

The CsDonations business object is based on the DonationReport table and contains a listing of gifts and pledges. The GiftType property differentiates gifts and pledges. The Amount property represents the total amount for pledges (there is no information in this BO about how much of a pledge balance has been paid). Any financial adjustments and split gifts are combined into one row per gift, and pledge installments are summarized into one row.

Common joins

Some common joins include:

Properties

The DonationPerformace business object contains a listing of gifts and pledges for analyzing the performance of particular funds and source codes. Paid and unpaid pledges are included, and pledge payments are not included as gifts.

properties

DonationSummary lists all gifts and pledges, and combines split gifts, pledge payments, installment pledges, and financial adjustments into one row per donation. Each row includes properties such as GiftType, which differentiates between gifts and pledges, and IsSplitGift, which denotes split gifts.

Common joins

Some common joins include:

Properties

The GiftHistory business object lists all gifts and pledges a donor should receive credit for and includes details such as the amount received, balance, and soft credit. The GiftType property identifies the source system and differentiates between In-Kind gifts, pledges, cash gifts, split gifts, and soft credits. Separate rows appear for split gifts and soft credits,the gift itself and its soft credit, and the row of the gift incorporates any financial adjustments.

Common joins

Some common joins include:

Properties

The GiftHistorySummary business object contains a list with one row per donor outlining their giving history, including their first gift, last gift, highest gift amount, lifetime gift value, average gift value, and more.

Common joins

Some common joins include:

Properties

The GiftHistorySummaryBasic business object draws directly from the GiftHistorySummary table to find donor data, including ID, gift amounts, lifetime gift value, and more. This business object uses fewer columns than GiftHistorySummary to improve performance.

Common joins

Some common joins include:

Properties

Common joins

Some common joins include:

Properties

This business object is not recommended for use in IQA queries.

Properties

The GiftDetail business object is used by the gift adjustments feature in staff gift entry to retrieve the current state of the gift or pledge. Includes both gifts and pledges. Adjustments are incorporated into the same row (there are not separate rows for adjustments). Includes columns for the properties used in the staff gift entry.

Split gifts are combined into a single row; therefore, the ProductCode should not be used for split gifts.

Common joins

Some common joins include:

Properties

Contains a row for each person for each communication preference type, even if the person has not changed the default communication preferences.

Common joins

Properties

Details one row for each communication preference (Settings > Contacts > Communication preferences). Lists the settings for each communication preference, such as the communication name, description, and whether the default is to opt in or out.

Common joins

Properties

Contains all the lookup data created in iMIS General Lookup tables. There are likely multiple rows per lookup table in this BO, one row for each lookup option.

The code field may contain duplicate values so ensure that you filter on table_name to get the right lookup value.

Common joins

Some common joins are any field that uses a General Lookup table. The table will generally store the code value of the lookup. Use CsGeneralLookupTables.Code to link to your user defined field.

Properties

The Date Dimension business object contains a row for every day from 1/1/2000 to 12/31/2050 in order to more easily create reports and queries that require dates. Use this business object to report on fiscal year information. Use TheDate property to filter the report on specific dates. For an example, see Finding invoice amounts for each month.

Note: The DateDimension business object contains several fiscal properties (e.g., FiscalMonth, FiscalYear, FiscalYearBegin). These properties are determined by the fiscal month defined in the First month of fiscal year (number) setting (Settings > Finance > General).

Common joins

Some common joins include:

Properties

Contains a row for every calendar month in order to more easily create reports and queries that require dates.

Common joins

Some common joins include:

Properties

Contains a row for every year from 2000 to 2050 in order to more easily create reports and queries that require dates.

Common joins

Some common joins include:

Properties