One of my posts that seems to remain in high demand, based on the site visitor analytics, is Advanced Queries with Advanced Find. Written over two years ago, people still tend to read it far more often than the newer posts dealing with the latest trends around Dynamics CRM or instructions on how to leverage other platform features like workflows and Business Process Flows. Thinking about the potential audience size, it’s of course understandable that a feature accessible to all CRM users will be much more popular than process configuration tools or the Dynamics product roadmap.
Although very few updates have been done on the Advanced Find functionality during the 10 years I’ve been using Dynamics CRM, it’s arguably still a real killer feature of the platform, at least when comparing it to the query capabilities of many similar business applications. The fact that you’re able to reference pretty much any related record in your query criteria (and in the CRM data model, absolutely everything is related) means that the tool can be used for building the most complex target group definitions for your marketing campaigns, for example, based on behavioral data stored many relationships away. You only have to use another Microsoft application to understand how powerful such a tool can be in the right hands.
It never hurts to have a good understanding of the CRM data model of your organization when launching Advanced Find to build some queries, since AF is a world of abundance when it comes to the available options to select from. Usually the relationships between records are something you can figure out from the end user UI if you spend a moment thinking about it – although with the “flat” design of CRM 2013+ menus and navigation structures, the front end ain’t as hierarchical in nature as the old popup-heavy UI used to be, thus sometimes leading you astray with the underlying data model. In some cases, though, Advanced Find will allow you to perform queries on entities that are completely invisible to the CRM end user. In this post we’ll take a look at one such entity, the activity party, and explore ways in which we can use it for providing the CRM users information on who they are interacting with.
Ain’t No Party Like The Activity Party…
…’Cause in the Activity Party everybody’s connected! OK, so what exactly is this “party” thing then? In the CRM user interface we have activities, which are divided into a number of different types, like email, appointment, phone call, letter, fax (everyone’s favorite default entity, right?) and potentially a selection of custom activity entities for non-standard communication channels like SMS or business specific record types for handling assignments, approvals and these types of work items. Each activity type shares a number of common fields that can be found from the entity called “activity”, which is what allows CRM to show this mixed bag of apples, oranges and pineapples in a single list of
fruit activities related to a business record like the customer account.
When we add people into an activity like a meeting invitation (remember: appointments are always invitations now in the server-side sync world, so be careful when including customer contacts there), CRM is not just populating a lookup field on the activity entity with the GUIDs of all the related users, contacts and other resources. What happens behind the scenes is that each of these related records will result in a new activity party record being created. This is an entity that you will not see in the CRM customization UI if you open the default solution. You can read about it in the SDK, or install a tool like the Metadata Browser (found inside the downloadable SDK package) and have a look at its contents from the live system, as we see below.
People who have been tasked to build SSRS reports that deal with activity records will have surely run into the ActivityParty table/view. If you’re interested in learning more about how the data is created and stored into the SQL database, go and read this great investigation by CRM MVP Aileen Gusni. If, on the other hand, you’d rather not spend too much time in Visual Studio / SQL Server Data Tools building reports but rather want to see how to leverage activity parties in Advanced Find, then this is the right article you’re reading right here.
View of “Activities Involving Me”
While activity parties are not accessible as a configurable entity in CRM (because ultimately they’re not), luckily they do exist in the Advanced Find UI. The first scenario in which we can take advantage of this capability is in building a view of activities that uses some criteria that’s not directly available on the activity record itself. Out of the box, CRM provides views like “My Activities” that show the records in which the current user is the owner (and which are open, even though the name of that default view is a bit misleading). Sure, it’s important to understand what’s on your To Do list right now, but sometimes it is beneficial to be able to reflect back a bit and see also what has happened in the past, to understand what you’ve spent your time on and who have you interacted with. For this, we’ll create a brand new view called “Activities Involving Me”.
Let’s decide that we want to show all activities, regardless of the time period they’ve been created on, and instead we’ll just sort them in an ascending order by the activity creation date in the resulting view. This means we can clear away any existing criteria from the activity view we’ve chosen as a template (which may save you some time in adjusting the view columns). The new criteria which we’ll need to add will be regarding the related Activity Party (Activities). It’s here where we’ll configure it so that only the records where the party equals the current user will be included into the view. That’s it! Once you execute this query, you’ll not only see activities you’ve managed but also any entries from co-workers who’ve invited you into meetings, email threads and other forms of (hopefully) customer communication.
If needed, you could easily expand the view criteria to look at the members of a specific team and their activities, but I think these sort of views are most useful for the users themselves, as a kind of “captain’s log” for finding recent discussions or quickly navigating to regarding records relevant to them.
Who Do I Interact With?
Let’s turn things up a notch and build a query that looks a bit more Advanced (like the ones in my earlier AF posts). Instead of going directly to the activity, what if we could use these connections to various different parties as kinds of network nodes that offer us insights not just on what has been happening but with whom? You know, a bit like the Facebook social graph but for business contacts? While we may not be able to quite match the mighty powers of Fort Zuckerberg with only Dynamics CRM Advanced Find at our disposal, there’s still a lot we definitely can achieve with this awesome tool.
So, we’ll want to produce a list of people, and in this case a list of external people rather than employees of our company, which means we’ll build the Advanced Find query on the contact entity. As we’ve learned, contacts can act as very similar activity parties as internal CRM users, so we’ll find the necessary relationship of Activities Party (Activity) in the exact same spot. We won’t stop here, though, but rather traverse the path over to the main entity record, and continue our journey all the way to yet another activity party. Yes, it takes at least two to party, so we know that the same activity record can connect us also to a user record. Following the pattern from the previous example, we’ll close off the query branch by referencing the current user again. If we wanted to, we could limit this relationship to only specific participation types by adding a criteria for it.
Executing this query will give us a view that can be even more insightful to examine that the activities view, as it represents the true “my contacts” list based on factual data about the interactions with these people, rather than just the cold “owner” designation on the contact records in CRM. Since we’re dealing with transactional data in this query, we could further narrow it down to show a list of only those contacts with whom you’ve interacted during the past 6 months, for example. Not seeing your key client contacts in that view? Then maybe it’s time to pick up the phone and check how things are, before their status turns into ex-client.
Visualizing Your Interactions
Having these new views available in the dropdown menus for activity and contact entities is a good start, but if you really want to demonstrate the power of managing your customer interactions with CRM activities to the end users, you better be ready to go the extra mile. This means making the information not only available but also accessible. By combining the power of Advanced Find queries with the visualization capabilities of Dynamics CRM charts, you can quickly build a “My Interactions” dashboard that will offer the users plenty of insights with a single glance: which users are working with me most often, what type of contacts do I mostly engage with, who are my top 10 customer accounts in terms of contact network reach, and so on. Now doesn’t that sound like a much more compelling activity tracking use case for a CRM application than a one where you’ll only see a bunch of mechanical tasks generated for you by a faceless enterprise information system?
That concludes our session for today. As always, if you have some examples of your own on how you’ve successfully built interesting new views for examining the core CRM customer and activity data with the help of Advanced Find, be sure to share your experiences via the comments box. Likewise, if you’ve been wondering whether some type of activity related query results are achievable with Advanced Find or not, then perhaps the readers of this blog will be able to help you out.
Update 2016-02-20: For further reading on how to make use of the activity party data, head over to the CRM Chart Guy’s blog post Charting on Activity Parties in MS Dynamics CRM.