Data Management

CV Pro

This guide is only applicable to enterprise clients using CV Pro or CV9.

Customizing Fields

ClearVantage allows you to adjust labels and settings for individual fields within each Notebook. These changes are made throughout the system and generally not at the Role or User level. You can relabel field names to better coordinate with your organization’s naming conventions, require a field be entered via ClearVantage, standardize formats of data in the field, and even change the color of the field. For example, you can label the field “PETNAMES” as “Research Area”.

From the “Pro Tools” menu in the upper right corner of ClearVantage Pro:

1. Click on the “Customize ClearVantage” button.

2. On the Field Specifications tab, use the “Table Name” dropdown to select the table where the field you want to relabel appears. Available tables include Action Item, Certification, Customer, Document, Employee, Event, Event Speaker, Ind. Committee, Location, Master Certification, Member Multi Company, Organization, Panelist, Product, Program, Product, Registration, Registration Functions, Subscription, Trainer, and Vendor.

   

3. To modify a field label for a field that is not already listed in the Field Name column, click the plus sign at the top of the tab. A new row will appear.

4. Click the Field Name you want to relabel from the dropdown menu in the blank row. Manually typing a field name into the Field Name column will not allow for the field to be relabeled or marked as required.

5. Click in the “Display Label” field and type the new label into the field.

6. You can add masks in the Edit Mask column to format information in a certain way. Detailed information on Edit Masks is below.

7. Once you have made all of your changes, click the checkmark button at the top of the tab to save your changes.

8. Close out of the Customize ClearVantage window.

9. Logout of ClearVantage and log back into ClearVantage.

Edit Masks

Edit Masks allow you to specify the format in which the information entered into the field is stored or displayed. Edit Masks contain three parts with each part separated by a semicolon. The first part indicates the format to be used for the data entered. The second part uses a single character to indicate whether characters used in the format should be saved in the text. The third part indicates the character used to represent spaces where characters have not been entered.

Note

Marking a field as required or adding an edit mask will impact data entry via ClearVantage but will not impact information entered via the web.

To enter an Edit Mask:

1. From on the “Pro Tools” menu in the upper right corner of ClearVantage Pro, click on the “Customize ClearVantage” button.

2. Enter the format of the Edit Mask using the characters you want to appear in the format (literal characters) as well as the following special characters:

   1. A: An alphanumeric character is required in this position. Alphanumeric characters include the letters a through z or the numerals 0 through 9.

   2. a: An alphanumeric character is permitted, but not required, in this position. Alphanumeric characters include the letters a through z or the numerals 0 through 9.

   3. C: An arbitrary character is required in this position. Arbitrary characters include any characters including the letters a through z or the numerals 0 through 9.

   4. c: An arbitrary character is permitted, but not required, in this position. Arbitrary characters include any characters including the letters a through z or the numerals 0 through 9.

   5. 0: A numeral is required in this position.

   6. 9: Only a numeral is permitted, but not required, in this position.

   7. #: A numeral or a + or a – sign is permitted, but not required, in this position.

   8. L: An alphabetic character is required in this position.

   9. l: Only an alphabetic character is permitted, but not required, in this position.

   10. >: All characters following this symbol are in uppercase until the end of the mask or until a < symbol is included in the mask format.

   11. <: All characters follow this symbol are in the lowercase until the end of the mask or until a > symbol is included in the mask format.

   12. _: Enters a space into the text.

3. If you want to use one of the special characters listed above as a literal character and have it appear in your formatting, precede the character with the “\” symbol.

4. Enter a semicolon to separate the format part of the edit mask and the second section.

5. Enter a “1” after the semicolon if you want to save the literal characters or “0” if you do not want to save the literal characters. Whether you are able to save the literal characters may depend on the length and type of field in the SQL table. In the screenshots below the format for Home Phone is (###)###- ####;0;_ and the format for Fax Phone is (###)###-####;1;_.

   1. If you save the literal characters, they will appear in reports and exports that you run from ClearVantage or SQL.

   2. If you do not save the literal characters, they will not appear in your reports or SQL data but will display within ClearVantage notebooks.

   3. If the System Setting “DISPLAYEDITMASK” is set to “Y”, the masks will display whether or not data is entered and regardless of whether or not you have clicked in the field.

   

6. If the system setting “DISPLAYEDITMASK” is set to “N” or the system setting does not exist in your system, not saving the literal characters will mean that the mask only displays when the field is clicked into but saving the literal characters means that they will always display.

   

7. Enter a semicolon to separate the third section of the edit mask from the final section.

8. Following the semicolon, enter the character that appears in the place for blanks.

9. Once you have made all of your changes, click the checkmark button at the top of the tab to save your changes.

10. Close out of the Customize ClearVantage window.

11. Logout of ClearVantage and log back into ClearVantage.

Sample Edit Masks

The following edits masks can be used in ClearVantage.

• Phone Mask: (###)_###-####;0;_

  • This phone number will appear as (301) 657-8089 in ClearVantage notebooks.

  • For reports, the phone number will appear as 3016578089.

• Date Mask: ##/##/####;1;_

  • This date will appear as 05/22/2019 in ClearVantage notebooks and reports

• First Name: >L<llllllllll;0;_

  • The first name will appear as John with the first letter being forced to be an uppercase letter.

• You can enter masks on open text fields such as ADDRESS1 or DEPARTMENT that will limit the number of characters that a user can enter via ClearVantage. The format entered in the Edit Mask column should be ccccccccccccccc;0;_ with a “c” for each character that should be allowed to be entered. This will not limit the number of characters that can be entered via the website. That limit will need to be indicated separately on the website.

Note

When entering a phone number into a notebook, users can override the phone mask for non-U.S./Canada phone numbers by entering a + sign before the number. Example: +99 (1) 234 567

User-Defined Fields

User-defined (UDEF) fields on the Profile tabs in the Individual, Organization, Event, Product, and Subscription notebooks can be changed to allow the fields to collect data as dropdown, checkbox, radio button, or multi-select fields. Reference values for radio button, dropdown, or multi-select fields can be queried from existing ClearVantage tables or can be entered as a string. The following document describes some examples of configuring UDEF fields for the Individual Notebook, but the same concepts apply to the other notebooks as well.

Configuring Profile UDEF Fields

From on the “Pro Tools” menu in the upper right corner of ClearVantage Pro:

1. Click on the “Customize ClearVantage” button.

2. Click on the UDFProfile Settings tab and select the appropriate table from the Table Name dropdown.

3. To add a new row, click the “+” button.

   

4. In the SearchFieldName column, select the UDEF field from the dropdown. Only UDEF fields for the table that are not hard-coded for other purposes are available. Manually typing a field name into the Field Name column will not allow for the field to be relabeled or marked as required.

5. Choose the type of format you want in the SelectControlType field.

   1. CHECKBOX: Provides a single checkbox for Yes/No data entry.

   2. DATEEDIT: Provides a calendar dropdown to select a date for data entry.

   3. DROPDOWNCHECKBOX: Provides a dropdown list from which multiple options can be selected.

   4. DROPDOWNCOMBOBOX: Provides a two-column, single-select dropdown.

   5. DROPDOWNLOOKUP: Provides a one-column, single-select dropdown.

   6. RADIOGROUP: Provides radio buttons for selecting options.

   7. TEXTEDIT: Provides a free text field.

6. Enter the appropriate data source type in the SelectSourceType field.

   1. STRING: Enter STRING to list specific values.

   2. DATASOURCE: Use DATASOURCE to pull from a Delphi query

   3. TABLE: Enter TABLE to pull directly from a table.

   4. QUERY: Use the value QUERY to write your own SQL where statement to select reference values. Queries from DATASOURCE optimize performance rather than pulling data into a table each time.

7. Enter the appropriate source values.

   1. If SelectSourceType is “STRING”, enter a list of values separated by commas. Each item on the list will be an option in the field. For example you can enter “Event,Membership,Education” as the SelectSourceValue.

   2. If SelectSourceType is “TABLE”, the value should be a table name such as “CHAPTERREF”.

   3. If the SelectSourceType is “DATASOURCE”, the source value should be a Delphi query. For example, to pull from list of Chapters, enter “dtsChapterRef”.

   4. If SelectSourceType is “QUERY”, the source value should be a SQL statement. For example, you could enter the query select CODE, DESCRIPTION from LOOKUPS where CATEGORY = “SPEAKERTYPE”.

8. If a table name is specified in the SelectSourceValue and the control type is a type of dropdown, enter the field that should show in the dropdown from the SourceFieldName column. If the control type has two columns, you can enter a second field to display in the SourceFieldName2 column.

9. Check the “Visible?” box for the field to be available on the notebook.

10. Click the “Save Changes” button before closing Customize ClearVantage window.

11. You can now add these fields to the Profile tab of the notebook for the user roles that you wish.

More User-Defined Fields

If you exhaust the User-Defined Fields on your Individual or Organization Notebook, ClearVantage More User-Defined Fields (UDF) allow you to add an unlimited number of additional fields to your Individual and Organization Notebooks. In order to create your additional fields in ClearVantage, a new SQL table will need to be created in your database and System Settings will need to be updated. Please contact Euclid Technology for additional information on this enhancement.

So that Euclid can create your new table, you will need to provide the names of the fields to be added and the names of the Search Groups to be used. The Search Groups will serve as categories for the new fields both in the Individual or Organization Notebook and in the query screen. You will also need to select the formats for the new fields.

Once your additional table has been created, you have the ability to adjust the field labels, adjust the field format and add additional fields. You can also create query groups and adjust your query settings in order to query on your userdefined fields.

Any roles can also have the Historical Information added to the Individual or Organization Notebook. This tab can be used to track historical information from the MoreUDF tab. Please contact Euclid Technology for additional information on this enhancement as well as it requires configuration.

Adding and Labeling the More UDF Tab

Note

The new table for the More UDF tab in the Individual Notebook will be CUSTOMER_MORE and the new table for the Organization Notebook will be ORG_MORE.

From the “Pro Tools” menu in the upper right corner of ClearVantage Pro:

1. Click on the “Customize ClearVantage” button.

2. Once your table has been added and your System Settings have been configured for additional user-defined fields, select the fourth tab, UDFMore Settings, to enter the fields desired.

   

3. Select the appropriate notebook from the Table Name dropdown. In the grid, complete the following fields:

   • Group: Enter the Group label as it was created in your table and as it will appear on the More UDF tab and in the Query Tool. Groups are sorted in alphabetical order so starting the group with a number allows you to define the group order.

   • FieldName: Select an existing table field name as it appears in the CUSTOMER_MORE or ORG_MORE table. For existing fields, these names will have been provided by Euclid Technology. For new fields, you can enter the new field name which should have no spaces or special characters.

   • FieldLabel: Enter the Field Label as you would like it to appear in the Individual or Organization Notebook and in the Query Tool.

   • FieldDataType: Select the data type from the dropdown. Options include String, Boolean, Integer, Amount, and Date.

   • DefaultMatchType: Select the default match type for the field in the query.

   • SelectControlType: Determines the data entry type on the More UDF tab in Notebooks and in the Query Tool.

   • SelectSourceType: Set to STRING to list specific values. Use DATASOURCE to pull from a Delphi query, TABLE to pull directly from a table, or QUERY to allow you to write a query. Queries from DATASOURCE optimize performance rather than making a call to a table each time.

   • SelectSourceValue: If the source type is a STRING, list the string values separated by commas such as “Yes,No,Maybe”. Otherwise, you can enter a table name. For example, to pull from list of Chapters, enter “dtsChapterRef”.

   • SourceFieldName: If a table name is specified in the SelectSourceValue and the control type is a type of dropdown, specify the field from the source table to appear in the dropdown. The new table for the More UDF tab in the Individual Notebook will be CUSTOMER_MORE and the new table for the Organization Notebook will be ORG_MORE.

   • SourceFieldName2: If a field is specified in the SourceFieldName, specify the field from the source table that houses the description to appear in the dropdown.

   • OrderNo: This is the order the field appears in the group in the notebook and in the query screen.

   • Visible?: If checked, the UDF field appears in the Notebook.

   • Include in Search?: If checked, UDF the field appears in the Query Tool.

4. Click the checkmark button under the Table Name dropdown to save your changes.

5. Click the “Save Changes” button.

6. Click on the Query Settings tab to create your Search Groups.

Customizing Queries

Whereas the Field Specifications tab allows you to customize the labels for fields in various notebooks, the Query Settings tab allows you to label the field names on the Individual, Organization, Event and Product query screens. You can also define the type of entry method (e.g., dropdown, checkbox, radio buttons) and the field values that are available. Additionally, you can show or hide certain search criteria.

From on the “Pro Tools” menu in the upper right corner of ClearVantage Pro:

1. Click on the “Customize ClearVantage” button and go to the Query Settings tab.

2. Select the query window to be modified (Individual, Organization, Event, or Product) from the “Query Name” dropdown.

   

3. Click on the plus sign to add an additional query setting or you can modify the settings that already appear.

4. Complete the following fields for each query setting.

   • SearchGroup: Groups the fields by category. Each category can be expanded or collapsed to view the fields using the plus/minus button on the category header in the query window. Some common categories for the SearchGroup column are listed in the table below. The 00-Main SearchGroup indicates what the primary table being queried is and should not be changed or deleted.

   Individual	Organization	Event	Product
   01-Membership 02-Contact 03-Demographic 04-Professional 07-Event 09-Certification 10-Association Memberships 11-Education 12-Political 13-Designations 14-Employment Detail 15-Home Zip 16-Work Zip 17-Speaker 18-Function 19-Master Certification 20-Awards 21-Products 22-Dues/Don./Subscription 23-Committees 24-Committee History 25-Relationships 26-Action Items 27-Survey 28-Documents 29-Invoice Info 30-Payment Info 31-Sponsor Impact 32-Organization 33-Campaign/Appeal 34-Others 35-GPS Search	01-Membership 02-Associated Memberships 03-Demographics/Other 04-Contact Info 05-Action Items 06-Documents 07-Dues/Don./Subscription 08-Awards 08-Events 09-Program 10-Invoice Info 11-Payment Info 12-Product Order 13-Project 14-Project Task 15-Opportunity 16-Relationships 17-User Defined	01-Basic 02-More Criteria	01-Product 02-Related Products 03-Publication 04-Keywords 05-Taxonomy

   • SearchTable: Enter the name of the table where the field you want to search is stored.

   • SearchFieldName: Enter the name of the field you want to search.

   • SearchFieldLabel: Enter the label for the field as you want it to appear in the query window.

   • SearchFieldDataType: Enter the data type for the value you are searching. This should always be set to “S”.

   • DefaultMatchType: Use the dropdown menu to set the default match type to be used in the query. This is often set to “=” but you can use “Between” for date ranges, “Contains” for memo fields, or “Like” to allow wildcard searches using “%”.

   • SelectControlType: Use the dropdown menu to choose the appropriate format that will appear in the Search For column of the query window. A few examples include:

     • DATEEDIT: Date fields.

     • TEXTEDIT: Text fields.

     • CHECKBOX: Yes/No values like NOEMAILFLG.

     • DROPDOWNCHECKBOX: Multi-select options.

     • DROPDOWNLOOKUP: Single value select.

     • RADIOGROUP: Choose between 2 options.

   • SelectSourceType: Use the dropdown menu to specify the source for the options users can select from for RADIOGROUP, DROPDOWNCHECKBOX, or DROPDOWNLOOKUP. Appropriate values include:

     • DATASOURCE: Pulls from the Delphi query. This is preferred to calling from a table directly.

     • TABLE: Pulls from the table directly.

     • STRING: Allows you to enter selection values in the SelectSourceValue column.

     • SYSSETSTR: Displays values from a system setting parameter.

   • SelectSourceValue: Enter a list of options for users to select using a comma separated list. For values that include spaces, enclose the option with quotation marks. You must enter the SelectSourceType as STRING in order to enter your own options. Some examples include:

     • Active,Inactive,Nominee

     • Active,Rejected,Pending,Completed,InActive

     • For a RADIOGROUP: O=Organization,I=Individual

     • For a DROPDOWNLOOKUP or DROPDOWNCHECKBOX to pull from a reference table:

       • dtsStateCD - pulls from StateRef

       • SESSION - pulls codes from the Session table

   • SourceFieldName: If using a dropdown, use the SourceFieldName column to determine what to display in the first column of the dropdown. For example, if SelectSourceValue = CHAPTERREF, then enter CHAPTERID to display the chapter codes in the dropdown.

   • SourceFieldName 2: Use in conjunction with SourceFieldName. This second field to display in the dropdown after the SourceFieldName (i.e., CHAPTERNAME).

   • OrderNo: The order the fields should appear in the SearchGroup in the query screen.

     • Visible?: Check to display the field in the query window. Uncheck to remove from query window.

     • Insearch?: Check to display the field in the query window. Uncheck to remove from query window.

5. After you have completed your changes and assigned a category in the SearchGroup column to each field, you must assign the category to a GROUP in the SearchGroup column. Enter the following fields for each GROUP:

   • SearchGroup: Enter GROUP. You will need a GROUP for each category other than “00-Main” or “01-Membership”.

   • SearchTable: Enter the table name that you are joining to “00-Main”, an alias name for the table (assigned by you), the primary key in the table, and the foreign key in the primary table. For example, to join the “30 – Payment Info” query to the Customer table in the Individual query you can enter PAYMENTS,P,PAYEECD,CUSTOMERCD where you are querying on the Payments table which joins to the Customer table using the PayeeCD field from the Payments table and the CustomerCD field from the Customer table.

   • SearchFieldLabel: Enter the category name. This should match the SearchGroup field assigned to fields that appear beneath this category. Note that a difference in dashes or spaces will cause problems.

   • SelectSourceValue: This column is optional. Within a GROUP you may limit the join to a subset of records. For example, the clause WW.KMEMBERTYPE=“I” limits searching on WHOKNOWSWHO to relationships for Individuals only when “WW” is the alias name assigned to the GROUP.

   Note

   The GROUP defines the table join with the SearchTable 00-MAIN. Neither 00 – MAIN nor 01- Membership needs a GROUP record. For other categories, you will receive an error in the query that the field “is not bound” unless a GROUP is created.

6. You can also modify the settings for the NOT tab in the individual query window.

   • Enter the fields for the NOT tab as you did for fields in the other categories.

     • The SearchGroup should be NOT CATEGORY.

     • In the SearchTable column, you can list TABLE,OR to display the OR command by default between fields in the NOT tab.

   • NOT CATEGORY can also act like a GROUP

     • Enter the table, alias, primary key and foreign key fields in the SearchTable column as you did for GROUP.

     • Enter the labels in SearchFieldLabel that you want to appear in the Category dropdown on the NOT tab.

7. Once you have made your changes, click the checkmark at the top of the screen or click “Save Settings”.

8. Close the Customize ClearVantage window.

Creating Search Groups for UDEF Fields

In order to search your new fields on the MoreUDF tab, you will need to add the search groups for the new table and fields. This allows the query to search both the Customer or Organization table and the CUSTOMER_MORE or ORG_MORE table simultaneously. There is an optional trigger that allows these search groups to be created automatically when adding fields on the UDFMore Settings Tab.

From on the “Pro Tools” menu in the upper right corner of ClearVantage Pro:

1. Click on the “Customize ClearVantage” button and go to the Query Settings tab.

2. Select the query window to be modified (Individual or Organization) from the Query Name dropdown.

   

3. Click on the plus sign to add an additional search group.

4. Enter the following fields for each SearchGroup created in your new table:

   • SearchGroup: Enter GROUP. You will need a GROUP for each SearchGroup that was listed in the MoreUDF Settings tab.

   • SearchTable: Enter the table name that you are joining to “00-MAIN”, an alias name for the table (assigned by you), the primary key in the table, and the foreign key in the table. For example, you should use CUSTOMER_MORE,CUSTUDEF,CUSTOMERCD,CUSTOMERCD for the search groups in the Individual Notebook and ORG_MORE,ORGUDEF,ORGCD,ORGCD for the Organization Notebook.

   • SearchFieldLabel: Enter the category name. This should match the Search Group field that appears in the MoreUDF Settings tab.

5. Once you have made your changes, click the checkmark at the top of the screen.

6. Click the “Save Settings” button.

7. Close the Customize ClearVantage window.

Advanced Query Examples

One of the advantages of the ClearVantage query tool is the ability to customize the query settings for each client. You can configure these options for your database, or you can contact Euclid Technology for assistance.

Configuring Searching Using Multiple Rows

You can configure ClearVantage so that users can search the same field in multiple rows. This allows you to place an “AND” or an “OR” criteria between values in the same field or enter multiple values to be searched.

From on the “Pro Tools” menu in the upper right corner of ClearVantage Pro:

1. Click on the “Customize ClearVantage” button and go to the Query Settings tab.

2. Select the query you wish to customize from the “Query Name” dropdown.

   

3. Click the plus sign to add a row in the grid.

4. Enter the query settings for the field you wish to add. Some examples are below.

5. If you wish to search for multiple Individual IDs at once, add additional search fields for the CustomerCD in the Individual query.

   • SearchGroup: Enter “01 – Membership”.

   • SearchTable: Enter “CUSTOMER”.

   • SearchFieldName: Enter “CUSTOMERCD”.

   • SearchFieldLabel: Enter the label for the field to appear in the query screen such as “Indiv. ID 2”.

   • SearchFieldDataType: Enter “S”.

   • DefaultMatchType: Select the default match type you want to appear in the query screen from the dropdown. Generally, this will be “=”.

   • SelectControlType: Select “TEXTEDIT” from the dropdown.

   • Visible? Check the box to make the search visible.

   • In Search? Check the box to enable the query.

6. If you wish to search for multiple Organization IDs at once, add additional search fields for the OrgCD in the Organization query.

   • SearchGroup: Enter “01 – Membership”.

   • SearchTable: Enter “ORGANIZATION”.

   • SearchFieldName: Enter “ORGCD”.

   • SearchFieldLabel: Enter the label for the field to appear in the query screen such as “Org CD 2”.

   • SearchFieldDataType: Enter “S”.

   • DefaultMatchType: Select the default match type you want to appear in the query screen from the dropdown. Generally, this will be “=”.

   • SelectControlType: Select “TEXTEDIT” from the dropdown.

   • Visible? Check the box to make the search visible.

   • In Search? Check the box to enable the query.

7. If you wish to search for multiple Zip Codes at once, add additional search fields for the ZIP in either the Individual or Organization query.

   • SearchGroup: Enter “02 – Contact”.

   • SearchTable: Enter “CUSTOMER” for the Individual query or ORGANIZATION for the Organization query.

   • SearchFieldName: Enter “ZIP”.

   • SearchFieldLabel: Enter the label for the field to appear in the query screen such as “Zip 2”.

   • SearchFieldDataType: Enter “S”.

   • DefaultMatchType: Select the default match type you want to appear in the query screen from the dropdown. Generally, this will be “=”.

   • SelectControlType: Select “TEXTEDIT” from the dropdown.

   • Visible?: Check the box to make the search visible.

   • In Search?: Check the box to enable the query.

8. If you wish to search for individuals that registered for more than one event, add additional search fields and groups for the SESSIONALTCD in the Individual query.

   For the Search Fields enter:

   • SearchGroup: Enter “07 – Event” followed by a unique number.

   • SearchTable: Enter “VW_REGISTRATIONSEARCH”.

   • SearchFieldName: Enter “SESSIONALTCD”.

   • SearchFieldLabel: Enter the label for the field to appear in the query screen such as “Events 2”.

   • SearchFieldDataType: Enter “S”.

   • DefaultMatchType: Select the default match type you want to appear in the query screen from the dropdown. Generally, this will be “Any”.

   • SelectControlType: Select “CHECKBOXDROPDOWN” from the dropdown.

   • Visible?: Check the box to make the search visible.

   • In Search?: Check the box to enable the query.

   For the Group enter:

   • SearchGroup: Enter “GROUP”.

   • SearchTable: Enter “VW_REGISTRATIONSEARCH, R#,CUSTOMERCD,CUSTOMERCD” without any spaces and replacing # with the same number you entered for the search field.

   • SearchFieldLabel: “Enter 07 – Event” followed by the same number you entered for the search field and the search group.

9. Click the check mark and then click the “Save Changes” button.

10. Close the Customize ClearVantage window.

11. Log out of ClearVantage and log back in to see the changes in your query screen.

Configuring Searching Using a Memo Field

A Memo search field can be configured in ClearVantage to allow you to enter a simple SQL statement for your search.

From on the “Pro Tools” menu in the upper right corner of ClearVantage Pro:

1. Click on the “Customize ClearVantage” button and go to the Query Settings tab.

2. Select the query you wish to customize from the Query Name dropdown.

   

3. Click the plus sign to add a row in the grid.

4. Enter the following query settings for the Memo field.

   • SearchGroup: Enter “34 – Others”.

   • SearchTable: Enter “CUSTOMER” for the Individual query or “ORGANIZATION” for the Organization query.

   • SearchFieldName: Enter “MEMO”.

   • SearchFieldLabel: Enter the label for the field to appear in the query screen such as “Other Parameters”.

   • SearchFieldDataType: Enter “S”.

   • DefaultMatchType: Select “=MEMO”.

   • SelectControlType: Select “MEMO” from the dropdown.

   • Visible?: Check the box to make the search visible.

   • In Search?: Check the box to enable the query.

5. Click the check mark and then click the “Save Changes” button.

6. Close the Customize ClearVantage window.

7. Log out of ClearVantage and log back in to see the changes in your query screen.

Duplicate Records in ClearVantage

Though you should make every effort to avoid duplicate records in ClearVantage, duplicate records may be entered into your database by staff and/or via the web. You should regularly check your database for duplicate entries. If you do find duplicate records, you can easily merge the most current information into a single record and delete the outdated information.

Note

The default Individual Duplicate Key consists of Last Name, First Name, the first 10 characters of the address, and the first 5 digits of the zip code. The default Organization Duplicate Key consists of the Organization Name and the entire zip code. The format of the Duplicate Key cannot be changed.

Checking for Duplicate Records

Talk to your Business Analyst to set up Saved List queries to find Duplicate Individuals and Duplicate Organizations in ClearVantage.

Merge Individual Records

1. One you have confirmed of the two records you want to merge and note the Individual ID of which is the source record, and which will be your target record.

2. Highlight the one of the records you wish to merge and click the “Merge Duplicates” button in the Activity Panel.

   

3. A Merge Duplicates window prompt will appear asking if the record you have highlighted is the “Target”, “Source”, or “Neither”. Select the appropriate response.

   

4. A Merge Duplicate Records window will appear. Use the “Select Source Record” or “Select Target Record” to search for either your source or target, depending on which one you choose in the previous step.

   5. Use the Individual ID to search for your source or target record. Click “Select Person” to add the record to the Merge Duplicate Records window.

   

5. Select the field values you wish to maintain. By default, the Merge Utility is set to use the value in the Target Record when merging. If there is no value for a field in the Target record, the system defaults to use the value in the Source Record. You can change which record field information comes from by changing the “Use Data from:” dropdown fields in the middle of the window:

   • Target: The record that was dragged into and appears on the right side of the Merge Records screen.

   • Source: The record that was dragged and appears in the middle of the Merge Records screen.

   • Skip: Maintains each record with the existing information. This is useful when you need to further research the information before deleting the record.

6. Click the “Perform Merge” button to complete the merge once you have selected all desired fields. You will get a confirmation prompt. Click “Yes” to proceed. When files are merged, associated information for the Source record such as Registrations, Subscriptions, Invoices, Payments, and Action Items will be transferred to the Target record.

7. A Confirm window will appear once the records are merged. Click “Yes” to delete the source record or you can delete the Individual Source Record manually. We recommend automatically deleting source records following the merge. You cannot manually delete an Individual or Organization Notebook without first deleting any information stored in the history tabs. Therefore, you will not be able to manually delete records containing posted payments.

Merge Organization Records

1. One you have confirmed of the two records you want to merge and note the Org ID of which is the source record, and which will be your target record.

2. Highlight the one of the records you wish to merge and click the “Merge Duplicates” button in the Activity Panel.

   

3. A Merge Duplicates window prompt will appear asking if the record you have highlighted is the “Target”, “Source”, or “Neither”. Select the appropriate response.

   

4. A Merge Duplicate Records window will appear. Use the “Select Source Record” or “Select Target Record” to search for either your source or target, depending on which one you choose in the previous step.

   

5. Use the Org ID to search for your source or target record. Click “Select Person” to add the record to the Merge Duplicate Records window.

   

6. Select the field values you wish to maintain. By default, the Merge Utility is set to use the value in the Target Record when merging. If there is no value for a field in the Target record, the system defaults to use the value in the Source Record. You can change which record field information comes from by changing the UseType in the Merge utility:

   • Target: The record that was dragged into and appears on the right side of the Merge Records screen.

   • Source: The record that was dragged and appears in the middle of the Merge Records screen.

   • Skip: Maintains each record with the existing information. This is useful when you need to further research the information before deleting the record. The information that is to be maintained in the profile tab will be in red.

7. Select the “Perform Merge” button to complete the merge. You will get a confirmation prompt. Click “Yes” to proceed. When the records are merged, associated information for the Source record such as Invoices, Payments, and Action Items will be transferred to the Target record.

8. The system will prompt you to delete the source record, or you can delete the Organization Source Record manually.

Importing Data into ClearVantage

ClearVantage allows you to import data from other applications. This feature is especially useful when working with large individual and organization lists. Importing allows you to enter information for many individuals/organizations at once rather than through manual data entry. Please note that there is no undo functionality for imports so it is recommended to always go through the entire process in the training environment first to ensure the results are as-expected before importing the data into the live environment.

Import Individual or Organization Data into ClearVantage

The instructions below are specifically written for importing Individual and Organization data, but the same instructions apply for importing Registration Lists, Subscription Lists, Multi-Line Dues, and Products. When importing registrations, subscriptions, and dues, you must be careful to make sure that the individual or organization record is in the system, or the record will not be linked to an individual or organization.

Note

For either individuals or organizations, you must include all required data fields for import for data to convert accurately into ClearVantage.

Warning

It is highly recommended that you run an import into your test database prior to running the import in your live database to ensure that your settings and field mappings are correct.

From on the “Pro Tools” menu in the upper right corner of ClearVantage Pro:

1. Click on “Import Data”. This will open the list of possible import types including Individual Lists, Organization Lists, Registration Lists, Subscription Lists, Multi-Line Dues, Product Inventory, and Other Data.

2. Select either “Individual” or “Organization List” to open the data import wizard.

   

3. Select the appropriate file format that your source file is in, either Text (*.txt, *.csv) or Excel (*.xls) and click the “Next” button to open the File Name window.

   

   Note

   If you do not see the file you are trying to import, try changing the file type to All files (.).

4. Click on the ellipse button next to the Import from File field to open a “Select a file for data importing” window.

5. Select the file you wish to import and click the “Open” button. The File Origin should remain as “ANSI (Windows)”.

6. Click the “Next” button and then follow the relevant instructions based on whether you are importing a text or Excel file.

Import Other Data into ClearVantage

Selecting the Other Data option allows you to import data into any table in ClearVantage. This does require knowledge of the specific table names that you wish to import into. Common uses for this functionality include importing Function Registrations and Certifications.

From on the “Pro Tools” menu in the upper right corner of ClearVantage Pro:

1. Click on “Import Data”. This will open the list of possible import types including Individual Lists, Organization Lists, Registration Lists, Subscription Lists, Multi-Line Dues, Product Inventory, and Other Data.

2. Select “Import Other Data” and the Selection Target Table window will open.

   

3. Select the table you want to import into from the dropdown and click “OK”. The File Format window of the Import Wizard will open.

   

4. Select the appropriate file format that your source file is in, either Text (*.txt, *.csv) or Excel (*.xls) and click the “Next” button to open the File Name window.

   

   Note

   If you do not see the file you are trying to import, try changing the file type to All files (.).

5. Click on the ellipse button next to the Import from File field to open a “Select a file for data importing” window.

6. Select the file you wish to import and click the “Open” button. The File Origin should remain as “ANSI (Windows)”.

7. Click the “Next” button and then follow the relevant instructions based on whether you are importing a text or Excel file.

Importing a Text File

1. Select from the options below based on how the data in your source file is spaced and separated.

   1. Delimited: Select this option if a file uses commas, tabs, or semicolons between fields in each record.

   2. Fixed Width: Select this option if fields in the import file are of a specified number of characters regardless of the data length.

   3. Field delimiter: Select the character used to separate fields in a record.

   4. Record separator: Select the format used to separate records. This is almost always CRLF (Carriage Return Line Feed).

   5. Text Qualifier: Select the character used to group characters in a field.

   

2. Click the “Next” button to proceed to the Data Formats window.

   

3. Enter the number of the row that contains the first row of data you wish to import in the “First Row” field.

   1. If your import file does not have column headers, leave the First Row field set to “1”.

   2. If your import file has column headers, change the First Row field to the first row of data (generally “2”) to avoid importing the headers.

   3. If the First Row field is set to “2” or higher, you can enter the number of the row that contains your field headers into the Row with Field Names field. This is probably “1”.

4. Enter in the last row field the row in the file that contains the last row of data that you want to import. This can be left blank if you want to import the entire file.

5. Check the “Show file header” box if your import file has column headers so that you can view the headers in the next screen.

6. Set the Date, Time, and Numbers formats to reflect the data as you want it to appear in ClearVantage. As a general rule, these can be left to the defaults.

   1. Date Order: Select the Date Order from the dropdown. “M” represents month, “D” represents day, and “Y” represents year. If you select “Custom”, a new dropdown will appear offering additional formats, including hours, minutes and seconds. This dropdown will replace the “Four Digit Years” and “Leading Zeros in Dates” options.

   2. Four Digit Years: Uncheck the “Four Digit Years” checkbox if you want the year to display as two digits rather than four. For example, if the box is checked the date will appear as “10/10/2022”. If the box is unchecked, it will appear as “10/10/22”.

   3. Leading Zeroes in Dates: Check the “Leading Zeroes in Dates” checkbox if you want the imported dates to include leading zeroes. For example, if the box is checked the date will appear as “04/07/2022”. If the box is unchecked, it will appear as “4/7/22”.

   4. Date Delimiter: Enter the character that you want to use to separate the month, day, and year in dates. For example, if you enter “/” the date will appear as “12/15/22”. If you enter “-”, it will appear as “12-15-22”.

   5. Time Delimiter: Enter the character that you would like to use to separate the hour, minutes, and seconds.

   6. Decimal Symbol: Enter the character that you would like to use to separate whole numbers from decimal places. For example, if you enter “.” the number will appear as “3.14” If you enter “,” the number will appear as “3,14”.

   7. Thousand Separator: Enter the character that you would like to use to separate hundreds from thousands. For example, if you enter “,” the number will appear as “525,600”. If you enter “.” the number will appear as “525.600”.

   8. Currency Symbol: Enter the character that you would like to use to indicate money. For example, if you enter “$” the number will appear as “$525,600”. If you enter “£” the number will appear as “£525.600”.

   9. Logical Values: Enter the values you would like to be imported.

7. Click on the “Next” button to proceed to the File Preview/Mapping window.

   

8. Each data field from the text or csv file needs to be mapped to the proper field in ClearVantage. To map data:

   1. Click on any of the column headers above row 1 and the dropdown menu will bring up a list of fields to which you can map the column.

   2. Click on the field into which you want to map the data in that column.

   3. Repeat for each of the columns you want to map. If you do not want to import a particular column, you do not need to map it.

   4. Click on the “Next” button once all the necessary fields are mapped.

9. On the Mappings screen, you will have a chance to review your mappings and edit them as needed. To edit a mapping, select the correct field from the dropdown in the source column.

   

   1. If you want to match records from your import file to records that already exist in ClearVantage, you need to select the primary key. For Individual lists, select “CUSTOMERCD” as your primary key by checking the box in the key column. For Organizational lists, choose “ORGCD”.

   2. The “Data preview” window gives you another chance to confirm that your data will import into the correct fields in ClearVantage.

   3. By selecting “Load when” or “Skip if” and clicking on the ellipse buttons, you can create constraints and conditions for data to be accepted or omitted. To continue without these options, click “Next”.

   

10. In the Summary window, select your import mode:

    

    1. Append: Select “Append” to add the imported records to the existing data in ClearVantage. When importing Individuals and Organizations, this will create new Individual and Organization Notebooks. This does not check for existing records and may cause duplicate records requiring a Duplicate Check and merging of records.

    2. Update: Update the existing records with the information from the import file. If you are unsure of the validity of information in the import file, this could potentially overwrite your existing data with inaccurate data from the import file. Updating the data requires the primary key (CustomerCD for individuals or OrgCD for organizations) to be listed in the import file in order to match the existing Notebooks with the imported records.

    3. Append/Update: Selecting this option will update the record if it already exists (based on the primary key) in ClearVantage. If the record does not exist, a new record will be created.

11. If this is an import format that you will run repeatedly, you can save the import specifications. Detailed instructions are found in the Saving Import Specifications section below.

12. Click “Execute” to run your import.

13. A summary of the data import will display the window below. Note that if you have a header row in your import file, that row will be counted in the “Processed” count but not in the “Added” count.

    

14. A log of the summary will be automatically added to the file EuclidImportLog which can be found in the folder “%appdata%\ Euclid Technology\CVPro” on your computer.

Importing an Excel File

If you are importing an Excel file:

1. Enter the number of the row that contains the first row of data you wish to import in the First Row field.

   

   1. If your import file does not have column headers, leave the First Row field set to “1”.

   2. If your import file has column headers, change the First Row field to the first row of data (generally “2”) to avoid importing the headers.

   3. If the First Row field is set to “2” or higher, you can enter the number of the row that contains your field headers into the Row with Field Names field. This is probably “1”.

2. Enter in the last row field the row in the file that contains the last row of data that you want to import. This can be left blank if you want to import the entire file.

3. Check the “Show file header” box if your import file has column headers so that you can view the headers in the next screen.

4. Set the Date, Time and Numbers formats to reflect the data as you want it to appear in ClearVantage. As a general rule, these can be left to the defaults.

   1. Date Order: Select the Date Order from the dropdown. “M” represents month, “D” represents day and “Y’ represents year. If you select “Custom”, a new dropdown will appear offering additional formats, including hours, minutes and seconds. This dropdown will replace the Four Digit Years and Leading Zeros in Dates options.

   2. Four Digit Years: Uncheck the “Four Digit Years” checkbox if you want the year to display as two digits rather than four. For example, if the box is checked the date will appear as 10/10/2019. If the box is unchecked, it will appear as 10/10/19.

   3. Leading Zeroes in Dates: Check the “Leading Zeroes in Dates” checkbox if you want the imported dates to include leading zeroes. For example, if the box is checked the date will appear as 04/07/2019. If the box is unchecked, it will appear as 4/7/2019.

   4. Date Delimiter: Enter the character that you want to use to separate the month, day and year in dates. For example, if you enter / the date will appear as 12/15/11. If you enter -, it will appear as 12-15-11.

   5. Time Delimiter: Enter the character that you would like to use to separate the hour, minutes and seconds.

   6. Decimal Symbol: Enter the character that you would like to use to separate whole numbers from decimal places. For example, if you enter . the number will appear as 3.14. If you enter , the number will appear as 3,14.

   7. Thousand Separator: Enter the character that you would like to use to separate hundreds from thousands. For example, if you enter , the number will appear as 525,600. If you enter . the number will appear as 525.600.

   8. Currency Symbol: Enter the character that you would like to use to indicate money. For example, if you enter $ the number will appear as $525,600. If you enter £ the number will appear as £525.600.

   9. Logical Values: Enter the values you would like to be imported.

5. Click on the “Next” button to proceed to the File Preview/Mapping window.

   

6. Each data field from the Excel file needs to be mapped to the proper field in ClearVantage. If the headers in your import file exactly match the field names in the table you are importing into, the import wizard will automatically assign the mappings. Any fields that do not match exactly, will need to be manually mapped. To map fields:

   1. Click on any of the column headers above row 1 and the dropdown menu will bring up a list of fields to which you can map the column.

   2. Click on the field into which you want to map the data in that column, you do not need to map it.

   3. Repeat for each of the columns you want to map. If you do not want to import a particular column, you do not need to map it.

   4. Click on the “Next” button once all the necessary fields are mapped

   Note

   Clicking the lighting icon next to the first field maps the spreadsheet to the database in the exact order of the fields in the ClearVantage table. This should only be used if the import file matches all the columns in the table to which you are importing.

7. On the Mappings screen, you will have a chance to review your mappings and edit them as needed. To edit a mapping, select the correct field from the dropdown in the source column.

   

8. If you want to match records from your import file to records that already exist in ClearVantage, you need to select the primary key. For Individual lists select CUSTOMERCD as your primary key by checking the box in the key column. For Organizational lists, choose ORGCD.

9. The Data preview window gives you another chance to confirm that your data will import into the correct fields in ClearVantage.

10. By selecting “Load when” or “Skip if” and clicking on the ellipse buttons, you can create constraints and conditions for data to be accepted or omitted. To continue without these options, click “Next”.

    

11. In the Summary window, select your import mode:

    

    1. Append: Select “Append” to add the imported records to the existing data in ClearVantage. When importing Individuals and Organizations, this will create new Individual and Organization Notebooks. This does not check for existing records and may cause duplicate records requiring a Duplicate Check and merging of records.

    2. Update: Update the existing records with the information from the import file. If you are unsure of the validity of information in the import file, this could potentially overwrite your existing data with inaccurate data from the import file. Updating the data requires the primary key (CustomerCD for individuals or OrgCD for organizations) to be listed in the import file in order to match the existing Notebooks with the imported records.

    3. Append/Update: Selecting this option will update the record if it already exists (based on the primary key) in ClearVantage. If the record does not exist, a new record will be created.

12. If this is an import format that you will run repeatedly, you can save the import specifications. Detailed instructions are found in the Saving Import Specifications section below.

13. Click “Execute” to run your import.

14. A summary of the data import will display the window below. Note that if you have a header row in your import file, that row will be counted in the Processed count but not in the Added count.

    

15. A log of the summary will be automatically added to the file EuclidImportLog which can be found in the folder %appdata%\ Euclid Technology\CVPro on your computer.

Saving Import Specifications

To save the import specifications you have configured to use for future imports:

1. On the Summary page, once you have entered all the settings you wish to save, click the “Specifications” button.

   

2. Click “Save Specification” to open the Save Specification window.

   

3. Give a name to the specification in the Save Specification field and rename the file in the Save File To field. The file will have the extension “.smi”. The file must remain in this folder in order to be used later.

4. Click the “OK” button to save the specifications.

Using Import Specifications

If you will be importing files in the same format repeatedly, you can import specifications and select them rather than reenter them for each import. From the import wizard:

1. Select the “Specifications” button on any of the windows to load your previously saved specifications.

2. Click “Open Specifications” and the Specifications window will open.

   

3. If you wish to delete old specifications, click on the specification and click the “Delete” button.

4. To load specifications to use, click on the specifications you wish to load and click the “Load” button.

5. You can now continue through the import wizard and review your settings prior to the import.

Creating and Modifying Labels & Badges

Create mailing labels directly from ClearVantage. Use the following instructions to modify the label sizes and information to be included based on your needs.

Setting Label and Badge Sizes

From on the “Pro Tools” menu in the upper right corner of ClearVantage Pro:

1. Click on “Reference Tables” to open an Edit Reference Tables window.

2. Select the “Address Label Setup” table from the Select Reference Table dropdown.

   

3. To modify an existing label size, click in the appropriate field and type in the new information. To create a new label size, click the “New” button to add a row to the reference table. Enter the following fields to set the dimensions and format of the label:

   

   • LABELNAME: Assign a name to the label format. This will appear in the options when running labels.

   • TOPMARGIN: The distance from the top edge of the sheet of labels to the top of the first label.

   • LEFTMARGIN: The distance from the left edge of the sheet of labels to the left of the first label.

   • LABELHEIGHT: The height of a single label not including the gaps between labels.

   • LABELWIDTH: The width of a single label not including the gaps between labels.

   • HORGAP: The horizontal (left to right) distance between labels on the sheet.

   • VERGAP: The vertical (top to bottom) distance between labels on the sheet. The example above has a VERGAP of 0.

   • LABELACROSS: The number of labels from left to right on the sheet.

   • LABELDOWN: The number of labels from top to bottom on the sheet.

   • ISCONTINUOUS: Enter “Y” if there is no gap between labels either horizontally or vertically. If there is a gap in either direction, enter “N”.

   • FONTSIZE: The default font size for your labels.

   • FONTNAME: The default font for your labels.

   • PAGEWIDTH: The width of the full label sheet.

   • PAGELENGTH: The length of the full label sheet.

   • PAGEORIENTATION: Enter “P” if your labels are oriented as Portrait or “L” if they are Landscape.

   • PAPERTYPE: This should be left blank.

   • PRINTERBIN: This should be left blank.

   • ISBADGEFLG: Enter “Y” if the format you have created is for Name Badges. Enter “N” if the format is not for Name Badges.

   • BOTTOMMARGIN: The distance from the bottom edge of the sheet of labels to the bottom of the last label.

   Note

   Though the reference table values are entered in decimals, the labels are generally measured in 1/8 inch increments. It is most accurate to enter decimals that correspond to 1/8 inch increments.

4. Once you have entered the label size you need, click “OK” to save and close the Reference Table window.

5. Log out of ClearVantage and log back in to begin using the label you have added.

Selecting Label and Badge Fields

From on the “Pro Tools” menu in the upper right corner of ClearVantage Pro:

1. Click on “Reference Tables’ to open an Edit Reference Tables window.

2. Select the “Mail Label Fields” table from the Select Reference Table dropdown.

   

3. To modify a label, click in the appropriate field and enter the changes.

4. To create a new label, click the “New” button to add a row to the reference table.

5. Enter the following fields to select and format the fields to appear on the label:

   • LABELTYPE: Enter “I” for individual labels, “O” for organization labels, or “R” for badges.

   • LINENUMBER: Enter the line of the label or badge on which you want the field to appear. Begin with “0”.

   • ISCALCULATED: Enter “N” to pull a single field for each line of the label or badge.

   • EXPRESSION: Enter the name of the field that you want to appear on the label or badge.

   • FONTSIZE: Enter the font size for the line.

   • FONTNAME: Enter the font name for the line.

   • USEBOLD: Enter “Y” to make the line bold. This option is only available for badges.

   • USEITALIC: Enter “Y” to italicize the line. This option is only available for badges.

   • USEUNDERLINE: Enter “Y” to underline the line. This option is only available for badges.

6. Once you have entered the label size you need, click “OK” to save and close the Reference Table window.

7. Log out of ClearVantage and log back in to begin using the label you have added.

Business Process Management

Company Information

Information on your organization rather than about your members or contacts is stored in the Company table and is used in other areas of ClearVantage. The company name and address information will flow through to invoices created in ClearVantage. Additional data fields in the table can also be used in Quick Letter merge fields. For example, company email and company phone can be merged onto any Quick Letter template. Some accounting information is also set in the Company table such as the AR and Cash accounts.

Add Company Information

Customized configuration is often required to support multiple companies in ClearVantage. If you are interested in maintaining multiple companies in ClearVantage, please contact Euclid Technology.

Edit Company Information

1. From the “Administration” Main Menu Dropdown, click on “Edit Companies” to open the Companies window.

2. Click on the Company Name for the company you wish to edit.

3. Enter or update the following information:

   

   • Name: This is the name of the company which will appear at the top of the ClearVantage window. Only the name of first company (generally company “01” or “001”) will appear at the top of the screen.

   • Legal Name: Enter the legal name of the company.

   • Address: Update the primary address for the company.

   • Email: Verify the primary email address for the company.

   • URL: You can track the primary URL for the company’s website.

   • Company Ph: Enter the main phone number for the company.

   • Fax Ph: Enter the primary fax number for the company.

   • Company Alt CD: This field can be used to track a secondary company code.

   • Fiscal Yr End: Select the month in which the company’s fiscal year ends. If you are changing your fiscal year, we suggest contacting Euclid Technology to ensure that other areas of the system account for this change.

   • Bill to Address: Enter the billing address to appear on Vendor Orders.

   • Ship to Address: Enter the shipping address that appears on Vendor Orders. Ms./Mr./… Select the prefix for the contact person for the company.

   • Contact: Verify the name of the contact person for the company.

   • Position: Enter the job title of the contact person.

   • Department: Track the department of the contact person.

   • Work Ph: Verify the phone number of the contact person.

   • Ext: Enter the phone extension of the contact person if available.

   • Mobile Ph: You can enter the cell phone number of the contact person.

   • Merchant ID: This field may be populated with credit card processor information from ClearVantage versions prior to ClearVantage 7.7. This information is now stored in CV-PASS.

   • Merchant Processor: This field may be populated with credit card processor information from ClearVantage versions prior to ClearVantage 7.7. This information is now stored in CV-PASS.

   • AR Account: Verify the accounts receivable account for this company.

   • Cash: Account. Verify the cash account for this company.

   • Liability Account: Confirm the liability account for this company.

   • Tax Account: Verify the tax account for this company.