Table of Contents
| Table of Contents | |||
|---|---|---|---|
|
...
|
...
|
...
...
...
...
...
...
...
...
...
...
...
...
...
...
...
...
...
...
...
...
...
Building a Query (example):
In this section we’ll build sample queries in the query builder. The first will be a simple report displaying some basic information on all contacts in our demo CRM that are Membership Prospects, and have a Lead Source defined. The second will be an example of a query using aggregate functions and containing a join between two Objects. Both walkthroughs will start from the query builder screen - see Accessing the Tool above if you need instructions on how to get to this point.
Example 1: All Prospects w/ Lead Source
In this example, we’re going to create a query that pulls records from our Contacts object, that have a Status of Membership Prospect, an Opportunity Status of Open, and that have any value entered in the Lead Source field. The query will display the First Name, Last Name, Primary Email, Primary Phone, Lead Source, and Created Date fields for each of these records. There will be no grouping, and we’ll order it by the Created date to show the most recently created records at the top.
To start, we’ll first navigate to the All Queries screen, then click Add New Query. Then, we have to give the query a name, and optionally a category and description. In this case, let’s name it ‘All Open Membership Prospects with Lead Source’, and give it a category of Membership Prospects. I’ll leave the Description field blank, but you could type something in here to help you know what this query will do, if you’d like. We’ll also leave the Maximum Number of Results field blank. In this walkthrough, we’ll skip over the Security section, as we want all of the users in our demo CRM to be able to view this query once it is complete.
...
We’re now ready to start building the query, but at this point it is a good idea to click the Save Query button, just to be safe.
...
When you first drag in the Contacts object, you may notice a few circular relationships that display above the Contacts block by default - these aren’t necessary for most queries, but they don’t typically hurt anything either, so you can choose to leave them alone, or delete them. I’ll delete them in this example.
...
Aside from some system fields, which will display first in the list of fields, the fields will be listed in alphabetical order. Once you find a field you’d like to display in the query, click on it and drag it down into the Fields section of the query builder. If you have a lot of fields, and/or a smaller screen, you may need to drag the field to the bottom of your screen to scroll the page down in order for you to see the Field section.
...
Now that we’ve told the query what fields of data to pull for the records it selects, it is now time to tell it which records to select! We’ll do this by creating Filters - by default, with no filters applied, the query will select ALL records from the object(s) it is targeting, but in this case we only want Membership Prospects with an Open opportunity status and a value in the Lead Source field, so we’ll have to add three filters.
...
This screen is asking us how the query will compare the value of all records it scans against this field (in this case, the Status field) to determine if it will select the record to display when the query is run. The most common options selected here are Static Value, or Blank Value. For this filter, we’ll select Static Value, which will then give another option, in which you will need to type the value against which the query will compare all scanned records. In this case, type in the words ‘Membership Prospect’, then click Save.
...
Once saved, you should then see the filter displayed in the query builder:
...
At this point, we now have a fully functional query! This is a good time to hit Save, just to be sure that we don’t lose any of our progress. After you save it, you can click Test Query to see how it is looking so far - at this point, it should be displaying the fields that we’ve defined for it, and selecting all of your contact records with a status of Membership Prospect.
...
This is close to our end goal, but not quite there yet, so let’s add in a couple more filters now. We can do this the same way as we did the first one - just find the field we want to filter on, then click and drag it down into the Filtering section. This time, let’s grab the field ‘Opportunity Status’, and compare it to a Static Value, in which we’ll type in ‘Open’.
...
With this second filter in place, you should notice that a new dropdown box appeared between the two filters. This box contains the words AND and OR, and defines how these two filters work together. Selecting the AND operator means that in order for a record to display in our query, it will have to pass both filters - Status = ‘Membership Prospect’ AND Opportunity Status = ‘Open’. Selecting the OR operator would mean that a record can display in the query if it meets just one of the criteria (if it meets both criteria, it would also pass the filter). In this case, we want to use AND so that records will only be selected if they meet both criteria.
...
This is another good time to click Save, and run a test of the query to see how it looks now. Depending on the data in your CRM, you probably will have fewer returned results now that you’ve added in the two new filters.
...
Once again, click Save - and now your query is complete! You can now access this query whenever you need it, or if you’d like to make it even easier to access, you can add it to your Quick Links.
Example 2: Count of Activities per Prospect
In this example, we’re going to create a query that pulls records from our Activities Object, which are joined to another record in the Contacts Object. We’ll just be selecting the Contact name, and an aggregate function to display how many attached Activity records there are to that Contact. As such, we will need to utilize the Grouping section in this query, as opposed to the previous example.
...
And here is how the results will look, accordingly:
...
A lot of the fundamentals covered in the previous example are applicable here as well, but in this case the query will be also be using an aggregate function and the grouping section, so we’ll focus mainly on these pieces.
...
In this selection, you will almost always want to select the same field that you had selected as the other field in the query, in this case Contacts.RecordName.
...
This allows you to give a name to each of these fields, since neither of them are self-descriptive on their own. In this case, I named the fields ‘Name’ and ‘Activities’, so when the query is run, each contact’s name will appear in the Name column, and the number of Activities they have attached will be in the ‘Activities’ column.
...