Personalized Community is here!
Quickly customize your community to find the content you seek.
Have questions on moving to the cloud? Visit the Dynamics 365 Migration Community today! Microsoft’s extensive network of Dynamics AX and Dynamics CRM experts can help.
2022 Release Wave 2Check out the latest updates and new features of Dynamics 365 released from October 2022 through March 2023
The FastTrack program is designed to help you accelerate your Dynamics 365 deployment with confidence.
FastTrack Community | FastTrack Program | Finance and Operations TechTalks | Customer Engagement TechTalks | Upcoming TechTalks | All TechTalks
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.RegardingObjectIdLEFT JOIN ActivityMimeAttachment AM ON Am.ObjectId = AP.OwnerIdLEFT JOIN Attachment A WITH(NOLOCK) ON A.AttachmentId = Am.AttachmentID WHERE (AP.Subject <> 'Closed' ) AND TicketNumber = @TicketNumber
In order to get "From" and "To" you should use ActivityParty entity - docs.microsoft.com/.../activityparty-entity
Thanks for your reply. I checked but still, I am not getting the right value. Can you please help to review my query?
Can you please post your updated query where you use ActivityParty?
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.RegardingObjectIdLEFT JOIN ActivityMimeAttachment AM ON Am.ObjectId = AP.OwnerIdLEFT JOIN Attachment A WITH(NOLOCK) ON A.AttachmentId = Am.AttachmentIDINNER JOIN ActivityParty APR ON APR.ActivityId = AP.ActivityIdWHERE (AP.Subject <> 'Closed' ) AND TicketNumber = @TicketNumber
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
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.
I'm sorry, I will not write queries for you. All the pointers you need are located in my replies. Good luck.
No problem. Thanks for your support. I will continue my research on the same by using your input.
Business Applications communities