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

34 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

jack wilson said...

Thanks so very much for taking your time to create this very useful and informative site. I have learned a lot from your site. Thanks!!

Salesforce Training

Omega software said...

Keep up the fantastic piece of work, I read few blog posts on this web site and I believe that your site is real interesting and has lots of great information. ERP Software in Mumbai || System Software || CRM Software in Mumbai || MLM Software

Omega software 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

jack wilson 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

jhansi joe 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

varshini devi 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

jhansi joe 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

Victoria John 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.

Victoria John 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

Jamuna Pandy said...

Salesforce is a cloud based CRM software. Today's most of the IT industry use this software for customer relationship management. To get more details about salesforce please refer this site.

Regards..
Salesforce Administrator Training in Chennai

Jamuna Pandy said...
This comment has been removed by the author.
Andria BZ 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

Andria BZ 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

Jhon David said...

Cloud is one of the tremendous technology that any company in this world would rely on(cloud computing training). Using this technology many tough tasks can be accomplished easily in no time. Your content are also explaining the same(Cloud computing training institutes in chennai). Thanks for sharing this in here. You are running a great blog, keep up this good work.

Pooja Doss said...

There are lots of information about latest technology and how to get trained in them, like Best Hadoop Training In Chennai have spread around the web, but this is a unique one according to me. The strategy you have updated here will make me to get trained in future technologies Hadoop Training in Chennai By the way you are running a great blog. Thanks for sharing this blogs..

Pooja Doss said...

I found some useful information in your blog, it was awesome to read, thanks for sharing this great content to my vision, keep sharing..
SalesForce Training in Chennai

Pooja Doss said...

Pretty article! I found some useful information in your blog, it was awesome to read,thanks for sharing this great content to my vision, keep sharing..
Unix Training In Chennai

Pooja Doss said...

This information is impressive..I am inspired with your post writing style & how continuously you describe this topic. After reading your post,thanks for taking the time to discuss this, I feel happy about it and I love learning more about this topic..
Android Training In Chennai In Chennai

Pooja Doss said...

I have read your blog and i got a very useful and knowledgeable information from your blog.You have done a great job.
SAP Training in Chennai

Pooja Doss said...

Oracle Training in chennai
Thanks for sharing such a great information..Its really nice and informative..

Pooja Doss said...

Selenium Training in Chennai
Wonderful blog.. Thanks for sharing informative blog.. its very useful to me..

Pooja Doss said...

Data warehousing Training in Chennai
I am reading your post from the beginning, it was so interesting to read & I feel thanks to you for posting such a good blog, keep updates regularly..

Pooja Doss said...

Whatever we gathered information from the blogs, we should implement that in practically then only we can understand that exact thing clearly, but it’s no need to do it, because you have explained the concepts very well. It was crystal clear, keep sharing..
Websphere Training in Chennai

Pooja Doss said...

Oracle DBA Training in Chennai
Thanks for sharing this informative blog. I did Oracle DBA Certification in Greens Technology at Adyar. This is really useful for me to make a bright career..

Pooja Doss said...
This comment has been removed by the author.
Dinju Thomas said...

This information is impressive; I am inspired with your post writing style & how continuously you describe this topic. After reading your post, thanks for taking the time to discuss this, I feel happy about it and I love learning more about this topic..
Selenium Training in Chennai | QTP Training in Chennai

Dinju Thomas said...

Thanks for Information Oracle Apps Technical is a collection of a bunch of collected applications like accounts payables, purchasing, inventory, accounts receivables, human resources, order management, general ledger and fixed assets, etc which have its own functionality for serving the business
Oracle Apps Training In Chennai

Dinju Thomas said...

Oracle Training in chennai | Oracle D2K Training In chennai
This information is impressive; I am inspired with your post writing style & how continuously you describe this topic. After reading your post, thanks for taking the time to discuss this, I feel happy about it and I love learning more about this topic..

Amlon Terence said...

pressure washing companies Virginia Atlantic Sweeping & Cleaning: Sweeping services, pressure washing, porter services in Virginia, Maryland & DC. Since 1977 best in class in parking lot sweeping & more

Savitha said...

Really awesome blog. Your blog is really useful for me. Thanks for sharing this informative blog. Keep update your blog.
Oracle Training In Chennai

Diya Patel said...

Best iOS Training Institute In Chennai It’s too informative blog and I am getting conglomerations of info’s about Oracle interview questions and answer .Thanks for sharing, I would like to see your updates regularly so keep blogging.

Amlon Terence said...

pressure washing companies Virginia Atlantic Sweeping & Cleaning: Sweeping services, pressure washing, porter services in Virginia, Maryland & DC. Since 1977 best in class in parking lot sweeping & more