archdiocese of boston connectnow intelligent … › uploads › 3 › 9 › ...connectnow iq's...

ARCHDIOCESE OF BOSTON

CONNECTNOW

INTELLIGENT QUERY (IQ)

GUIDE Updated October 2015

Prepared by Karina Salas & Lisa Ann Trainor For Distribution and Use in the Archdiocese of Boston

Table of Contents

About ConnectNow IQ Chapter 1 : Getting Started

Getting Started About the Intelligent Query Page

Chapter 2 : Building Queries

How to Create a New Query Suggestions for Designing Queries Working with Columns

How to Add, Remove, or Replace a Column How to Reorder the Columns in Your Query Results How to Sort a Column's Data How to Rename a Column Heading in Your Query Results How to Calculate a Column's Data (Aggregate Function)

Applying Conditions Chapter 3 : Managing Queries

How to Open and View a Saved Query How to View and Edit the Properties of a Query How to Modify the Design of a Query How to Save a Query under a New Name How to View the List of Saved Queries How to Make a Query Public or Private How to Delete a Query How to View the SQL Statements for a Query How to View the Properties of a Query How to Copy a Query's SQL Code How to Rename a Query

Chapter 4 : Using Query Tags How to Assign a Tag to a Query How to Create and Manage Query Tags How to Use Tags to Find Queries

Working with Query Results How to Export Query Results to a CSV File How to Limit the Number of Records Returned by a Query How to Create a Family Workgroup How to Use Query Results a Data Source for a Mail Merge How to Create a List of Email Recipients to Send Web-based Email How to Remove Duplicate Records from Your Query Results How to Create a Member Workgroup How to View Your Query Results in Full Screen Mode

Troubleshooting Why Aren't I Getting the Expected Results from My Query? How to Get Customer Assistance with a Query How to Find ConnectNow IQ Model Information

1

http://cnhelp.parishsoft.com/cnhelp/iq/building_queries/columns/how_to_calculate_a_column_s_data_(aggregate_functions).htm

About ConnectNow IQ

This topic introduces you to ConnectNow IQ, ParishSOFT’s user-friendly query building

application that enables you to create custom queries to obtain the exact information you need

from your database.

What Is ConnectNow IQ?

ConnectNow IQ's Key Features and Benefits

What Is ConnectNow IQ?

Ask a question, get an answer. ConnectNow IQ (Intelligent Query) makes it easy for you to

create queries to retrieve information from your database without having knowledge of SQL or

any other query language. ConnectNow IQ features a graphical user interface that allows you to

simply select the data sources (columns) for a query, specify conditions to apply to those

columns, and then run the query. ConnectNow IQ quickly extracts just the information you need

from your database to answer your questions.

After you build and run a query, ConnectNow IQ lets you save it so that you can use it over and

over again. You can also designate a query as "global" so it can be shared with other users, or

you can keep it private so that only you can see the results. You can also print query results, use

them as a data source for a mail merge, and export them to Excel.

With a few clicks of the mouse, you can generate simple and complex queries to find the

information you are looking for in these modules in ConnectNow Family Suite:

● Offering

● Ministry Scheduler

● Religious Education

● Family Directory (includes data from family and member notes, family and member workgroups, organization-specific envelope data, family registration status, and family groups.

The number of queries you can run is practically unlimited, but here are a few examples of

questions asked and answered by users of ConnectNow IQ:

● “What is the average age of the registered adults in our organization?"

● “How many families registered within the last two years? And, how many registered between three to five years ago?”

● “Which families made an offertory pledge last year but not this year?”

● “Which of our older teens ages 15–19 have been baptized?”

● "What are the names of members who have pledged $500 or more to the building fund?

Whatever question you imagine about families, members, and contributions, just build the query. ConnectNow IQ provides you the answers in seconds.

2

ConnectNow IQ's Key Features and Benefits

Building your own queries has never been easier than with ConnectNow IQ. ConnectNow IQ

combines powerful functionality with an intuitive interface designed to meet the needs of those

who have basic database querying skills as well as those who have advanced expertise in query

design and development.

No advanced SQL query skills or knowledge of the syntax of SQL statements is required to use

ConnectNow IQ. The interface allows all users to focus on the content of a question or problem

without having to be concerned with SQL commands and syntax. With a few mouse clicks,

beginning users can select the elements needed to compose a query that extracts the

information needed to answer a basic question or solve a simple problem, and then, with a

single click of a button, run the query and view results.

ConnectNow IQ’s wide range of features and functions facilitates the creation of more

sophisticated queries, which also makes it a powerful tool for more experienced query

designers. Experienced users can save time by building queries programmatically. Once the

query is built, users can toggle into View SQL mode where they can see the query’s underlying

SQL structure.

The following illustration highlights the key features and benefits of ConnectNow IQ.

Ready to learn how to use ConnectNow IQ? To get started, go to Getting Started.

3

Chapter 1 : Getting Started

Getting Started

Here are some quick steps for how to get started using ConnectNow IQ.

Step 1. Watch the ConnectNow IQ Demonstration Video

Step 2. Familiarize Yourself with the ConnectNow IQ User Interface

Step 3. Practice Designing Queries

Step 1. Watch the ConnectNow IQ Demonstration Video

To get started, we recommend that you go to http://vimeopro.com/parishsoft/cn-iq and watch

ParishSOFT's video demonstration of ConnectNow IQ. The video explains what ConnectNow IQ

is and shows how you can use the application's features to get the data you need from your

database without having knowledge of SQL or any other query language. The video covers the

fundamentals to enable you to start building queries using data from other ConnectNow Family

Suite modules, including Family Directory, Offering, Religious Education, and Ministry

Scheduler.

Step 2. Familiarize Yourself with the ConnectNow IQ User Interface

The next step is to familiarize yourself with the components of ConnectNow IQ's user interface.

To do this, start the ConnectNow IQ application and explore the main panel and buttons. As

shown in the following illustration, the ConnectNow IQ user interface consists of four main

components:

● Column Picker panel

● Result Columns panel

● Query Conditions panel

● Query Results panel

Brief descriptions of these panels are provided below. For more detail, go to About the

Intelligent Query Page.

4

Column Picker Panel

The Column Picker panel is your starting point for designing a query. Open the Column Picker

panel and explore the groups listed there and the columns of data. The columns are organized

into main groups (entities) and presented in a tree-style list. Click to expand a group to the

columns it contains.

To pick a column for your query, you simply select its checkbox to add it to the Result Columns

panel. Only the columns you specifically select from this panel are included in your query.

Result Columns Panel

The Result Columns panel lists all of the columns you select for your query. These are the columns that will appear in your results after you run a query. Note that each column listed in this panel occupies a line by itself. A column entry consists of two elements: an Expression, which is the name of the selected column, and a Title, which displays as the column header in the Query Results panel.

The Results Column panel also contains controls that let you quickly reorder the columns in

your results and sort the results in a specific order.

Query Conditions Panel

5

The Query Conditions panel is where you can limit the results your query by specifying specific conditions on the columns you select. The results of your query will include only the data that satisfies the conditions you specify. Each condition is an expression that consists of three elements: the column name, an operator, and a value. For example:

The condition in the previous illustration is applied to the Members Age column. The operator, is between, limits the records selected from the Members Age column and instructs the query to return records only those of members whose age is between 13 and 19.

Query Results Panel

After you run a query, the results appear in the Query Results panel. The results displayed are

reflective of how the query was constructed: the sort options and column arrangement used and

the conditions applied to the data.

Controls located at the top of the Query Results panel provide functions that enable you to

control the number of records returned by the query and to remove duplicate records. A special

option lets you expand the panel to create a larger viewing area for viewing results. You can also

select different output options to export results to a .csv file or use them as a data source for a

performing a mail merge or for creating family and member workgroups.

Step 3. Practice Designing Queries

Now that you are familiar with the elements of the user interface, try playing with a few sample

questions and designing queries to answer them. We have provided a couple of tutorial lessons

to get you started. Try working through the lessons in the order given.

● ConnectNow IQ Tutorial: How to Design a Simple Query

● ConnectNow IQ Tutorial: How to Design Complex Queries

● ConnectNow IQ Tutorial: Practice Problem

The tutorials familiarize you with the process of query design and show you how to build a query

in the application.

After working through the tutorials, try the practice problem as a self test. After that, you have

a basic grounding in query design. Feel free to experiment with your own questions and designs.

About the Intelligent Query Page

6

Overview

Clicking the IQ tab opens the Intelligent Query page. This standalone page provides a visual

interface that contains all of the controls you need to create and run queries in a user-friendly

way with no knowledge of database design, table relationships, or SQL.

As shown in the following illustration, the Intelligent Query page has a row of command

buttons across the top. These buttons enable you to create, view, and manage the queries you

create. Beneath the row of button are four main panels. These panels contain features that help

you build and define your queries. The illustration below identifies the main areas on this page.

Place your mouse pointer over a link (without clicking) to view a general description of an area.

Click the link to jump to the section on this page that provides more detailed information.

Query Management Buttons

Use these button controls to create and manage your queries.

opens the design view where you can start designing a new query. If you are

currently working on a query and have columns and conditions selected, clicking this button

clears your current selections, allowing you to start a new query. You are prompted to save your

current selections.

opens the Save Query window where you can give the query you are working on a

name. You can also specify other query-related details and then save the query.

(a variant of the Save Query feature) saves a copy of the current query to the

My Queries list with a different name.

7

opens the My Queries window to show a list of private queries (those created by

the logged in user) as well as global (public) queries, which are those shared by other users).

opens the Recent Queries window to display a list of the last 12 queries opened

by the logged in user.

opens the Query Tags window to display a list of existing query tags. The Add Tag

feature in this window enables you to add new tags to the list.

opens the View SQL window where you can instantly view the SQL statements

generated for a query so you can see what the underlying SQL looks like. This information can

give you a better understanding of the SQL language and how statements are structured. The

information is view-only so you cannot change it.

opens the Data Model Properties window. The information displayed can help

the ParishSOFT Technical Support professional troubleshoot a problem you are having with the

application.

Main User Interface Components

The ConnectNow IQ user interface consists of four main components:

● Column Picker panel

● Result Columns Panel

● Query Conditions Panel

● Query Results Panel

Column Picker Panel

The Column Picker panel lists all of the columns you can select (pick) to create your query. The

columns are organized into main groups (or entities). The columns in the Column Picker panel

are presented in a tree-style list. Expand the list to find the column or field you need. When you

find it, select the checkbox to the left to add it to Result Columns panel on the right.

Expand and Collapse List Buttons

The columns in the Column Picker panel are grouped by entity. The entities are listed

alphabetically. By default, the columns in each group are hidden. Open and close the groups by

using these buttons:

● opens a group to show all of the columns in the group.

● closes a group so the individual columns within the group are not visible.

8

http://cnhelp.parishsoft.com/cnhelp/iq/query_management/tags/how_to_create_and_manage_querytags.htm#Creating_a_Tag

Checkbox Controls

Checkbox controls allow you to select columns. A checkbox appears next each name of the

group and next to each column within the group. With a single click of the group checkbox, you

can select all columns within the group at the same time. If you want to select an individual

column within a group, simply select the column's checkbox.

In its initial state, a checkbox is empty: . When you click inside the empty box, a small

checkmark appears inside the box to indicate that the column is selected. Clicking inside

the checkbox again removes the checkmark and deselects the column.

● If you want to select all columns in a group, select the checkbox next to the group name. As shown in this illustration, all of the columns in the Batches group are selected when you select Batches:

● If you want to select specific columns, select the checkbox next to the desired column's name, as shown below:

Column Picker Panel Buttons

deselects all columns currently selected in the Column Picker panel.

adds condition statements to all columns currently selected in the Column Picker panel.

The condition statements appear in the Query Conditions panel.

9

adds the columns selected in the Column Picker panel to the Result Columns panel.

Result Columns Panel

The Result Columns panel shows the names of the columns you want to display in your query

results. When you first open the Intelligent Query page to start a new query, the panel is

empty. As you select columns, they are added to the Result Columnspanel.

Add Column Controls

You add columns to the Result Columns panel one of three ways:

● By dragging columns one at a time from the Column Picker panel into the Result Columns panel. For instructions, see Drag_and_Drop_Method.

● By selecting columns in the Column Picker panel and then clicking . For instructions,

see Select_Columns_and_Click_Method

● By clicking the Add new column link in the Result Columns panel and then selecting the column from the displayed menu. For instructions, see Select_the_Add_Column_Link_Method.

Result Column Headers

Each column you add to the Result Columns panel appears on a separate line. Each line item

contains the following information:

● Expression

Shows the name of the selected column.

If you select the wrong column, click the link under the column's name. A dropdown menu

is displayed with the names of columns. Select the column you want from the menu.

● Title

Shows the title of the column that displays in your query results.

If you want to change title to make it more descriptive, click the Title link. In the Title field,

type a new name for the column, and then press Enter to save the change.

10

To re-display the default column title, select all of the text currently displayed in the field. Press the

Del key on your keyboard to erase the contents of the field. Then, press Enter to display the original title.

Operations Buttons

(Sorting)

This button appears to the left of each column in the Result Columns panel. Click it to

display a menu of options you can select to sort and move the columns in your query

results. For details, go to How to Sort a Column's Data.

(Delete)

This button appears to the right of each selected column. Click it to remove the column

from the Result Columns panel. The data in the column does not appear in your query

results. For details, go to Removing_a_Query_Column_from_a_Query.

(Change to Aggregate Column)

This button appears to right of each selected column. Click it to display a menu of options

you can select to aggregate the values in the column. Aggregate functions operate on a set

of rows and return a summary value. For details, go to How to Calculate a Column's Data

(Aggregate Functions).

Add New Column Link

Click [Add a new column] to add a new column to the query. For details, go to

Adding_a_Column_to_a_Query.

Query Conditions Panel

In this panel, you can specify the filtering conditions (criteria) to apply to the data in a selected

column.

Select Records Link

Clicking the all link in the "Select records" statement displays a menu of filters that you can

apply to the query conditions. For details, go to Filters.

Operations Buttons

11

(Add Condition)

This button appears to the right of the Select records link. Click it to add a condition to a

query. For details, go to Adding_a_Condition.

(Add Group of Conditions)

This button appears to the right of the Select records link. Click it to add a group of conditions

to a query. For details, go to How to Add a Group of Conditions.

(Toggle Enable)

This button appears to the right of an applied condition. Click once to disable the condition.

Click a second time to enable it. For details, go to How to Disable and Re-enable a Condition.

(Delete)

This button appears to the right of an applied condition. Click it to remove the condition from

the query. For details, go to Removing_a_Condition.

Add New Condition Link

[Add a new condition]: adds a query condition. For details, go to Adding_a_Condition.

Query Results Panel

After you run a query, the results are displayed in the Query Results panel. The results include

only the columns you selected with data that satisfies the applied conditions (criteria).

Query Results Counter

This counter appears in the top left corner of the Query Results panel. The counter shows the

number of records that meet your query criteria. For example, in the previous illustration,

95,607 records were found.

Results Per Page Dropdown List

From this dropdown list, you can specify how many records you want the system to display at

one time. You can select to display all results (the default) or select a value between 50 (the

minimum) and 5000 (the maximum). For details, go to How to Limit the Number of Result

Records Returned by a Query.

12

Unique Records Only Checkbox

Select this checkbox to ensure that only unique records are displayed in the Query Results

panel. For details, go to How to Remove Duplicate Records from Your Query Results.

Execute Query Button

After setting up the query, click this button to run it.

Query Results Button

Click this button to display a menu of options that enable you to use your query results in other

applications. For example, you can export a query to a .csv file. You can also use the results as a

data source for a mail merge or use them to create family workgroups and member workgroups

in Family Directory.

Full-screen View Button

Click this button to switch to a full-screen view of the results shown in the Query Results

panel. The view is automatically scaled to fit the size of your computer screen. For details, go to

How to Switch to a Full-Screen View of Your Query Results.

13

Chapter 2 : Building Queries

How to Create a New Query

A query enables you to pull information from your database based on criteria you specify.

This topic steps you through the process of creating, running, and saving a new query.

Creating a New Query

If you are a beginner, you may find it helpful to review some suggestions that can help you save

time and avoid some design problems. For details, go to Best Practices for Designing Queries.

1. Click .

2. Choose the columns that you want to include in the query. To do this, complete these

steps:

1. In the Column Picker panel, find the group that contains the columns you need.

Then, click to expand the group to view the columns.

Some groups contain subgroups. Expand the subgroup to view the columns within it.

1. Scroll down the list and select the columns you need. For example:

At this point, the order in which you select the columns does not matter. You can

change the order in which the columns appear in your results later.

1. Add the columns to the Result Columns panel by doing one of the following:

● Drag them to the Result Columns panel.

● At the bottom of the Column Picker panel, click .

14

1. If you need to add more columns to the query, select the [Add new column] link at

the bottom of the Result Columns panel. Continue adding columns until all of the you

need in the query are showing in the Result Columns panel.

1. Note that the columns in the Result Columns panel are listed in the order in which

they will display in your results. If you want to change the column order, complete

the following:

1. Place your cursor over the name of the column whose list order you want to

change. Slide the cursor to the left until you see this icon: . Click it to display

this menu:

1. The column order options are at the bottom of the menu. Select the desired

option to move the column:

● Move to the first: moves the column to the top of the Result Columns list. After

you run the query, this column's results will appear first in the Query Results

panel.

● Move to the previous: moves the column up one position in the list.

● Move to the next: moves the column down one position in the list.

● Move to the last: moves the column to the last position in the list. After you

run the query, this column's results will display last in the Query Results panel.

1. If you want to summarize the data retrieved for a column, you need to apply an

aggregate function to it. To do this:

1. In the Results Column, place your cursor over the name of the column whose data

you want to summarize.

2. Slide your cursor to the right until you see this icon: . Click it to display this

menu:

15

1. Select the function you want to apply to the column's data.

For example, if you want to count the data in the column, select Count. The name of the function is added to column name to indicate it will be applied to your results, as shown below:

To remove the function from the column, click again.

1. By default, the information in a column is not sorted. If you want to a column's

information to display in a specific order, do the following:

1. Place your cursor over the name of the column. Slide the cursor to the left until

you see this icon: . Click it to display this menu:

1. The sort order options are in the group at the top of the menu. Select one of these

sort options:

● Ascending: sorts the column's results in ascending order. For numeric results,

sorts the information from lowest to highest. For alphabetic results, sorts the

information in A to Z order. For dates and times, sorts the information from

least recent to most recent.

16

● Descending: sorts the column's results in descending order. For numeric

results, sorts the information from highest to lowest. For alphabetic results,

sorts the information in Z to A order. For dates and times, sorts the

information from most recent to least recent.

1. By default, no conditions are applied to columns in the query. Conditions are criteria

your can apply to a column to limit the results. For each column you need to apply a

condition to, do the following:

If necessary, take a look at some examples of query conditions to help you think about your own

query criteria.

1. Click the [Add new condition] link in the Query Conditions panel.

2. Select the column that you want to apply the condition to.

A condition statement that applies to that column's data is displayed. For example, selecting the Families > Family DUIDcolumn displays this condition statement:

1. Specify the condition by selecting an operator and a value. For operator

descriptions and examples, see Query_Operators_and_Examples.

1. Now that you have chosen the columns you want to include in your query, specified

the arrangement of the information, and applied conditions, you are ready to run

your query. Go to Running_a_Query, which follows.

Running a Query

After you build a query, run it to retrieve results from your database. To run a query:

1. If you want to make sure that the query results show unique records only (no

duplicates), select the Unique Records Only checkbox at the top of the Query Results

panel.

2. To run the query, click .

The results of your query are displayed in the Query Results panel. The top right portion of the panel shows the number of records found that match your query criteria.

1. To display results in full-screen view, click . (This button is located at the top of

the Query Results panel.)

This button is toggle. If you click it again, the results are re-displayed in the Query Results panel.

17

http://cnhelp.parishsoft.com/cnhelp/iq/building_queries/conditions/understanding_query_conditions.htm

Saving a Query

After you run the query, you can save it for your own future use. You can also indicate that

you want to share it with other users.

1. To save your query, click .

The Save Query window opens:

1. Complete the following:

1. (Required) In the Name field, type a name for the query.

2. To share the query with other users in your organization, select the Global

checkbox. If you want the query to remain private (for your eyes only), leave the

box unchecked.

3. To assign a tag to the query, select one from the Tags dropdown list.

The Tags list contains only the tags saved in your system. If you need to apply a tag not found in the list, you must create it first. For instructions, go to Creating_a_Tag. You can then edit the properties of the query to apply the tag you created.

1. In the Description field, type a description of the query.

It is a good idea to add a description that indicates the purpose of the query.

1. Click .

The system saves the query and adds it to the My Queries and Recent Queries lists. You

see this message next to the name of the query in the title bar: Query Saved.

How to Create a New Query

18

A query enables you to pull information from your database based on criteria you specify. This

topic steps you through the process of creating, running, and saving a new query.

Creating a New Query

If you are a beginner, you may find it helpful to review some suggestions that can help you save

time and avoid some design problems. For details, go to Best Practices for Designing Queries.

1. Click .

2. Choose the columns that you want to include in the query. To do this, complete these

steps:

1. In the Column Picker panel, find the group that contains the columns you need.

Then, click to expand the group to view the columns.



At this point, the order in which you select the columns does not matter. You can

change the order in which the columns appear in your results later.

1. Add the columns to the Result Columns panel by doing one of the following: ● Drag them to the Result Columns panel.

19

● At the bottom of the Column Picker panel, click .

1. If you need to add more columns to the query, select the [Add new column] link at the bottom of the Result Columns panel. Continue adding columns until all of the you need in the query are showing in the Result Columns panel.

1. Note that the columns in the Result Columns panel are listed in the order in which they will display in your results. If you want to change the column order, complete the following: 1. Place your cursor over the name of the column whose list order you want to change.

Slide the cursor to the left until you see this icon: . Click it to display this menu:

1. The column order options are at the bottom of the menu. Select the desired option to

move the column: ● Move to the first: moves the column to the top of the Result Columns list. After

you run the query, this column's results will appear first in the Query Results panel.

● Move to the previous: moves the column up one position in the list. ● Move to the next: moves the column down one position in the list. ● Move to the last: moves the column to the last position in the list. After you run

the query, this column's results will display last in the Query Results panel. 1. If you want to summarize the data retrieved for a column, you need to apply an

aggregate function to it. To do this: 1. In the Results Column, place your cursor over the name of the column whose data

you want to summarize.

2. Slide your cursor to the right until you see this icon: . Click it to display this

menu:

20


For example, if you want to count the data in the column, select Count. The name of the function is added to column name to indicate it will be applied to your results, as shown below:

To remove the function from the column, click again.

1. By default, the information in a column is not sorted. If you want to a column's

information to display in a specific order, do the following: 1. Place your cursor over the name of the column. Slide the cursor to the left until you

see this icon: . Click it to display this menu:

1. The sort order options are in the group at the top of the menu. Select one of these

sort options: ● Ascending: sorts the column's results in ascending order. For numeric results,

sorts the information from lowest to highest. For alphabetic results, sorts the information in A to Z order. For dates and times, sorts the information from least recent to most recent.

21

● Descending: sorts the column's results in descending order. For numeric results, sorts the information from highest to lowest. For alphabetic results, sorts the information in Z to A order. For dates and times, sorts the information from most recent to least recent.

1. By default, no conditions are applied to columns in the query. Conditions are criteria your can apply to a column to limit the results. For each column you need to apply a condition to, do the following:

If necessary, take a look at some examples of query conditions to help you think about your own

query criteria.

1. Click the [Add new condition] link in the Query Conditions panel. 2. Select the column that you want to apply the condition to.

A condition statement that applies to that column's data is displayed. For example, selecting the Families > Family DUIDcolumn displays this condition statement:

1. Specify the condition by selecting an operator and a value. For operator descriptions

and examples, see Query_Operators_and_Examples. 1. Now that you have chosen the columns you want to include in your query, specified the

arrangement of the information, and applied conditions, you are ready to run your query. Go to Running_a_Query, which follows.

Running a Query

After you build a query, run it to retrieve results from your database. To run a query:

1. If you want to make sure that the query results show unique records only (no duplicates),

select the Unique Records Only checkbox at the top of the Query Results panel.

2. To run the query, click .

The results of your query are displayed in the Query Results panel. The top right portion of the panel shows the number of records found that match your query criteria.

1. To display results in full-screen view, click . (This button is located at the top of

the Query Results panel.)

This button is toggle. If you click it again, the results are re-displayed in the Query Results panel.

Saving a Query

After you run the query, you can save it for your own future use. You can also indicate that you want to share it with other users.

22

1. To save your query, click .

The Save Query window opens:

1. Complete the following: 1. (Required) In the Name field, type a name for the query. 2. To share the query with other users in your organization, select the Global checkbox.

If you want the query to remain private (for your eyes only), leave the box unchecked.


The Tags list contains only the tags saved in your system. If you need to apply a tag not found in the list, you must create it first. For instructions, go to Creating_a_Tag. You can then edit the properties of the query to apply the tag you created.



1. Click .

The system saves the query and adds it to the My Queries and Recent Queries lists. You

see this message next to the name of the query in the title bar: Query Saved.

How to Add, Remove, or Replace a Column

Adding a Column to a Query

You must tell ConnectNow IQ what columns you want to include in your query. To do this, you

must add them. Use any one of the methods described below to do this.

23

Drag and Drop Method

1. In the Column Picker panel, find the group that contains the columns you need. Then,

click to expand group to view all of the columns in the group.



At this point, the order in which you select the columns does not matter. You can change the

order in which the columns appear in your results later.

1. Position your mouse (without clicking) on the name of the selected column.

When the cursor is in the correct position, its appearance changes to a double-arrow, as

shown in the following illustration:

24

1. Click the column and drag it into the Result Columns panel, making sure that you place it directly over the [Add new column]link. Then, release the mouse button.

The column is now added to the query.

The order in which the columns are listed in the Result Columns panel determines the order in which the columns will display in your results. If you want to change the order, go to How to Reorder the Columns in Your Query Results for instructions.

Select Columns and Click Method

1. In the Column Picker panel, find the group that contains the columns you need. Then,

click to expand group to view all of the columns in the group.



At this point, the order in which you select the columns does not matter. You can change the

order in which the columns appear in your results later.

1. At the bottom of the Column Picker panel, click .

The selected columns are added to the Result Columns panel on the right.

1. If you need to add more columns to the query, select the [Add new column] link in the Result Columns panel. Then, from the displayed menu, select the desired column.

1. Repeat Step 4 until all of the columns you want in your query are listed in the Result Columns panel.

25

The order in which the columns are listed in the Result Columns panel determines the

order in which the columns will display in your results. If you want to change the order, go to How to Reorder the Columns in Your Query Results for instructions.

Select the Add Column Link Method

1. In the Result Columns panel, select the [Add new column] link. 2. From the displayed menu, select the desired column.

The selected column is added to the Result Columns panel.

1. Repeat Steps 1 -2 until all of the columns you want in your query are listed in the Result Columns panel.

The order in which the columns are listed in the Result Columns panel determines the

order in which the columns will display in your results. If you want to change the order, go to How to Reorder the Columns in Your Query Results for instructions.

Removing a Query Column from a Query

If you change your mind and do not want to include a particular column, you can easily remove

it.

1. In the Results Column panel, position your cursor on the column you want to delete.

2. Slide your cursor to the right until you see this button, and then click it: .

The column is immediately removed from the query.

Replacing a Column

Sometimes, you select the wrong column or decide that you want to use a different column in

your query. Use this procedure to replace a column:

1. In the Results Column panel, click the link in the Expression column for the column

you want to replace.

A list of columns is displayed.

1. From the list, find the name of the column you want to replace and select it.

The original column is replaced with your selection.

How to Reorder the Columns in Your Query Results

Sometimes, you may want the columns in your query results to display in a specific order.

This topic shows you how to make sure they display in the exact order in which you want to

view them.

26

By default, the order in which the data columns are listed in the Result Columns panel

determines the order in which they appear in your results.

If you need to change the order in which the columns appear in your results, you need to

modify the order in which they display in the Result Columns panel.

Follow these steps for each column you want to move:

1. In the Results Column panel, position your cursor on the name of column you want to

move. Slide your cursor to the left until you see this icon: . Click the icon to

display this menu:

How to Sort a Column's Data

Sometimes, you may find it easier to examine the results of your query if the information

appears in a certain order. This topic shows you how to sort the data in a column so that the

information displays in a specific order

By default, the data in a column is not sorted. If desired, you can sort the data in one of two

ways:

● Ascending order: arranges the information in increasing order.

● For numeric results: lowest to highest.

27

● For alphabetic results: in A to Z order. ● For dates and times: from least recent to most recent.

● Descending order: arranges the information in decreasing order.

● For numeric results: from highest to lowest.

● For alphabetic results: in Z to A order. ● For dates and times: from most recent to least recent.

Applying a Sort to a Column's Data

The following procedure shows you how to apply a sort to a column's data.

1. In the Results Column panel, position your cursor on the column whose results you

want to sort. Slide your cursor to the left until you see this icon: .

For example:

1. Click to display this menu:

1. The sort order options are in the first group. Select one:

● Ascending

● Descending

An icon appears next to the column's name to indicate that the sort function is

applied. The direction of the arrow on the icon indicates the direction of the sort:

ascending or descending.

28

Now, when you run the query, results for the column display in the Query Results panel in the order you specified.

Removing a Sort from a Column's Data

If a sort is currently applied to a column's data, one of these icons is displayed next to the

name of the column in the Result Columns panel: ascending or descending. The

direction of the arrow on the icon indicates the direction of the sort.

To remove a sort from a column:

1. Click the icon to display this menu:

1. Select the Not sorted option.

The sort icon is removed from the column to indicate that the sort is no longer applied to the column's data.

29

1. The column order options are located in the group at the bottom of the menu. Select

one of the following:

● Move to the first: moves the column to the top of the Result Columns list. After you

run the query, this column's results appear first in the Query Results panel.



● Move to the last: moves the column to the last position in the list. After you run the

query, this column's results display last in the Query Results panel.

The Results Column panel is immediately updated, and the selected column is relocated in the list.

1. Run your query.

The columns appear in the Query Results now display in the order you specified.

How to Rename a Column Heading in Your Query Results

ConnectNow IQ provides a default title for each column you select for a query, but you can easily

change the title of a column to any name you want. When working with your results, you may

find it helpful to change the title of a column to make your results more meaningful.

1. In the Results Column panel, click the link in the Title column for the column you want

to rename.

A text field is displayed. For example:

30

1. Press the Back key on your keyboard to remove the current text from the field. Type a

new name for the column in the field, and then press Enter.

The Title field is updated with the change.

After you run the query, the new title appears as the column heading in the Query Results panel.

Any new reports (for example, Excel exports) created from the query use the new title. You will also see the new title listed as a merge field if you use the query results as a data source for a mail merge.

How to Calculate a Column's Data (Aggregate Functions)

Sometimes you need to extract data from your database and calculate it. Aggregate functions

(for example, sum, count, and average) enable you to calculate and summarize data in your

queries. This topic explains what an aggregate function is and shows you how to apply this

type of function to your data.

What Is an Aggregate Function?

Aggregate Functions Menu

Applying an Aggregate Function to a Column's Data

Examples of Applying an Aggregate Function to a Column's Data

Count Function

Average Function

Maximum Function

What Is an Aggregate Function?

To aggregate means to put together or combine. An aggregate function, therefore, performs

a calculation on the values in a column and returns a single (combined) value, such as a

count or an average. Examples of using this type of function are as follows:

● Finding the sum (total) values in a column (for example, the total amount contributed to a specific fund).

● Obtaining a count of a set of records (for example, the total number of 18-year-old members in an organization).

● Determining the average value in a column (for example, the average dollar amount of pledges made to a specific fund).

31

● Finding the lowest value in a column (for example, the lowest contribution amount made to a specific fund).

● Finding the highest value in a column (for example, the oldest member in your organization).

In each of these examples, all you want is a summary of the column's data—not the

column's actual data.

Aggregate Functions Menu

Five aggregate functions are available in ConnectNow IQ. These functions are available on a

special Functions menu ( ). The menu is available for each column listed in the Results

Column panel. To display the menu:

1. Position your cursor on top of the column you want to apply an aggregate function to.

2. Drag your cursor to the right until you see this icon, and then click it: to display

the Functions menu:

The following table lists and describes the aggregate functions available.

Aggregate Function

Description

Sum Adds all of the values in the column together and displays a total.

Count Counts the number of columns that meet the specified criteria and displays the number.

Average Averages the values in the column.

Minimum Finds the lowest value in the column.

Maximum Finds the highest value in the column.

32

Applying an Aggregate Function to a Column's Data

To apply an aggregate function to a column's data, do the following:

1. In the Results Column, place your cursor over the name of the column whose data you

want to calculate.

2. Slide your cursor to the right until you see this icon: . Click it to display the

Functions menu:


The selected function is added to the column name and title. For example:

1. To remove the function from the column, click again.

Examples of Applying an Aggregate Function to a Column's Data

Following are examples of applying an aggregate function.

Count Function

The Count function counts the number of records that match a specific condition. In this

following example, a Count function is applied to the Members Age column to find out how

many members (count) in the organization are 18 years old (the condition). The Query

Results panel shows that 1285 records (a count) were found.

Be sure to click to run the query to view the aggregated result.

33

Average Function

The Average function returns an average value of a specific column. In the following

example, the Average function is applied to thePledges > Total Pledge Amount column to

find out the average total pledge amount made to the Building Fund Campaign (the

condition). The Query Results panel shows that the average pledge to the fund is $1675.00.


Maximum Function

The Maximum function returns the highest value the selected column. In the following example, the Maximum function is applied to the Members Agecolumn to find out the age of

34

the oldest members in the organization. The Query Results panel shows that oldest member is 118 years old.


Applying Conditions

Understanding Query Conditions

Query conditions enable you to define specific search criteria so that you can find the exact data

you want to work with. This topic introduces you to query conditions and provides examples of

the types of conditions you can apply to your data to enable you to find the information you

need.

What Is a Condition?

A query condition is an expression you apply to a column that limits the results based on values

you tell the query to search for. When you create a query condition, you identify the column and

then refine the search of the column's data by choosing a specific property or content that

satisfies the condition. A record must meet the criteria you specify to be included in the query's

results.

A condition varies depending on the type of data in the column. That is, a condition you specify

for a column that contains numeric data looks different from one specified for a column

containing text or date data. To view the various types of conditions available and examples of

each, go to Query_Operators_and_Examples later in this topic.

35

http://cnhelp.parishsoft.com/cnhelp/iq/building_queries/conditions/understanding_query_conditions.htm#Query_Operators_and_Examples

Elements of a Condition

A condition is an expression that consists of three elements: the column name, an operator,

and a value. For example:

● Column: identifies the column the condition applies to. It tells the query which column's data to evaluate. In the above illustration, Members Age is the name of the column.

● Operator: specifies the condition (criteria) to apply to the column's data. In other words, the operator lets you specify the type of comparison you would like to perform (go to Query_Operators_and_Examples for a list of operators you can use. In the above illustration, is between is the operator.

● Value: identifies exactly what you want the query to look for in the specified column. The value is a link that opens a text entry field in which you specify the value or values the query must look for. The query searches for values that match the numbers, letters, words, or phrases you type into the field or fields.

Record Filters

Filters are used to restrict or limit the records the query retrieves.

The records filter is located in the "Select records where" statement that appears at the top of

the Query Conditions column. Clicking the all link in the statement displays this menu of

filters:

Select one of these record filters:

● all (the default): displays all of the records (including any duplicates) that match all of the specified query conditions.

● any: displays all records that match any—but not all—of the specified query conditions.

● none: displays records that do not meet any of the specified query conditions.

36


● not all: displays records that match some—but not all—of the specified query conditions.

Displaying Query Conditions

Click any of the following to display a list of query conditions you can apply to a column's data.

The conditions available depend on the type of data (for example, text, numeric, date) in the

column:

● : this button is located at the bottom of the Column Picker panel. Click it to add

condition statements to the columns listed in the Results Column panel.

● [Add new condition]: this link is located at the bottom of the Query Conditions panel. Click it to display a list of columns. Select the column to apply the condition to.

● : this button is located at the top of the Query Conditions panel. Click it to display a

list of columns. Select the column to apply the condition to.

Applying a Query Condition

The three methods of applying a condition are provided below:

Method 1

1. In the Query Conditions panel, click .

2. From the displayed menu, select the column you want to apply the condition to.

The condition expression is displayed in the Query Conditions panel. An expression contains three elements: the column name, an operator, and a value. For example:

1. To add the condition to the column, complete these steps:

1. Click the Operator link (see previous illustration) and select the desired operator

from the menu.

The operators listed in the menu depend on the type of data contained in the column. For operator


The operator is added to the condition.

1. Click the Value link (see previous illustration). In the text field, type a value or

values for the condition.

37


Method 2

1. In the Query Conditions panel, select this link: [Add new condition]. 2. From the displayed menu, select the column you want to apply the condition to.




from the menu.






Method 3

1. In the Column Picker panel, select the columns you want to include in your query.

The names of the columns are added to the Result Columns panel.

1. At the bottom of the panel, click .

For each column you selected, a condition expression is displayed in the Query

Conditions panel. For each column, the condition expression may vary, depending on the types of data in the column. However, each expression contains three elements: the column name, an operator, and a value. For example:

1. To add the condition, complete these steps:


from the menu.

38







Query Operators and Examples

Following is a list of operators you can apply to columns in your query. Click the link to jump to

a description of the operator and an example of how it works.

This list includes all operators. The operators available for selection depend on the type of data

contained in the column.

is equal to

Retrieves only those records where the value in the specified column's field is equal to [enter

value]. The value in the column's field must exactly match the value specified in the text entry

field.

Click [enter value] to display a text entry field. Type the desired value in the field.

Example

This example applies the is equal to condition to the Batches Actual Cash Total column to

retrieve only those records with batches having a cash total equal to $1500.00:

This second example applies the is equal to condition to the Families.Owner Organization ID

column to retrieve only those records with an organization ID equal to 21447:

is not equal to

Retrieves only those records where the value in the specified column's field is not equal to

[enter value]. The value in the column's field must not equal the value you specify in the text

entry field.


Example

39


The following example applies the is not equal condition to the Members is Active column to retrieve only those records of individuals whose current member status is not equal to "Active":

is less than

Retrieves only those records where the value in the specified column's field is less than (smaller

than) [enter value]. The value in the column's field must be less than the value specified in the

text entry field.


Example

The following example applies the is less than condition to the Pledges Down Payment Amount column to retrieve only down payment amounts less than $100.00:

is less than or equal to

Retrieves only those records where the value in the specified column's field is less than (smaller

than) or equal to [enter value]. The value in the column's field must be less than or the same as

the value specified in the text field.


Example

The following example applies the is less than or equal to condition to the Pledges Down Payment Amount column to retrieve only those records in which the down payment amount is less than or equal to $100:

is greater than

Retrieves only those records where the value in the specified column's field is greater than

(larger than) [enter value]. The value in the column's field must be greater than the value

specified in the text field.


Example

40

The following example applies the is greater than condition to the Pledges Total Pledge Amount column to retrieve only those records in which the total pledge amount is more than $500.00:

is greater than or equal to

Retrieves only those records where the value in the specified column's field is greater than

(larger than) or equal to [enter value]. The value in the column's field must be greater than or

the same as the value specified in the text field.


Example

The following example applies the is greater than or equal to condition to the Members Age

column to retrieve only those records of members 13 years of age or older:

is null

Retrieves only those records where the value in the specified column's field is blank (contains

no value).

Note that "null" is not the same as zero (0). Zero is considered a value—not considered null.

Example

The following example applies the is null condition to the Families.Contact Info Email Address

column to find records of families that have no email address in the system:

is not null

Retrieves only those records where the value in the specified column's field is not blank.

Example

The following example applies the is not null condition to the Pledges End Date column to

retrieve records that have a value in the Pledge End Date field. The value indicates the date that

the pledge ended or will end as of the date shown in the field.

41

is in list

Retrieves only those records where the value in the specified column's field is contained in the

list.

Example

The following example applies the is in list condition to the Pledges.Down Payment Check No

column to retrieve records that match the check numbers in the list. The query is looking for

columns in which the check number for a pledge down payment is 100, 101, or 102.

is not in list

Retrieves only those records where the value in the specified column's field is not contained in

the list.

Example

The following example applies the is not in list condition to the Funds. Fund Name column to

retrieve records that do not match the funds in the list. The query is looking for columns in

which the fund name is anything other than the following:

● 005-540-333

● 005-540-111

● 005-540-4444

starts with

Retrieves only those records where the value in the specified column's field starts with [enter

value].

Example

The following example applies the starts with condition to the Families Last Name column to

retrieve records of families whose last name starts with the letter M:

does not start with

42

Retrieves only those records where the value in the specified column's field does not start with

[enter value].

Example

The following example applies the does not start with condition to the Families.Bank

Accounts Type column to retrieve records of families whose bank account type does not start

with the letters "Cr":

contains

Retrieves only those records where the value in the specified column's field contains [enter

value].


Example

The following example applies the contains condition to the Members Special Needs Desc

column to retrieve only those records of members in which the special needs description

contains the phrase "wheelchair access":

does not contain

Retrieves only those records where the value in the specified column's field does not contain

[enter value]. In other words, the query excludes any records that contain the value specified in

the text field.


Example

The following example applies the does not contain condition to the Members Career

Description column to retrieve only those records of members in which the career description

does not contain the words "self-employed":

is between

Retrieves only those records where the value in the specified column's field is between [enter

value] and [enter value].

43

Click [enter value] to display the text entry fields. Type the desired value in each field.

Example

The following example applies the is between condition to the Members Age column to retrieve

only those members between the ages 13 and 19:

is not between

Retrieves only those records where the value in the specified column's field is not between

[enter value] and [enter value].

Click [enter value] to display the text entry fields. Type the desired value in each field.

Example

In the following example, applying the is not between condition to the Pledges Balance column

retrieves only those records that have a pledge balance greater than $1000.

year is

Retrieves only those records where the year in the specified column's field is [enter value].

Allows you to search a date field for a specific four-digit year.

Click [enter value] to display a text entry field. Type the desired year in the field.

Example

In the following example, applying the year is condition to the Sacraments.Baptism Date

Completed column retrieves only those records in which the baptism was completed in 1987.

month is

Retrieves only those records where the month in the specified column's field is [enter value].

Allows you to search a date field for a specific month.

The month value entered must be numeric and be between 1 and 12. For example, enter 6 for the

month of June.

44

Click [enter value] to display a text entry field. Type the desired number representing the

desired month in the field.

Example

In the following example, applying the month is condition to the Sacraments.Matrimony Date

Completed column retrieves only those records in which the marriage was completed in the

month of June.

day is

Retrieves only those records where the date in the specified column's field is [enter value].

Allows you to search a date field for a specific date.

The date value entered must be numeric and between 1 and 31. For example, enter 4 for the 4th.

Click [enter value] to display a text entry field. Type the desired number representing the day

in the field.

Example

In the following example, applying the day is condition to the Sacraments.Death Burial Date

column retrieves only those records in which the burial date is the 4th day of the month.

in sub query

not in sub query

is maximum of

Retrieves only those records in which the maximum value in the specified column's field is

[enter value].


Example

The following example, applying the is maximum of condition to the Members Age column

retrieves the age of the oldest member.

before

45

Retrieves only those records in which the value specified in the column's field is before [enter

value].

Click [enter value] to display a calendar.

The calendar displays today's date as the default. Select the desired month and year from the

calendar displayed. If the year you want is not visible, select the earliest year possible to load it

into the field. Then, click inside the year field again to display another set of years and select

the earliest year in the displayed group. Continue in this manner until you find the year you

need. Then, select the desired month and day.

Example

In the following example, applying the before condition to the Ministry Scheduler EndDate

column retrieves only those records of ministers whose assignment end date is before August

31, 2015.

before (special)

Retrieves only those records in which the value specified in the column's field is before the

special day option selected from the following menu:

Click Today to display the menu.

46

Select one of the special day options.

Example

In the following example, applying the before (special) > Today condition to the Contributions

Payment Date column retrieves records with a payment date before today's date.

after

Retrieves only those records in which the value specified in the column's field is after [enter

value].


The calendar displays today's date as the default. Select the year from the calendar displayed.

The calendar displays today's date as the default. If the year you want is not visible, select the

earliest year possible to load it into the field. Then, click inside the year field again to display

another set of years and select the earliest year in the displayed group. Continue in this manner

until you find the year you need. Then, select the desired month and day.

Example

47

In the following example, applying the after condition to the Members Birthdate column

retrieves records of members date of birth is after July 27, 1990:

after (special)

Retrieves only those records in which the value specified in the column's field is after the

special day option selected from the following menu:



Example

In the following example, applying the after (special) > First day of the year condition to the

Pledges Start Date column retrieves only those records with a pledge start date after January 1.

custom period

Retrieves only those records in which the value specified in the column's field is within [enter

value] and [enter value].


48

The calendar displays today's date as the default. Select the year from the calendar displayed. If

the year you want is not visible, select the earliest year possible to load it into the field. Then,

click inside the year field again to display another set of years and select the earliest year in the

displayed group. Continue in this manner until you find the year you need. Then, select the

desired month and day.

Example

In the following example, applying the custom period condition to the Contributions Payment

Date column retrieves only those records with a contributions payment date between August 18,

2015 and August 31, 2015:

is equal to (special date)

Retrieves only those records in which the value specified in the column's field is equal to the special day option selected from the following menu:



Example

In the following example, applying the is equal to (special date) > Tomorrow condition to the

Contributions Payment Date column retrieves only those pledge records with payment date of

tomorrow:

is not equal to (special date)

49

Retrieves only those records in which the value specified in the column's field is not equal to

the special day option selected from the following menu:



Example

In the following example, applying the is not equal to (special date) > First day of the year

condition to the Ministry Scheduler EndDate column retrieves only those records of ministers

whose assignment end date is not today:

How to Add, Edit, or Remove a Condition

Adding a Condition

The Query Conditions panel is where you specify conditions that limit the selection of data

from your database. The results of your query include only the data that satisfies the conditions

you specify.

Use either of the following methods below to add a condition to your query.

Method 1

This method lets you find and select the exact column you want to add the condition to. Repeat this procedure for each column you want to add a condition to:

1. In the Query Conditions panel, click or select this link: [Add new condition]. 2. From the displayed menu, select the column you want to apply the condition to.

50




from the menu.

The operators listed in the menu depend on the type of data contained in the column.


1. Click the Value link (see previous illustration) and type a value or values for the

condition.

The condition you specified is now added to the selected column.

Method 2

This method automatically adds a condition to each column that is currently selected in the

Result Columns panel.

Because this method adds a condition to each selected column, you may need to remove the

conditions you do not want to apply.

1. In the Column Picker panel, select the columns you want to include in your query.

The names of the columns are added to the Result Columns panel.

1. At the bottom of the panel, click .

For each column you selected, a condition expression is displayed in the Query

Conditions panel. For each column, the condition expression may vary, depending on the types of data in the column. However, each expression contains three elements: the column name, an operator, and a value. For example:

1. To add the condition, complete these steps:

51


from the menu.



1. Click the Value link (see previous illustration) and type a value or values for the

condition.

The condition you specified is now added to the selected column.

Editing a Condition

A condition expression contains three elements: the column name, an operator, and a value.

For example:

To edit a condition, you can change any of these three elements.

● To change the column, click the Column link and select a different column from the list.

● To change the operator, click the Operator link and select the desired operator from the menu.


● To change a value, click the Value link and type a new value in the displayed field..

Removing a Condition

To remove a condition from a column:

1. In the Query Conditions panel, position your cursor on the column whose condition you

want to remove.

2. Slide your cursor to the right until you see this icon: . Click the icon.

The condition is removed immediately and is no longer applied to the column's data.

How to Disable and Re-enable Condition

A condition is automatically enabled as soon as you add it to a column. Sometimes, you may

want to specifically exclude or ignore an applied condition in your query. Instead of removing it

52

completely, you can simply disable it. This topic shows you how to disable a condition and how

to enable it when you want to re-apply it.

Disabling a Condition


want to disable.

2. Slide your cursor to the right until you see this icon: .

For example:

1. Click .

The condition is removed immediately from the column and is no longer applied to the column's data. The text of the condition is grayed out, and this icon appears to indicate

that the condition is currently disabled: .

Re-enabling a Condition


want to re-enable.


1. Click .

The condition is once again enabled and applied to the column's data.

53

Chapter 3 : Managing Queries

How to Open and View a Saved Query

Opening a Saved Query

You can open any query that you saved or any query saved by other users that they designated

"Global".

1. Click to open the My Queries window.

2. Select the query you want to open, and then click .

Details for the query are displayed on the Intelligent Query page:

● Name of the query

● Columns selected ● Specified conditions

For example:

If desired, you can perform any of the following tasks:

54

● View the properties assigned to this query (for example, the name of the owner). To do

this, click .

● Execute the query. To do this, click .

● Modify the query and save over it (replace it). To do this, make the desired changes, and

then click .

● Modify the query and save a copy of it. To do this, make the desired changes, and then

.

Opening a Recently Viewed Query

ConnectNow IQ provides quick access to the last 12 queries you viewed.

1. Click to open the Recent Queries window.


Details for the query are displayed on the Intelligent Query page:

● Name of the query

● Columns selected (in the Result Columns panel) ● Specified conditions (in the Query Conditions panel)

For example:

55

If desired, you can perform any of the following tasks:

● View the properties assigned to this query (for example, the name of the owner). To do

this, click .

● Execute the query. To do this, click .

● Modify the query and save over it (replace it). To do this, make the desired changes, and

then click .

● Modify the query and save a copy of it under a new name. To do this, make the desired

changes, and then .

How to View and Edit the Details for a Saved Query

This topic shows you how to view and edit the properties for a query. Properties are the

descriptive details about the query, such as the query name, tags, and creation date, that the

system maintains for each query you create. Some of these properties are view-only while

others can be edited.

Viewing a Query's Details


The My Queries list contains all of the queries created by the logged-in user and queries created by other users and marked as "public" (shared).

For example:

56

Each query is listed on a line by itself. The following properties are provided for each query in the list:

● Query Name: name of query. You can rename the query by editing this field (go to

Editing a Query's Properties).

● Query Tags: tags assigned to the query. You can change the tags by editing this field (go

to Editing a Query's Properties).

● Date Created: date the query was created and saved.

● Date Modified: date the query was last changed and saved.

● Owner: name of the individual who created the query.

● Global: indicates whether the query is public (box is checked) or private (box is not

checked). If desired, you can change the public or private designation (go to Editing a

Query's Properties).

1. You can perform any of the following tasks:

● If you are looking for a specific query, you can locate it by using the Filter by Query Tags

list. Simply, select a tag from the dropdown list and click to show only those

queries with the selected tag.

● The list has a built-in column sort feature. If you want to reorder the queries in the list

based on the information in a particular column, click the column heading you want to

sort on. For example, to view the queries in order by the date created, click the Date

Created column header.

● If you want to change a query's properties, go to Editing a Query's Properties for

instructions.

1. When done, click to exit the window.

Editing a Query's Details

1. To edit a query's details, click the query's Edit link.

The query is now in edit mode so you can edit the properties. For example, selecting the Active Teen Members query opens the edit modal for the query:

As you can see in the above illustration, you can edit these four properties for a query:

● Query Name: to add or change the name, type a new name in the field.

57

● Query Tag: to add or apply a different tag, click and select the desired tag from the

dropdown list.

The only tags in the list are the ones you created. If you need to create a query tag,

go to Creating_a_Tag. After you create the tag, it is available for selection.

● Description: to add or replace a description, type your description in this field.

● Global: to make the query public (shared), select the Global checkbox. To make the

query private, deselect (to uncheck) the Global checkbox.

1. Make the desired changes. Then, click the Update link in the lower right.

The system saves your changes, exits edit mode, and returns you to the My Queries window. The updated properties for the query are visible in the list.

1. Click to exit the My Queries window.

How to Modify the Design of a Query

This topic shows you how to modify the design of a query. You will learn how to add and remove

columns of information, change conditions, and sort results, to name a few.

Opening a Query

Your first step is to open the query whose design you want to modify.

1. Do one of the following:

● Click to open the My Queries window to view a list queries created by the

logged-in user and queries created by other users and designed as public (global).

● Click to open the Recent Queries window to view the last 12 queries

created by the logged-in user.


The query is presented in design view. The following details are displayed:

● Name of the query.

● Columns selected (visible in the Result Columns panel). ● Sort options, if any, applied to a column (visible in the Result Columns panel).

58

● Specified conditions, if any. These are visible in the Query Conditions panel.

For example:

From the design view, you can make a variety of modifications to the query, including:

● Adding a column.

● Removing a column. ● Renaming a column. ● Adding, editing, or removing an aggregate function. ● Reordering result columns. ● Sorting a column's data. ● Adding, editing, or removing a condition.

Modifying a Query

The following section shows you how to modify elements of a query.

Adding, Editing, and Renaming a Column

The following sections show you how to add, remove, and rename a column.

Adding a Column

Adding a column is probably one of the most common modifications to a query. Sometimes, you

need to add a column because you want include additional information in your query results or

because you need to add a condition to the query for information that was not included in your

original results.

In Lesson One, we designed a simple one-column query to produce a list of last names of

Religious Education class leaders. We want to know the first names of these individuals and also

59

assigned roles. Because this information (first name and role) were not in our original query, we

need to add them as columns. The following steps show you how to modify the query by adding

new columns of information.

1. Open the query you want to modify by following the instructions presented earlier under

Opening_a_Query.

For example, opening our one-column query displays the following:


1. In the Result Columns panel, click [Add new column], and then select Religious

Education > Class Leader First Name. 2. In the Result Columns panel, click [Add new column], and then select Religious

Education > Class Leader Role.

The Result Columns panel now shows the three columns in our query: the Class Leader Last Name column from our original query plus the two columns we just added:

60

1. Click to run the query.

The Query Results panel displays the results. Our modified query now shows the first names of our class leaders and their respective roles.

1. If desired, save the query under a new name. To do this, click , provide

the information for the modified query, and then click .

Removing a Column

Sometimes, you want to remove unnecessary information from a query. If a column of

information is no longer required, you can simply remove it. Removing unnecessary columns

keeps the query from looking cluttered and helps with the readability of your design.

To remove a column:

1. Open the query you want to modify by following the instructions presented earlier under

Opening_a_Query.

2. In the Result Column panel click to the far-right of the column you want to

remove.

The system removes the column.

Renaming a Column

You can change the name of a column as it is displayed in your query results. To do this,

complete the following:

1. In the Results Column panel, click the link in the Title column for the column you want

to rename.

A text field is displayed. For example:

61

1. Press the Back key on your keyboard to remove the text from the field. Type a new name

for the column, and then press Enter.

The Title field is updated with the change.

After you run the query, the new name appears as the column heading in the Query Results panel.

Any new reports (for example, Excel exports) created from the query use the new name. You will also see the new name listed as a merge field if you use the query results as a data source for a mail merge.

Adding, Editing, and Removing an Aggregate Function on a Column's Data

The following sections show you how to add, change, and remove aggregate functions.

Adding an Aggregate Function

To add an aggregate function to a column's data, do the following:

1. In the Results Column, place your cursor over the name of the column whose data you

want to summarize.

2. Slide your cursor to the right until you see this icon: . Click it to display the

Functions menu:


The selected function is added to column name to indicate it will be applied to your results. For example:

62

Example of Adding an Aggregate Function

Suppose you want to know how many members in your organization are 100 years old.

1. Set up you query by doing the following:

● Select the Members Age column.

● Add a condition to the Members Age column to limit the results to members whose age

is equal to 100.

The initial setup looks like this:

1. To answer the question "how many", you need to apply the aggregate Count function to

the Members Age column. To do this:

1. Position your cursor over the Members Age column in the Result Columns panel.

Then, slide the cursor to the right until you see this icon: .

2. Click to display the Functions menu. Select the Count option.

The word "Count" is added to the Members Age column to indicate it will be applied to your results:

63


The system displays the aggregate result in the Query Results panel. For example:

As the above illustration shows, we have 81 individuals who are 100 years old.

Editing an Aggregate Function

To edit an aggregate function:

1. In the Results Column panel, place your cursor over the name of the column whose

function you want to change.


3. Click to remove the currently selected function from the column.

4. Click again to display the Functions menu. Then, select the desired aggregate

function from the menu.

The selected function is now applied to the column. The name of the function is added to

the column name to indicate it will be applied to your results.

Removing an Aggregate Function from a Column.

To remove an aggregate function that is currently applied to a column:

1. In the Results Column panel, place your cursor over the name of the column whose

function you want to remove.

2. Slide your cursor to the right until you see this icon: . Click the icon.

64

The aggregate function is removed from the column.

Reordering the Results Columns

If you need to reorder how the columns display in your results, you need to modify the order in

which they display in the Result Columns panel. Here's how:

1. In the Results Column panel, position your cursor on the name of column you want to

move. Slide your cursor to the left until you see this icon: . Click the icon.

The following menu is displayed. The sort order options appear at the bottom:

1. Select the desired sort order option:

● Move to the first: moves the column to the top of the Result Columns list. This

column's results will display first in your query results.



● Move to the last: moves the column to the last position in the list. This column's results

will display last in your query results.

The Results Column panel updates and the selected column is relocated in the list.

Now, when you run the query, the column order in the Query Results panel will match the order in which the columns are listed in the Results Column panel.

Sorting a Column's Data

If you want to add or change the sort on a column of data, do the following:

65

1. In the Results Column panel, position your cursor on the column whose results you

want to sort. Slide your cursor to the left until you see this icon: .

For example:

1. Click .

The following menu is displayed:

1. At the top of the menu, select one of these order options:

● Not sorted (the default): no sorting.

● Ascending: sorts results in A to Z order (for text data) or 0 to 9 order (for numeric data,

including dates).

● Descending: sorts results in Z to A order (for text data) or 9 to 0 order (for numeric data,

including dates).

Now, when you run the query, results for the column display in the Query Results panel in the order you specified.

Adding, Editing, and Removing a Condition

The following sections show you how to add, change, and remove conditions from a column.

Adding a Condition

66

To add a condition to a column in your query.

1. In the Query Conditions panel , click or select this link: [Add new condition].

1. From the displayed menu, select the column you want to apply the condition to.


1. To add the condition, complete the following:

1. Click the Operator link and select the desired operator from the menu.



1. Click the Value link and type a value or values for the condition.

Editing a Condition

The condition expression is displayed in the Query Conditions panel. An expression contains

three elements: the column name, an operator, and a value. For example:

You can edit any of the three elements by clicking the link and selecting a new value. For

example, to change the operator, click the Operator link to display a menu of available

operators.

67

Select the desired operator.

Removing a Condition

1. In the Query Conditions panel, place your cursor over the name of the column whose

condition you want to remove.

2. Slide your cursor to the right until u see this icon: .

3. Click to remove the currently selected function from the column.

How to Save a Query under a New Name

The Save As function lets you save an existing query under a new name, which allows you to

create a new query. For example, suppose you want to edit or revise an existing query but keep

the original one. Instead of starting from scratch to create a new query, you can open the

existing query, make the desired changes, and then save it under a new name. The original

query remains unchanged.

68

To save a query under a new name:


● Click to open the My Queries window.

ORaggreg

● Click to open the Recent Queries window.

A list of queries is displayed.

1. Select the query you want to work with from the list. Then, click .

2. If desired, edit the query, changing the columns, conditions and sort options as

necessary.

3. If desired, run the query. To do this, click .

4. To save a copy of the query under a new name, click .

The Save Query As... window opens. For example:


1. In the Name field, type a name for the query.


The Tags list contains only the tags saved in your system. If you need to create a tag, go to Creating_a_Tag.


69


1. Click .

The query now appears under the new name in the My Queries and Recent Queries lists.

How to View the List of Saved Queries

Displaying the List of Saved Queries

Click to open the My Queries window.

For example:

This list contains all of the queries created and saved under your login along with queries other

users in your organization are sharing.

Paging Through the Queries List

The page buttons along the bottom of the window show the number of pages and the number of

items (in parentheses) in the My Queries list. To page through the list, use these methods:

● Click to page forward or page back a page at a time.

70

● Click the desired page number to go directly to that page.

Sorting the List of Queries

When working with the list of queries, you many want to sort the contents on a specific column.

The My Queries list has five columns that you can sort the list on:

● Query Name

● Query Tags

● Date Created

● Date Modified

● Owner

● Global

When you click a column header, the list is sorted in order based on the contents of the column.

A directional arrow appears on the column header to indicate the direction the information in

the column is being sorted on: ascending or descending.

For example, suppose that you want to sort the queries on the date they were created so that

you can see them in order from least recent (oldest) to most recent (newest). To do this, follow

these steps:

1. Click Date Created.

An arrow appears on the Date Created column to let you know it is selected. The direction of the arrow indicates how the dates in the column are arranged.

In the following illustration, the dates are arranged in descending order according to the date they were created so you see the newer queries first.

1. To view the older queries first (ascending order), click the column header again.

71

For example:

Filtering the List by Query Tags

If you tag your queries, you can filter the My Queries list to see only those queries matching the

tag description.

1. Click in the Filter by Query Tags dropdown list.

A list of tags is displayed. The list includes all of the tags created by the logged in user and tags assigned to shared (global) queries.

1. Select checkbox next to the tag you want to use as a filter. Then, click Close to exit the

list.

The tag you selected appears in the Filter by Query Tags field.

1. Click .

The list updates to show only the queries matching the tag you selected.

How to Make a Query Public or Private

By default, ConnectNow IQ makes all of the queries you save private, which means they are

available to your user login only. If desired, you can share a query with other authorized users in

your organization. This topic gives shows you how.

72


For example:

This window lists all of the queries you created along with queries other users in your organization are sharing. For each query in the list, the Global checkbox on the far right indicates whether or not the query is public or shared (checked) or private (unchecked).

1. To change a query's public or private designation, click the query's Edit link.

The details for the query can now be edited.


● To make the query public, select (to check) the Global checkbox.

● To make the query private, deselect (to uncheck) the Global checkbox.

1. Click the Update link in the lower right.

The system saves the change and returns you to the My Queries window. The Global checkbox for the query is updated with the change.

How to Delete a Query

This topic shows you how to delete a query. You can delete only those queries created under

your user login.

73


For example:

This list shows you all of the queries you created along with queries other users in your organization are sharing.

1. Click the Delete link to the left of the query you want to delete.

You are prompted to confirm the deletion.

1. Click to delete

The system deletes the query and returns you to the My Queries window. The query is no longer in the My Queries list.

How to View the SQL Statements for a Query

This topic shows you how to view the underlying SQL statements generated for any saved query

(public and private). It also shows you how to view the SQL statements for any query you plan to

run or have run but not yet saved. Note that SQL views are view-only, which means you can

view the statements but you cannot modify them.

74

Viewing SQL Statements for Saved Queries

The My Queries list shows both the queries created and saved by the logged-in user and the

queries created and made public by other authorized users. The Recent Queries list shows the

last 12 queries created by the logged-in user only.

1. Locate the query. Do one of the following:



1. Select the query whose SQL statements you want to view. Then, click .

The elements of the query (selected columns, applied conditions, and sort operations) are displayed in panels on the Intelligent Query page. For example:

1. Click .

The View SQL window opens to display the underlying SQL statements for the selected

query. For example:

75

The SQL statements are view-only, which means you can view but not modify them.

1. Click to exit the window.

Viewing SQL Statements for Designed But Not Saved Queries

1. Design the query by selecting columns and applying the desired conditions and sort

operations.

As you design the query, the elements are added to the respective panels on the Intelligent Query page.

1. To view the underlying SQL statements for the query, click .

The View SQL window opens to let you see the SQL statements.

How to View the Properties of a Query

Properties of a query are the descriptive pieces of information (metadata) about it that are not

included in the query's contents. This topic shows you how to view a query's properties, which

include tags assigned to the query, the name of the owner, and the unique ID (Query DUID) that

identifies the query.

76

1. Open the list of queries by doing one of the following:



1. Select the query whose properties you want to view, and then click .

Details for the query are displayed on the Intelligent Query page. You see the columns selected for the query and the conditions applied to it. For example:

1. To view the properties of the query you are viewing, click . The button

is located in the upper-right.

The Query Properties window opens to show you the following information:

● Name

● Assigned tags

● Description

● Global designation (that is, whether the query is public or private)

● Query DUID (the unique ID assigned to the query)

● Owner's name.

77

How to Copy a Query's SQL Code

A query's SQL code consists of the SQL statements that underlie your query.

To copy your query's SQL code, do the following:

1. Open the query.

2. Click to open the View SQL window.

The SQL code for the query appears in the window. For example:

1. With your left mouse button, select all of the SQL code in the View SQL window. Then,

click the right mouse button and select Copy from the displayed menu.

The SQL code you selected is copied to your computer's clipboard.

1. Click to close the View SQL window.

You can now copy the code into a file. If you are troubleshooting an issue with your query, you can copy the code into an email and send it to ParishSOFT Customer Support.

78

http://cnhelp.parishsoft.com/cnhelp/iq/query_management/how_to_open_and_view_a_saved_query.htm

How to Rename a Query

You can change the name of any query you create or any public (global) query.


For example:

This list shows you all of the queries you created along with queries other users in your organization are sharing.

1. Click the Edit link to the left of the query whose name you want to change.

The details for the selected query are now in edit mode.

1. In the Query Name field, type a new name for the query.


The system saves the change and returns you to the My Queries window. The list is updated with the newly named query.

79

Chapter 4 : Using Query Tags

How to Assign a Tag to a Query

Tags are labels that you can assign to identify a query. After you assign a tag to a query, you can

use the tag to quickly locate the query in your list of queries. This topic shows you two ways to

assign a tag to a query.

Assigning a Tag When Saving a Query

Editing a Query Tag

Assigning a Tag When Saving a Query

1. Click to open the Save Query window:

1. Do the following: 1. (Required) In the Name field, type the name of the query.

2. In the Tags field, click and select the checkbox for each tag you want to assign to

the query.

A query can have any number of tags assigned to it. The tags mark the query so that you can more

easily locate it.

1. Click Close.

The selected tags are displayed in the Tags field.

1. If desired, type a description of the query in the Description field.

1. Click .

80

The query is saved and associated with the tags you selected.

Editing a Query Tag


The My Queries list contains all of the queries created by the logged-in user and queries created by other users and marked as "public" (shared). For example:

Each query is listed on a line by itself.

1. Locate the query for which you want to assign or edit a tag. Click the query's Edit link.

The query is now in edit mode so you can edit the tag. For example, selecting the Active Teen Members query opens the edit modal for the query:

1. To add or apply a different tag to the query, click and select the desired tag from the

Query Tags dropdown list.

81

The only tags in the list are the ones you created. If you need to create a query tag, go to

Creating_a_Tag. After you create the tag, it is available for selection.


The system saves the tag change, exits edit mode, and returns you to the My Queries window. The Query Tags column is updated with the change.

1. Click to exit the My Queries window.

How to Create and Manage Query Tags

What Is a Query Tag?

A query tag is a user-defined label that describes or categorizes a query. As long as it is

meaningful to you, the label can be a single word or phrase that identifies the type of query or

its purpose.

What Are the Benefits of Tagging?

Tagging your queries is optional, but having a tagging system is useful especially if you have a

lot of queries. One benefit of using tags is that they are searchable elements. When you want to

locate a query saved in your system, you can use the tag to help you find it quickly. Tags can be

used to identify various collections of queries. So, if you devise a tagging scheme that you

consistently apply when saving your queries, you can use the tag as a filter to quickly locate all

of the queries in your system that share the same tag.

Creating a Tag

The following procedure shows you how to create a tag. After you create the tag, it appears in

the Tag dropdown list and is available for selection when you save the query.

Follow these steps for each tag you want to create.

1. Click to open the Query Tags window.

2. Click Add Tag. Then, in the Tag Name field, type the name of the tag.

For example:

82

1. Click Update.

The list in the Query Tags window is updated with the name of the tag you added. You can

now assign the tag to a query and use the tag as a filter to locate queries in the My Queries list..

1. Click to exit.

Renaming a Tag

If you need to change the name of a tag in your system, complete the following:


2. Next to the tag you want to edit, click Edit. Then, in the Tag Name field, type a new name for the tag.

3. Click Update to apply the new name to the tag.

The Query Tags list is updated with the tag's new name.

1. Click to exit the Query Tags window.

Deleting a Tag

This procedure shows you how to remove an unused tag from your system.


2. Next to the tag you want to delete, click Delete.

3. When prompted to confirm the deletion, click .

The tag is removed from the list.

1. Click to exit the Query Tags window.

83

Viewing the List of Available Tags

The tags you create using the Add Tag feature are listed in the Query Tags window. To view the

list:


The Query Tags list is displayed. This list shows the names of all of the query tags in your system.

1. Click to exit.

How to Use Tags to Find Queries

If you created tags to label your queries, you can locate all of the queries that share the same tag

by using the tag as a filter. This topic shows you to locate your queries by using the tag.

1. Click to view the list of all of your queries.

2. Click in the Filter by Query Tags dropdown list.

A list of tags is displayed. The list includes all of the tags created by the logged-in user and tags assigned to global queries by other users.

84

1. Select checkbox next to the tag you want to use as a filter. Then, click Close to exit the

list.

The tag you selected appears in the Filter by Query Tags field.

1. Click .

The My Queries list updates to show only the queries matching the tag you selected. If no queries have the selected tag assigned, the following message is displayed: No data to display.

85

Chapter 5 : Working with Query results

How to Export Query Results to a .csv File

After you run a query, you can export it to a .csv file where you can modify the data. After

opening the .csv file in Excel, you can export the data into any one of the many formats Excel

offers and then import the records into any other application. This topic shows you how to

export results to a .csv file.

1. Build the query the way you typically do by choosing columns from the Column Picker and specifying conditions.

For instructions on building a new query, go to How to Create a New Query.

1. At the top of the Query Results panel, click to run the query.

The results are displayed in the Query Results panel.

1. To export the results to a .csv file, complete the following:

1. Click .

2. From the menu, select the Export to CSV option.

The results are exported to a .csv file.

1. Save the .csv file to your local hard drive.

1. If desired, do one or both of the following: ● Open the file in Excel and make changes, if necessary.

● Export the data into any one of the many formats Excel offers.

How to Limit the Number of Records Returned by a Query

To improve performance, you may find it useful to limit the number of records returned by a

query instead of retrieving the entire set. This topic shows you how to limit the results to show

the first N records resulting from your query.

1. Build the query the way you typically do by choosing columns from the Column Picker

and specifying conditions.

2. In the Return results dropdown list, select the number of records you want your query to

retrieve.

86

http://cnhelp.parishsoft.com/cnhelp/iq/building_queries/how_to_create_a_new_query.htm

The dropdown list is located above the Query Results panel. You can select to display all results (the default) or select a value between 50 (the minimum) and 5000 (the maximum) records.

We recommend that when you first run a query that you select the lowest number of records. The

more records you select, the more time it takes for the system to process the query. If the system detects that a query will return a large number of results, it displays a message to let you know that processing may take a while. You can then cancel or continue letting the query process. Canceling and then selecting a lower number of records from the list can help the query run faster.

1. Click .

The number of records you selected is displayed in the Query Results panel. For example, if you select Return 100 results, you see the first 100 records returned by the query.

Note that if you specify a sort on the results, the sort order affects which records are displayed. In

other words, an ascending order sort returns the same number of records but a different set of N records from a descending order sort.

How to Create a Family Workgroup

You can design and write queries that create family workgroups. The workgroups you create are

then immediately available in ConnectNow Family Directory where you can perform

workgroup-related tasks, such as creating labels and reports and sending email to families in

the workgroup.

IMPORTANT

Unlike other types of queries, the workgroups you create are not dynamically

updated. Therefore, if you need to change the information for a query-created

workgroup, you must edit the query, run it again, and then save the newly created

87

workgroup with a different name. You can remove the obsolete workgroup from

your system using the delete feature on the Family Workgroups page.




1. A family workgroup query must include the Family DUID column. Add this column using

either of the following methods:

● In the Column Picker panel, click to open the Families group. Scroll down and select

the Family DUID column. Then, click to add the column to your query.

OR

● In the Result Columns panel, select this link: [Add new column]. From the displayed

menu, select Families > Family DUID.



1. You can now create a workgroup using the results of your query. Complete the following:

1. At the top of the Query Results panel, click .

2. From the menu, select the Create Family Workgroup option.

The Create Family Workgroup window opens.

1. (Required) Type a name for the workgroup and select the name of the owner

organization.

For example:

Only those organizations tied to your login credentials are available for selection in the Owner

Organization list.

1. Click .

A message displays to inform you that the workgroup was successfully created.

88

1. Click to dismiss the message.

The family workgroup you created is now available in ConnectNow Family Directory

anywhere you use or see family workgroups.

Using the previous example, if we go to the Family Workgroups page, the Moved Families workgroup now appears in the Workgroups list:

With the workgroup, you can perform workgroup-related tasks, such as sending email to members in the workgroup. You can also create mailing lists and labels for the workgroup by selecting options from the Quick Reports menu.

The workgroup is also available for use as a filter in the Family Workgroups filter list

(accessed by clicking on the Family List page).

For example, the Moved Families workgroup now appears in the Family Work Groups filter list:

How to Use Query Results as a Data Source for a Mail Merge Document

This topic shows you how to generate a query and use the results as a data source for a mail

merge document.

Mail Merge & Requirements were covered in Family Directory.

89

Using Query Results as a Mail Merge Data Source

What's Needed?

You need two main elements to perform a mail merge: a main document and a data source.

● Main Document

The main document contains fixed content. "Fixed" means that the information does

not change. Fixed content can consist of text you create from within the Mail Merge

application. Alternatively, you can pull text from an existing template (for example, one

in your Global or My Templatesfolder) or copy from a saved text document stored on

your local computer. The fixed content can even include a graphic, such your church

logo.

● Data source

Technically, the data source is your database, but it is your query results that will

transfer the actual data values to the placeholder (column) fields in the main document.

For this reason, you must make sure that the query contains the exact columns you need

to deliver to the merged document (for example, Members Title, Members Last Name,

and Members.Contact Info Home Address 1).

When you run Mail Merge, the process outputs a document for each record in the query using

the fixed text content and substituting the data values from the matching columns.

Using Query Results as a Mail Merge Data Source

This section shows you how to set up a mail merge process that uses a query as a data source

for Microsoft Word document. We will use a thank-you letter as our sample document to

help you understand the process.

Sample Document

Suppose that you want to create a thank-you letter to express your appreciation to givers

who gave a donation to your school building fund. An example of a thank-you letter you

might create is shown in the following illustration:

In the example letter in the illustration below, we inserted placeholder text (the text in

all caps and highlighted in yellow). We will use the placeholders as a guide for where we want to insert the merge fields (query columns) into the document.

90

Our sample letter contains both variable fields and fixed text. For illustration purposes only,

the six variable fields (for example, TOTAL AMOUNT OF PLEDGE) in the sample letter are

shown in all caps and highlighted in yellow. These are the placeholders (merge fields) that

indicate where unique information (for example, the actual title and last name of a giver)

will be inserted into the letter. The placeholders correspond to the columns that you must

have in your query. After the merge process is completed, information in the columns will

replace the placeholder fields in each recipient's copy of the letter. The unique information

in each letter comes from the columns in the query. The fixed text, on the other hand, is the

main content that remains the same for each person you send the letter to.

Overview of the Process of Creating a Link between a Query and a Word Document

The mail merge process entails these steps:

1. Set up and run a query.

The query is the data source. When setting up the query, you must select the columns and apply conditions in the query to generate the information you need to be merged into the document.

1. Open Mail Merge and create the main document.

The main document contains the fixed text and graphics that are the same for each version of the merged document—for example, the date on the letter and the words in

91

the closing ("Warm Regards"). In this step, you will open a blank document and enter your text.

1. Add the merge fields (query columns) into the main document.

The main document contains placeholder fields that vary for each version of the document—for example, the giver's street address. These fields correspond to columns in your query. In this step, you will add the columns. After you perform the mail merge, the columns you have in the document will be replaced with information from your data file.

1. Merge the query data into the main document.

Perform the mail merge. You can then preview each individual document before saving or printing the entire set.

Detailed instructions for each step follow.

Step 1. Set Up and Run the Query

To use a query as a data source, complete the following:


● Select an existing query from the Recent Queries list or My Queries list to use the data

source.

OR

● Build the query the way you typically do by choosing columns and conditions from

the Column Picker.

For the sample thank-you letter, these six columns must be included in the query:

● Members > Contact Info > Home Address 1

● Members > Contact Info > Home City

● Members > Contact Info > Home State

● Members > Contact Info > Home State

● Members > Title

● Members > Full Name

● Pledges > Total Pledge Amount

1. Select the Unique Records Only checkbox so that the query returns no duplicate

records.



1. If desired, click to save the query.

92

2. You are now ready to create the main document. Go to Step 2. Open Mail Merge and

Create the Main Document.

Step 2. Open Mail Merge and Create the Main Document

1. At the top of the Query Results panel, click . Then, select the Mail

Merge option from the dropdown menu.

The Mail Merge application opens to the Templates tab, as shown in the illustration

below:

1. In the left panel, select New Template to open a blank document page.


● Type the text of your document on this page.

● If you have a text document saved, open it. Then, copy and paste the text on to this

page

In the following example, we saved the text of our thank-you letter and then copied it

into the blank mail merge document:

1. You can now add the merge fields (query columns) into the main document. Go to

Step_3._Add_the_Merge_Fields__Query_Columns__to_the_Document.

93

Step 3. Add the Merge Fields (Query Columns) to the Document

1. Select the Mail Merge tab, located at the top of the document window.

2. To add the merge fields to your document, complete the following: 1. Select the first location where you want to insert a merge field (query

column).

When you select the location, the application highlights it for you. For example:

1. Click .

A list of columns generated by your query is displayed. For example:

1. Select the desired field from the list.

For example, for GIVER'S STREET ADDRESS select Members_Contact Info Home Address 1.

The placeholder text in the document is replaced with your column selection. For example:

1. Repeat Step 2a - 2c until all of the placeholder text is replaced by column

selections.

Using our example thank-you letter, the document looks like this:

94

1. Proof and then preview your document. If required, edit the content and/or

merged fields. When done, select the File tab and click to save the

document.

Step 4. Merge the Query Data into the Main Document

In this step, you will merge the actual data derived from your query into the

corresponding fields in your main document.

1. Select the Mail Merge tab, located at the top of the document window.

2. Click .

The merge fields in your document are replaced with data query. The first recipient's

copy is displayed, as shown in the following illustration:

95

1. If you have multiple recipients, click to view the copy for the next recipient.

2. Repeat Step 3 until you finish previewing each recipient's copy of the letter, making

sure that each one is correct.

3. You can now save the final merged document to your computer or you can print it. Do

one of the following:

● To save all copies of the merged document to a destination on your local computer,

click . Click to dismiss the Mail Merge complete message.

Navigate to the location on your hard drive, name the document, and then save it.

The merged document is saved in a single file that contains a page break between

each recipient's copy.

● To print the merged document, click . Click to dismiss the Mail

Merge complete message. In the Print window, select a printer. Click .

A single copy of the document is printed for each recipient.

96

How to Create a List of Email Recipients to Send Web-based Email

This topic shows you how to generate query results that consist of a list of email addresses.

You can then use the email address list as a data source to send a web-based email to each

recipient on the list.




1. The query must include a column with email addresses. Add an email address column

by using either of the following methods:

● In the Column Picker panel, click to open the Families or Members group. Next,

click to open the Contact Info group. Select the Email Address column. Then, click

to add the column to your query.

OR


menu, select Families > Contact Info > Email Address or Members > Contact Info >

Email Address.



1. Click . Then, select the Send Email option from the dropdown

menu.

The Email Addresses from Query Results window opens to show a list of email

addresses. Only unique email addresses are pulled from your query results.

1. To send email to the recipients whose email addresses are listed in the window,

complete the following:

1. Click .

The Send Web Based Email window opens. The system loads the recipients' email addresses into the To: field and puts your email address in the From: field as the sender.

You can use your keyboard's Delete key to delete any email address. You can also cut or

copy and paste an email address from one field into another field, such as the Cc: or Bcc: field.

97

Be aware that the email addresses of multiple recipients listed in the To: and CC: fields

can be seen by all other recipients. Typically, this is not desirable because it makes your

recipients' email addresses public. Consider using the Bcc: field instead to keep all of the email

addresses private. Use the cut and paste operation to move email addresses from the To: field

into the Bcc: field.

1. In the Password field, type the password you use to log in to your web-based

email application. If you want the system to remember your password, select the

Save Credentials checkbox.

2. Type the subject of the email in the Subject: field.

3. In the Message area, type the content of your email message.

4. Below the Message area, select one of these formats for the content of your

message:

● Design: Rich Text Format (RTF). This is the default message format for

Web-based email. RTF format supports formatting, including bullets and

alignment, various font styles and sizes, and background and text colors.

● HTML: an HTML version of the body content for recipients who prefer to

receive HTML mail.

1. To send your message, click .

The system sends the email message to your recipients.

How to Remove Duplicate Records from Your Query Results

This topic shows you how to add a "unique" rule to your query. This rule specifies that no

two records can have the same value. Use this procedure to remove duplicate records from

your query results.

To remove duplicate records from your results:




1. At the top of the Query Results panel, select the Unique Records Only checkbox.

This checkbox is located at the top of the Query Results panel, as shown in the

following illustration:

98



The Query Results panel displays unique records only.

How to Create a Member Workgroup

You can design and write queries that create member workgroups. The workgroups you

create are then immediately available in ConnectNow Family Directory where you can

perform workgroup-related tasks, such as creating labels and reports and sending email to

members in the workgroup. This topic shows you how to create a member workgroup query.

IMPORTANT

Unlike other types of queries, the workgroups you create are not dynamically

updated. Therefore, if you need to change the information for a query-created

workgroup, you must edit the query, run it again, and then save the newly created

workgroup with a different name. You can remove the obsolete workgroup from

your system using the delete feature on the Member Workgroups page.




1. A family workgroup query must include the Member DUID column. Add this column

using either of the following methods:

● In the Column Picker panel, click to open the Members group. Scroll down and

select the Member DUID column. Then, click to add the column to your query.

OR


menu, select Members > Member DUID.


99



1. You can now create a workgroup using the results of your query. To do this, complete

the following:

1. At the top of the Query Results panel, click .

2. From the menu, select the Create Member Workgroup option.

The Create Member Workgroup window opens.

1. (Required) Type a name for the workgroup and then select the name of the owner

organization.

For example:

Only those organizations tied to your login credentials are available for selection in the Owner

Organization list.

1. Click .

A message displays to inform you that the workgroup was successfully created.

1. Click to dismiss the message.

The member workgroup you created is now available in ConnectNow Family Directory

anywhere you use or see member workgroups.

Using the previous example, if we go to the Member Workgroups page in Family Directory, the Teachers workgroup we created is now in the Workgroups list:

100

In Family Directory, you can perform workgroup-related tasks with the new workgroup, such as sending email to its members. You can also create mailing lists and labels for the workgroup (by selecting options from the Quick Reports menu).

The workgroup is also available for use as a filter in the Member Workgroups filter list

(accessed by clicking on the Member List page). For example, the Teachers

workgroup we created now appears in the Member Work Groups filter list.

How to Switch to a Full-Screen View of Your Query Results

Sometimes you need a lot of space to view your results. ConnectNow IQ provides a control

that enables you to switch to a full-screen view of the results displayed in the Query Results

panel.

After you run a query, the results are displayed in the Query Results panel. If you find the

panel view too small or limiting, try replacing it with a full-screen display.

To view your results in full-screen, click . This button is located at the top of the Query

Results panel (see illustration below).

This button toggles the view between a full-screen display and a regular display. To switch

back to the regular display, click again.

Chapter 6 : Troubleshooting

Why Aren't I Getting the Expected Results from My Query?

101

What do you do when your query does not return the results you expected? If you are stuck, try working through the following steps to see if you can locate the source of the problem.

1. Did you get the right number of records?

Just above the Query Results panel, the application displays the number of records found. If the number is much larger and smaller than anticipated, that is one sign that you need to check your query.

1. Are there duplicates in your results?

Look through the results in the Query Results panel. If you find duplicates, select the Unique Records Only checkbox to ensure that only unique records are returned.

Re-run the query.

1. Did you select the right columns? Are you missing any columns?

Some of the columns have similar names. Make sure you selected all of the columns you need and that you selected the right ones.

Make changes, if necessary. Re-run the query.

1. Did you select the correct record filter?

Filters are used to restrict or limit the records the query retrieves. The records filter is located in the "Select records where" statement that appears at the top of the Query Conditions column. Clicking the all link in the "Select records where" statement displays this menu of filters:

Be sure that you selected the desired filter for your query:

● all (the default): displays all of the records (including any duplicates) that match all of

the specified query conditions. ● any: displays all records that match any—but not all—of the specified query conditions.

102

● none: displays records that do not meet any of the specified query conditions. ● not all: displays records that match some—but not all—of the specified query

conditions. 1. Did you apply the desired conditions? Are they specified correctly?

To check your conditions (for information on query conditions, go to Understanding Query Conditions), follow these steps:

1. Verify that the columns you want conditions applied to actually have conditions applied and they are correctly formulated.

2. Starting with the first column that has a condition applied, reapply the condition.

For example, suppose you want to view only those members whose last name starts with "s". Re-apply following condition applied to the Members Last Name column:

Members Last Name starts with s

1. Re-run the query, and then view the Query Results panel to double-check that

the condition actually works. Modify the condition, if necessary, and then re-run the query.

Using our example, the panel should show only members whose last name start with the letter "s".

1. Move on to the next column that has a condition applied and re-apply the

condition.

For example, suppose you also want to limit your results to members in the 21 to 30 age range. Verify that the expression for the query condition is set up correctly to apply to the Members Age column:

Members Age is between 21 and 30

1. Re-run the query, and then check the results to verify that the results match

the two conditions applied. Modify the conditions, if necessary, and then re-run the query.

Using our example, only those members ages 21 - 30 whose last name starts with "s" should appear in the Query Results panel.

1. Continue in this manner, re-applying the conditions one at a time and

re-running the query to verify your results. Modify the conditions, if necessary. Every time you make a change, re-run the query and check your results.

If you are still unable to get the expected results, you can contact ParishSOFT Customer Support for assistance. Go to How to Get Customer Assistance with a Query for details.

103



How to Get Customer Support Assistance with a Query

We want you to have the best user experience with ConnectNow IQ, and that means getting the

most out of application.

ParishSOFT Technical Support is here to provide help and support to answer questions, provide

instruction, or help you troubleshoot a challenge you are experiencing with a query. Please feel

free to contact us.

Got a Question?

Need Assistance with a Query?

Got a Question?

You can use any of the following methods to contact us:

Our Website:

Go to http://parishsoft.com/contactus/. Click to ask our support team a

question. Alternatively, complete and then submit the form on the Contact page. A member of the Customer Support team will follow up with you.

Telephone:

Give us a call: (734) 205-1000 (main) extension 2 (866) 930-4774 (support) extension 2 Customer Support Hours: Monday - Friday 9 am to 7 pm Eastern Time

Email:

Send an email to: [email protected]

Need Assistance with a Query?

So that we can best serve you and help you with a query, either call ParishSOFT Customer

Support or send an email (preferred) to ParishSOFT Customer Support.

104

http://cnhelp.parishsoft.com/cnhelp/iq/troubleshooting/how_to_get_customer_support_assistance_with_a_query.htm#Got_a_Question_

http://cnhelp.parishsoft.com/cnhelp/iq/troubleshooting/how_to_get_customer_support_assistance_with_a_query.htm#Need_Assistance_with_a_Query_

http://parishsoft.com/contactus/

We prefer that you contact us by email. That way, we can review the information you provide,

research the issue, and then contact you to provide assistance.

Have all of the following information ready or include it in your email:

● A brief description of the purpose of your query (what you are trying to find out) and the problem you are having (what you think you should or should not be seeing in your results).

● Model Information. Go to How to Find ConnectNow IQ Model Information for instructions on where this information is located.

● Do one of the following:

● Provide a copy of the SQL code for the query you need help with. This code can help

Customer Support troubleshoot your query. For instructions on how to obtain a copy the code, go to How to Copy a Query's SQL Code.

OR

● Make the query a public (global) query so that Customer Support can find it. In the

email, provide the name of the query and the Query DUID (the query's unique ID). For instructions on making a query public, go to How to Make a Query Public or Private. To find the query DUID, go to How to View the Properties of a Query.

How to Find ConnectNow IQ Model Information

If you have a problem with ConnectNow IQ and contact Technical Support, you may be asked to

supply the data model properties of the module. To locate that information, do the following:

Click to open the Model Properties window.

For example:

The data model properties (Model DUID, Name, and Description) shown in the window can help ParishSOFT Technical Support personnel troubleshoot a problem.

105

The data model defines the following:

● Tables that can be used in queries and the links between them.

● Fields shown to users and their presentation.

● Operators that can be used in query conditions.

● Columns available for each entity (in the Column Picker panel).

106

archdiocese of boston connectnow intelligent … › uploads › 3 › 9 › ...connectnow iq's...

Documents