Skip to main content

Notifications

Announcements

No record found.

Microsoft Dynamics CRM forum
Suggested answer

How to write DB query to get Email data associated with a ticket

Posted on by 5

Hello Team,

I need to write SQL queries to get all email data associated with a ticket. Can you please help? below the query, I wrote, but for sent item TO and From data is showing wrong.

SELECT
AP.Subject As Subject
,AP.Sender AS [From]
,AP.ToRecipients AS [To]
--,I.Description
,AP.Description
,I.Title AS [Regarding]
,I.prioritycodename AS Priority
, Case WHEN AP.statuscode = 1
THEN 'Draft'
WHEN AP.statuscode = 2
THEN 'Completed'
WHEN AP.statuscode = 3
THEN 'Sent'
WHEN AP.statuscode = 4
THEN 'Received'
WHEN AP.statuscode = 5
THEN 'Canceled'
WHEN AP.statuscode = 6
THEN 'Pending Send'
WHEN AP.statuscode = 7
THEN 'Sending'
ELSE 'Failed'
END AS [Status Reason]
,DATEADD( HOUR,8,AP.ModifiedOn) AS [Modified On]
,I.TicketNumber [Case Number]
,I.Title AS [Title]
,I.statecodename AS [Status (Regarding)]
,AM.ActivityMimeAttachmentId
FROM FilteredIncident I WITH(NOLOCK)
INNER JOIN ActivityPointerBase AP WITH(NOLOCK)
ON I.IncidentId = AP.RegardingObjectId
LEFT JOIN ActivityMimeAttachment AM
ON Am.ObjectId = AP.OwnerId
LEFT JOIN Attachment A WITH(NOLOCK)
ON A.AttachmentId = Am.AttachmentID
WHERE (AP.Subject <> 'Closed' )
AND TicketNumber = @TicketNumber

  • Email data Profile Picture
    Email data 5 on at
    RE: How to write DB query to get Email data associated with a ticket

    No problem. Thanks for your support. I will continue my research on the same by using your input.

  • a33ik Profile Picture
    a33ik 84,321 Most Valuable Professional on at
    RE: How to write DB query to get Email data associated with a ticket

    I'm sorry, I will not write queries for you. All the pointers you need are located in my replies. Good luck.

  • Email data Profile Picture
    Email data 5 on at
    RE: How to write DB query to get Email data associated with a ticket

    Thanks for your reply. I am working on DB migration task and need to write DB query to generate email data for the below fields:

    Subject , From, To, Description, Regarding, Priority, Status Reason, Modified On, Case Number (Regarding), Title (Regarding), Status (Regarding)

    The query I wrote is showing all the email details per ticket but for the Sent item From and To field, data are incorrect.

    I am very confused and really need urgent help to write DB query. PLease help.

  • Suggested answer
    a33ik Profile Picture
    a33ik 84,321 Most Valuable Professional on at
    RE: How to write DB query to get Email data associated with a ticket

    Hello,

    I'm afraid AddressUsed is used in case of unresolved emails only - niravpancholi.wordpress.com/.../

    If you want to get an email of the real record (like user/account/contact/e.t.c.) you will have to join to correspond view and use the field of it.

    Also instead of join to ActivityParty, I would recommend using subqueries instead - docs.microsoft.com/.../subqueries

  • Email data Profile Picture
    Email data 5 on at
    RE: How to write DB query to get Email data associated with a ticket

    Thanks. I have pasted my sql query below. I am still unable to find the relationship and sender/to field value for a ticket email data. Can you please review and help me ?


    SELECT
    CASE WHEN APR.ParticipationTypeMask = 1 THEN APR.AddressUsed END AS [From]
    ,CASE WHEN APR.ParticipationTypeMask = 2 THEN APR.AddressUsed END AS [To]
    --,AP.Sender AS [From]
    --,AP.ToRecipients AS [To]
    FROM FilteredIncident I WITH(NOLOCK)
    INNER JOIN ActivityPointerBase AP WITH(NOLOCK)
    ON I.IncidentId = AP.RegardingObjectId
    LEFT JOIN ActivityMimeAttachment AM
    ON Am.ObjectId = AP.OwnerId
    LEFT JOIN Attachment A WITH(NOLOCK)
    ON A.AttachmentId = Am.AttachmentID
    INNER JOIN ActivityParty APR
    ON APR.ActivityId = AP.ActivityId
    WHERE (AP.Subject <> 'Closed' )
    AND TicketNumber = @TicketNumber

  • a33ik Profile Picture
    a33ik 84,321 Most Valuable Professional on at
    RE: How to write DB query to get Email data associated with a ticket

    Can you please post your updated query where you use ActivityParty?

  • Email data Profile Picture
    Email data 5 on at
    RE: How to write DB query to get Email data associated with a ticket

    Thanks for your reply. I checked but still, I am not getting the right value. Can you please help to review my query?

  • Suggested answer
    a33ik Profile Picture
    a33ik 84,321 Most Valuable Professional on at
    RE: How to write DB query to get Email data associated with a ticket

    Hello,

    In order to get "From" and "To" you should use ActivityParty entity - docs.microsoft.com/.../activityparty-entity

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Anton Venter – Community Spotlight

Kudos to our October Community Star of the month!

Announcing Our 2024 Season 2 Super Users!

A new season of Super Users has arrived, and we are so grateful for the daily…

Dynamics 365 Community Newsletter - September 2024

Check out the latest community news

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 290,558 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 228,647 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,148

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans