Fetch XML count records in link-entity

(0) Share

Report

Posted on by Community Member

I am trying to write a report that lists our CRM users and the number of times (from the Audit entity) that they have connected between 2 dates.

The link from the systemuser entity to the audit entity, works when I make it select the records from the audit entity, but due to the number of records returned, I just want a count of the audit records.

I can't get the syntax correct for the groupby/count clauses, can anyone see where I've gone wrong.

        <value>@StartDate</value>
        <value>@EndDate</value>
    </condition>

</filter>

</link-entity>
</entity>

</fetch>

*This post is locked for comments

I have the same question (0)

All responses (6)

Answers (3)

Rajkumar Rajaraman on at

Like (0)

Report

Refer this:

stackoverflow.com/.../fetchxml-get-count-of-records-based-on-a-field-of-a-linked-entity

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Thanks, but I had already tried both the answers in the stackoverflow post, neither work for me.

Was this reply helpful? Yes No
Suggested answer

Abby Kong 6 on at

Like (0)

Report
Hi Steve,

Tested below fetchxml works, see highlighted the part I changed.

Also, I'm not sure what kind of date range you are looking at, please beware the record limitation of fetchxml. Read point 9 of this article:

http://www.kingswaysoft.com/blog/2013/06/18/Limitations-with-CRM-FetchXML

<fetch distinct='false' no-lock='false' mapping='logical' aggregate='true' > <entity name='systemuser'> <attribute name='fullname' alias='fullname' groupby='true' /> <filter type='and'> <condition attribute='siteid' operator='not-null' /> <condition attribute='siteid' operator='ne' value='' /> <condition attribute='isdisabled' operator='eq' value='0' /> </filter> <link-entity name='audit' from='objectid' to='systemuserid' alias='Audit' aggregate='true' link-type='outer' > <attribute name='createdon' aggregate='count' alias='RecC' /> <filter type='and'> <condition attribute='action' operator='eq' value='64'/> <condition attribute='createdon' operator='on-or-after' value='2018-01-01'/> <condition attribute='createdon' operator='on-or-before' value='2018-03-01'/> </filter> </link-entity> </entity> </fetch>

Regards,
Abby

Was this reply helpful? Yes No
Verified answer

RaviKashyap 55,410 Moderator on at

Like (0)

Report

Hi Steve,

You can try the tools available in XRM toolbox to build the sysntact/ fetchxml query and also see the outcome.

www.xrmtoolbox.com/.../Cinteros.Xrm.FetchXmlBuilder

Hope this helps.

Was this reply helpful? Yes No
Verified answer

gdas 50,091 Moderator on at

Like (0)

Report
Hi Steve,

Filter condition for date should be like below.

<filter type="and"> <condition attribute="createdon" operator="on-or-after" value="2017-08-01" /> <condition attribute="createdon" operator="on-or-before" value="2018-03-28" /> </filter>

You can refer below msdn reference to write aggregation and group by.

https://msdn.microsoft.com/en-us/library/gg309565.aspx

Hope this helps.

Was this reply helpful? Yes No
Verified answer

Community Member on at

Like (0)

Report

Thanks guys for your suggestions, I never did solve this so I will give these a try.

Was this reply helpful? Yes No