Friday, April 26, 2013

Get FROM, TO, CC, BCC parties in Reports/SQL in MS CRM 2011


It's very common requirement to get the TO and FROM party list  in our report for activities. As we know it doesn't store as part of activity/activitypointer. It get stores separably in activityparty table, and all parties are separate entry in activityparty table.

I wrote a function to get the FROM, TO, CC, BCC or any other party list from CRM in your report. Juse use below function in your report, it will return all parties as comma separated string.

-- To : To Recipient
-- From : Sender
-- TO get "TO" party list : SELECT [dbo].[fn_GetPartyList] ('B6166B16-9AA2-E211-854C-000C29749A28','To Recipient')
-- TO get "FROM" party list : SELECT [dbo].[fn_GetPartyList] ('B6166B16-9AA2-E211-854C-000C29749A28','Sender')

CREATE FUNCTION [dbo].[fn_GetPartyList](@ActivityId NVARCHAR(100),@PatryAttribute nvarchar(100))
RETURNS NVARCHAR(MAX)
AS
BEGIN
DECLARE @Result NVARCHAR(1000)
DECLARE @Indivisual NVARCHAR(100)
SET @Result=''
DECLARE @Count INT

SET @Count =(SELECT COUNT(partyidname) from FilteredActivityParty
   WHERE participationtypemaskname = @PatryAttribute AND activityid = @ActivityId)
IF(@Count>0)
BEGIN

DECLARE @Table1 TABLE(Row INT IDENTITY(1,1), partyidname nvarchar(100))
INSERT INTO @Table1
SELECT partyidname from FilteredActivityParty
WHERE participationtypemaskname = @PatryAttribute AND activityid = @ActivityId

WHILE(@Count>=1)
BEGIN
SET @Indivisual=(SELECT partyidname FROM @Table1 WHERE Row=@Count)
SET @Result=@Indivisual+','+@Result

SET @Count=@Count-1
END

END

DECLARE @LENGTH INT
SET @LENGTH=LEN(@Result)
RETURN SUBSTRING(@Result,0,@LENGTH)
END

13 comments:

peterson said...

Thanks for updating new information about CRM. As an application developer you have shared lot of details about CRM. Also share your updated details about cloud in this website.
Cloud computing course in Chennai
Friday erspipt

Unknown said...

I really appreciate spending some time to talk about that, I believe firmly regarding this and so really enjoy understanding more about this kind of subject.This is also a very good post which I really enjoyed reading. It is not everyday that I have the possibility to see something like this. CRM Software || MLM Software in Mumbai || ERP Software || System Software in Mumbai

Unknown said...

Cloud Computing Training

I have read your blog and i got a very useful and knowledgeable information from your blog.its really a very nice article.You have done a great job . If anyone want to get real time Cloud Computing Course in Chennai, Please visit FITA academy located at Chennai Velachery which offer best Cloud Computing Training in Chennai.

Cloud Training in Chennai

Unknown said...

I am following your blog from the beginning, it was so distinct & I had a chance to collect conglomeration of information that helps me a lot to improvise myself. FITA is glad to inform you that; we provide practical training on all the technologies with MNC exports. We assure you that through our training the students will gain all the sufficient knowledge to have a voyage in IT industry.
Informatica training in chennai|Fita Chennai Reviews|Best Informatica Training In Chennai

Unknown said...

i have read your post, it was good to read & i am getting some useful info's through your blog keep sharing...
Salesforce training in Chennai|Salesforce courses in Chennai

Unknown said...

The information you have given here is truly helpful to me. CCNA- It’s a certification program based on routing & switching for starting level network engineers that helps improve your investment in knowledge of networking & increase the value of employer’s network, if you want to take ccna course in Chennai get into FITA, thanks for sharing…
ccna training in Chennai | ccna training institute in Chennai

Unknown said...

Salesforce Training

The information you posted here is useful to make my career better keep updates..I did Salesforce Training in Chennai at FITA academy. Its really useful for me to make bright future in IT industry.

Unknown said...

SAP Training

Thanks for sharing this valuable information.and I gathered some information from this blog. I did SAP Training in Chennai, at FITA Academy which offer best SAP Course in Chennai with years of experienced professionals.

Regards.......

SAP Training Institute in Chennai

Unknown said...
This comment has been removed by the author.
Unknown said...

It was good to read & I am getting some useful info's through your blog keep sharing... Informatica is an ETL tools helps to transform your old business leads into new vision. Learn Informatica training in chennai from corporate professionals with very good experience in informatica tool.
Regards,
Best Informatica Training In Chennai|ccna course in Chennai

Melisa said...

Thanks for sharing this useful post; Actually Salesforce crm cloud application provides special cloud computing tools for your client management problems. It’s a fresh technology in IT industries for the business management.
Regards,
Salesforce training institutes in Chennai|Salesforce courses in Chennai|Salesforce institutes in Chennai

Unknown said...

Thanks for sharing this niche useful informative post to our knowledge, Actually SAP is ERP software that can be used in many companies for their day to day business activities it has great scope in future.
Regards,
SAP training in chennai|SAP Training|SAP Institutes in Chennai|sap training in Chennai

Unknown said...
This comment has been removed by the author.