In This Article:

    Advanced Query Wizard Overview

    In This Article:

      The Advanced Query Wizard enables you to find data in your Salsa CRM. You can then use the data for many purposes, such as creating mailing labels, creating Smart Groups, assigning constituents to a group, and purging records.

      The queries that you create here can be used in other parts of CRM. However, queries will contain only the data they acquired the first time they were run. If you want new data, you will need to rerun the query. If you want a query to be updated with new data continually, you can create a Smart Group in the Post-Query Options step. Smart-Groups are updated twice daily, and if they are synchronized with Salsa Engage, Salsa Engage is updated twice daily.

      The Advanced Query Wizard has four main screens:

      • Step 1: Select Query Type - Make basic decisions about your query, such as query type and the purpose of the query.
      • Step 2: Select Query Criteria - Choose data (fields) to include in your query and add filters, merge options, calculations, and sort rules.
      • Step 3: Preview, Exclude, and Modify Data - Choose what data you want to save and/or use for other Salsa CRM functions, such a mail merge, label printing, and Salsa Engage updating.
      • Post-Query Options - Choose how to save and use the results of your query.

      Getting Started

      Select Reports from the top tool-bar > Queries > Advanced Query Wizard. The Advanced Query Wizard window opens.


      Step 1: Select Query Type

      1. Select a Query option (Create new query, Open existing query, etc.).
      2. For a new query, select a Query Type. Alternately, choose an already existing query from a table in one of the other screens.
      3. Click Next.

      The red numbers in the following screenshot are documented, by number, below the screenshot.

      #1 - Query Options - Options include Create new query, Open existing query definition, view results of previous query, and Open another data source.

      • Create new query allows you to define the type of query you need to run based on the information you need to report. This window enables you to select a Query Type and the query's purpose. Query Types include constituent, donation, volunteer and more. Query Purpose options automatically populate your query with required fields.

      #2 - Query Type - This frame appears for only the Create new query option. These are all of the queries types that are available in Salsa CRM.

      #3 - Description - This frame appears for only the Create new query and Open and existing query options. It contains a brief description of the selected query type.

      #4 - Purpose of the Query - This frame appears for only the Create new query option. The query purpose determines the fields that are retrieved, by default, with the data set. For example, if you search Salsa CRM using Mail Merge or Making Labels, the fields included by default will not include Constituent UUID and email, so you would not be able to push query results up to Salsa Engage. Consequently, if you know that you are going to want to push query results up to Salsa Engage, you must choose the "Salsa Engage" purpose on Step 1 of the Advanced Query Wizard.

      It features the following options:

          • General - There are no required fields for this option.
          • Mail Merge or Mailing Labels - Includes fields required to merge mail or create mailing labels.
          • Assign Constituents to Group/Smart Group - A group is a static collection of constituents that meet certain criteria; a smart group is a collection of constituents--that is updated twice daily--that meet certain criteria. When new constituents meet the criteria of a smart group, they are added to the smart group automatically when Salsa CRM updates it. Available groups will be pulled from groups that you have already created in Manage > Configuration > Groups. (Click here for information about using the Advanced Query Wizard to create groups from queries.) If your smart group is synchronized with Salsa Engage, Salsa Engage will be updated every time the smart group is updated.
          • Salsa Engage - Automatically includes all of the fields required to send data to Salsa Engage.
          • Purge Records from the Database - Includes fields required to delete records.

      • Open existing query definition opens a table of queries that have already been created and saved. (For information about saving queries, see Post-Query Options below.) Choose a query, tweak it if necessary, and run it again--with the current version of your database. Click a query and then click Next to edit and then run it.

        Alternately, if you have a query in XML format that you saved locally with CRM, click Open Local Query Definition to find and open the file. Then, click Next. The query parameters will be defined by the XML file. 

      #3 - Description - This frame appears for only the Create new query and Open and existing query definition options. It contains a brief description of the selected query.

      • View the results of a previous query allows you to view the results of a previously saved query. This option does not re-run the query, therefore any changes that have been made to your database, such as new donations, updated address, etc. will not be reflected in the results. If you wish to view updated data, select Open existing query definition and run the query again.

      • Open another data source offers three options for running queries:
        • Pre-populated - Available only when Advanced Queries is accessed from another screen, such as the Donor Detail, SYBUNT or LYBUNT report screens.
        • Open existing comma-delimited file - Allows you to open any .csv file in Salsa CRM.  This allows you to use the features on the last Advanced Query Wizard screen (such as Label Printing) without importing the information into Salsa CRM. The field names must be the same as the Salsa CRM field names.  Please refer to the File, Data Sync, Import screen for the list of field names.
        • Open pre-defined script - Allows you to run a script that was created for you by the Salsa CRM Customer Care team.  This allows you to run custom queries that may not be possible or practical to run using other Advanced Query options.  Please contact the Salsa CRM Customer Care team for more details on this feature.

      #5 - Query Wizard Controls - These buttons or a subset of them appear on all of the screen of the Advanced Query Wizard. They enable you to execute the options you have selected, go back to a previous step, restart the wizard, and close the wizard.

      Caution: If you click Restart Wizard or Finish before saving your query, your query and its results will be lost.


      Step 2: Select Query Criteria for a New Query

      This is where you'll define the parameters for your query.

      1. Ensure that the Output Fields, Calculations, Filters, Sort Fields, and Merge Options that you want are defined.
      2. Click Next.

      The red numbers in the following screenshot are documented, by number, below the screenshot.

      #1 - Gear Icon (Available Fields) - This icon opens a menu that gives you the following options:

      • Output as... - Enter an alternate output field column name for the selected field.
      • Aggregate - This functionality is for advanced users only. Apply an aggregate value to limit the selected field. Aggregation allows you to determine the Minimum, Maximum, Average, Sum, or Count of any numeric field. It will also allow you to List items in a single column such as listing multiple groups a constituent belongs to or multiple donations a constituent has made in a single field instead of listing the same constituent many times if they belong to many groups or have made multiple donations.
      • Filter - Add a filter comprising the selected field. The filter you create will appear in the Filter Fields frame (#10).
      • Sort - Add sort parameters based on the selected field. The wort option you choose will appear in the Sort Fields frame (#11).

      #2 - Available Fields - These are the fields that you can include in your Output Fields. Depending on the Purpose of the Query that you chose in Step 1, Output Fields may be pre-populated with fields that are required for the given query. Required fields are indicated by a red outline.

      #3 - Output, Filter, and Sort Options - These radio buttons enable you to place a field from Available Fields into the Output fields frame:

      • Output - Select this radio button and double-click any field that you wanted added to the Output Fields frame.
      • Filter - Select this radio button and double-click any field that you wanted added to the Filter Fields frame.
      • Sort - Select this radio button and double-click any field that you wanted added to the Sort Fields frame.

      #4 - Gear Icon (Output Fields) - This icon opens a menu that gives you the following options:

      • Filter - Add a filter comprising the selected field. The filter you create will appear in the Filter Fields frame (#10).
      • Sort - Add sort parameters based on the selected field. The wort option you choose will appear in the Sort Fields frame (#11).

      #5 - Output Fields - This is where the fields you selected from Available Fields (#2) will appear. These are the fields that will be included in your Query. Fields that are required for the type of query you selected are outlined in red.

      #6 - Editing Controls - These controls appear in the Output Fields, Filter Fields, and Sort Fields frames. They enable you to move the selected element (output field, filter, or sort parameter) up or down relative to other elements. The red X deletes the selected element from the given list.

      #7 - Merge Spouses - Check this box to return one combined record for spouses. Note: If you select this option and spouses are not being combined into a single record, ensure that address information in the CRM is identical for both souses. 

      #8 - Merge Exact Duplicate Results - Select the Merge Duplicates checkbox located below the Output Fields column if you want only 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.

      #9 - Add Calculations - This functionality is for only advanced users. For details, see Advanced Query Browser: Query Calculations.

      #10 Filter Fields Controls - These controls, beneath the Filter Fields text box, enable you to edit, change, or and group filters.

      • Click the Edit button to open the Filter Criteria window to change the Operator and/or Value of the selected filter.
      • When you have more than one filter, click the And or Or button to change the conjunction for the selected filter to And or Or.
      • Use the parenthesis buttons () to group filters. For example, if you wanted to filter on Head of Household and [zip code] or [city code], your filters would look like this:

      #11 Sort Field Controls - If you want to change the order of your Sort Fields, use the Ascending or Descending button. If you have two sort fields results will be sorted first by the top field and then by the next field down the list. For example, if you had Head of Household at the top of the list and Zip Code as the second item in the list, results would be sorted first on Head of Household value and then by Zip Code.

      Note: You can save your query by using Save... on the Post-Query Options screen.


      Step 3: Preview, Exclude, and Modify Data

      This is where you select the data that you will to include in the final output. Notice that all of the records are checked (selected) by default to be included in output. To exclude a record from output, un-check it. Once you've made any desired changes, click Next at the bottom of the screen to open the Post Query Options screen.

      The red numbers in the following screenshot are documented, by number, below the screenshot.

      #1 - Gear Icon (Step 3) - Hover over the icon to open a drop-down menu featuring the options described below. To act on a specific record in the table, click the record to highlight it. The record you click will be highlighted in yellow. Similarly, you can highlight a column by clicking it. You can also highlight a group of rows by clicking top most row that you need to and then (with the click button on our mouse still depressed) drag the cursor down to the the bottom most row that you want to highlight. All of the rows in the range you specified will be highlighted. In addition, you can pick and choose the rows you would like to highlight by holding down the Ctrl key and selecting individual rows with your mouse.

      • The first two menu options allows you to Include Highlighted Rows or Exclude Highlighted Rows. When rows are highlighted, you can use this feature to include or exclude them from output.
      • The next two options allow you to Insert Prefix and/or Insert Suffix to a specific column.
      • The Find And Replace option enables you to find AND replace information just like you would using the Find and Replace tool in a Word document. Be sure to highlight the column that contains the information that you're looking for. For example, if you are looking for a specific last name, right-click in the Last or Org Name column, select Find, enter the search phrase and replace phrase, and then click OK. The phrase you searched for will be replaced with the replacement phrase that you entered. Caution: If you leave the replace field blank, the phrase you enter you are searching on will be replaced with a blank.
      • Reformat Data requires that you highlight a column heading then click the Reformat Data button. This enables you to change the formatting of dates or change the content's format (from, for example, date to text).  
      • View as a spreadsheet exports the data to an XML file that is downloaded to the default location defined in your browser settings.
      • Insert Column allows a column to be inserted before or after an existing column. Select the column heading, then click Insert Column. A pop-up will display. Name the new column, select if the new column should be placed before or after the selected column, and then click OK.
      • Include All Rows includes (checks) all rows in the full data set, including those that you may have de-selected.

       

      #2 - Records - These are all of the records that have been returned by your query.

      • Include a record - All records are are checked (included) by default.
      • Exclude a record - To exclude a record un-check it.
      • See query details for a specific record - Click the magnifying glass icon for the record.
      • Edit the data that will be output for a record- Double-click any value and manually enter that data that you want to see in the final output.

      Post-Query Options

      This screen gives you all of the working with query results from Salsa CRM. When you choose an option, the screen for that option will open.

      Buttons along the left side of this window are as follows:

      • Save...
        • Save Locally allows you to save the query results to a file that can be opened with your spreadsheet software or with a browser
        • Save on Server allows you to save the query results to the database so that you can view the results in the future
        • Save Query Criteria allows you to save the query criteria to the database so that you can re-run this query in the future
        • Save Query Criteria Locally allows you to save the query criteria to your hard drive or other network location so that you may share it with other Salsa CRM users
      • Global Update...
        • Update Individual Type allows all selected constituents to be updated with the selected Individual Type
        • Update Organization Type allows all selected constituents to be updated with the selected Organization Type
        • Assign to Group allows you to assign all selected constituents to a group
        • Link Query to Smart Group allows you to assign all selected constituents to a Smart group
        • Remove from Group allows you to remove all selected constituents from a group
        • Global Status Update allows you to change the event status of all selected guests
        • Update Custom Field allows you to globally set the value of a custom field
      • Salsa Engage allows you to upload information directly to your Salsa Engage database
      • Print Labels allows you to print the labels associated with the query results
      • Cultivate allows you to add a number of constituents to a Cultivation Process at once
      • Mail Merge opens the Word Processor, supplying the results of this query as the data source
      • National Change of Address allows you to select or de-select addresses for the next NCOA export
      • Create Note for Constituents displays the standard Notes popup window so that you can add a note to all selected constituents
      • Process Constituents allows you to sequence through all selected constituents. Pressing the "Shift" button while selecting the "Save and Next" or "Skip to Next" buttons on the Constituent screen will allow you to sequence backward through the file.
      • Set Correspondence Reason allows you to set the chosen correspondence reason to all selected addresses
      • Assign Donation Letter allows you to assign a letter to all selected donations
      • Move Donations allows you to re-assign the fund, campaign, appeal, donation source or batch on the donations that were returned in this query
      • Global Delete allows you to choose between deleting selected donations, forms, Emails, or constituents

      Once you are done with the option, you can return to this screen and select a different option. When you are finished, click Finish.

      Note: If your query was complex and you may want to re-run it at a later time, be sure to save it using Save... > Save Query Criteria. You can save to the server, in which case your query will be available for all CRM users, or save Locally on your own computer. If you save locally, the query will be converted to XML file format and saved to only your machine; Salsa CRM will not retain it in any way, and it will not be accessible by CRM users on other computers (unless you provide them with the file). The file will be saved to the default location you have defined in your browser. To run a locally saved query select Open existing query definition > Open Local Query Definition. See Step 1 above for additional information.

      Caution: Be very careful when using the Global Delete option. Before you delete anything, ensure that you have a current backup of your database.

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

      Comments

      0 comments

      Article is closed for comments.