Today's #TipTuesday post is about troubleshooting an issue with the My Reports home page area/pane and how to remove SmartList favourites from there. The specific scenario here related to favourites that were "stuck" in MyReports after an upgrade to GP 2013 a few years ago. I was reminded of this when I saw this post on the Microsoft Dynamics community forum and someone asked me if I kept the scripts I used to fix this issue.
What's the issue?
After a client's GP 2013 upgrade, a couple of users noted that the SmartLists they had saved under their My Reports pane on the home page weren't working. It turns out the underlying Favourite was missing, and we didn't find out why this happened. Ultimately the important part was we could not remove the reference in My Reports using any out-of-the-box functionality in Dynamics GP. If we clicked in the Edit pencil in MyReports, the items we saw there weren't in the navigation list that came up so we had no way to remove them, within the normal functionality one might use to do so!
What would specifically happen if the user tried to use the shortcut, it would open Smartlist to the right Smartlist "object" (i.e. the "yellow folder level") but not the specific favourite itself that it was linked to. From the user's perspective, the link was broken, it only half worked.
What changed in GP 2013?
Starting with GP 2013, when a user creates a new Smartlist Favourite saved as "Visible To" User, i.e. themselves, it then automatically appears on My Reports. That reference can be removed from My Reports via the edit functionality and it can be removed by changing the Visible To in the Smartlist Favourite to something other than "User" as well.
However, for any items that were there BEFORE the upgrade to GP 2013, there was no option to remove them.
SQL to the rescue!
There are some times when I have no choice but to resort to SQL to address an issue and this is one of those times. The table that stores a user's My Reports pane contents is the SY70700 table. This stores all types of reports, not just SmartLists. Here's a simple query to view a list of what's in the table:
SELECT USERID, CMPANYID, MyReportName, DEX_ROW_ID
FROM DYNAMICS..SY70700
This will return something like this, in my case, a regular GL trial balance report and 2 SmartLists - one added as a favourite visible to the User and one as a manually added MyReport.
If something is stuck, I can add a WHERE clause to the query to narrow it down specifically by USERID, and then cross-check the exact "MyReportName" from a screenshot from the user vs. what's in the table. My preference is to then use the DEX_ROW_ID as the filter for a DELETE script which may look like this, if I want to remove the "Accounts created this month" favourite from My Reports. (I also opt to include the USERID even though I shouldn't need to as DEX_ROW_ID is unique).
DELETE DYNAMICS..SY70700
WHERE USERID = 'sa' and DEX_ROW_ID = 2
If I were using this script for real, I would replace the USERID 'sa' with the user in my system and one or more DEX_ROW_IDs based on the first "SELECT" script. My system database is the default DYNAMICS db and some organizations may have a differently named system database where this table is.
Of course, if I am doing this for real, I want to test my WHERE clause with the select script first. If I am getting only the results I want to see with that, then I should be safe.
The last suggestion, since this is editing a table in the system database, and not all organizations have a separate standalone environment, make a backup and/or use something like this code below to make a quick "table" backup. That way if I delete more than I expect or something doesn't work, the data is there to script back in with an insert statement.
SELECT *
INTO DYNAMICS..SY70700_BACKUP
FROM DYNAMICS..SY70700
When I am done, if all goes well, I can drop the "_backup" table so I don't have clutter in my database.
Hopefully, someone out there finds this useful!
