Friday, 30 January 2015

SQL Query in CRM 2011/2013/2015 Connection Entity

Using Advanced Find, you can get the Connection records easily

image

Now what if you want to run SQL Query in your SSRS Report, it might be not as easy as you did through Advanced Find because CRM does not store the Contact fields in the Connection table.
Here is the sample of SQL Query to retrieve records from Connection Entity.

select record2idname as ConnectedTo, record2roleidname as RoleTo, record2objecttypecodename as EntityRoleTo, --Account
       record1idname as ConnectedFrom, record1roleidname as RoleFrom, record1objecttypecodename as EntityRoleFrom, --Contact
       --Account fields (Connected To)
       faccount.address1_composite as OrganizationAddress1, 
       faccount.address2_composite as OrganizationAddress2,
       faccount.fax as OrganizationFax,
       faccount.telephone1 as OrganizationPhone,
       --Contact fields (Connected From)
       fcontact.address1_composite as ContactAddress1, 
       fcontact.address2_composite as ContactAddress2,
       fcontact.fax as ContactFax,
       fcontact.telephone1 as ContactPhone       
 from filteredConnection fconnection
 --make sure that only show Connection connect from Contact
 inner join FilteredContact fcontact
 on fconnection.record1id = fcontact.contactid
 and fcontact.statecode = 0 
 --make sure that only show Connection connect to Account
 inner join FilteredAccount faccount
 on fconnection.record2id = faccount.accountid
 --on fconnection.record2id = faccount.accountid
 
 --this is the criteria, I have no individual sub type field for querying GP
 --so I leave this one for you I just filter by active status
 where fconnection.statecode = 0
 -- If you want to filter only show connection 
 -- between Account and Contact please add following line, 
 -- but you need to join to other table to show all
 -- and fconnection.record1objecttypecode = 2
 --and fconnection.record2idobjecttypecode = 1


*This example is to explain the concept only, in order to retrieve Connection records between Account and Contact, you might try to tweak the query based on your requirement or feel free to ask me through the Comments.

Hope this helps!

Thanks.

2 comments:

  1. Packers and Movers Gurgaon Provide Reliable, Safe and Certified Service Provider list, Get Free ***Best Price Quotaition and Compare Charges. ???Hassle free Household Shifting Services, High Quality packing Material, Office Relocation, Car Transportaion, ###Local and Domestic Shifting Service @
    Packers And Movers Gurgaon

    ReplyDelete
  2. Get Packers and Movers Chandigarh List of Top Reliable, 100% Affordable, Verified and Secured Service Provider. Get Free ###Packers and Movers Chandigarh Price Quotation instantly and Save Cost and Time. ???Packers and Movers Chandigarh Reviews and Compare Charges for household Shifting, Home/Office Relocation, ***Car Transportation, Pet Relocation, Bike SHifting @ Packers And Movers chandigarh

    ReplyDelete

My Name is..