In This Article:

    Advanced Query Wizard Overview

    In This Article:

      What Is a Query?

      In a relational database model, all data fields are stored in specialized tables. Getting a report on that information requires querying the database using specific rules of behavior, typically using a computer language called SQL.

      The Advanced Query Wizard enables you to generate custom source data to generate your own reports without knowing how to query the database directly. You can choose which data points you wish to see, how you wish to include or exclude information for your report, in which order you wish to view your data results, and even what to do with this information once you get your results. You can then use the data results in a wide variety of activities, such as creating mailing labels, adding constituents to Groups or Smart Groups, and purging records.

      Every query is ad hoc, meaning the results of the query are unique to the time the query is run. As your senior photo represents what you looked like at the time the picture was taken, the same is true of query results. If you want to see updated results, you have to run the query again.

      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. NOTE: Users with Advanced Query Wizard permissions have access to all CRM data. Any user who has Advanced Query Wizard access will also have access, through the Advanced Query Wizard, to all of the data in CRM. 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 Workflow

      AQW_Fundraising_Big_Picture_Prototype.png

      When you need an Advanced Query, it's important to consider these five things:

      1. Could your needs be met with an existing Gift report? Can you get this information from the SYBUNT or Donor Detail Report? Could you use the Donor Detail Report results in an Advanced Query to further filter the report?
      2. What information to you want to see in the report? There are hundreds of available fields; which ones do you want to see? These will be your Output Fields.
      3. Whom do you wish to include and exclude from the report? Knowing this in detail will help with building your Filter Fields.
      4. In what order to you wish to see it? Do you need it sorted by donor last name or by zip code for the mailing house? Knowing this will help determine your Sort Fields.
      5. What is your ultimate purpose for the report? What are you doing with the information? Start with the right Query Purpose and Query Type to end up where you want.

      Accessing The Advanced Query Wizard

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

      AQW_Tabs.png

      The main screen displays three tabs relating to already-generated queries and query results:

      • Saved Queries stored by CRM users.
      • Built-In Queries created by Salsa subject matter experts.
      • Saved Query Results contain source data you saved after previously running query and saving the results. This data table represents a snapshot of the way your data looked at the time the query was run.

      In the top right corner, click the Import button to import external data or saved query criteria to work with the Query Wizard. Click the Create New Query button to begin a new Advanced Query from scratch. There are four steps in the process to create a new Advanced Query.

      Step 1: Select Query Type

      Define the type of query you need based on the information you need to report. Select a Query Type and Query Purpose.

      New_Query_Step_1.png

      Selecting the right Query Type will ensure that the Available Fields can choose from in Step 2 will include the data points you want to see. Below, you can find all possible query types and the reasons for selecting that particular query type. The majority of the time, you would want to select Constituent or Donation for your query type:

      • Constituent queries contain all Constituent fields and the majority (but not all) Donation fields for your use.
      • Donation query types contain all Donation fields and the majority (but not all) Constituent fields for your use.
      Query Type Description

      Appeal

      For queries on appeals and related donations.

      Audit Trail

      For queries on user activity, such as logins and updates or deletions of key database records.

      Campaign

      For queries on campaigns and related donations.

      Card Authorization

      For queries on credit card merchant account activity, including successful and failed authorization attempts.

      Certifications and Licenses

      For queries on employee Certificates and Licenses (HR Module only).

      Constituent

      For queries strictly on constituents and their addresses, relations, groups, etc. A limited amount of donation data is available in this query type.

      Conversion

      This table contains conversion information, including the pre-conversion IDs for constituents and donations. This query will not function if no conversion was performed on your data.

      Donation

      For queries on donations, including donors, honor/memorial constituents, gift amounts, first/last/largest gifts, etc. A limited amount of constituent information is available in this query type.

      Donation Posting History

      For queries on donations, including donors, honor/memorial constituents, gift amounts, first/last/largest gifts, etc.

      Donation Receipts

      This query provides a means for organizations that require contiguous, sequential receipt numbers to ensure there are no gaps in the sequence. If there are gaps, they are likely due to reversals, and will be documented as such in this query.

      Emergency Contacts

      For queries on employee Emergency Contact information (HR Module only).

      Employee

      For queries on Employee data (HR Module only).

      Equipment

      For queries on Equipment. (HR Module only).

      Event Management

      For queries on Events, event members, sponsors, fees, and expenses.

      Forms

      Information on Constituent Forms.

      Fund

      For queries on funds and related donations.

      HR Classification

      For queries on HR job classifications (HR Module only).

      HR Position

      For queries on HR job positions (HR Module only).

      HR Posting

      For queries on HR job postings (HR Module only).

      Inventory Assets

      Information on Assets Inventory (Inventory Module only).

      Inventory Equipment

      Information on Equipment Inventory (Inventory Module only).

      Inventory Incentives

      Information on Incentives Inventory (Inventory Module only).

      Inventory Library

      Information on Library Inventory (Inventory Module only).

      Inventory Meds

      Information on Meds Inventory (Inventory Module only).

      Matching Donation Policy

      For queries on Matching Donation Policies. These policies are set up for any constituent that matches donations.

      Patient Services

      For queries on Patient Services data (Patient Services Module only).

      Recruitment

      For queries on HR Recruitment Activity (HR Module only).

      Reversed Donation

      For queries on reversed donations.

      Schedule Event

      For queries related to scheduling events.

      Schedule Shift

      For queries related to scheduling shifts.

      Services

      Information on Service Requests and their associated logs.

      Skills and Languages

      For queries on employee skills and languages (HR Module only).

      System Users

      For queries on users that have been assigned login IDs to the system, and the roles that have been assigned to them.

      Training

      For queries on employee training (HR Module only).

      Volunteer

      For queries on HR volunteer management (HR Module only).


      Query Purpose 
      defines what you're going to do with your information and automatically populates your query with required output fields. It features the following options.

      Query Purpose Description

      General

      There are no required fields for this option. This allows you to create a brand new query from scratch with whatever settings you need.

      Mail Merge or Mailing Labels

      Output fields required to create a mail-merge query or to create mailing labels.

      Assign Constituents to Group/Smart Group

      A Group is a static collection of constituents that are manually assigned. A Smart Group is a collection of constituents-updated twice daily-that meet certain criteria. Queries can be saved and linked to Smart Groups so that constituents who meet that query criteria are automatically added or updated twice daily. If Groups are synchronized with Salsa Engage, Salsa Engage is then updated twice daily. When new constituents meet the criteria of a smart group, they are added to the smart group automatically when Salsa CRM updates the Group. Manage your groups in Manage > Configuration > Groups. Click here for information about using the Advanced Query Wizard to create groups from queries.

      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.


      Step 2: Select Query Criteria

      This is where you'll define the parameters for your query. The green numbers in the following screenshot are documented, by number, below the screenshot.

      New_Query_Step_2.png

      1. Available Fields—These are all the fields that are available for the Query Type you selected in Step #1. This data field listing is sorted by the table that stores that data element, because there are literally hundreds of fields. They include Custom Fields pushed down from Salsa Engage.
      2. Gear Icon (Available Fields)—This icon opens a menu that allows the following actions on your Available Fields:
        • 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. Aggregat ion 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).
      3. Output, Filter, and Sort Options—These radio buttons enable you to place a field from Available Fields into the appropriate frame when double-clicking on the Available Field:
        • 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: Save your query on the Post-Query Options screen in Step 4.

      Filter Operator Definitions

      Filters focus your report results on a particular subset of your data by clarifying exactly what you're looking for with your report. Filter operators determine a relationship between a field in the database and a particular value or range of values of that field.

      Filter_Operators.png

      Operators may include:

      • Equals—Data values must be an exact match on text or value selected.
      • Does Not Equal—Data values do not exactly match text or value selected.
      • Greater Than—For numbers, the data values must be larger than the selected value. For dates, data values must be after that date.
      • Greater Than or Equal To—For numbers, data values must be equal to the selected value or larger. For dates, data values must be equal to that date or after.
      • Less Than—For numbers, data values must be smaller than the selected value. For dates, data values must be before that date.
      • Less Than or Equal To—For numbers, data values must be equal to the selected value or smaller. For dates, data values must equal that date or earlier.
      • Is Between—For numbers, value must be in-between (and including) the values. For dates, data values must be in between those dates from 00:00 hours at the lower limit to 23:59:59 at the upper limit.
      • Is Not Between—For numbers, data values must be outside (not matching) the upper and lower limits set for the range of numbers. For dates, data values must be in-between those dates from 00:00 hours at the lower limit to 23:59:59 at the upper limit.
      • Is One Of—Data values must match at least one of the values added. The record can match one or all of those values to return in the query.
      • Is Not One Of—Data values do not match any of the values added. The record cannot match any of the values inserted.
      • Is Blank—There is no data at all for field selected. This does not mean that the number would be zero, for example, it means there is a null data point or nothing at all in the field. For example, Deceased Date Is Blank.
      • Is Not Blank—There is a data value for the field selected. For example, this could be used to find records that have an address with Address Line 1 Is Not Blank.
      • Begins With—Data values must start with the string or character entered for value. For example, you might want to filter on only values that start with the same couple of letters or only last names that begin with 'Mc'.
      • Does Not Begin With—Data values must not start with the string or character entered for value. For example, you may have bad address records from a legacy database that contain 'unknown' for the Address Line 1 field.
      • Contains—Data values have a specified string of characters within the field. For example Last or Org Name Contains 'Foundation'.
      • Does Not Contain—Data values do not have a specified string of characters within the field. For example Last or Org Name Does Not Contain 'Foundation'.
      • Is All Of—Data values must match all values selected for that field. For example, Group Name Is All Of 'Supporters', 'Gala Attendees', 'Riverside County'.

      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.

      2018-08-07_1532.png
      • 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. NOTE: This does not change stored values from the database.

      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.

      Therefore,  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 need to be deleted that originated in Salsa Engage.
        • constituent records need to be deleted that 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

      Comments

      0 comments

      Article is closed for comments.