In This Article:

    Advanced Query Wizard Overview

    In This Article:

      The Advanced Query Wizard enables you create custom reports based on data you enter into Salsa CRM. If the built-in reports are not sufficient for your needs, generate your own report. You can then use the report results for creating mailing labels, adding constituents to Groups or Smart Groups, and purging records.

      Users with Roles that contain data-related Query 'View' permissions have access to the Advanced Query Wizard. Permissions to Add, Edit, and Delete queries are also available.

      Every query is ad hoc, meaning you generate the results of the query at the time the query is run. Think of photographs being a representation of what you looked like at the time? The same is true of query results. If you want to see updated results, you have to run the query again.

      Queries can be saved and linked to Smart Groups. Constituents that meet that query criteria are automatically added to a Smart Group. Smart Groups are updated twice daily, and if they are synchronized with Salsa Engage, Salsa Engage is then updated twice daily.

      NOTE: Any user given Advanced Query Wizard access will also have access, through the Advanced Query Wizard, to all of the data in CRM. In other words, users with Advanced Query Wizard permissions have unrestricted access to all CRM data. The user can export all CRM data through a query, even if the user doesn't have permission to view information elsewhere in Salsa CRM due to their Role permissions.

      Advanced Query Wizard Navigation

      From the menu, Select Reports > Queries > Advanced Query Wizard. The Advanced Query Wizard window opens.

      The Advanced Query Wizard has four main screens:

      Step 1: Select Query Type

      Select a Query Type. 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 a 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, data audit trail, and more. Query Purpose options automatically populate your query with required fields.

          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. 

          Description - This frame appears for only the Create new query and Open an 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.
      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.
      5. Query Wizard Controls - These buttons or a subset of them appear on all of the screens 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. They include Activity Custom Fields pushed down from Salsa Engage. 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 want to be added to the Output Fields frame.
      • Filter - Select this radio button and double-click any field that you want to be added to the Filter Fields frame.
      • Sort - Select this radio button and double-click any field that you want to be 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 include in the final output. Notice that all of the records are checked (selected) by default to be included in the output. To exclude a record from the 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 topmost row that you need to and then (with the click button on our mouse still depressed) drag the cursor down to the bottommost 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 enable you to Include Highlighted Rows or Exclude Highlighted Rows. When rows are highlighted, you can use this feature to include or exclude them from the 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 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.
      • Row Count indicates the total number of rows in the query results.
      • Column Count indicates the total number of columns of data.


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

      • Include a record - All records 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.

      A Note About Duplicate Results

      Salsa CRM is a relational database where all the CRM data are stored in tables and linked to other tables by key data such as constituent IDs or donation IDs.  When you query a relational database, the query returns one row of data for all records that meet the filter criteria.  If the user selects filter criteria that can return multiple rows for each constituent, the database is obligated to return all possible combinations of data - otherwise, the results would not be complete.  So if the user selects, for instance, the Group name as an output field, and then filters on "Constituent is in Group A or Group B", if a constituent is in both of those groups, that constituent will have two rows in the output - one row displaying "Group A" in the Group Name column and one displaying "Group B".  This duplication is magnified if the user then selects other output fields that might have more than one value, such as address or email.  For instance, if the user adds "Email Address" to the above query, any constituent that has more than one email address will have two times the number of email addresses rows returned in the result set.  This is because the database is obligated to return one row for each group, times one row for each email address.  If the constituent has two email addresses, the output must include four rows:

      Group A Email Address 1
      Group B Email Address 1
      Group A Email Address 2
      Group B Email Address 2 

      If the user then adds more columns that may have multiple values, such as phone numbers, addresses, notes, etc, then the above output will also be multiplied by however many duplicate phones, addresses, and notes are stored for that constituent.  Again, the database is obligated to return all these rows because it can't possibly guess which possible combination of values the user wanted to see.

      We have the concept of primary emails, phones and addresses for precisely this case.  In the example above, the query will still return 2 rows because the user included the Group name in the output fields, but if they filtered on only primary email, the other two "duplicate" rows will be eliminated.  If the user did not really need to see the group name in the above output, they can remove the Group Name from the output fields, and the query will return just one row for this constituent.

      Furthermore, combinations of aggregate fields and fields that have more than one value can produce inflated aggregate results in your queries.  For instance, if you have a query that contains the Total Gift Amounts field and you filter on constituents in a certain series of Groups, you will find that your Total Gift Amounts field is multiplied by the number of Groups in which the constituent was a member.

      With this understanding, when defining a query, the user must take care to include only the output fields that are absolutely necessary.  Salsa CRM allows users to filter and sort on fields even if those fields are not included in the output columns.  Eliminating unnecessary fields from the Output Fields panel may eliminate "duplicate" rows.  

      Another way to reduce or eliminate "duplicate" rows is to use the Aggregate List function under the "gear" icon.  For instance, if the user really needs to see all email addresses for each constituent, but they only want to see one row per constituent, they can use the Aggregate List function, grouped by Constituent Number, to return all the emails in a single, comma-delimited cell.

      Step 4: Post-Query Options

      This screen gives you all of the options for 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

        NOTE: Global delete will not work if...
        • donation records to be deleted originated in Salsa Engage
        • constituent records to be deleted have donations tied to Engage.

      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



      Article is closed for comments.