Field Mapping

This guide provides detailed information about the structure of the database and the relationships between tables. When creating reports and performing queries, the specific field names are crucial to the accuracy of the results.

ClearVantage notebooks display any applicable fields, hide unused fields and can be configured by each organization’s system administrators.

For example, the field “Paid Thru Date” can be renamed on the notebook. Many organizations have other terms such as “Expire Date” or “Termination Date” and can change this display label. The database field name will always remain the same, even if the change is made to the field on the notebook.

Field Title: Paid Thru Date

Database Field: TERMINATIONDATE

Using both a Field Title and Database Field, users have the flexibility to rename fields as needed on any notebook. Database fields have no special characters or spaces and cannot be changed.

These database field names are used when writing reports.

Notebook Editor

Please note that changes made in the notebook editor apply to all records and not specifically the record that is currently being viewed. Please review the System Administrator user guide for more information.

To find the database name for any field on a notebook. There will be an “Activities” dropdown or a cog wheel in the upper right-hand corner of the notebook.

Select Activities or the Cog Wheel Edit Layout

Selecting Edit Layout will open the notebook editor.

The editor will display all fields that are used on the notebook, as well as any unused fields.

The notebook editor allows users to view the Database Field name for a field.

This is where users can view the Field Title and change the label if needed.

Selecting the settings wheel for any field will display the database field name and the field title at the same time.

The notebook editor allows any system administrator to easily find and reference database fields when creating reports and queries.

Individual Notebook

Customer Table Field Mapping

Shown below is an image of the notebook editor for the customer table. This is where users can see the field mappings.

Note

Member Type and Status can be found under Reference Tables

Customer Table Joins

Some common joins using the CUSTOMER table.

Tables to Join	Key Field	Key Field in the CUSTOMER table
ORGANIZATION	ORGCD	ORGCD
INDSESSION	CUSTOMERCD	CUSTOMERCD
INDCOMMITTEE	CUSTOMERCD	CUSTOMERCD
SUBSCRIPTION	CUSTOMERCD (where CUSTOMERTYPECD = I)	CUSTOMERCD
SUBSCRIPTION	PAYEECD (where PAYEETYPECD = I)	CUSTOMERCD
INVOICE	PAYEECD (where PAYEETYPECD = I)	CUSTOMERCD
PAYMENTS	PAYEECD ( where PAYEETYPECD = I)	CUSTOMERCD
CERTIFICATION	CUSTOMERCD	CUSTOMERCD

Sample Table Joins

Customer Education

Shown below is an image of the notebook editor the CUSTOMEREDUCATION table and how they are mapped to the ClearVantage application screen for this information.

CUSTOMEREDUCATION Table Joins

Some common joins using the CUSTOMEREDUCATION table.

Tables to Join	Key Field	Key Field in the CUSTOMEREDUCATION table
CUSTOMER	CUSTOMERCD	CUSTOMERCD

Customer Addresses

Shown below is an image of the notebook editor CUSTOMERADDRESS table and how they are mapped to the ClearVantage application screen for this information.

CUSTOMERADDRESS Table Joins

Some common joins using the CUSTOMERADDRESS table.

Tables to Join	Key Field	Key Field in the CUSTOMERADDRESS table
CUSTOMER	CUSTOMERCD	CUSTOMERCD

Relationships

Shown below is an image of the notebook editor for Relationships. This is the WHOKNOWSWHO table and displays how they are mapped to the ClearVantage application screen for this information.

WHOKNOWSWHO Table Joins

Some common joins using the WHOKNOWSWHO table.

Tables to Join	Key Field	Key Field in the WHOKNOWSWHO table
CUSTOMER	CUSTOMERCD	MEMBERCD (where MEMBERTYPE = I) or KMEMBERCD (where KMEMBERTYPE = I)
ORGANIZATION	ORGCD	MEMBERCD (where MEMBERTYPE = O) or KMEMBERCD (where KMEMBERTYPE = O)

Organization Notebook

Organization Table Field Mapping

Shown below is an image of the notebook editor for the ORGANIZATION table and how they are mapped to the ClearVantage application screens for the Organization notebook.

Note

Please refer to the Reference Tables for Membership Status, Type, Class, and Options.

ORGANIZATION Table Joins

Tables to Join	Key Field	Key Field in the ORGANIZATION table
CUSTOMER	ORGCD	ORGCD
SUBSCRIPTION	CUSTOMERCD ( where CUSTOMERTYPECD = O)	ORGCD
SUBSCRIPTION	PAYEECD ( where PAYEETYPECD = O)	ORGCD
INVOICE	PAYEECD ( where PAYEETYPECD=O)	ORGCD
INVOICE	SHIPTOCD ( where SHIPTOCD=O)	ORGCD
PAYMENTS	PAYEECD ( where PAYEETYPECD = O)	ORGCD

Some common joins using the organization table.

Events Notebook

SESSION Table Field Mapping

Shown below is an image of the notebook editor for the session table and how they are mapped to the ClearVantage application screen for the Event notebook.

Note

Please refer to Company Information for more information.

SESSION Table Joins

Some common joins using the SESSION table.

Tables to Join	Key Field	Key Field in the SESSION table
INDSESSION	SESSIONALTCD	SESSIONALTCD
SESSIONFUNCTION	SESSIONALTCD	SESSIONALTCD
VW_SESSIONCOUNTS	SESSIONALTCD	SESSIONALTCD
EVENTBOOTH	SESSIONALTCD	SESSIONALTCD
PRICELIST	ITEMALTCD (Where PRICEFORCD = ‘S’)	SESSIONALTCD
EVENTTRAINER	SESSIONALTCD	SESSIONALTCD
CERTIFICATION_DETAIL	SESSIONALTCD (Where TYPECD = ‘S’)	SESSIONALTCD
CERTIFICATION	SESSIONALTCD	SESSIONALTCD

Sample Table Joins

Event Registration Notebook

INDSESSION Table Field Mapping

Shown below is an image of the notebook editor for the INDsession table and how they are mapped to the ClearVantage application screen for the Registration notebook.

INDSESSION Table Joins

Some common joins using the INDSESSION table.

Tables to Join	Key Field	Key Field in the INDSESSION table
CUSTOMER	CUSTOMERCD	CUSTOMERCD
CUSTOMER	CUSTOMERCD	PAYEECD Where PAYEETYPECD = I
ORGANIZATION	ORGCD	PAYEECD Where PAYEETYPECD = O
INDFUNCTION	ASSIGNEDSESSIONCD	ASSIGNEDSESSIONCD
EVENTBOOTH	ASSIGNEDSESSIONCD	ASIGNEDSESSIONCD
CERTIFICATION	REGNUM	ASSIGNEDSESSIONCD

Functions Notebook

Shown below is an image of the notebook editor for the SESSIONFUNCTION table and how they are mapped to the ClearVantage application screen for event functions

Common joins using the SESSIONFUNCTION table

Tables to Join	Key Field	Key Field in the SESSIONFUNCTION table
SESSION	SESSIONALTCD	SESSIONALTCD
INDFUNCTION	FUNCTIONCD	FUNCTIONCD
CERTIFICATION_DETAIL	SESSIONALTCD, FUNCTIONCD (Where TYPECD = ‘F’)	SESSIONALTCD, FUNCTIONCD

INDFUNCTION Table Field Mapping

Shown below is an image of the notebook editor for the indfunction table and how they are mapped to the ClearVantage application screens for the member functions in the Registration notebook.

INDFUNCTION Table Joins

Some common joins using the INDFUNCTION table.

Tables to Join	Key Field	Key Field in the INDFUNCTION table
INDSESSION	ASSIGNEDSESSIONCD	ASSIGNEDSESSIONCD
VW_SESSIONFUNCTION_ALL	ASSIGNEDSESSIONCD, FUNCTIONCD	ASSIGNEDSESSIONCD, FUNCTIONCD

NOTE: Not every database has a view, if not available, check with your Euclid Support Analyst.

Invoice Notebook

INVOICE Table Field Mapping

Shown below is an image of the notebook editor for the the invoice table and how they are mapped to the ClearVantage application screens for the Invoice notebook. There may be additional fields not displayed here.

INVOICE Table Joins

Some common joins using the Invoice Table.

Tables to Join	Key Field	Key Field in the INVOICE table
INVOICEITEM	INVOICENUM	INVOICENUM
INVOICEPAYMENT	INVOICENUM	INVOICENUM
SUBSCRIPTION	INVOICENUM	INVOICENUM
PAYMENTS	INVOICENUM	INVOICENUM
CUSTOMER	CUSTOMERCD	PAYEECD (where PAYEETYPECD = I)
ORGANIZATION	ORGCD	PAYEECD (where PAYEETYPECD = O)

INVOICEITEM Table Field Mapping

Shown below is an image of the notebook editor for the invoiceitem table and how they are mapped to the ClearVantage application screen for the Invoice notebook.

INVOICEITEM Table Joins

Some common joins using the INVOICEITEM table.

Tables to Join	Key Field	Key Field in the INVOICEITEM table
INVOICE	INVOICENUM	INVOICENUM
PRODUCT	PRODUCTCD	ITEMALTCD (Where ITEMSOURCE = P)
SUBSCRIPTION	SUBSCRIPTIONNAME	ITEMALTCD (Where ITEMSOURCE = D)
SUBSCRIPTION	SUBSCRIPTIONNUM (Where INVOICE ITEM ITEMSOURCE = ‘D’), INVOICENUM	ITEMCD, INVOICENUM
INVOICESUBHISTORY	SUBSCRIPTIONNUM (Where INVOICE ITEM SOURCE = ‘D’), INVOICENUM	ITEMCD, INVOICENUM
INDSESSION	ASSIGNEDSESSIONCD, SESSIONALTCD ( Where INVOICE ITEM ITEMSOURCE = ‘S’)	ITEMCD, ITEMALTCD
INDFUNCTION	ASSIGNEDSESSIONCD, FUNCTIONCD (Where INVOICE ITEM ITEMSOURCE = ‘F’)	ITEMCD, ITEMSUBCD
EVENTBOOTH	ASSIGNEDSESSIONCD, BOOTHCD (Where INVOICE ITEM ITEMSOURCE = ‘B’)	ITEMCD, ITEMSUBCD

Payments Notebook

PAYMENTS Table Field Mapping

Shown below is an image of the notebook editor for the PAYMENTS table and how they are mapped to the ClearVantage application screens for the Payment notebook.

PAYMENTS Table Joins

Some common joins using the PAYMENTS table.

Tables to Join	Key Field	Key Field in the PAYMENTS table
BATCH	BATCHCD	USERBATCHCD
INVOICEPAYMENT	PAYMENTNUM	PAYMENTNUM
CUSTOMER	CUSTOMERCD	PAYEECD (where PAYEETYPECD = I)
ORGANIZATION	ORGCD	PAYEECD (where PAYEETYPECD = O)

INVOICEPAYMENT Table Field Mapping

Shown below is an image of the notebook editor for the INVOICEPAYMENT table and how they are mapped to the ClearVantage application screens for the individual payments notebook.

Subscriptions Notebook

SUBSCRIPTION Table Field Mapping

Shown below is an image of the notebook editor for the SUBSCRIPTION table and how they are mapped to the ClearVantage application screens for the Subscription notebook. Note the SUBSCRIPTION table stores information on Subscriptions, Dues records, and Donation records.

SUBSCRIPTION Table Joins

Some common joins using the SUBSCRIPTION table.

Tables to Join	Key Field	Key Field in the SUBSCRIPTION table
CUSTOMER	CUSTOMERCD	CUSTOMERCD ( where CUSTOMERTYPECD = I)
INVOICE	INVOICENUM	INVOICENUM
SUBSCRIPTIONREF	SUBSRIPTIONNAME	SUBSCRIPTIONNAME

SUBSCRIPTIONREF Table Field Mapping

Shown below is an image of the notebook editor for the SUBSCRIPTIONREF table and how they are mapped to the ClearVantage application screens for the Subscription, Dues, and Donation notebooks. The type of information for the type of record is stored in the UDEF1TXT field as either Membership, Subscription, or Donation.

SUBSCRIPTIONREF Table Joins

Some common joins using the SUBSCRIPTIONREF table.

Tables to Join	Key Field	Key Field in the SUBSCRIPTIONREF table
SUBSCRIPTION	SUBSCRIPTIONNAME	SUBSRIPTIONNAME

Committee Notebook

COMMITTEE Table Field Mapping

Shown below is an image of the notebook editor for the COMMMITTEE table and how they are mapped to the ClearVantage application screens for the committee notebook.

COMMITTEE Table Joins

Some common joins using the COMMITTEE table.

Tables to Join	Key Field	Key Field in the COMMITTEE table
INDCOMMITTEE	COMMITTEECD	COMMITTEECD
SUBSCRIPTIONREF	UDEF5TXT	COMMITTEECD
ORGANIZATION	ORGCD	ORGCD
CUSTOMER	CUSTOMERCD	CHAIRPERSONCD

INDCOMITTEE Table Field Mapping

Shown below is an image of the notebook editor for the INDCOMITTEE table and how they are mapped to the ClearVantage application screens for the individual Committee notebook.

CUSTOMER Table Field Mapping for Committee Member Notebook

Shown below is an image of the notebook editor for the CUSTOMER table and how they are mapped to the ClearVantage application screens for the Committee member notebook.

INDCOMMITTEE Table Joins

Tables to Join	Key Field	Key Field in the INDCOMMITTEE table
CUSTOMER	CUSTOMERCD	CUSTOMERCD
COMMITTEE	COMMITTEECD	COMMITTEECD
INDCOMMITTEEH	COMMITTEECD	COMMITTEECD
INDCOMMITTEEH	CUSTOMERCD	CUSTOMERCD

Some common joins using the INDCOMMITTEE table.

INDCOMITTEEH Table Field Mapping

Shown below is an image of the notebook editor for the INDCOMITTEEH table linked with the customer table and how they are mapped to the ClearVantage application screens for the Committee History notebook.

INDCOMMITTEEH Table Joins

Tables to Join	Key Field	Key Field in the INDCOMMITTEEH table
CUSTOMER	CUSTOMERCD	CUSTOMERCD
COMMITTEE	COMMITTEECD	COMMITTEECD
INDCOMMITTEE	COMMITTEECD	COMMITTEECD
INDCOMMITTEE CUSTOMER	CUSTOMERCD	CUSTOMERCD

Some common joins using the INDCOMMITTEEH table.

Action Item Notebook

Shown below is an image of the notebook editor for the LISTMEMBERS table and how they are mapped to the ClearVantage application screens for action items.

LISTMEMBERS Table Joins

Tables to Join	Key Field	Key Field in the LISTMEMBERS table
CUSTOMER	CUSTOMERCD	MEMBERCD (where MEMBERSOURCE = I)
ORGANIZATION	ORGCD	MEMBERCD (Where MEMBERSOURCE = O)
DATABASE_USER	USERCD	ACTIONASSIGNEDTOCD or ACTIONASSIGNEDBYCD
ACTIONDETAIL	LISTITEMNUM	LISTITEMNUM

Some common joins using the LISTMEMBERS table.

Campaigns/Appeals

Campaign Table Field Mapping

Shown below is an image of the notebook editor for Campaigns and how they are mapped to the ClearVantage application screen.

Campaigns Table Joins

Some common joins using the CAMPAIGN table.

Tables to Join	Key Field	Key Field in the Campaign table
APPEAL	CAMPAIGNCD	CAMPAIGNCD
APPEALMEMBERS	CAMPAIGNCD	CAMPAIGNCD
SUBSCRIPTION	CAMPAIGNCD	CAMPAIGNCD

APPEAL Table Field Mapping

Shown below is an image of the notebook editor for Appeals and how they are mapped to the ClearVantage application screen.

APPEAL Table Joins

Some common joins using the APPEAL table.

Tables to Join	Key Field	Key Field in the APPEAL table
CAMPAIGN	CAMPAIGNCD	CAMPAIGNCD
PACKAGEREF	PACKAGECD	PACKAGECD
CUSTOMER

Products Notebook

Shown below is an image of the notebook editor for the PRODUCT table and how they are mapped to the ClearVantage application screen for product information.

PRODUCT Table Joins

Some common joins using the PRODUCT table.

Tables to Join	Key Field	Key Field in the PRODUCT table
PRODUCTCOGS	PRODUCTCD	PRODUCTCD
INVOICEITEM	ITEMALTCD	PRODUCTCD

Surveys

TEST_MASTER Table Field Mapping

The following image documents the database fields for TEST_MASTER and how they are mapped to the ClearVantage application screen for surveys.

Common Joins Using the TEST_MASTER Table

Some common joins using the TEST_MASTER table.

Tables to Join	Key Field	Key Field in the TEST_MASTER table
SESSION	TESTCD	TESTCD
PROGRAM	TESTCD	TESTCD
TEST_QUESTION	TESTCD	TESTCD

TEST_QUESTIONS Table Field Mapping

The following image documents database fields for TEST_QUESTIONS and how they are mapped to the ClearVantage application screen for survey questions.

Common Joins Using the TEST_QUESTIONS Table

Some common joins using the TEST_QUESTIONS table.

Tables to Join	Key Field	Key Field in the TEST_QUESTIONS table
TEST_MASTER	TESTCD	TESTCD
TEST_ANSWERS	QKEYNUM	QKEYNUM
TEST_RESPONSE	QKEYNUM	QKEYNUM

TEST_ANSWERS Table Field Mapping

The following image documents database fields for TEST_ANSWERS and how they are mapped to the ClearVantage application screen for survey questions.

Common Joins Using the TEST_ANSWERS Table

Tables to Join	Key Field	Key Field in the TEST_ANSWERS table
TEST_QUESTIONS	QKEYNUM	QKEYNUM

Some common joins using the TEST_ANSWER table.

TEST_CUSTOMER_RESPONSE Table Field Mapping

The following image documents database fields for TEST_CUSTOMER_RESPONSE and how they are mapped to the ClearVantage application screen for survey questions.

Common Joins Using the TEST_CUSTOMER_RESPONSE Table

Some common joins using the TEST_CUSTOMER_RESPONSE table.

TEST_RESPONSE Table Field Mappings

The following image documents the database fields for TEST_RESPONSE and how they are mapped to the ClearVantage application screen for survey questions.

Tables to Join	Key Field	Key Field in the TEST_CUSTOMER_RESPONSE table
TEST_MASTER	TESTCD	TESTCD
CUSTOMER	CUSTOMERCD	CUSTOMERCD

Common Joins Using the TEST_ RESPONSE Table

Some common joins using the TEST_ RESPONSE table.

Tables to Join	Key Field	Key Field in the TEST_ RESPONSE table
TEST_MASTER	TESTCD	TESTCD
TEST_QUESTION	QKEYNUM	QKEYNUM

Industry-Specific Mappings

The CVINDUSTRY system setting makes industry-specific field labeling and functionality available for certain industries. For example, for bar associations, certification credits are relabeled as “CLE,” and license dates are relabeled as bar admission dates. These settings are available for legal and medical associations.

Reference Tables

Throughout the system, dropdowns and multiselect fields have their options controlled from Reference Tables, which can be accessed via the “Reference Tables” menu option on the Administration tab of the main menu ribbon. Typically, the reference table provides a “code” value which is a shorter or numeric identifier to represent the actual option chosen, and the reference table also provides a plain English “description” value to be used in reports and online display. The following section describes common reference tables and how to join to them.

Company Information

The Company Information table in ClearVantage is named as COMPANYINFO table in database. The values of this table populate any dropdown fields where company information is required. Examples include invoices, dues and products. Attached below is a screenshot of the Company Information form in ClearVantage. It lists the columns of the table.

Following is the description of the fields of the Company Information Table.

Field Name in Database	Field Name Description
COMPANYCD	Code assigned to company, typically 3 digits starting with 001
COMPANYNAME	Name of company, this is the name of your association/business
LEGALNAME	Legal Name
MAILADDRESS	Mailing Address, this address is displayed on the default invoice in CV
SALUTATION	Salutation for the main contact
CONTACTNAME	Main Contact Name
JOBTITLE	Job Title of Main Contact
DEPARTMENT	Department of Main Contact
COMPANYPHONE	Company Phone Number
FAXPHONE	Fax Number
CONTACTPHONE	Main Contact Phone Number
CONTACTPHONEEXT	Main Contact Phone Extension
PAGERPHONE	Main Contact Pager Number
MOBILEPHONE	Main Contact Mobile Phone Number
EMAIL	E-mail Address
WWWADDRESS	URL for Company Website
BILLTOADDRESS	Bill to Address
SHIPTOADDRESS	Ship to Address
FISCALYEAREND	Month which fiscal year ends
INVOICELOGO	File location of Company Logo
COMPANYALTCD	Alternate Company Code
AR_ACCOUNT	Accounts Receivable Account Number
CASH_ACCOUNT	Primary Cash Account Number
LIABILITY_ACCOUNT	Liability Account Number
TAX_ACCOUNT	Tax Account Number
OTHERTAX_ACCOUNT	Other Tax Account
ACCOUNTEXPORT	Name of Accounting Software that CV will export to (only relevant to specific configurations)
OTHERINFO	Other Miscellaneous Information
POSTINGFLG	Back-end field that indicates whether a company is currently being posted using the ClearVantage Accounting Wizard
FEDEX_ACCOUNTID	FedEx Account Number
FEDEX_PWD	FedEx Account Password
FEDEX_KEY	FedEx API Key
FEDEX_METERNO	FedEx Meter Number
FEDEX_UPS_SHIPFROMADDRESS	Default FedEx Ship From Address

CompanyInfo Table Joins

Some common joins using the CompanyInfo table.

Tables to Join	Key Field	Key Field in the COMPANYINFO table
INVOICE	COMPANYCD	COMPANYCD
PAYMENTS	COMPANYCD	COMPANYCD
SESSION	COMPANYCD	COMPANYCD
PRODUCT	COMPANYCD	COMPANYCD

Sample Table Joins

Committee Positions

Committee Positions table in ClearVantage is named as JOBTITLEREF table in database. The value in the JOBTITLEREF table populates the Position drop down of the committee notebook. Attached below is a screenshot of the Committee Positions that are found under Reference Tables.

Following is a description of the columns of the Committee Positions Table.

Field Name in Database	Field Name Description
JOBTITLE	Name of Committee Position
ORDERNUM	The order which the dropdown/web should display the Committee Positions. By default, committee positions list will be in alphabetical order.
ONWEBFLG	When set to ‘Y’, the JOBTITLE of the Committee Position is displayed on the web. If it is set to ‘N’, the value of Committee Position exists in database but is not displayed on the web. This option is a client specific configuration.

Committee Types

The Committee Type table in ClearVantage is named COMMITTEETYPEREF in the database. The values of this table populate the “Type” dropdown in the Committee Notebook.

Following is a description of the columns of the Committee Types Table.

Field Name is Database	Field Name Description
TYPECD	Shorthand code for Committee Type. Limited to 10 characters.
DESCRIPTION	Full Name of Committee Type/Description of Committee Type.
ONWEBFLG	When set to ‘Y’, the selected Committee Type will be displayed on the web. If it is set to ‘N’, the Committee Type exists in database but is not displayed on the web. This option requires client specific web configuration.

COMMITTEETYPEREF Table Joins

Some common joins using the CommitteeTypeRef table.

Tables to Join	Key Field	Key Field in the COMMITTEETYPEREF table
Committee	TypeCD	TypeCD

Designations

The Designations table in ClearVantage is named DESIGNATIONREF in the database. The values of this table populate the Designation dropdown field in the Certification Setup notebook, the Designation List in the Customer notebook, and the Designation dropdown on the Designations history tab of the Customer notebook.

Screenshot from Certification notebook:

Screenshot from Customer notebook:

Following is a description of the columns of the Designations Table.

Field Name is Database	Field Name Description
DESIGNATIONCD	Shorthand code for Designation. Limited to 10 characters.
DESCRIPTION	Full Name of Designation/Description of Designation.
DESIGNATOR	Name of Person/Group that provides a given designation.

DESIGNATIONREF Table Joins

Joining to the DESIGNATIONREF table is a more complex join than most other tables, because the data stored on the CUSTOMER table could contain multiple values. For example, the DESIGNATIONLST field on the CUSTOMER table could contain several values, comma separated. For this reason, this join must be accomplished through an intermediary object that converts the comma separated DESIGNATIONLST field into separate rows. The join to CERTIFCATIONREF and CUSTOMERDESIGNATION do not require an intermediary object since the selection dropdowns only allow one value.

Tables to Join	Intermediary Object	Key Field	Key Field in the DESIGNATIONREF table
CUSTOMER	vw_Designations	DESIGNATIONCD	DESIGNATIONCD
CUSTOMERDESIGNATION	-	DESIGNATIONCD	DESIGNATIONCD
CERTIFICATIONREF	-	DESIGNATIONCD	DESIGNATIONCD

Sample Table Joins

Skills

The Skills Code table in ClearVantage is named SKILLREF in database. The value in this table populates the Skills multiselect field in the customer notebook. Attached below is a screenshot of the Skill Codes table. It lists all the columns of the table.

Screenshot from Customer notebook:

Following is the description of the fields of the Skill Codes Table.

Field Name in Database	Field Name Description
SKILLCD	Shorthand Code for the Skill. Limited to 5 characters.
DESCRIPTION	Description of the Skill code
MASTERCD	This field can be used to create a hierarchy of skills. Requires specific configuration.
ORDERNUM	The order which the dropdown/web should display skills. By default, it will be in order they are entered into CV.
COMPANYCD	Company for which the Skill should be available as an option. For multi-company databases only. This value is a foreign key and must exist in the Company Info table.
SKILLTYPECD	Optional field to further categorize a Skill.

SKILLREF Table Joins

Joining to the SKILLREF table is a more complex join than most other tables, because the data stored on the CUSTOMER and ORGANIZATION table could contain multiple values. For example, the SKILLCDLST field on the CUSTOMER table could contain several values, comma separated. For this reason, this join must be accomplished through an intermediary object that converts the comma separated SKILLCDLST field into separate rows.

Tables to Join	Intermediary Object	Key Field	Key Field in the SKILLREF table
CUSTOMER	vw_Skills_Ind	SKILLCDLST	SKILLCD
ORGANIZATION	vw_Skills_Org	SKILLCDLST	SKILLCD

Sample Table Joins

Interests

The Interest Codes table in ClearVantage is named INTERESTREF in the database. The value in this table populates the Skills multiselect field in the customer notebook. Attached below is a screenshot of the Interest Codes table. It lists all the columns of the table.

Screenshot from Customer notebook:

Following is the description of the fields of the Interest Codes Table.

Field Name in Database	Field Name Description
INTERESTCD	Shorthand Code for the Interest. Limited to 5 characters.
DESCRIPTION	Description of the Interest code
MASTERCD	This field can be used to create a hierarchy of interests. Requires specific configuration.
ORDERNUM	The order which the dropdown/web should display skills. By default, it will be in order they are entered into CV.
COMPANYCD	Company for which the Interest should be available as an option. For multi-company databases only. This value is a foreign key and must exist in the Company Info table.
ONWEBFLG	When set to ‘Y’, the Interest will be displayed on the web where appropriate. If it is set to ‘N’, the Interest exists in database but is not displayed on the web. This option requires client specific web configuration.

INTERESTREF Table Joins

Joining to the INTERESTREF table is a more complex join than most other tables, because the data stored on the CUSTOMER and ORGANIZATION table could contain multiple values. For example, the INTERESTCDLST field on the CUSTOMER table could contain several values, comma separated. For this reason, this join must be accomplished through an intermediary object that converts the comma separated INTERESTCDLST field into separate rows.

Tables to Join	Intermediary Object	Key Field	Key Field in the INTERESTREF table
CUSTOMER	vw_Interests_Ind	INTERESTCDLST	INTERESTCD
ORGANIZATION	vw_Interests_Org	INTERESTCDLST	INTERESTCD

Sample Table Joins

Education Level/Degree

The Education Level/Degree table in ClearVantage is named EDUCATIONLEVELREF in the database. The value in this table populates the Education List multiselect field in the customer notebook. Attached below is a screenshot of the Education Level/Degree table. It lists all the columns of the table.

Screenshot from Customer notebook:

Following is the description of the fields of the Education Level/Degree Codes Table.

Field Name in Database	Field Name Description
EDUCATIONLEVELCD	Shorthand Code for the Education Level/Degree. Limited to 5 characters.
DESCRIPTION	Description of the Education Level/Degree
TYPECD	Type of Education/Degree
AUTOFILLFLG	When set to ‘Y’ the Institution field in the Education Tab will be populated automatically. When set to ‘N’ the field will not populate. See Screenshot below.
CERTIFYINGBOARD	Optional field to provide additional information regarding Certifying Body of an Education Level/Degree

Screenshot from Education Tab:

EDUCATIONLEVELREF Table Joins

Joining to the EDUCATIONLEVELREF table is a more complex join than most other tables, because the data stored on the CUSTOMER and ORGANIZATION table could contain multiple values. For example, the EDUCATIONCDLST field on the CUSTOMER table could contain several values, comma separated. For this reason, this join must be accomplished through an intermediary object that converts the comma separated EDUCATIONCDLST field into separate rows.

Tables to Join	Intermediary Object	Key Field	Key Field in the EDUCATIONLEVELREF table
CUSTOMER	vw_Education_Ind	EDUCATIONLEVELCD	EDUCATIONLEVELCD

Sample Table Joins

Ethnicity

The Ethnicity table in ClearVantage is named ETHNICITYREF in the database. The values in this table populate the Ethnicity dropdown field in the Customer notebook. Attached below is a screenshot of the Ethnicity table. It lists all the columns of the table.

Following is the description of the fields of the Ethnicity Codes Table.

Field Name in Database	Field Name Description
ETHNICITYCD	Ethnicity Code; limited to 10 characters.
DESCRIPTION	Full name of ethnicity
ONWEBFLG	When set to ‘Y’, the Ethnicity will be displayed on the web where appropriate. If it is set to ‘N’, the Ethnicity value exists in the database but is not displayed on the web. This option requires client specific web configuration.

Ethnicity Table Joins

Some common joins using the EthnicityRef table.

Tables to Join	Key Field	Key Field in the ETHNICITYREF table
CUSTOMER	ETHNICITY	ETHNICITYCD

Sample Table Joins

Industry

The Industry table in ClearVantage is named INDUSTRYREF in the database. The values in this table populate the Industry dropdown field in the Customer and Organization notebook. Attached below is a screenshot of the Industry table. It lists all the columns of the table.

Following is the description of the fields of the Industry Codes Table.

Field Name in Database	Field Name Description
INDUSTRYCD	Industry Code; limited to 10 characters.
DESCRIPTION	Description of Industry Code
ONWEBFLG	When set to ‘Y’, the Industry will be displayed on the web where appropriate. If it is set to ‘N’, the Industry value exists in the database but is not displayed on the web. This option requires client specific web configuration.

Industry Table Joins

Some common joins using the IndustryRef table.

Tables to Join	Key Field	Key Field in the INDUSTRYREF table
CUSTOMER	INDUSTRY	INDUSTRYCD

Sample Table Joins

Lookups

The Lookups table in ClearVantage is multi-use reference table used for a wide variety of customizations and client-specific modules. Please refer to the System Administrators Guide for more guidance on common categories and usage of this table.

Following is the description of the fields of the Industry Codes Table.

Field Name in Database	Field Name Description
CODENUM	Auto-incremented numeric value
CATEGORY	Group name: when querying the lookups table, you will use the Category column as your filter. (E.g., select * from lookups where Category = “Your Value”)
CODE	Code/Value/Acronym
DESCRIPTION	Description of the Code
SORTORDER	(Optional) Order which values should be listed
TEXTVAL	(Optional) Additional text value field
NUMVAL	(Optional) Additional numeric value field
LINKKEY	(Optional) Used for integrations with 3rd party applications

Member Type (Individual)

The Member Type (Individual) table in ClearVantage is named CUSTOMERTYPEREF in the database. The CUSTOMERTYPE field of the CUSTOMERTYPEREF Table populates the drop-down list for the member type in the individual notebook.

Following is a description of the commonly used fields in the CUSTOMERTYPE table.

Field Name in Database	Field Name Description
CUSTOMERTYPE	Type of customer
DESCRIPTION	Description of the customer type
MEMBEROPTIONS	When set to ‘Y’, users are prompted to make a choice on loading the member options linked to the customer type they have selected
JOURNALFLG	When set to ‘Y’, users are prompted to make a choice of loading the journal flag associated with the customer type they have selected
COMPANYCD	Company Code associated with the chosen customer type
PRIMARYFLG	Used for Multi Company clients to filter what Member Types appear on the Individual Profile tab vs. the Specialty Membership tab in the Customer Notebook.
ROLENAME	When set to 'Y', users have the ability to see a different notebook layout based on member type. It stores the associated role the user should see when viewing an Individual Notebook of a particular type.
NEWRECORD_ROLENAME	When set to 'Y', a form will appear to select a particular member type when adding an Individual. If that member type has a specific new record role attached, it will load that role for users to enter the new record.

CUSTOMERTYPEREF Table Joins

Some common joins using the CUSTOMERTYPEREF table.

Tables to Join	Key Field	Key Field in the CUSTOMERTYPEREF table
CUSTOMER	CUSTOMERYPE	CUSTOMERTYPE
SUBSCRIPTIONREF	CUSTOMERTYPE	CUSTOMERTYPE (SUBSCRIPTIONREF.UPDATETYPEFLG = I)

Sample Table Joins

Member Class (Individual)

Member Class (Individual) table in ClearVantage is named INDMEMCLASSREF table in the database. The values in the table populate the drop-down list of the member class in an Individual Notebook.

Following is the description of the fields of the Member Class (Individual Table).

Field Name in Database	Field Name Description
MEMCLASSSTT	Member Class Code
DESCRIPTION	Description of the member class code
COMPANYCD	Company code associated with the chosen member class

INDMEMCLASSREF Table Joins

Some common joins using the INDMEMCLASSREF table.

Tables to Join	Key Field	Key Field in the INDMEMCLASSREF table
CUSTOMER	CUSTOMERCLASSSTT	MEMCLASSSTT

Sample Table Joins

Membership Class (Organization)

Membership Class (Organization) table in ClearVantage is named ORGMEMCLASSREF in the database. The values in the ORGMEMCLASSREF field populates the drop-down for the organization class in the Organization Notebook in ClearVantage. Attached is a screenshot of the table of Membership Class (Organization). It lists all the fields that the table can hold.

Following is the description of the fields of the Membership Class (Organization)

Field Name in Database	Field Name Description
MEMCLASSSTT	Member Class Code
DESCRIPTION	Description of the Member Class Code

ORGMEMCLASSREF Table Joins

Some common joins using the ORGMEMCLASSREF table.

Tables to Join	Key Field	Key Field in the ORGMEMCLASSREF table
ORGANIZATION	ORGCLASSSTT	MEMCLASSSTT

Sample Table Joins

Member Type (Organization)

Member Type (Organization) Table in ClearVantage is named as ORGTYPEREF table in database. The value in this table populates the drop down of the member type field in the organization notebook. Attached below is a screenshot of the Member Type (Organization) table. It lists all the columns of the table.

Following is the description of the fields of the Member Type (Organization) Table.

Field Name in Database	Field Name Description
ORGTYPE	Organization Code
DESCRIPTION	Description of the organization code
MEMBEROPTIONS	When set to ‘Y’, users are prompted to make a choice on loading the member options linked to the customer type they have selected
COMPANYCD	Company code that is associated with the corresponding organization type
PRIMARYFLG	Used for Multi Company clients to filter what Member Types appear on the Organization Profile tab vs. the Specialty Membership tab in the Organization Notebook
ROLENME	When set to 'Y', users have the ability to see a different notebook layout based on Organization type. Field 'ROLENAME' has been added to reference tables ORGTYPEREF and CUSTOMERTYPEREF to store the associated role the user should see when viewing an Organization Notebook of a particular type.
NEWRECORD_ROLENAME	When set to 'Y', a form will appear to select a particular member type when adding an organization. If that member type has a specific new record role attached, it will load that role for users to enter the new record.

ORGTYPEREF Table Joins

Some common joins using the ORGTYPEREF table.

Tables to Join	Key Field	Key Field in the ORGTYPEREF table
ORGANIZATION	ORGTYPE	ORGTYPE

Sample Table Joins

Membership Options

Membership Options table in ClearVantage is named as MEMBEROPTIONSREF table in database. The value in the MEMBEROPTIONSREF table populates the drop down of the membership options for individual as well as organization notebook. Attached below is a screenshot of the Membership Options table in ClearVantage. It lists all the columns of the table.

Following is a description of the columns of the Membership Options Table.

Field Name is Database	Field Name Description
MEMBEROPTION	Member Option Code
DESCRIPTION	Description of the Member Option Code
OPTIONTYPE	When set to ‘M’, the option type will be displayed in Individual and Organization Notebook. When set to ‘S’, the option type will be displayed in Subscription Notebook.
COMPANYCD	Company code associated with the chosen Member Option Code
ONWEBFLG	When set to ‘Y’, the corresponding value of the MEMBEROPTION is displayed on the web. If it is set to ‘N’, the value of MEMBEROPTION exists in database but is not displayed on the web. This option is a client specific configuration.
PRIMARYFLG	If the primary flag is set to ‘Y’, it makes the corresponding member option as default

MEMBEROPTIONSREF Table Joins

Joining to the MEMBEROPTIONSREF table is a more complex join than most other tables, because the data stored on the CUSTOMER and ORGANIZATION table could contain multiple values. For example, the MEMBEROPTIONSLST field on the CUSTOMER table could contain several values, comma separated. For this reason, this join must be accomplished through an intermediary object that converts the comma separated MEMBEROPTIONSLST field into separate rows.

Tables to Join	Intermediary Object	Key Field	Key Field in the MEMBEROPTIONSREF table
ORGANIZATION	vw_MemberOptions_Org	MEMBEROPTIONS	MEMBEROPTION
CUSTOMER	vw_MemberOptions_Ind	MEMBEROPTIONLST	MEMBEROPTION

Sample Table Joins

Org. Products

The Org. Products table in ClearVantage is named ORGPRODUCTREF in the database. The values in this table populate the Product dropdown field in the Organization notebook. Attached below is a screenshot of the Org. Products table. It lists all the columns of the table.

Example screenshot from Organization Notebook:

Following is the description of the fields of the Occupation Codes Table.

Field Name in Database	Field Name Description
PRODUCTCD	Product Code; limited to 5 characters.
DESCRIPTION	Description of Product

Org. Product Table Joins

Joining to the ORGPRODUCTREF table is a more complex join than most other tables, because the data stored on the ORGANIZATION table could contain multiple values. For example, the PRODUCTCDLST field on the ORGANIZATION table could contain several product values, comma separated. For this reason, this join must be accomplished through an intermediary object that converts the comma separated PRODUCTCDLST field into separate rows.

Tables to Join	Intermediary Object	Key Field	Key Field in the ORGPRODUCTREF table
ORGANIZATION	vw_Products_Org	PRODUCTCDLST	PRODUCTCD

Sample Table Joins

Organization Type / Property Type

(IMPORTANT! This table does not populate the Organization Member Type field in the Organization notebook. Please refer to the Member Type (Org) table for information regarding that field)

The Organization Type table in ClearVantage is named PROPERTYTYPEREF in the database. The values in this table populate the Org. Type dropdown field in the Organization notebook. Attached below is a screenshot of the Organization Type table. It lists all the columns of the table.

Following is the description of the fields of the Organization Type Codes Table.

Field Name in Database	Field Name Description
PROPERTYTYPE	Property Type Code; limited to 20 characters.
DESCRIPTION	Description of Property

Organization Type Table Joins

Some common joins using the PropertyTypeRef table.

Tables to Join	Key Field	Key Field in the PROPERTYTYPEREF table
ORGANIZATION	PROPERTYTYPE	PROPERTYTYPE

Sample Table Joins

Relationship Type

The Relationship Type table in ClearVantage is named RELATIONSHIPREF in the database. The values in this table populate the Relationship dropdown field in the Relationships Tab of the Customer and Organization notebook. Attached below is a screenshot of the Relationship Type table. It lists all the columns of the table.

Following is the description of the fields of the Relationship Codes Table.

Field Name in Database	Field Name Description
RELATIONSHIP	Relationship Code; limited to 20 characters.
DESCRIPTION	Description of Relationship
RECRELATIONSHIP	Reciprocal Relationship

Relationship Type Table Joins

Some common joins using the RelationshipRef table.

Tables to Join	Key Field	Key Field in the RELATIONSHIPREF table
WHOKNOWSWHO	RELATIONSHIP	RELATIONSHIP

Zip to Chapter

The Zip to Chapter table in ClearVantage is named ZIPTOCHAPTER in the database. The values in this table populate allow CV to auto-populate Chapter information based on an Individual or Organizations zip code. Attached below is a screenshot of the Zip to Chapter table. It lists all the columns of the table.

Following is the description of the fields of the Zip to Chapter Table.

Field Name in Database	Field Name Description
ZIPSTART	Starting range of zip code number
ZIPEND	End range of zip code
CHAPTERCD	Name of Chapter
COUNTYNAME	(Optional) County Name
REGIONCD1	(Optional) Region Code
REGIONCD2	(Optional) Secondary Region Code
CITY	(Optional) City
STATECD	(Optional) State
ZIPCODE	(Optional) Region Specific Zip Code

Useful Database Views

The ClearVantage database contains numerous “views,” which are pre-built queries in the database that can be referenced for report writing just like a table. This section documents views that may be useful in your report writing. For each view, a brief description is provided, along with notes on key fields in the view and how to join the view to other tables.

Most views in the baseline database serve some specific purpose for the software, where possible, this is noted below. Because of this, these views cannot be customized on a per client basis.

If you are finding it to be especially difficult or complex to create a report because of the complexity of your criteria and/or the range of tables needed for your report, you may find it helpful to ask your support analyst to investigate creating a view for your purpose. This can greatly ease your report-writing process to have a view encapsulate complex logic. In the long run, this can also be a benefit to have critical reporting logic centralized into a view that you can rely on in multiple reports.

General: vw_allrecords

Lists all individuals and organizations in the database and includes frequently used columns such as name and contact info. This view is especially useful when joining transaction history tables, such as INVOICE or SUBSCRIPTION, to the payee or customer for the transaction, since transactions can be linked to either an individual or an organization. Note, there is a similarly named view vw_allrecords_info which is the same as vw_allrecords, with a handful of additional email-specific fields included.

Each record in the view is uniquely identified by the combination of CUSTOMERTYPECD and CUSTOMERCD. Individuals will have CUSTOMERTYPECD = “I,” and the CUSTOMERCD will correspond to the CUSTOMERCD field in the CUSTOMER table. Organizations will have CUSTOMERTYPECD = “O,” and the CUSTOMERCD corresponds to the ORGCD field in the ORGANIZATION table.

Tables to Join	Key Field	Key Field in the View
CUSTOMER	CUSTOMERCD	CUSTOMERCD and CUSTOMERTYPECD = ‘I’
ORGANIZATION	ORGCD	CUSTOMERCD and CUSTOMERTYPECD = ‘O’
SUBSCRIPTION	CUSTOMERCD CUSTOMERTYPECD	CUSTOMERCD CUSTOMERTYPECD
INVOICE	PAYEECD PAYEETYPECD	CUSTOMERCD

General: vw_changelog

All changelog data is available in this view, which reads from the CHANGELOG table, and converts the CHANGEINFO field to text for readability and easy reporting (the actual CHANGEINFO field stores data in a non-human-readable format).

Each record is uniquely identified by the combination of TABLECD, which indicates the table being logged, and RECORDID, which corresponds to the primary key. The full list of these is noted below in the table of example joins and is stored in the LOOKUPS table for reference as well. The “Key Field” noted below corresponds to the RECORDID in the view.

Tables to Join	Key Field	TABLECD
CUSTOMER	CUSTOMERCD	C
ORGANIZATION	ORGCD	O
CERTIFICATION	TRANSACTIONNUM	R
CERTIFICATION_MASTER	MASTERID	M
INDSESSION	ASSIGNEDSESSIONCD	S
INVOICE	INVOICENUM	I
PAYMENTS	PAYMENTNUM	P
REFERRAL_MASTER	REFERAL_NUM	J
REFERRAL_PANELIST_CATEGORIES	KEYNUM	L
SCHEDULE	UNIQUEID	A

Membership: vw_DuesInvoice

Lists all unpaid dues renewal invoices, by CUSTOMERCD. In the baseline system, this view is used by the website to know which users to prompt to pay their dues renewal. It includes both individual and organization dues invoices, linked to the CUSTOMERCD of the individual renewing their dues or, in case of an organization dues, the CUSTOMERCD of the organization’s primary contact. This view is more likely to have been customized for client-specific web form rules.

The view returns two columns, it returns the CUSTOMERCD, and the INVOICENUM.

Tables to Join	Key Field	Key Field in the View
CUSTOMER	CUSTOMERCD	CUSTOMERCD
ORGANIZATION	CUSTOMERCDLINK	CUSTOMERCD
INVOICE	INVOICENUM	INVOICENUM

Membership: vw_FlowThroughAvailable

For organization flow-through membership structures, this view lists flow-through quantities for the organization’s current membership dues. The view is used on the website and in CV Online when making flow-through assignments.

The view returns the following columns:

• ORGCD – which organization the record is for

• PRIMARYCUSTOMERCD – organization primary contact

• PARENTSUBNUM – the organization’s membership dues

• FLOWQTY_TOTAL – total available flow-through included in the dues ($0 rate)

• FLOWQTY_USED – total flow-through slots already assigned

• FLOWQTY_REMAINING – unused flow-through slots

Tables to Join	Key Field	Key Field in the View
ORGANIZATION	ORGCD	ORGCD
CUSTOMERCD	CUSTOMERCD	PRIMARYCUSTOMERCD
SUBSCRIPTION	SUBSCRIPTIONNUM	PARENTSUBNUM

Membership: vw_DuesTotalbyYear

This view lists, by year, each customer’s membership dues amount for non-cancelled membership dues. It includes the CUSTOMERCD, TOTAL, INVOICENUM, and YEAR.

Tables to Join	Key Field	Key Field in the View
CUSTOMER	CUSTOMERCD	CUSTOMERCD
INVOICE	INVOICENUM	INVOICENUM

Organizations: vw_PARENTORG_LEVEL1

This view identifies parent-child organization relationships. It returns the parent org (PARENTORGCDORGCD) and child org (ORGCD).

More broadly, the database contains 5 parent-child organization views, numbered by the level down from the parent. In addition to vw_PARENTORG_LEVEL1, there is …LEVEL2, …LEVEL3, …LEVEL4, and …LEVEL5. Each is structured to return 3 columns:

• MAINORGCD – the top-level parent

• ORGCD – for the given level, the child organization

• PARENTORGCDORGCD – for the given level, the parent organization

The diagram below shows the meaning of fields from the view for vw_PARENTORG_LEVEL2.

In this scenario, Level 2 provides the relationships between ABCDEF Subsidiaries and its West Coast and Midwest regions. ABCDEF is the parent, and West Coast and Midwest are each child ORGCD’s to the parent. The top-level organization in the hierarchy is ABC Corporation.

Events: vw_SessionRegQty

This view lists total registrants for each event (non-cancelled, non-waitlisted).

Tables to Join	Key Field	Key Field in the View
SESSION	SESSIONALTCD	SESSIONALTCD

Events: vw_SESSIONCOUNTS

This view lists registration totals for each event. It returns several useful columns for reporting:

• TotalRegisteredQty – total registered (no filters; sum of INDSESSION.COUNTQTY)

• TotalCancelledQty – total cancelled (sum INDSESSION.CANCELLEDQTY)

• TotalExpectedQty – total registered, less cancelled and waitlisted

• TotalShowQty – total attended (sum INDSESSION.ATTENDEDQTY)

• TotalWaitlistQty – total waitlisted (sum INDSESSION.WAITLISTQTY)

The view also includes a field named TotalRevenue, but the accuracy of this field can depend on client-specific configuration, and it is not recommended to use this field in reporting without verifying the data being returned.

Tables to Join	Key Field	Key Field in the View
SESSION	SESSIONALTCD	SESSIONALTCD

Events: vw_SessionFunctionCounts

This view lists registration totals for each event function. It returns the following columns, based the actual registration data, useful for reporting:

• TotalRegistered – total registered (no filters; sum of INDFUNCTION.COUNTQTY)

• TotalCancelled – total cancelled (sum INDFUNCTION.COUNTQTY, if CANCELLEDFLG=Y)

• TotalWaitlisted – total waitlisted (sum INDFUNCTION.COUNTQTY, if WAITLISTFLG=Y)

• TotalExpected – total registered to function, less cancelled and waitlisted

Additionally, the view returns several columns labeled with “Attendee,” these columns reflect totals stored directly on the function table (SESSIONFUNCTION). Generally speaking, these values should match those listed above. It is recommended to rely on the columns listed above as they reflect actual registration data.

Tables to Join	Key Field	Key Field in the View
SESSIONFUNCTION	SESSIONALTCD FUNCTIONCD	SESSIONALTCD FUNCTIONCD
SESSION	SESSIONALTCD	SESSIONALTCD

Events: vw_SpeakerQty

This view lists total speakers for each event.

Tables to Join	Key Field	Key Field in the View
SESSION	SESSIONALTCD	SESSIONALTCD

Events: vw_SESSIONFUNCTION_ALL

This view provides information about each registrant’s purchased functions, along with info about the setup of the event and its functions. It combines key event tables for easier reporting: events (SESSION) event functions (SESSIONFUNCTION), registrations (INDSESSION), and registration functions (INDFUNCTION). Columns include all columns from INDFUNCTION, function TITLE, FUNCTIONTYPE, and FUNCTION LEVEL from SESSIONFUNCTION, and the event’s SESSIONNAME and SESSIONBEGDATE.

Tables to Join	Key Field	Key Field in the View
SESSION	SESSIONALTCD	SESSIONALTCD
SESSIONFUNCTION	SESSIONALTCD FUNCTIONCD	SESSIONALTCD FUNCTIONCD
INDFUNCTION	ASSIGNEDSESSIONCD FUNCTIONCD	ASSIGNEDSESSIONCD FUNCTIONCD
INDSESSION	ASSIGNEDSESSIONCD	ASSIGNEDSESSIONCD

Events: vw_EXHIBITOR_DIRECTORY

This view returns information used in the online exhibitor directory. It is frequently updated for client-specific web configurations. It can be useful for reporting in that it allows reports to be based on the same info used on the website. Typical key fields include: SESSIONALTCD, BOOTHCD, INVOICENUM, INVOIECDATE, TOTALAMT, ASSIGNEDSESSIONCD, EXHIBITORORGADDRESS, PRODUCTCDLST.

Tables to Join	Key Field	Key Field in the View
SESSION	SESSIONALTCD	SESSIONALTCD
EVENTBOOTH	SESSIONALTCD BOOTHCD	SESSIONALTCD BOOTHCD
INVOICE	INVOICENUM	INVOICENUM
ORGANIZATION	ORGCD	ORGCD

Certifications: vw_CertificationCEU

This view provides CEU totals by individual, certification, and year. It is frequently configured on a per-client basis.

Tables to Join	Key Field	Key Field in the View
CUSTOMER	CUSTOMERCD	CUSTOMERCD
CERTIFICATIONREF	CERTIFICATIONCD	CERTIFICATIONCD

LRS: vw_REFERRAL_MASTER

For the Lawyer Referral Service module, this view returns all referral-related fields from REFERRAL_MASTER, along with name information for the panelist from CUSTOMER. This view is used by CV9 for the referral list screen.

Tables to Join	Key Field	Key Field in the View
REFERRAL_MASTER	REFERAL_NUM or CASE_NUM	REFERAL_NUM or CASE_NUM
CUSTOMER	CUSTOMERCD	CUSTOMERCD

LRS: vw_REFERRAL_PANELISTS

For the Lawyer Referral Service module, this view returns fields about panelists from REFERRAL_PANELIST_POOL, CUSTOMER, and CUSTOMERADDRESS. It is used by CV9 for the referral panelist notebook screen and in other panelist-related functionality. Key fields include LRSAddress, panelist_status, rotation_status, LRSPREFPHONE, and LRSPREFEMAIL.

Tables to Join	Key Field	Key Field in the View
REFERRAL_PANELIST_POOL	CUSTOMERCD	CUSTOMERCD
CUSTOMER	CUSTOMERCD	CUSTOMERCD

LRS: VW_LRS_Preferred_Addresses

For the Lawyer Referral Service module, this view is used to return additional LRS-specific addresses from CUSTOMERADDRESS, for use in CV9 in the panelist notebook where the user can indicate a preferred LRS address. By default, this view returns all records in CUSTOMERADDRESS for the panelist, but the view can be customized to filter the addresses.

Tables to Join	Key Field	Key Field in the View
CUSTOMER	CUSTOMERCD	CUSTOMERCD
CUSTOMERADDRESS	ADDRESSNUM	ADDRESSNUM

Products: vw_CVO_FulfillableItems

This view identifies the distinct list of products (PRODUCTCD) that require fulfillment. It is used by CV Online in the “Order Fulfillment” interface; specifically, this view defines the fulfillable items, and another view, vw_CVO_FulfillableInvoices, lists the applicable invoices and is used directly by CV Online.

This view is configured on a per-client basis for the specific items requiring fulfillment. By default, it includes products that are (a) not downloadable, (b) do not have a product type of ACCOUNT, ACCOUNTING, MISC, or CERTFEE, and (c) are marked as “On Web,” or have a shipping fee entered, or are marked as calculating shipping.

Tables to Join	Key Field	Key Field in the View
PRODUCT	PRODUCTCD	PRODUCTCD
INVOICEITEM	ITEMALTCD and ITEMSOURCE=’P’	PRODUCTCD

Products: vw_CVO_FulfillableInvoices

This view identifies the distinct list of invoices (INVOICENUM) that require fulfillment. It is used by CV Online in the “Order Fulfillment” interface. It relies on the view listed above, vw_CVO_FulfillableItems, to know which products require fulfillment. It includes all fields from the INVOICE table along with a calculated field ITEMINFO which lists the first item on the invoice and an indication of how many total items are on the invoice.

Tables to Join	Key Field	Key Field in the View
INVOICE	INVOICENUM	INVOICENUM

Invoices/Payments: vw_INVOICEITEMSUM

Total of invoice items for each invoice. Specifically returns INVOICENUM, PRICEAMT, FREIGTHAMT, TAXAMT, and TOTALAMT. The dollar amount fields are totaled from the INVOICEITEM table and should in all cases match the fields with the same names on the INVOICE table. Join to the INVOICE table on INVOICENUM.

Invoices/Payments: vw_INVOICEPAYTOTAL

Total of payment amount(s) applied to each invoice. Specifically returns INVOICENUM and PAIDAMT. The paid amount field is totaled from the INVOICEPAYMENT table and should match the amount in the PAIDAMT field of the INVOICE table. Join to the INVOICE table on INVOICENUM.

Invoices/Payments: vw_InvoiceTransactions

List of transactions for each invoice; conceptually, you can imagine summing the transactions for a given invoice to get its current balance. The view returns three types of transactions:

• Revenue

  • 1-Billing are items included when invoice was created

  • 2-Adjustments are items added later

• Cash

  • 3-Payment are payments applied to invoice; payments are expressed as negative values

View Field	Revenue, corresponds to INVOICEITEM table	Cash, corresponds to PAYMENTS / INVOICEPAYMENT table
RECORDNUM	ITEMNUM	PAYMENTNUM
RECORDDATE	ITEMDATE	PAYMENTDATE
RECORDSOURCE	ITEMSOURCE	N/A, blank
RECORALTCD	ITEMALTCD	USERBATCHCD
RECORDSUBCD	ITEMSUBCD	PAYMENTTYPECD
RECORDESCRIPTION	ITEMDES	Transaction description in form of ‘$XX applied to invoice, of total payment $YY’
RECORDAMT	TOTALAMT	PAYMENTAMT (specifically, from the INVOICEPAYMENT table, this is the amount applied to the invoice, expressed as a negative number)
POSTEDFLAG	POSTSTT	POSTSTT
POSTEDDATE	POSTPERIOD	POSTPERIOD
RUNNUM	RUNNUM	RUNNUM
AMTBILLED	For “1-Billing,” TOTALAMT	N/A
AMTADJUSTED	For “2-Adjustment,” TOTALAMT	N/A
AMTPAID	N/A	For “3-Payment,” PAYMENTAMT from INVOICEPAYMENT table

The view includes the following fields that have a different meaning for revenue vs. cash transactions.

Accounting: vw_GL_TRANSACTIONLOG_SUMMARY

This view provides unexported debits and credits from the Posting Wizard, summarized by account, run number, post period, and companycd. It is used by ClearVantage when generating the export for Great Plains.

Accounting: vw_PostingWizardPartialPay

This view provides a list of partial payments by invoice number. It is used in the Posting Wizard “Partial Payments” screen. For each invoice, it returns the invoice total and payment information.

Tables to Join	Key Field	Key Field in the View
INVOICE	INVOICENUM	INVOICENUM
PAYMENTS	PAYMENTNUM	PAYMENTNUM

Accounting: vw_PostingWizard_TB

This view provides the trial balance shown at the end of the Posting Wizard. It is organized by company, run, account, postperiod, and billperiod. It provides the debits and credits summarized in a standard accounting format.

Accounting: vw_PostingWizard_TB_2

This view provides the trial balance available in the Posting Analysis tool. It is organized by company, run, account, postperiod, and billperiod. It provides the debits and credits summarized in a standard accounting format.

Accounting: vw_Aging_detail

This view provides a simple aging query of non-dues invoices, organized into <30 days, 30-60 days, 60-90 days, and 90+ days, based on invoice due date. It returns the INVOICENUM, COMPANYCD, BALANCEDUE, AGE, and also includes a SORTORDER to make it easy to display the aged invoices in a desired order.

Tables to Join	Key Field	Key Field in the View
INVOICE	INVOICENUM	INVOICENUM

Other: Data SnapShot views

These views provide easy reporting on current-year data snapshot data, by summarizing each grouping across the months of the year. The views return grouping information as configured in the reference table, and a column for each month. There are two such views:

• vw_DATASNAPSHOTAMT_CURRENTYEAR – summary of snapshot amounts, typically used for dollar amount data which may have decimals

• vw_DATASNAPSHOTQTY_CURRENTYEAR – summary of snapshot quantities, typically used for counts, which are integers (no decimals)

Useful Structured Query Language Statements

It is not necessary to know Structured Query Language (SQL) to create reports and gadgets in ClearVantage. For users who have or wish to explore knowledge of SQL, this section describes basic information about SQL and using it in the context of ClearVantage, along with information about several specific SQL statements that may be useful when creating calculated columns in the Standard Report Writer or making modifications to Xtreme Reports or dashboard gadgets.

SQL is a common data manipulation language, and in all cases, more information about can be easily obtained by online research. ClearVantage specifically uses “SQL Server” technology, created by Microsoft. Information noted below assumes SQL Server and may not apply to other SQL technologies such as MySQL or Oracle.

It will be helpful to know a few things at the outset:

• Use single quotes around any strings, i.e., alphanumeric values like ‘Matt.’

• Enter dates in single quotes, e.g., ‘4/21/1984’ (MM/DD/YYYY) or ‘19840421’ (YYYYMMDD).

• When combining or comparing values, they must be of the same type, e.g., the database will give an error if you compare ‘Matt’ to 1984. However, this same comparison would be allowed if entered as ‘Matt’ and ‘1984,’ since each is entered as a string.

• When combining or comparing values, beware of NULL. This refers to the absence of data, and is akin to multiplying by zero, i.e., anything combined with NULL will return NULL. Anything compared to NULL will return false.

  • A ClearVantage example of this would be doing a report to list individuals not in the Midwest chapter. An individual in the West Coast chapter would be picked up in this query. An individual without any data in the chapter field, i.e., NULL, would not.

• It is conventional to type SQL in upper case, but this is not necessary. This convention is followed in the examples in this section.

CASE statement

A CASE statement is used like an IF-THEN-ELSE to display information based on other information being returned from the database. For example, “return an ‘X’ if the committee position contains the word ‘chair.’”

The statement is structured as follows:

1. CASE – the statement begins with the word CASE

2. WHEN X THEN Y – the statement contains 1 or more combinations of WHEN X THEN Y, where X is a logical statement that returns true or false, and Y is the value to be returned if X is true

3. ELSE – the statement can contain a default value to return if no WHEN criteria are met; if this is not provided, the default value is NULL

4. END – the statement ends with the word END

An example statement, created in the Standard Report Writer to return an “X” if the position contains the word chair, is as follows:

**CASE **

**WHEN INDCOMMITTEE.CPOSITION LIKE ‘%CHAIR%’ THEN ‘X’ **

**ELSE ‘’ **

END

The example statement listed above was created in the Standard Report Writer by typing it into the Expression window. Note that any column used in a CASE statement should be referenced in the format of TABLE.FIELD, as shown in the example, INDCOMMITTEE.CPOSITION.

LIKE statement and wildcards

When entering criteria, you can use the LIKE comparison to perform a wildcard search. This is demonstrated in the example above. The percent sign is the most common wild card character used in SQL, and it stands for any value. In the example above, a person would be picked up in the query if their position is any of “Chair,” “Co-Chair,” or “Chair-Elect.”

DATEDIFF

In any SQL database, the DATEDIFF function allows calculated a difference between two dates. This can be used in the report writer to create a calculated column, such as when doing an invoice aging report. The DATEDIFF function is built-into SQL for this purpose. By way of example, it is entered as follows:

DATEDIFF (DAY, INVOICE.DUEDATE, GETDATE())

Specifically,

1. The first parameter is what unit of difference to calculate. Common options include DAY, WEEK, MONTH, YEAR (check online for a more complete list).

2. The second parameter is the older date; note that the DUEDATE field from INVOICE is referenced as INVOICE.DUEDATE, which is the most specific way to tell the database which field to use.

3. The third parameter is the newer date; in this example a SQL keyword GETDATE() is used, which returns the current date and time.

DATEADD

In any SQL database, the DATEADD function allows adding some number of days, weeks, months, etc., to a date. It looks like:

DATEADD(DAY, 90, INVOICE.INVOICEDATE)

Specifically,

1. The first parameter is what unit of addition to do, in date terms. Common options include DAY, WEEK, MONTH, YEAR (check online for a more complete list).

2. The second parameter is the amount to add.

3. The third parameter is the date; older date; note that the INVOICEDATE field from INVOICE is referenced as INVOICE. INVOICEDATE, which is the most specific way to tell the database which field to use newer date.

GETDATE()

In any SQL database, the GETDATE() function returns the current date and time. Users should beware of the time portion:

• Any comparison using GETDATE() needs to account for the time being include. For example, a query for invoices created today, based on INVOICEDATE = GETDATE(), will return no results because GETDATE() will look like ‘2019-10-28 10:44am,’ whereas invoice dates do not have a time portion. It may be necessary to convert the GETDATE() to a date for this purpose.

• The time is returned based on the server’s location. For Sapphire clients, this is Central time. For self-hosted clients, this may be your office, or, if your database is hosted by another provider such as AWS, this would be the time zone the server is in.

CONVERT

In any SQL database, the CONVERT function allows converting data into other types. For example, converting a date into a string for purpose of displaying it in a certain way, comparing it, or combining it with other data. The function has 3 parts, by way of example:

CONVERT(VARCHAR, GETDATE(), 101)

1. The first parameter is the data type being converted to. Use VARCHAR for strings, INT for integers, DATETIME for dates.

2. The second parameter is the value being converted.

3. The last parameter is optional. It specifies the desired format. For example, for a date conversion, enter 101 to get a date formatted like MM/DD/YYYY.

   1. In the example above, CONVERT(VARCHAR, GETDATE(), 101) will return “10/28/2019”

   2. If the 101 was not entered, i.e., CONVERT(VARCHAR, GETDATE()), the return value will be “Oct 28 2019 10:52AM”

The CONVERT function is very common and useful. A wealth of online documentation exists describing its options.