GP2013 Smartlist Questions

(0) Share

Report

Posted on by Lisa Hunter

1,616

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?

Thank you!

*This post is locked for comments

I have the same question (0)

All responses (9)

Answers (0)

Sort by

Suggested answer

babubaskaran@outloo... 12 on at

Like (0)

Report
Copy link

Link copied!

Hi Lisa,

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.

Was this reply helpful? Yes No
Suggested answer

Beat Bucher GP Gee... 28,058 Moderator on at

Like (0)

Report
Copy link

Link copied!

Hi Lisa,

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 GP
Some 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 DYNAMICS
SELECT     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_ID
FROM         ASIEXP81 LEFT OUTER JOIN
                      SY01400 ON ASIEXP81.USERID = SY01400.USERID
WHERE      --(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 to
1. However, you also need to remove the USERID--it must be blank. Also,
when you covert the favorites to System visibility, you cannot have any
duplicates--such as two Customer favorites called "test". Here's one way to
do 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..asiexp81
set ASI_Favorite_Name = 'X_'+ASI_Favorite_Name
where USERID = 'buchb'
GO
-- STEP (2) Convert the favorites to be visible to the system
update dynamics..asiexp81
set ASI_Favorite_Save_Level =1 where
ASI_Favorite_Save_Level = 4
and USERID = 'buchb'
GO
-- STEP (3) Lastly, remove the User ID from the converted favorites

update dynamics..asiexp81
set USERID = '' where
ASI_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..asiexp81
set USRCLASS = 'FT_IT_HD' where
ASI_Favorite_Name like 'IT Help Desk%'
and USRCLASS = 'OPEN_ACCESS'
GO

Was this reply helpful? Yes No
Jen Kuntz 3,220 on at

Like (0)

Report
Copy link

Link copied!

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.

Was this reply helpful? Yes No
L Vail 65,271 on at

Like (0)

Report
Copy link

Link copied!

Lisa,

Did Beat's script work for you?

Kind regards,

Leslie

Was this reply helpful? Yes No
Kristie McNulty 1,008 on at

Like (0)

Report
Copy link

Link copied!

Hi Jen, were you able to find a way to remove the orphaned favorite SmartList from the user's My Reports page?

Was this reply helpful? Yes No
Jen Kuntz 3,220 on at

Like (0)

Report
Copy link

Link copied!

Hi Kristie,

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!

Jen

Was this reply helpful? Yes No
Jen Kuntz 3,220 on at

Like (1)

Report
Copy link

Link copied!
Hi again,

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

The "My Reports" pane on my home page in GP had reports that were Smartlists "visible to user" but once we upgraded to GP 2013, those old references could not be removed, even if I removed the Smartlist Favourite. It was specifically favourites saved pre-GP2013 upgrade visible to user that were the problem. We did not save anything as visible to user class but I suspect the same issue might have occurred there.

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

Jen

Was this reply helpful? Yes No
Kristie McNulty 1,008 on at

Like (1)

Report
Copy link

Link copied!

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

Was this reply helpful? Yes No
Frank_Heslin 895 on at

Like (1)

Report
Copy link

Link copied!

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.

Was this reply helpful? Yes No