web
You’re offline. This is a read only version of the page.
close
Skip to main content
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Microsoft Dynamics CRM (Archived) / Custom entities - expo...
Microsoft Dynamics CRM (Archived)

Custom entities - export to excel issue/ 'where' clause not included

(0) ShareShare
ReportReport
Posted on by Kimberley Bolton

MS CRM 2015 0.2 On-prem

Hi everyone,

We've recently noticed an issue whereby using the "Export Activities" button on custom entities produces incorrect data.

We can replicate the issue on all three custom activities, however not on any of the out-of-the-box entities.

To begin we navigate to the custom entity, open one of the records and go to activities.
As expected we see a list of (13) records, in the "Open Activity Associated View" (can replicate with all views - using this one as an example).
Then we press the "Export Activities" button or tick all records in the view, click the ellipsis button and then click "Export Activities", export the sheet as static or dynamic (both produce the same issue) and open the sheet in excel.

The data displayed is incorrect, in some cases 50 records are shown and in some cases 1000 records are shown.
We've ran all the activities views in the system and none give us the results the export does, so we have no idea where they are coming from!

We've run a trace at the time of export and can see the below query is firing:

>select

top 251 "activitypointer0".ActivityTypeCode as "activitytypecode"

, "activitypointer0".Subject as "subject"

, "activitypointer0".StateCode as "statecode"

, "activitypointer0".CreatedBy as "createdby"

, "activitypointer0".RegardingObjectId as "regardingobjectid"

, "activitypointer0".ActivityId as "activityid"

, "activitypointer0".PriorityCode as "prioritycode"

, "activitypointer0".ScheduledEnd as "scheduledend"

, "activitypointer0".InstanceTypeCode as "instancetypecode"

, "activitypointer0".Community as "community"

, "activitypointer0".ModifiedOn as "modifiedon"

, "activitypointer0".CreatedByYomiName as "createdbyyominame"

, "activitypointer0".CreatedByName as "createdbyname"

, "activitypointer0".RegardingObjectIdYomiName as "regardingobjectidyominame"

, "activitypointer0".RegardingObjectTypeCode as "regardingobjecttypecode"

, "activitypointer0".RegardingObjectIdName as "regardingobjectidname"

from

ActivityPointer as "activitypointer0" order by

"activitypointer0".Subject asc

, "activitypointer0".ActivityId asc


Notice there is no where clause included.

Then we did the same but with an out of the box entity (case) and the below query was used:

>select

"sdkmessagerequest0".SdkMessageRequestId as "sdkmessagerequestid"

, "sdkmessagerequest0".CustomizationLevel as "customizationlevel"

, "sdkmessagerequest0".SdkMessagePairId as "sdkmessagepairid"

, "sdkmessagerequest0".PrimaryObjectTypeCode as "primaryobjecttypecode"

from

SdkMessageRequest as "sdkmessagerequest0" join SdkMessagePair as "sdkmessagepair1" on ("sdkmessagerequest0".SdkMessagePairId  =  "sdkmessagepair1".SdkMessagePairId) join SdkMessage as "sdkmessage2" on ("sdkmessagepair1".SdkMessageId  =  "sdkmessage2".SdkMessageId)

where

(("sdkmessagerequest0".Name = 'RetrieveEntityXml')) and (("sdkmessagepair1".Endpoint = '2011/Organization.svc'))

This time a where clause is used, which we think is the issue.

I have also tested in a CRM 2016 environment and incorrect data is shown again for custom entities, although I haven't run a trace and looked at the query that is running just yet so can't confirm if the behaviour is exactly the same.

Has anyone seen this behaviour before? Is anything needed on custom entities in order for the "Export Activities" button to work as expected?

Any info/ advice would be appreciated!

Thanks,
Kimberley.

*This post is locked for comments

I have the same question (0)
  • Community Member Profile Picture
    Community Member on at
    RE: Custom entities - export to excel issue/ 'where' clause not included

    Well, i think you are watching the wrong query in the trace. May I know your second query is related to which OOB entity ? The second query may be a precursor to the actual query.

  • Kimberley Bolton Profile Picture
    Kimberley Bolton on at
    RE: Custom entities - export to excel issue/ 'where' clause not included

    Hi Purbang, as mentioned the OOB entity is case.

  • Verified answer
    Kimberley Bolton Profile Picture
    Kimberley Bolton on at
    RE: Custom entities - export to excel issue/ 'where' clause not included

    Microsoft have confirmed the above is a known issue which will be resolved in the MS CRM 2015 0.3 update.

    "Our escalation team considered this issue as known issue/product defect.

    Basically what happens is – following query is executed to retrieve the data that will be exported to Excel:

    select

    top 51 "activitypointer0".ActivityTypeCode as "activitytypecode"

    , "activitypointer0".Subject as "subject"

    , "activitypointer0".StateCode as "statecode"

    , "activitypointer0".CreatedBy as "createdby"

    , "activitypointer0".RegardingObjectId as "regardingobjectid"

    , "activitypointer0".ActivityId as "activityid"

    , "activitypointer0".PriorityCode as "prioritycode"

    , "activitypointer0".ScheduledEnd as "scheduledend"

    , "activitypointer0".InstanceTypeCode as "instancetypecode"

    , "activitypointer0".Community as "community"

    , "activitypointer0".ModifiedOn as "modifiedon"

    , "activitypointer0".CreatedByName as "createdbyname"

    , "activitypointer0".CreatedByYomiName as "createdbyyominame"

    , "activitypointer0".RegardingObjectIdYomiName as "regardingobjectidyominame"

    , "activitypointer0".RegardingObjectTypeCode as "regardingobjecttypecode"

    , "activitypointer0".RegardingObjectIdName as "regardingobjectidname"

    from

    ActivityPointer as "activitypointer0"

    where

    (((("activitypointer0".IsRegularActivity = 1 and (("activitypointer0".StateCode = 0 or "activitypointer0".StateCode = 3)))))) order by

    "activitypointer0".Subject asc

    , "activitypointer0".ActivityId asc

    This query returns all open Activities from the CRM Organization as it doesn’t contain any additional filtering. (this is why you see more records in Excel than you see in the Associated View)

    In the past ( CRM 2015 UR 0.1)  following query gets generated:

    select

    DISTINCT top 51 "activitypointer0".ActivityTypeCode as "activitytypecode"

    , "activitypointer0".Subject as "subject"

    , "activitypointer0".StateCode as "statecode"

    , "activitypointer0".CreatedBy as "createdby"

    , "activitypointer0".RegardingObjectId as "regardingobjectid"

    , "activitypointer0".ActivityId as "activityid"

    , "activitypointer0".PriorityCode as "prioritycode"

    , "activitypointer0".ScheduledEnd as "scheduledend"

    , "activitypointer0".InstanceTypeCode as "instancetypecode"

    , "activitypointer0".Community as "community"

    , "activitypointer0".CreatedByName as "createdbyname"

    , "activitypointer0".CreatedByYomiName as "createdbyyominame"

    , "activitypointer0".RegardingObjectIdYomiName as "regardingobjectidyominame"

    , "activitypointer0".RegardingObjectTypeCode as "regardingobjecttypecode"

    , "activitypointer0".RegardingObjectIdName as "regardingobjectidname"

    from

    ActivityPointer as "activitypointer0" join ActivityParty as "activityparty1" on ("activitypointer0".ActivityId = "activityparty1".ActivityId and (("activityparty1".PartyId = 'c585d967-9029-e611-80d7-001dd8b98d3e')))

    where

    (((("activitypointer0".IsRegularActivity = 1 and (("activitypointer0".StateCode = 0 or "activitypointer0".StateCode = 3)))))) order by

    "activitypointer0".Subject asc

    , "activitypointer0".ActivityId asc

    Here we do a join with the Activity Party table and return all Active Activities for which the parent record is a Party. (and this way we cover all scenarios through the ParticipationTypeMask)

    A similar query is generated (the one containing the JOIN with the ActivityParty) when opening the Open Activities Associated View – this is why when simply opening that view we get the correct data. It breaks when we try to use the Export to Excel functionality.

    For an Out of the box entity (ex Invoice) this returns:

    "select \ntop 51 \"activitypointer0\".ActivityTypeCode as \"activitytypecode\"\r\n,

    \"activitypointer0\".Subject as \"subject\"\r\n,

    \"activitypointer0\".StateCode as \"statecode\"\r\n,

    \"activitypointer0\".CreatedBy as \"createdby\"\r\n,

    \"activitypointer0\".RegardingObjectId as \"regardingobjectid\"\r\n,

    \"activitypointer0\".ActivityId as \"activityid\"\r\n,

    \"activitypointer0\".PriorityCode as \"prioritycode\"\r\n,

    \"activitypointer0\".ScheduledEnd as \"scheduledend\"\r\n,

    \"activitypointer0\".InstanceTypeCode as \"instancetypecode\"\r\n,

    \"activitypointer0\".Community as \"community\"\r\n,

    \"activitypointer0\".ModifiedOn as \"modifiedon\"\r\n,

    \"activitypointer0\".CreatedByName as \"createdbyname\"\r\n,

    \"activitypointer0\".CreatedByYomiName as \"createdbyyominame\"\r\n,

    \"activitypointer0\".RegardingObjectIdYomiName as \"regardingobjectidyominame\"\r\n,

    \"activitypointer0\".RegardingObjectTypeCode as \"regardingobjecttypecode\"\r\n,

    \"activitypointer0\".RegardingObjectIdName as \"regardingobjectidname\" \n

    from\n ActivityPointer as \"activitypointer0\" \n

    where\n ((((\"activitypointer0\".IsRegularActivity = @IsRegularActivity0 and ((\"activitypointer0\".StateCode = @StateCode0 or \"activitypointer0\".StateCode = @StateCode1))))

    and ((\"activitypointer0\".RegardingObjectId = @RegardingObjectId0))))

    order by\n \"activitypointer0\".Subject asc\r\n, \"activitypointer0\".ActivityId asc"

    Here we filter after the RegardingObjectId – which should return all associated Activities, unless the entity is configured to be also an Activity Party – then it will return less data than expected

    The fix for this issue will be published in the 2015 0.3 version."

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

News and Announcements

Join experts in person to explore low code and AI agents at the Power Platform Community Conference sponsored by Microsoft

Quick Links

Responsible AI policies

As AI tools become more common, we’re introducing a Responsible AI Use…

Abhilash Warrier – Community Spotlight

We are honored to recognize Abhilash Warrier as our Community Spotlight honoree for…

Congratulations to the September Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > 🔒一 Microsoft Dynamics CRM (Archived)

#1
HR-09070029-0 Profile Picture

HR-09070029-0 2

#1
UllrSki Profile Picture

UllrSki 2

#3
ED-30091530-0 Profile Picture

ED-30091530-0 1

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans