Sign in
Microsoft.com
 
Microsoft
 
 
 

locked
Get List of Citizens with no Associated Entity or Activity or Case RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote

    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

  • Question
    Sign in to vote
    0
    Sign in to vote

    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 PM
    Moderator
  • Question
    Sign in to vote
    0
    Sign in to vote

    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

  • Question
    Sign in to vote
    0
    Sign in to vote

    There are 2 main options:

    1. If you have CRM OnPremise, then you can write a report using a SQL query with a NOT IN clause to identify these records
    2. 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 AM
    Moderator
  • Question
    Sign in to vote
    0
    Sign in to vote

    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
  • Question
    Sign in to vote
    0
    Sign in to vote

    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 PM
    Moderator
  • Question
    Sign in to vote
    0
    Sign in to vote

    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.se

    Thursday, November 12, 2015 12:51 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    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
  • Question
    Sign in to vote
    0
    Sign in to vote

    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
  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi Rickard both are pointing at the same record.

    Its a many to one relationship

    Thursday, November 12, 2015 2:31 PM