How to Query for Accounts with No Open Activities

(0) Share

Report

Posted on by Community Member

Afternoon!

I've been messing with this for hours, and I've officially thrown in the flag.

I need to query CRM for accounts that have closed activities, and no open activities (i.e. phone calls, e-mails, etc.). It appears that accounts and activities aren't fully queryable (if that's a word) together, which surprises me.

Again, these accounts all have a variety of activities/tasks that are closed. I want to query so that I get the accounts that have closed activities, and no open activities.

I'm aware I can query for accounts with 0 activities, which is not my goal. Hope this makes sense!

*This post is locked for comments

I have the same question (0)

All responses (11)

Answers (0)

Community Member on at

Like (0)

Report

Hello,

You can add the relative entity in fetch query for all activities entities. for ex. Task, phone call etc.

Hope you can understand what I mean!

Was this reply helpful? Yes No
Shahbaaz Ansari 6,211 on at

Like (0)

Report

Hi,

You can go to advance find and you can use below screenshot for query account with completed activity.

You can also download the XML query from Download Fetch XML button highlighted below,

If you find it helpful,Please mark as Verified.

Best Regards,

Shahbaaz

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Thank you for this feedback!! Unfortunately when I do that, it brings up accounts with activities that are both completed and open. I want to bring up accounts that only have completed. (I don't want accounts with both open and completed accounts to show up on query -- just completed, which means they have no open accounts currently).

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Yes I've done that -- I've not been successful with filtering out accounts with open activities. Thank you for the feedback!

Was this reply helpful? Yes No
Wayne Walton 13,730 on at

Like (0)

Report

"Activity Status Does Not Equal Open" doesn't do that for you?

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Unfortunately that doesn't work. Made the modification -- I believe the system thinks I want accounts with activities that aren't open -- it doesn't necessarily think that I want accounts that are exclusively not open. In a nutshell, I get accounts with both completed and open activities using that query.

Was this reply helpful? Yes No
Suggested answer

Tadhg 75 on at

Like (2)

Report
Hi Serpx,

I think that what you are after is some form of a left outer join.

You want accounts where the open activities is null AND the closed activities is not null. The below fetch should work for you.

<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="true"> <entity name="account"> <attribute name="name" /> <attribute name="accountnumber" /> <order attribute="name" descending="false" /> <link-entity name="activitypointer" from="regardingobjectid" to="accountid" alias="bad" link-type="outer"> <filter type="or"> <condition attribute="statecode" operator="eq" value="0" /> <condition attribute="statecode" operator="eq" value="2" /> <condition attribute="statecode" operator="eq" value="3" /> </filter> </link-entity> <link-entity name="activitypointer" from="regardingobjectid" to="accountid" alias="good" link-type="outer"> <filter type="or"> <condition attribute="statecode" operator="eq" value="1" /> </filter> </link-entity> <filter type="and"> <condition entityname="bad" attribute="regardingobjectid" operator="null" /> <condition entityname="good" attribute="regardingobjectid" operator="not-null" /> </filter> </entity> </fetch>

NOTE: This query only filters on the activity 'regarding' field. You will have to implement further filtering if you wish to cater for phone calls where the account is in the 'to' field.
You can get more examples of these join type queries here: https://msdn.microsoft.com/en-us/library/dn531006.aspx

Hope this helps!

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Thank you! This sounds like something that'd give me what I need -- how do I upload this?

I copied and pasted that into a .xml document (also tried .txt), and went to File -> Tools -> Import Data and it keeps giving me errors.

When I try to upload it as an .xml, I get:

"Invalid import file

The data file can't be imported because it contains invalid entity data or it's in the wrong format. Make sure that the file contains correct data and that it's in the XML Spreadsheet 2003 format, and then try uploading again."

When I try to upload it as an .txt, I get:

"The column heading contains an invalid combination of data delimiters.

The column heading contains an invalid combination of data delimiters. If you contact support, please provide the technical details."

Do I have to get into the XML code to make this work?

Was this reply helpful? Yes No
Suggested answer

gdas 50,091 Moderator on at

Like (0)

Report

Hi,

The import file of Dynamics CRM should be allign with data import template so I would recommend download the template file and filled your data in the template and don't make any changes in the file format. Only just paste from third line of the template.

Hope this will work .

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Thank you for the tip -- how do I make this work as a query?

I don't think I can import this code since I am not an admin. Is there a way to do this under the Advanced Find tab without going into the XML?

Was this reply helpful? Yes No