Customer Engagement & Dynamics CRM Forum

Expand all | Collapse all

Creating a View with Top 100 Records

  • 1.  Creating a View with Top 100 Records

    SILVER CONTRIBUTOR
    Posted 10 days ago
    I may be missing something obvious, but is there a way to create views that show just the top 50 or top 100 records based on estimated revenue? It seems so simple, but I can't seem to find a way to limit the number of records to show in a view based on a count. Please don't laugh at me if I'm missing the obvious. Any help would be greatly appreciated.

    Thanks.

    ------------------------------
    Scott Grimes
    ITW / Hobart
    Troy OH
    ------------------------------
    The first step toward cloud success. - Migrate from CRM to D365 with expert guidance from Microsoft. I'm Ready


  • 2.  RE: Creating a View with Top 100 Records

    TOP CONTRIBUTOR
    Posted 10 days ago
      |   view attached
    Scott,

    If you have access to XrmToolbox, a quick way to accomplish this is with Jonas Rapp's FetchXML Builder tool. You can create a simple advanced find query in CRM for the entity you want such as opportunity. Include the columns you want and save it. Then, in XrmToolbox open the query in the FetchXML builder. In the left pane in the Node Properties there is a top property. If you type in 50 or 100 there, it will modify the fetch xml to limit the results. You can then save it back to CRM either with the same name or a new name.

    There are some other ways I've seen to do this, but this is probably one of the fastest and easiest ways to modify an advanced find view in ways that you can't in the CE designer.

    I have attached a screen shot of an example.

    ------------------------------
    Andy Arndt
    Minitab, Inc.
    State College PA
    ------------------------------

    The first step toward cloud success. - Migrate from CRM to D365 with expert guidance from Microsoft. I'm Ready


  • 3.  RE: Creating a View with Top 100 Records

    SILVER CONTRIBUTOR
    Posted 10 days ago
    Andy,

    Thank you so much for your reply. I used the FetchXML Builder tool in the XRMToolbox to build my query as you described, but how do I then get that query into a view in CRM? I couldn't find a way to build the view using XML or import the XML file.

    Scott

    ------------------------------
    Scott Grimes
    ITW / Hobart
    Troy OH
    ------------------------------

    The first step toward cloud success. - Migrate from CRM to D365 with expert guidance from Microsoft. I'm Ready


  • 4.  RE: Creating a View with Top 100 Records

    SILVER CONTRIBUTOR
    Posted 10 days ago
    Hi,

    You can use View Designer plugin from xrmtoolbox to update view fetchxml.
    https://www.arbelatech.com/insights/blog/creating-system-views-with-complex-not-in-filter-in-dynamics-365-for-customer-engagement.html

    If found helpful, Please mark my answer verified.

    ------------------------------
    Bipin Kumar
    Avanade
    Hyderabad
    ------------------------------

    The first step toward cloud success. - Migrate from CRM to D365 with expert guidance from Microsoft. I'm Ready


  • 5.  RE: Creating a View with Top 100 Records

    TOP CONTRIBUTOR
    Posted 10 days ago
    I hadn't tested this end to end before posting my original suggestion, but I knew it worked in FetchXML Builder to limit the results. As Jan Van Haver posted I thought I just needed to save it back to CRM. When I did that and closed and re-opened FetchXML Builder and re-opened the query, it retained the top clause in the FetchXML. However, it seems like the Advanced Find code in CE v9 online does not read the top clause correctly. The results are not limited when I run the query in Advanced Find. Also, when I choose download FetchXML in Advanced Find, the top clause is not there. When I open the query in XrmToolbox FetchXML Builder, it's there, but not when opened from within CRM. I think it's saving the full query definition including the top clause to the SavedQuery table in CRM, but Advanced Find is ignoring that part of it.

    ------------------------------
    Andy Arndt
    Minitab, Inc.
    State College PA
    ------------------------------

    The first step toward cloud success. - Migrate from CRM to D365 with expert guidance from Microsoft. I'm Ready


  • 6.  RE: Creating a View with Top 100 Records

    GOLD CONTRIBUTOR
    Posted 10 days ago
    Edited by Jan Van Haver 10 days ago
    Scott, it's easier that what you might already have tried: create a view fitting your needs in D365, open FetchXML Builder, then open that view there an click the top node "fetch" (in the top left area). In the first field under Node Properties (bottom left area), enter the number you want (e.g. 50) and then simply save the view using the Save menu options in FetchXML Builder.

    The updated view is then available in D365 right away.

    ------------------------------
    Jan Van Haver
    G Data CyberDefense AG
    Bochum
    ------------------------------

    The first step toward cloud success. - Migrate from CRM to D365 with expert guidance from Microsoft. I'm Ready


  • 7.  RE: Creating a View with Top 100 Records

    SILVER CONTRIBUTOR
    Posted 9 days ago
      |   view attached
    Thank you so much for your suggestion. I did as you suggested, creating a view in D365 that was what I needed, then opened it in FetchXML Builder and entered 100 in the first field under Node Properties. I then executed it in FetchXML Builder and it ran fine and returned only the 100 records that I wanted. I then saved it to  D365 from FetchXML Builder. However, when I go into D365 and bring up the view, I get an Invalid Argument error (see the screen shot attached). Any ideas or suggestions?

    Thanks.

    ------------------------------
    Scott Grimes
    ITW / Hobart
    Troy OH
    ------------------------------

    Attachment(s)

    The first step toward cloud success. - Migrate from CRM to D365 with expert guidance from Microsoft. I'm Ready


  • 8.  RE: Creating a View with Top 100 Records

    GOLD CONTRIBUTOR
    Posted 9 days ago
    Hmm, it might then be different in different versions, Scott. We are currenly using 8.2 On Prem, and I guess your are using the online version? I hope another community member with Online could test this too.

    ------------------------------
    Jan Van Haver
    G Data CyberDefense AG
    Bochum
    ------------------------------

    The first step toward cloud success. - Migrate from CRM to D365 with expert guidance from Microsoft. I'm Ready


  • 9.  RE: Creating a View with Top 100 Records

    TOP CONTRIBUTOR
    Posted 9 days ago
    I have tested a few scenarios and I get different outcomes. Our org hasn't been transitioned yet to Unified Interface (UCI) only, but we are using a Model Driven app to access CE in the UCI as well as still using classic for some things. I noticed that the changes made for the top clause in FetchXMLBuilder are being saved in the query, but various places in CRM interpret it differently. We are online with the latest North America service update for v9. 2020 Release Wave 2 is applied. Here are the different outcomes.

    1. Run the query in FetchXML Builder: returns 100 records (expected result)
    2. In the classic interface, select the Opportunity entity and select the view and it returns 100 records (expected result)
    3. In the UCI, select the Opportunity entity and select the view and it displays the "Invalid Argument" error that Scott attached.
    4. Run advanced find from the classic interface and it returns all records, not limited to 100, but no error.
    5. Run advanced find from the UCI and it returns all records, not limited to 100, but no error.

    This makes me think that the internal code that processes queries and returns results is a little different for Advanced Find vs. when the view is selected from the home screen of an entity. Also, it appears that the UCI has some slightly different code than classic for presenting views from the home screen of an entity.

    This seems like a good candidate for a support case to find out if this is a bug or by design.

    ------------------------------
    Andy Arndt
    Minitab, Inc.
    State College PA
    ------------------------------

    The first step toward cloud success. - Migrate from CRM to D365 with expert guidance from Microsoft. I'm Ready


  • 10.  RE: Creating a View with Top 100 Records

    SILVER CONTRIBUTOR
    Posted 8 days ago
    Thanks everyone for the great ideas, suggestions, and help. As Andy suggested, we're going to submit this as a support case. If I get any additional information, I will post here for everyone to see.

    Thanks again!

    ------------------------------
    Scott Grimes
    ITW / Hobart
    Troy OH
    ------------------------------

    The first step toward cloud success. - Migrate from CRM to D365 with expert guidance from Microsoft. I'm Ready


  • 11.  RE: Creating a View with Top 100 Records

    MICROSOFT MVP
    Posted 8 days ago
    Been interesting to follow this thread, and I only want to add my support to opening a support case with Microsoft and highlighting the different behavior regarding "top" that @Andy Arndt found. The UCI team loves getting that kind of feedback :)​

    ------------------------------
    Jonas Rapp
    MVP
    Sweden
    ------------------------------

    The first step toward cloud success. - Migrate from CRM to D365 with expert guidance from Microsoft. I'm Ready


If you've found this thread useful, dive deeper into User Group community content by role