Customer Engagement & Dynamics CRM Forum

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
------------------------------

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" > </fetch>

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

Original Message:
Sent: Jan 06, 2021 11:24 AM
From: James Walsh
Subject: Retrieve all personal views referencing a specific field

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
------------------------------

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

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

Original Message:
Sent: Jan 07, 2021 10:42 AM
From: Gerry Yurko
Subject: Retrieve all personal views referencing a specific field

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

Original Message:
Sent: Jan 06, 2021 11:24 AM
From: James Walsh
Subject: Retrieve all personal views referencing a specific field

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
------------------------------

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
------------------------------

Original Message:
Sent: Jan 07, 2021 10:42 AM
From: Gerry Yurko
Subject: Retrieve all personal views referencing a specific field

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

Original Message:
Sent: Jan 06, 2021 11:24 AM
From: James Walsh
Subject: Retrieve all personal views referencing a specific field

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
------------------------------

​

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
------------------------------

Original Message:
Sent: Jan 13, 2021 10:50 AM
From: James Walsh
Subject: Retrieve all personal views referencing a specific field

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

Original Message:
Sent: Jan 07, 2021 10:42 AM
From: Gerry Yurko
Subject: Retrieve all personal views referencing a specific field

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

Original Message:
Sent: Jan 06, 2021 11:24 AM
From: James Walsh
Subject: Retrieve all personal views referencing a specific field

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
------------------------------

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" > </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" > </fetch>

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

Original Message:
Sent: Jan 13, 2021 10:50 AM
From: James Walsh
Subject: Retrieve all personal views referencing a specific field

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

Original Message:
Sent: Jan 07, 2021 10:42 AM
From: Gerry Yurko
Subject: Retrieve all personal views referencing a specific field

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

Original Message:
Sent: Jan 06, 2021 11:24 AM
From: James Walsh
Subject: Retrieve all personal views referencing a specific field

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
------------------------------

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
------------------------------

Original Message:
Sent: Jan 15, 2021 11:41 AM
From: Gerry Yurko
Subject: Retrieve all personal views referencing a specific field

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

Original Message:
Sent: Jan 13, 2021 10:50 AM
From: James Walsh
Subject: Retrieve all personal views referencing a specific field

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

Original Message:
Sent: Jan 07, 2021 10:42 AM
From: Gerry Yurko
Subject: Retrieve all personal views referencing a specific field

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

Original Message:
Sent: Jan 06, 2021 11:24 AM
From: James Walsh
Subject: Retrieve all personal views referencing a specific field

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
------------------------------

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
------------------------------

Original Message:
Sent: Jan 06, 2021 11:24 AM
From: James Walsh
Subject: Retrieve all personal views referencing a specific field

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
------------------------------