Advanced Query Wizard (AQW) Overview

Pro Tip: Be sure to check out Salsa CRM pre-made Reports. Chances are that you'll be able to get the data you need without having to create a custom query in the AQW.

The AQW enables you to generate your own source data for creating custom reports (without need to know how to query the Salsa CRM 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 AQW. Permissions to Add, Edit, and Delete queries are also available. NOTE: Users with AQW permissions have access to all CRM data. Any user who has AQW access will also have access, through the AQW, 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.

AQW Workflow

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 do 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.

AQW Main Screen

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

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 a query. This data table represents a snapshot of the way your data looked at the time the query was run. This data table does not include the original query criteria that produced the results.

Import Button

In the top right corner, click the Import button to open query criteria saved to your desktop or to your network. This option also allows you to import external data stored as a comma-delimited (CSV) file (appended with constituent numbers in their own column) to work with the Query Wizard's Post Query Options.

• Other Data Source—This option also allows you to import external data stored as a comma-delimited (CSV) file (appended with constituent numbers in their own column) to work with the Query Wizard's Post Query Options. For example, you exported a CSV file of constituents for a special mailing from your board. Your Executive Director wanted to review the file personally and hand-pick the list. They deleted rows from the list electronically and want emails sent only to that list. There's no way to build a query to identify their personal whim. Open this mailing data source here, and in the Post-Query Options, either add them to a CRM Group or send them directly to an Engage Group or email blast.
  NOTE: Do not include single quotes or other special characters in the CSV import file name.
• Query Definition—This option also allows you to open query criteria saved to your desktop or to your network. Query criteria within an organization's CRM database can be viewed by anyone who has permission to open the Advanced Query Wizard. This option is typically available to CRM users who have a friend in another foundation who uses Salsa CRM and has a really great query to share with you. They can export the XML file, share it with you, and you can import it!

NOTE: Do not include single quotes or other special characters in the CSV import file name.

Create a New Query

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.

Selecting the right Query Type will ensure that the Available Fields 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 queries 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.
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 or automatically applied output 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 those 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 AQW 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. This cannot be undone.

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 the number, below the screenshot.

1. Available Fields—These are all the fields that are available for the Query Type you selected in Step #1. This field listing is sorted by the table that stores that data element, because there are literally hundreds of fields—so many we can't list all descriptions of fields here but there are definitions if you put your mouse over the field name. They include Custom Fields you create in CRM and those 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. Aggregati on 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 either zip code or city district, your filters would look like this:
      
      NOTE: aggregate fields do not work within parentheses.
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.

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 the 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, the 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 the 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 Results

This is where you may preview results and select the data that you will include in the final output.

Notice that in the first column of the results table, 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 the 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, replace the 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 Excel Spreadsheet (.XLS) 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 that displays "Group A" in the Group Name column and one that displays "Group B".

In an extreme example, a client once tried to generate a query that contained all donations and all groups without any filters. The query would have returned 263,000 donations times 98 groups. That equates to over 25 million rows of data. Scrolling at 30 rows per second, it would have taken 9.5 days to scroll through all that data!

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 in this window are as follows:

• Save...
  • Save Locally allows you to save the query results to a file you can open with your spreadsheet software or with a browser.
  • Save on Server allows you to save the query results to the database to view the results in the future.
  • Save Query Criteria allows you to save the query criteria to the database to 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 organizations who use Salsa CRM.
• Global Update (CRM data only)...
  • Update Individual Type allows you to update all selected constituents with the selected Individual Type.
  • Update Organization Type allows you to update all selected constituents with the selected Organization Type.
  • Assign to Group allows you to assign all selected constituents to a group.
  • Create a Smart Group allows you to assign all selected constituents to a Smart group that already exists or that you create "on the fly".
  • Remove from Group allows you to remove all selected constituents from a group (in CRM only).
  • Global Status Update allows you to change the event status of all selected guests.
  • Update Custom Field allows you to change the value of a custom field for all constituents returned in the query.
• 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 several 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 progress through all constituents returned in the query results and make changes that cannot be made globally. 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.
  
  NOTE: If you use the Constituent Dashboard view, you will only see partial details when you Process Constituents. You must change the Constituent view setting in the User Configurable Options before running your query.
• 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, batch, or class on the donations that were in the query results.
  
  NOTE: if you have previously posted donations to Financial Edge, the Query Wizard will not move transactions from bulk updates made to funds, campaigns, appeals, or classes.
• Global Delete allows you to choose between deleting selected donations, forms, Emails, or constituents.
  
  NOTE: Be very careful when using the Global Delete option. This operation cannot be undone.
  
  NOTE: Global delete will not work if...
  • Donation records originated in Salsa Engage.
  • Constituent records have donations tied to Engage.
    

Once you complete an option, you can return to this screen and select a different option. Click Finish when done.

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 it to the server, in which case your query will be available for all CRM users, or save it locally on your computer. If you save locally, the query is converted to XML file format and saved to your machine; Salsa CRM does not retain it in any way and is not accessible by CRM users on other computers (unless you provide them with the file). The file is saved to the default location 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.