In This Article:

    Creating a New Query with a General Purpose

    In This Article:

      1. From the main menu, choose Reports, Queries, and then Advanced Query Wizard.          

      2. The Query Wizard launches.

      3. Select the Create new query option at the top of the Query Wizard window. Note: DonorPro automatically pre-selects the query type from the last query you ran.                 

      4. From the Query Type panel along the left side of the window, choose a category of data that you wish to query. Select a query type. An explanation of the highlighted item will appear in the Description section of the panel. NOTE: The Constituent type query contains a Donation Info node with basic donation information. This allows you to identify constituents who have not donated, without running multiple queries. The Conversion type query allows customers who were converted by TowerCare to view constituent and donation IDs from their previous database.                                      

      5. In the Purpose of this Query section, choose General.  

      6. To add a field to the query, select the next to the appropriate table name in the Available Fields list to expand the table and show all of the available fields.

      7. Double click on the desired field to add it to the query. The field name will then be listed in the Output Fields column. Note: Constituent queries include the ability to add Smart fields to the Output Fields. Smart Addressee, Formal Salutation and Informal Salutation are shown in the image to the right. Smart Address is located under the Address node of the Constituent query. Filtering is not supported on these Smart fields, as they are formatted after the query has run. These fields are convenient when you wish to send the output of a query directly to a commercial mailing vendor. Please refer to the end of this section for Available Field definitions in a Donation Query.                                               

      8. To sort by a particular field, right click on that field in the Available Fields list and choose Sort. That field is added to the Sort Fields section in the lower right corner of the screen.                            

      NOTE: The fields should be listed in the Sort Fields section in order of sort priority. If sorting names in alphabetical order for example, Last Name should be listed before First Name.

      1. To change the order the fields are sorted by, in the Sort Fields section, select the desired field.    

      2. Select the Move Up or Move Down buttons on the right side of the panel to move the field to the desired position in the list.

      3. By default fields are sorted in Ascending order. To change the order to Descending, in the Sort Fields section, select the desired field, and then choose the Descending button.

      4. To remove a field from the sort order, in the Sort Fields section, select the field you want to remove.

      5. Select the Delete button to the right of the Sort Fields section.

      6. To apply a filter to a particular field, right click on that field in the Available Fields list and choose Filter.                                                                                                                    

      7. The Filter Criteria dialog box opens. Select the drop down arrow and choose the desired Operator.

      8. Type the desired Value.

        NOTE: If the field that is being filtered is a date then type the appropriate date or select the Calendar button, select the date, and then select OK.                                                                              

      17. Select OK from the Add Filter Criteria dialog box. The field is listed in the Filter Fields section in the top right corner of the wizard screen.

      18. Add any additional filters following steps 14-17.

      NOTE: When filtering on Fund, Campaign, Appeal or Group you will need to filter using the Fund ID, Campaign ID, Appeal ID and Group ID, using these fields allows you to choose which Fund, etc you would like to filter on. The Fund Name, etc. requires that you manually type in the name on which you wish to filter. Be sure to select Fund Name, Campaign Name, etc as Output Fields. The returned information displays the ID as a numeric value and the name as the actual name of the Fund, Campaign, etc.

      1. Multiple filters are, by default, connected by the AND operator. For example, Donor Address
        Primary? Equals “Yes”
        AND Donation Received Date is between “01/01/2006”, “08/13/2008”. This means that the data returned must meet both of the parameters. To change the filter so that the data returned matches either filter “A” OR filter “B”, select the appropriate filter item in the Filter Fields list and choose the Or button.                                                                   

        NOTE: A good way to tell if the filter is going to return the data you expect is to read the filter statement to yourself to confirm that it makes sense before continuing.

      2. To change the order of the filter items in the Filter Fields list, select the field you want to move to select it.

      3. Select the Move Up or Move Down buttons on the right side of the panel to move the field to the desired position in the list.

      4. To remove a field from the Filter Fields list, select the field you want to remove. Select the Delete button to the right of the Filter Fields section.

      5. You may also choose to perform calculations within your query. The Add Calculation button in the Advanced Query Wizard button is located directly below the Output Fields panel.  

      6. The Calculation button offers various calculating possibilities. Select the Add Calculation button and the Add Calculated Column popup window appears.                                               

      7. Enter a name in the Assign Column Name box to identify the calculated column.

      8. Select the Build button to create the calculation. The Build Calculation window is displayed.  

      9. By selecting the Insert Column button the Select Column window appears. Highlight the field you wish to use in your calculation and select OK.                                                           

      10. Then select an operation from the Build Calculation window. Continue in this manner until your calculation is complete.                                                                         

      11. Add Parentheses to the calculation as needed. Parentheses are needed when mixing addition or subtraction operations with multiplication and division operations.                                 

      For example: (2+2) x 15 = 60 whereas 2+2 x 15 = 32

      1. Once you have completed the calculation, select OK. Finish creating your query in the usual manner. The calculation will be displayed in your query results as a column with the name you assigned. 

      2. If you choose to add the Spouse name to the output for a mail merge (the spouse name fields are available through the Spouse table in the Available Fields list), select the Merge Spouses checkbox.

      3. Select the Merge Duplicates checkbox located below the Output Fields column if you only want one record returned for each constituent. This is important when doing a mail merge so that you don’t print multiple letters or labels for the same household or business.                                                

      4. Once the fields, sort order, and filters have been properly set, choose the Next button. The data is returned.                                                                  

      NOTE: Select the Back button to go back one step in the Wizard if you make a mistake.
      TIP: Right-clicking in the results of Step 3 of the Advanced Query Wizard (or any similar looking screen) provides you with additional functionality.                                         

      The dropdown box is divided into four segments. The top segment allows you to include or exclude highlighted rows. A group of rows can be highlighted by holding down your shift key and then selecting them with your mouse. Select the top most row you need to and then the bottom most row. This will select a group of rows that are listed near each other. You can also ‘pick and choose’ the rows you would like to highlight by holding down your control key and selecting the rows with your mouse. When rows are highlighted, you can then use this dropdown feature to include or exclude them from the results. Error rows are highlighted in pink and can be excluded from the results or, if you only want the error rows, select Include Only Error Rows.               

      The second segment allows you to locate specific information just as you would using the Find option in a Word document. Be sure to right-click in the column pertaining to the information for which you are looking. For example, if you are looking for a specific last name, right-click in the Last or Org Name column, select Find and then type the last name into the window that appears.

      The third segment lists the number of rows returned in the query (Row Count), how many rows have been selected (if you have excluded any rows, this number will be lower than the row count) and the column count. Right-clicking in a numerical column will enable you to ‘Sum’ that column. Selecting ‘Go to Row’ will give you the chance to enter the number of a row to which you would like to ‘jump’.

      The fourth segment gives you various options for working with the results. You can print the data, copy the data to your clipboard, save the table, view the data in your browser or even view the data as a spreadsheet, this option will open the data in the spreadsheet software you have on your system.

      34. Select the Next button.

      NOTE: If any items returned are highlighted in pink it indicates that they do not have complete address information in the database for the type of query you selected. If desired you can deselect the checkbox next to each of these records to remove them from your finished product or select the Back button to go back a step in the Wizard and edit the filters to exclude those records with incomplete addresses.

      35. Choose the desired output.

      Save will open a window giving you four options for saving your query/query results:

      Save Locally allows you to save the query results to a file that can be opened with your spreadsheet software or with a browser. For this choice give the file a name and location in which you want it saved, and then select the Save button. Saving the query results in this manner is similar to a “snapshot” of the results. You will only be able to view these exact results at a later time; they will not be updated or refreshed.

      Save on Server allows you to save the query results to the database so that you can view the results in the future. For this choice give the results of the query a name and then select the Save button. Saving the query results in this manner is similar to a “snapshot” of the results. You will only be able to view these exact results at a later time; they will not be updated or refreshed.

      Save Query Criteria allows you to save the query criteria to the database so that you can re-run this query in the future. For this choice give the query criteria a description and then select the Save button. Saving the query criteria in this manner allows other users access to this query as well.                                    

      Save Query Criteria Locally allows you to save the query criteria to your hard drive or other network location. Saving the query in this manner means that only you will have access to this file. This file will have an .xml file type and can be emailed to other DonorPro users – even those in different chapters or organizations. Those users may then import your definition by opening the Advanced Query Wizard; selecting the Open exisitng query definition button and then using the Open Local Query Definition button on that screen to open the definition file.

      Note: If you have included Custom Fields in your query definition, it is unlikely that a user outside of your organization will be able to use your definition.

      Global Update (when you select the Global Update option, three choices are presented):

      Update Individual Type allows you to update all selected constituents' Individual Type field.

      Update Organization Type allows you to update all selected constituents' Organization Type field.

      Assign to Group allows to add all selected constituents to a group.
      Remove from Group allows you to remove all selected constituents from a group.

      TIP: When globally assigning, or removing, constituents to or from a group, you may select the checkbox to include spouses in the group update. In order to include spouses in this global update you will need to include Spouse Constituent Number in the query output.

      Copy to Clipboard allows you to paste the query table into other programs, such as your spreadsheet software or word processor.

      Print Labels allows you to print the labels associated with the query results. The Label Printing screen will be displayed. See the Word Processing section of this manual for detailed instructions on printing labels.

      Cultivate allows you to add a number of constituents to a Cultivation Process at once. When creating a query with this purpose you must include the Constituent Number in the output of the query. If you did not include the Constituent Number column in your query, you will be prompted to add this column. The window that appears is the exact same Constituent Phase popup on the other Cultivation screens. The only difference is that a Constituent is not required - the button and search field are disabled, and the search field displays “(Multiple Constituents)".

      Mail Merge allows you to open the query results in the word processor, enabling you to quickly create a mail merge to be used for standard mailings or emails. NOTE: The Word Processing option you selected under Tools > Options > Word Processing tab will determine if DonorPro’s built-in word processor or OpenOffice.org’s word processor will open when you select to run a mail merge from this report.

      Process Constituents allows you to sequence through all of the selected constituents. When using this feature, holding the Shift key while pressing the Save and Next or Skip to Next buttons on the constituent screen will bring you to the previous record. Constituent ID must be selected as an output field. If the appropriate fields have not been selected, select the OK button to close the Missing ID dialog box, choose the Back button, and then add the appropriate fields to the query.

      Create Note for Constituents displays the standard Notes popup window so that you can add a note to all of the selected constituents.

      Set Correspondence Reason allows you to set the chosen correspondence reason to all selected addresses. NOTE: The Reason field, under the address section on the constituent’s main information page, is the Correspondence Reason and allows you to set the reason this address is part of the constituent’s file. A Reason could be set for All Correspondence to be sent to this address, Newsletter only, Solicitations only, etc. This is one of the configurable drop down fields on the constituent screen. To customize to your needs select Manage, Configuration and then Constituent node under the Configuration Explorer panel on the left. Choose to add, edit or delete items that appear in the Correspondence Reasons box.

      The Address ID field must be one of your output fields if you are going to select this output option. If the appropriate fields have not been selected, select the OK button to close the Missing ID dialog box, choose the Back button, and then add the appropriate fields to the query. For this choice select the drop down arrow, choose the desired correspondence reason, and then select the OK button.

      Assign Donation Letter allows you to assign the same letter en-masse to a large number of donations. This feature would typically be used after importing a number of donations from another source, such as the Convio datasync.

      Move Donations allows you to re-assign the campaign, appeal, fund and batch on the donations that were returned in this query. NOTE: When re-assigning donations you will need to include the Donation ID in the query output. Also, when you want to re-assign batches, the Move to Batch field works in the same manner as the constituent search window, type the name of the batch or, with your cursor in the Move to Batch field, select the Enter key to open the Batch Search window.

      Global Delete allows you to choose between deleting selected constituents, donations, or both. CAUTION: Be very careful about using this option. Always have a backup of your database. A warning will appear. Choose to Delete Donations Only or Delete Constituents and Donations and then select OK.                 

      1. The Donation ID must be one of the output fields in order to delete donations. The Constituent ID must be one of the output fields in order to delete constituents. If the appropriate fields have not been selected, select the OK button to close the Missing ID dialog box, choose the Back button, and then add the appropriate fields to the query.                                                              

      2. Select the Finish button.

      Was this article helpful?
      0 out of 0 found this helpful
      Have more questions? Submit a request

      Comments

      0 comments

      Please sign in to leave a comment.