Dynamics CRM 365 - On Premise - Find Accounts that Don't Have A Related Record

(0) Share

Report

Posted on by Community Member

Hello everyone,

I have a business requirement that is asking me to come up with a way for individuals to search for a list of accounts that do not have a related entity.

There are also a wide range of scenarios and conditions, for example:

Let's say I have a custom entity that has a N : 1 relationship with an account and each record of this custom entity may represent a product / service the account may be subscribed to.

I want to generate a list of accounts that have a particular service but not another.

I thought about using a custom 'tracker' entity that has fields for each product and I could then search based on those fields... However, I believe there are limitations with the max number of fields that you can have on an entity.

I know this can be done easily with a direct SQL query but I am also asked to come up with a 100% CRM supported way of doing this.

The only other idea that I have would be to put together a custom HTML web resource, allow users to input details, and have it attempt to build the fetch XML's and if it cannot be done in one Fetch XML to build multiple and have the system try to iterate through the result sets and put together a final account list.

I'm not asking for a direct solution such as code but I am just looking for ideas / suggestions on a way I could put this together.

Any and all advice would be greatly appreciated.

Thank you!

*This post is locked for comments

I have the same question (0)

All responses (6)

Answers (2)

Suggested answer

sandeepstw 4,601 on at

Like (0)

Report
Copy link

Link copied!

Hi,

Use this Fetch XML -

<fetch mapping='logical'>

<entity name='account'>

<attribute name='name'/>

<link-entity name='customentity'

from='coustomentityid'

to='customaccountid'

link-type='outer'/>

<filter type='and'>

<condition entityname='customentity'

attribute='customentityid'

operator='null'/>

</filter>

</entity>

</fetch>

Was this reply helpful? Yes No
Suggested answer

Drew Poggemann 4 on at

Like (0)

Report
Copy link

Link copied!

Hi AnimalHealthCRM,

Not sure if this is supported yet on premises for Dynamics 365 but Microsoft supports a "NOT IN" with Advanced Find since July 2017 update.

community.dynamics.com/.../advanced-find-not-in-query-is-supported-in-v9-0

www.magnetismsolutions.com/.../microsoft-dynamics-365-july-2017-update---not-in-queries-for-views-and-advanced-find

Hopefully you can utilize this to hep you with your business case and create a view.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report
Copy link

Link copied!

Thank you for the suggestions and fetch XML provided:

Unfortunately, maybe I am just misunderstanding, isn't this only going to get me accounts that do NOT have any of the related custom entity?

Let's say I want to find a list of accounts that a related record to this custom entity AND I want to find a list of accounts that don't have a related record to this custom entity at all...

Is this even possible in one query because I can only have one link-entity element per fetch XML?

Or am I correct in saying that I would have to run 2 different queries.. One for accounts with related entity and one without?

Thanks again.

Was this reply helpful? Yes No
David Jennaway 14,065 on at

Like (0)

Report
Copy link

Link copied!
You can have multiple link-entity elements in one FetchXML query, so you should be able to do this with something like:

<fetch mapping='logical'> <entity name='account'> <attribute name='name'/> <link-entity name='customentity' from='customentityid' to='customaccountid' link-type='outer' alias='NoService'> <filter type='and'> <condition attribute='name' operator='eq' value='service do not have'/> </filter> </link-entity> <link-entity name='customentity' from='customentityid' to='customaccountid' link-type='outer' alias='HasService'> <filter type='and'> <condition attribute='name' operator='eq' value='Has service'/> </filter> </link-entity> <filter type='and'> <condition entityname='No Service' attribute='customentityid' operator='null'/> </filter> </entity> </fetch>

As you say you could do this with a SQL query, maybe it'd help to post a sample SQL query so we can determine if it can be done with one FetchXML query

Was this reply helpful? Yes No
Verified answer

Ben Thompson 6,350 on at

Like (0)

Report
Copy link

Link copied!

My interpretation of the query is show me "accounts who have either never bought a product from us or who have bought product X" from us.

That requires 2 separate queries in FetchXML as you cannot link to the same entity twice in a single query and nor can you place an or condition at the link-entity level of a fetchxml query.

If you need to do this you will need to create 2 different FetchXML queries or use an SSRS report.

Was this reply helpful? Yes No
Verified answer

Community Member on at

Like (0)

Report
Copy link

Link copied!

Hello everyone,

I appreciate you all taking the time to get back to me on this one.

After looking at this business requirement further, I don't think there are that many records of this custom entity that I will have to potentially track....

So I created another custom entity and created fields on the custom entity that represent different 'products' or 'versions' of the main custom entity that is associated with the account.

Then when that main custom entity is updated it will update the second custom entity field that represents that product.

This approach works for my stakeholders because they can still use the advanced find easily without me needing to generate custom reports using fetch xml.

I know the downfall of this though is scalability as you can only have ~ 1024 columns per table but they should never reach that point.

Thanks again.

Was this reply helpful? Yes No