Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Dynamics 365 Community / Forums / Dynamics 365 general forum / Filter on a amount of ...
Dynamics 365 general forum
Answered

Filter on a amount of a related entity.

(0) ShareShare
ReportReport
Posted on by salmy

I'm trying to get a report from customer filtered on related records for example, all customers which opened more then 100 cases during the last year, how can I achieve this, I tried with fetch-xml using aggregation since I wasen't able to filter on the aggregation, also note: both the customer and the related entity contains more than 5000 records.

Categories:
Other Functionality
  • Verified answer
    Bipin D365 Profile Picture
    Bipin D365 28,960 Super User on at
    RE: Filter on a amount of a related entity.

    Hello,

    I'm glad that it worked.

    Could you please mark my answer verified by clicking Yes under 'Did this answer your question?'

    Please mark my answer verified if this is helpful!

    Regards,

    Bipin Kumar

    Follow my Blog: xrmdynamicscrm.wordpress.com/

  • salmy Profile Picture
    salmy on at
    RE: Filter on a amount of a related entity.

    Thanks for the answer, it worked like a charm, and BTW I learned a lot of new things from your blogs, it's amazing.

  • Verified answer
    Bipin D365 Profile Picture
    Bipin D365 28,960 Super User on at
    RE: Filter on a amount of a related entity.

    Hi,

    Have you tried creating a rollup field on Customer which will show count of Opened cases duing last year?

    Then use this filter in your fetchxml.

    Ohhh....We don't have option to filter Case records created during last year...

    I have tried writting below SQL code based on Account (OOB Entity) and Employee (Custom entity)-

    SELECT a.name FROM msft_employee

INNER JOIN account AS a ON msft_employee.msft_company = a.accountid

WHERE msft_employee.statecode = 0 AND msft_employee.createdon = lastxyears(1)

group by a.name having COUNT(a.name)>=100

    I did try to see fetchxml with the help of SQL4CDS plugin in xrmtoolbox but it shows 3 fetchxml -

    markcarrington.dev/.../

    Please mark my answer verified if this is helpful!

    Regards,

    Bipin Kumar

    Follow my Blog: xrmdynamicscrm.wordpress.com/

  • salmy Profile Picture
    salmy on at
    RE: Filter on a amount of a related entity.

    Thanks for your response, I checked the link you posted, but I can't find how to filter on the amount of the retrieved records from the related records, as I wrote I only want to return the customers which had opened more than 100 cases in last year.

  • Verified answer
    mobeenmohsin Profile Picture
    mobeenmohsin on at
    RE: Filter on a amount of a related entity.

    Hi,

    Please find information to get large records using paging as mentioned in https://docs.microsoft.com/en-us/powerapps/developer/data-platform/org-service/page-large-result-sets-with-fetchxml.

    Best Regards,

    Mobeen

Helpful resources

Quick Links

Replay now available! Dynamics 365 Community Call (CRM Edition)

Catch up on the first D365 Community Call held on 7/10

Community Spotlight of the Month

Kudos to Saurav Dhyani!

Congratulations to the June Top 10 community leaders!

These stars go above and beyond . . .

more Community News

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 288,768 Super User

#2
Martin Dráb Profile Picture

Martin Dráb 225,985 Super User

#3
nmaenpaa Profile Picture

nmaenpaa 101,148

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans