SBX - Search With Button

SBX - Forum Post Title

Neglect Accounts

Microsoft Dynamics CRM Forum

Damon (nomad) asked a question on 12 Nov 2015 11:41 AM
My Badges

Question Status

Suggested Answer

Hello Everyone:

I having some problems creating a Neglect Account report.

We only want to show Accounts that have no Activities with Minimum Days Neglected 90 days.

When I run the report we have a few Accounts that have Opportunities that are Active within the report and we don't want those record to show

ACCOUNTS

  Status = Active

  Owner= Current Owner

Note I tied this too

Opportunities (Potential Customer)

   Status =Lost, Open Won

Activities

   Activities Type = closed, Orders Closed, Quotes Closed, phone calls.

Regarding (Opportunities)

   Status = Lost, Open Won

Note: if I had certain criteria it remove the Active Opportunities, but it also remove records that should not be removed.

Any help would be great.

Reply
Aiden Kaskela (MVP) responded on 12 Nov 2015 12:14 PM
My Badges
Suggested Answer

Hi _damon,

If you want to get accounts that don't have an opportunity in the last 90 days, you want to write FetchXml with an outer join-type condition. I wrote a query almost identical to this recently - it gets active accounts that don't have an appointment in the last 90 days. Your query would be almost identical except you'd change the link and condition for opportunity, and whatever date you want to use from the opportunity to be your date check.

<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="true">
  <entity name="account">
    <attribute name="name" />
    <attribute name="accountid" />
    <order attribute="name" descending="false" />
    <filter type="and">
      <condition attribute="statecode" operator="eq" value="0" />
      <condition entityname='appointment'
              attribute='activityid'
              operator='null'/>
    </filter>
    <link-entity name="appointment" from="regardingobjectid" to="accountid" alias="ab" link-type="outer">
      <filter type="and">
        <condition attribute="scheduledstart" operator="last-x-days" value="90" />
      </filter>
    </link-entity>
  </entity>
</fetch


Hope this helps! I'd appreciate if you'd mark this as a Verified answer.

Thanks,

  Aiden

Reply
Damon (nomad) responded on 12 Nov 2015 12:36 PM
My Badges

Hello Aiden:

I would have no ideal how to do this. Is there a link on how to do this.

Note I inherited/ took over CRM with no programing background over a year ago. Still learning how to develop CRM

Reply
Aiden Kaskela (MVP) responded on 12 Nov 2015 12:59 PM
My Badges

Hi _damon,

You can't do this in an Advanced Find; CRM just doesn't make the interface for you to do this. If you want to run a Fetch query, you can do it in a report like this: zsoltzombik.com/.../how-to-start-with-dynamics-crm-2015-online-fetch-xml-reporting

or by using a tool to run fetch XML outside of CRM: crm2011fetchexecute.codeplex.com

or if you look a little,  you could probably find a FetchXml solution to run in CRM.

To tweak the FetchXml, I would start by looking at a guide like this one: msdn.microsoft.com/.../gg328117.aspx

Thanks,

 Aiden

Reply
Damon (nomad) responded on 12 Nov 2015 1:18 PM
My Badges

Thanks Aiden:

I was trying to use the Advance Find, Thanks for the heads up on that.

Will look at the links you gave me.

Reply
Aileen Gusni responded on 13 Nov 2015 7:21 AM
My Badges

Hi Damon

Actually you can either using old style fetch xml or if you use the existing report for oob Neglected Account report then you can use adv find for filter.

Now in crm 2015 (as I know you using crm online 2015) you can utilize the rollup field to do filter query.

So create a new field a rollup field to query the count of total opportunity record with status = active or inprogress or whatever status you want.then use the operator equals to zero to eliminate the account with active opportunity.

crmtipoftheday.com/.../not-in-for-the-rest-of-us

This idea coming up only for crm 2015 with new rollup field feature.

Hope this helps

Thanks

Reply
Aiden Kaskela (MVP) responded on 12 Nov 2015 12:14 PM
My Badges
Suggested Answer

Hi _damon,

If you want to get accounts that don't have an opportunity in the last 90 days, you want to write FetchXml with an outer join-type condition. I wrote a query almost identical to this recently - it gets active accounts that don't have an appointment in the last 90 days. Your query would be almost identical except you'd change the link and condition for opportunity, and whatever date you want to use from the opportunity to be your date check.

<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="true">
  <entity name="account">
    <attribute name="name" />
    <attribute name="accountid" />
    <order attribute="name" descending="false" />
    <filter type="and">
      <condition attribute="statecode" operator="eq" value="0" />
      <condition entityname='appointment'
              attribute='activityid'
              operator='null'/>
    </filter>
    <link-entity name="appointment" from="regardingobjectid" to="accountid" alias="ab" link-type="outer">
      <filter type="and">
        <condition attribute="scheduledstart" operator="last-x-days" value="90" />
      </filter>
    </link-entity>
  </entity>
</fetch


Hope this helps! I'd appreciate if you'd mark this as a Verified answer.

Thanks,

  Aiden

Reply

SBX - Two Col Forum

SBX - Migrated JS