Client had some USER ID Saved favorites from standard smartlists and no one can see their saved smartlists anymore, under the object. When they go to save a new favorite, they get an error that that name already exists. Also, they saved these favorites to their home page under my reports.
1. How can we possibly see or find the original User id saved smartlists?
2. What could have happened to those smartlists?
3. How do we remove these favorites from their Home Page?
Additional issue, their are no columns appearing in the payables transaction smartlist at all. When you click on the default Payables Transaction favorite nothing appears and when you click on columns it is blank. If you go to Smartlist Options their are columns marked to be displayed. Any thoughts on the issue or how to resolve the issue?
What is the version of Smartlist Builder you have, have you upgraded when you went to GP 2013. If your GP 2013 the SLB should be Version: 12.00.0067 and 2013 R2 should be - Version: 12.00.0086.
Working with SmartLists can be sometimes tricky, especially when you had users that were pretty creative and kept them for themselves, but at some point left the company.
I had such a challenge way back in 2009 and created a few SQL scripts for that purpose, allowing me to do a clean-up and re-assign some of the saved smartlists to other users or to the system/company level.
The one bellow will allow you to get a picture based on various criterias (the field USERID & Favorite_Name being the most obvious).
/*This DTS allows to make different queries in the SmartList table of GPSome values to know : ASI_Favorite_Save_Level = 0 => ??? (seems to be some default system SL) private unless secured by a Task ID 1 => System 2 => Company 3 => User Class 4 => User ID(c) B. Bucher / 2009-11-11*/USE DYNAMICSSELECT ASIEXP81.CMPANYID, ASIEXP81.USRCLASS, ASIEXP81.USERID, SY01400.USERID AS GP_User, ASIEXP81.ASI_Favorite_Name, ASIEXP81.ASI_Favorite_Type, ASIEXP81.ASI_Favorite_Save_Level, ASIEXP81.ASI_Favorite_Dict_IDFROM ASIEXP81 LEFT OUTER JOIN SY01400 ON ASIEXP81.USERID = SY01400.USERIDWHERE --(SY01400.USERID IS not NULL) AND --(ASIEXP81.USERID = 'dummy5') (ASI_Favorite_Name like '%Employee%') -- (ASIEXP81.USRCLASS <> '') -- ASIEXP81.ASI_Favorite_Save_Level = 4 -- ASIEXP81.ASI_Favorite_Type = 80 -- ASIEXP81.ASI_Favorite_Dict_ID = 3830
You need to have access to SQL Server Management Studio to run the query. If you don't have, ask someone from you IT department to run them for you.. or you can also use Excel for that purpose.. just you won't be able to do any updates outside of SSMS.
You need to change the ASI_Favorite_Save_Level in ASIEXP81 form 4 to1. However, you also need to remove the USERID--it must be blank. Also,when you covert the favorites to System visibility, you cannot have anyduplicates--such as two Customer favorites called "test". Here's one way todo this: -- STEP (1) make all of that user's Favorites name unique. In this case I'll prefix-- all of the user's favorites with "X_" update dynamics..asiexp81set ASI_Favorite_Name = 'X_'+ASI_Favorite_Namewhere USERID = 'buchb' GO-- STEP (2) Convert the favorites to be visible to the system update dynamics..asiexp81set ASI_Favorite_Save_Level =1 whereASI_Favorite_Save_Level = 4and USERID = 'buchb' GO-- STEP (3) Lastly, remove the User ID from the converted favoritesupdate dynamics..asiexp81set USERID = '' whereASI_Favorite_Name like 'X_%'and USERID = 'buchb' GO-- Change the USER-CLASS assignment for a give SL (i.e. change of users in security)update dynamics..asiexp81set USRCLASS = 'FT_IT_HD' whereASI_Favorite_Name like 'IT Help Desk%'and USRCLASS = 'OPEN_ACCESS' GO
Smartlists saved as Visible to User automatically are added to that user's My Reports list on the home page. Changing that favourite to be visible to something else (i.e. modify the favourite) should remove it from the My Reports area. However, if some of those favourites are from prior to upgrading to GP 2013 or later, they are actually "stuck" on the My Reports section and need a script to remove them, as you won't see them on that users' My Reports list if you go to edit the list.
I haven't looked at Beat's specific script but check out what he's got there to find some answers on where to find them.
Did Beat's script work for you?
Hi Jen, were you able to find a way to remove the orphaned favorite SmartList from the user's My Reports page?
Yes, I did but we've upgraded since so I am not sure I have the script any longer. Let me check when I get into work tomorrow and try to find them. I'm sure we can figure something out!
Found it, thank god for keeping old emails! I will actually write a blog about this as I think it may be useful for long term reference!
My scenario was this: (just to make sure we have the same situation, otherwise the solution may not be relevant).
What I did was this: I had to get a screenshot from the user(s) who were affected, so I knew names of the favourites I needed to look for and remove (in case I wasn't removing all things for a given userID). Script 2 is assuming you are identifying the dex row IDs from the first script and using those as the filter on what to remove.
-- script 1 - check the table and find the records to remove
select * from DYNAMICS..SY70700 where USERID = 'XYZ'
-- script 2 - assuming you obtain the DEX_ROW_ID of the items to be removed, use this to remove them
delete DYNAMICS..SY70700 where DEX_ROW_ID in (1, 2, 3)
I hope this helps or at least narrows it down a bit on where to start. As always, if you have a test environment (completely separate full install), that is ideal to test and if not, ensure you take a backup before you run any delete statements (or run a SELECT INTO statement to "copy" the SY70700 table's existing contents before you begin in case you need to insert a record back).
Thank you Jen - I will start in test first and take all sorts of copies/backups before doing so in production. Thank you very much for being a hoarder...I am one as well, when it comes to issues such as these. ;-D
To follow up on Jen's post; I have used that same exact script, and may have even gotten it from her:-) (I know Aaron Back had some posts about this on GPUG.com too!)
After running the script, I've had my users remove the reports from the Home Page, then log out of GP, and when they log back into GP, I have them add back any reports that they need on the Home Page.
Business Applications communities