Advanced Filters in Dynamics 365 – Part 2

Advanced Filters in Dynamics 365 – Part 2

Filter All Contacts for Accounts in Connecticut with an Advanced Filter.

Transcript

This is Steve Pestillo from P2 Automation, and welcome to the second video in our short miniseries on Advanced Filters in Dynamics 365 and Power Apps. 

We get this one all the time. A client has a booth at a trade show, let’s say it’s in Connecticut, and they want to invite all their Contacts that work for companies in Connecticut. And they want to do this by email. Now the problem here is many users of Dynamics 365 don’t store the mailing address at the Contact level, they store it at the Account level. But we want to email the contacts. So how do we filter contacts that work for companies in Connecticut? This is an example of a Many to One Relationship. Remember for every Account, you can have many Contacts, but for every Contact, they can only work for one Account.

So how do we do this? We need to start at the many side or at the contact list because we need those email addresses. And I always like to start with the closest view that I have available to me. In this case, I don’t want to send to All Contacts, I just want to send it to Active Contacts. So let’s start with that one. I’m going to Edit Filters, and you can see the Status Equals Active is already selected. And I want to include contacts that work for companies, so I’m going to start here at the Many to one section, and go to Company Name (Organizations). It’s not a very good description but that’s how you get to the parent account. And we want to see companies whose State/Province Equals CT. And you can see we started with 867 and when I apply this filter, I have 20. 

But you can see, there’s some missing email addresses and we want to invite these people by email. So what I have to do here is Edit Filters and I don’t want to filter at the Company level, I want to filter at the Contact level. So just add another filter down here where the Email Contains data. We just want email addresses that aren’t blank, and that 20 goes down to 17. 

Now do these contacts all work for companies in Connecticut? Let’s find out. So I’m going to Edit columns and let’s just add the State/Province. Aaaannnd no. Most of them do. This one’s from California. Most are from Connecticut but why not all? The reason is in our demo environment, we actually do store addresses at the contact level, and that’s what this is showing you, the state from the contact. But we want to filter companies that work in Connecticut. 

So how do we display that? Let’s add a column again. And this time, we don’t want to add from the Contact list of fields, we want to go to a related table. And here it is again, the Company Name (Organization), not intuitive, but that’s the one you want. And let’s pick the State/Province from that. And there you go, they’re all in Connecticut! 

If you’d like help on Advanced Filters for your organization, reach out to us today at www.p2automation.com. And make sure to subscribe to our YouTube channel.