Customer Engagement & Dynamics CRM Forum

Expand all | Collapse all

Retrieve all personal views referencing a specific field

  • 1.  Retrieve all personal views referencing a specific field

    Posted 20 days ago
    Context: We are swapping out an important field used in our system. Before pushing this change into production, it would be ideal to have a list of users and their personal views that contain a reference to this field (either a filter or a column). We can then target them for a communication letting them know they need to swap out the field to avoid confusion or consumption of bad data.

    Question: By owning user, is there a way to retrieve a list of all personal views containing a reference to a specific field?

    ------------------------------
    James Walsh
    Burns & McDonnell
    Kansas City
    ------------------------------
    Digital Acceleration Binge Day.  January 28th 2021 | 11:00 AM - 2:00 PM EST


  • 2.  RE: Retrieve all personal views referencing a specific field

    TOP CONTRIBUTOR
    Posted 19 days ago
    Edited by Gerry Yurko 19 days ago
    If you use XrmToolbox then open FetchXML Builder.  Here is a sample Query.  You have to choose the Schema Name, not the Field Name (Label).

    The OwnerIdName searches the User's personal Views.  You can also search System Views by changing userquery to savedquery.

    <fetch top="50" >
    <entity name="userquery" >
    <attribute name="name" />
    <attribute name="owneridname" />
    <attribute name="layoutxml" />
    <filter>
    <condition attribute="owneridname" operator="eq" value="Gerry Yurko" />
    <condition attribute="layoutxml" operator="like" value="%new_churnperiodid%" />
    </filter>
    </entity>
    </fetch>

    ------------------------------
    Gerry Yurko
    CRM Developer
    Crown Castle Fiber Networks
    Boxborough MA
    ------------------------------

    Digital Acceleration Binge Day.  January 28th 2021 | 11:00 AM - 2:00 PM EST


  • 3.  RE: Retrieve all personal views referencing a specific field

    Posted 14 days ago
    Thank you for the response! We'll give this a try and see how it turns out!

    ------------------------------
    James Walsh
    Burns & McDonnell
    Kansas City
    ------------------------------

    Digital Acceleration Binge Day.  January 28th 2021 | 11:00 AM - 2:00 PM EST


  • 4.  RE: Retrieve all personal views referencing a specific field

    Posted 13 days ago
    After looking into this a bit further, we cannot necessarily put in the name/s of users because we do not know which ones have a personal view that contains a reference to a particular field.

    Is there a way to leverage the same query that will return a list of users and their personal views that reference a specific field?

    ------------------------------
    James Walsh
    Burns & McDonnell
    Kansas City
    ------------------------------

    Digital Acceleration Binge Day.  January 28th 2021 | 11:00 AM - 2:00 PM EST


  • 5.  RE: Retrieve all personal views referencing a specific field

    Posted 12 days ago

    If your CRM is on premise, the following SQL query should get you the information that you are looking for:

    SELECT Name,OwnerIdName,CreatedOn,FetchXml,UserQueryId

    FROM UserQuery

    WHERE FetchXml like '%entity name="InsertEntitySchemaNameHere"%' and FetchXml like '%InsertFieldSchemaNameHere%'

    ORDER BY OwnerIdName

     

    The query is filtered on the field in question, but also the entity in case you have fields that are named the same on different entities.

    To take this a step further (if you would like to delete a view and confirm that it is not used in a dashboard) you can take the resulting UserQueryId from the query above and look for it in the UserForm (Personal Dashboards) or SystemForm (System Dashboards) views.

    SELECT name,OwnerIdName,CreatedOn,FormXml

    FROM UserForm

    WHERE FormXml like '%InsertUserQueryIdHere%'

    ORDER BY OwnerId

     

    I do not have experience with FetchXML, but the query that Gerry provided looks very similar. I am wondering if removing the line with owneridename would work.

     <condition attribute="owneridname" operator="eq" value="Gerry Yurko" />



    ------------------------------
    Kim Jopp
    Opus Group
    MN
    ------------------------------

    Digital Acceleration Binge Day.  January 28th 2021 | 11:00 AM - 2:00 PM EST


  • 6.  RE: Retrieve all personal views referencing a specific field

    TOP CONTRIBUTOR
    Posted 11 days ago
    I agree with @Kim Jopp that a SQL Query for On-Prem can easily pull the results.  But if you are online that is not possible​.  Perhaps @Jonas Rapp can shed some light on why I can't see all User Views in FetchXML Builder, even though I am System Administrator.

    I have updated the Fetch a bit because even though I asked for OwnerIdName on the original Fetch, it doesn't display on the results.  This Fetch will dispay the User Name in its own Column.

    <fetch top="500" >
    <entity name="systemuser" >
    <attribute name="fullname" />
    <link-entity name="userquery" from="ownerid" to="systemuserid" link-type="inner" >
    <attribute name="name" />
    <attribute name="fetchxml" />
    </filter>
    <condition attribute="fetchxml" operator="like" value="%%Opportunityid%%" />
    </filter>
    </link-entity>
    </entity>
    </fetch>

    When I run the Fetch I do not pull as many Records as I expected, even though I am System Admin in CRM.  Something native to FetchXml is preventing me from seeing all Queries.

    When I change the Fetch to pull in all User Queries for a specific User I get zero results even though SQL says he has 247 Queries.

    <fetch top="500" >
    <entity name="systemuser" >
    <attribute name="fullname" />
    <filter>
    <condition attribute="fullname" operator="like" value="%Jeff M%" />
    </filter>
    <link-entity name="userquery" from="ownerid" to="systemuserid" link-type="inner" >
    <attribute name="name" />
    <attribute name="fetchxml" />
    </link-entity>
    </entity>
    </fetch>

    ------------------------------
    Gerry Yurko
    CRM Developer
    Crown Castle Fiber Networks
    Boxborough MA
    ------------------------------

    Digital Acceleration Binge Day.  January 28th 2021 | 11:00 AM - 2:00 PM EST


  • 7.  RE: Retrieve all personal views referencing a specific field

    TOP CONTRIBUTOR
    Posted 9 days ago
    Security roles don't give you the option to see other people's saved views - even the System Administrator role only has user-level access. This isn't anything FetchXML Builder specific.

    As a system administrator you can impersonate other users, so you may be able to write an application that loops over all users, impersonates them and runs the query to see if they have any views with the required attribute.

    ------------------------------
    Mark Carrington
    Chief Technologist
    Data8
    Chester
    ------------------------------

    Digital Acceleration Binge Day.  January 28th 2021 | 11:00 AM - 2:00 PM EST


  • 8.  RE: Retrieve all personal views referencing a specific field

    Posted 11 days ago
    Hi James,

    Taking Gerry's approach and running with it I made the following modifications:
    - Added a link to the systemuser table to get the user's name
    - Added the returnedtypecode to see what entity the view is against since fields of parent records can show as columns or filters on child record views
    - Added querytype filter to restrict to user created views as there are other personal views that are system generated (My Active Accounts)
    - Switched layoutxml to fetchxml so that you can find the field used as a column or as a filter

    <fetch>
      <entity name="userquery" >
        <attribute name="name" />
        <attribute name="returnedtypecode" />
        <attribute name="fetchxml" />
        <filter>
          <condition attribute="querytype" operator="eq" value="0" />
          <condition attribute="fetchxml" operator="like" value="%new_tier%" />
        </filter>
        <link-entity name="systemuser" from="systemuserid" to="createdby" >
          <attribute name="fullname" />
        </link-entity>
      </entity>
    </fetch>​


    ------------------------------
    Scott Florance
    Business Applications Consultant
    KTL Solutions, Inc, Frederick MD
    ------------------------------

    Digital Acceleration Binge Day.  January 28th 2021 | 11:00 AM - 2:00 PM EST


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