Answered by:
Get List of Citizens with no Associated Entity or Activity or Case

Question
-
I have a Citizen table containing all the records of the citizens.
I have a number of entities that each record has an associated citizen record.
Though not all citizens have an associated entity.
Does anyone know how I can find out what Citizen records do not have an associated Entity, Activity or Case?
Thursday, November 12, 2015 11:18 AM
Answers
-
With CRM 2011 OnPremise, the only option is to use SQL. The following example returns all contacts that do not have an associate case (incident)
select * from FilteredContact where contactid not in (select contactid from FilteredIncident)
Microsoft CRM MVP - http://mscrmuk.blogspot.com/ http://www.excitation.co.uk
- Marked as answer by JMcCon Friday, November 13, 2015 9:49 AM
Thursday, November 12, 2015 12:48 PMModerator -
That sounds really strange, are both of those lookups and they are pointing at the same record? If it's a N:N relationship you'll get the guid of the intersect entity which might explain why you get this effect.
Regards
Rickard Norström Developer CRM-Konsulterna
http://www.crmkonsulterna.se
Swedish Dynamics CRM Forum: http://www.crmforum.se
My Blog: http://rickardnorstrom.blogspot.se- Marked as answer by JMcCon Friday, November 13, 2015 9:50 AM
Thursday, November 12, 2015 1:51 PM
All replies
-
There are 2 main options:
- If you have CRM OnPremise, then you can write a report using a SQL query with a NOT IN clause to identify these records
- CRM 2015 (but not previous versions), supports Fetch queries that can do this. The limitation though is that the Advanced Find UI does not support this. You can however create a report using FetchXml that will return the data you want, and this should work in CRM Online or OnPremise
Microsoft CRM MVP - http://mscrmuk.blogspot.com/ http://www.excitation.co.uk
Thursday, November 12, 2015 11:27 AMModerator -
Thanks for the reply david.
I am using CRM 2011 on premise.
Have you any examples or how would I do as you say above?
Thursday, November 12, 2015 11:42 AM -
With CRM 2011 OnPremise, the only option is to use SQL. The following example returns all contacts that do not have an associate case (incident)
select * from FilteredContact where contactid not in (select contactid from FilteredIncident)
Microsoft CRM MVP - http://mscrmuk.blogspot.com/ http://www.excitation.co.uk
- Marked as answer by JMcCon Friday, November 13, 2015 9:49 AM
Thursday, November 12, 2015 12:48 PMModerator -
I don't know if it makes any difference but wouldn't it be better to use the Contact/Incident views rater than the filtered? Just to make sure that the result isn't impeded by the security model.
Regards
Rickard Norström Developer CRM-Konsulterna
http://www.crmkonsulterna.se
Swedish Dynamics CRM Forum: http://www.crmforum.se
My Blog: http://rickardnorstrom.blogspot.seThursday, November 12, 2015 12:51 PM -
It seems that my problem is with custom entities that I have created. There are some contacts that are associated with more than one custom entity and in each custom entity the contact has a different contactid.
I have an example:
I have James who is in the Contact table with id 6a0a5933-0b2f-e211-9e4a-0050568a23a2 .
When I associate James with EntityA James has the contact id 6a0a5933-0b2f-e211-9e4a-0050568a23a2 which is correct and as expected.
But if I add James to EntityA while creating a different record and I select James from the Contact table, James gets a contact id ef3728b9-8b5e-e411-868b-0050568a23a2 which isnt what I expect and when I query the Contact table for contact id ef3728b9-8b5e-e411-868b-0050568a23a2 it doesnt exist.
Does anyone know why this is and how to solve it?
Thursday, November 12, 2015 1:27 PM -
That sounds really strange, are both of those lookups and they are pointing at the same record? If it's a N:N relationship you'll get the guid of the intersect entity which might explain why you get this effect.
Regards
Rickard Norström Developer CRM-Konsulterna
http://www.crmkonsulterna.se
Swedish Dynamics CRM Forum: http://www.crmforum.se
My Blog: http://rickardnorstrom.blogspot.se- Marked as answer by JMcCon Friday, November 13, 2015 9:50 AM
Thursday, November 12, 2015 1:51 PM -
Hi Rickard both are pointing at the same record.
Its a many to one relationship
Thursday, November 12, 2015 2:31 PM