How can you do a SmartList bulk company change

(0) Share

Report

Posted on by Community Member

We have GP9

We recently created a second company, restored our original company to the new company, ran the appropriate SQL scripts to make everything work and everything does except one thing. All our custom smart lists which point to the original company still point to the original company. So if we had a SQL view or table in a smart list, the smart list points to comapny 1.

Short of opening each smart list up in smart list builder, deleting the table or views and re-adding them, resetting all the default columns, is there a way to just change the company1 to company2?

Seems like there should be a utility, or SQL trick.

*This post is locked for comments

I have the same question (0)

All responses (7)

Answers (0)

Victoria Yudin 22,769 on at

Like (0)

Report

Jay,

SmartLists are setup up globally in GP for the whole installation, not per company. So if you do open up your SmartLists and change them to Company 2, when you are in Company 1 they will not work properly any more, since they will be pointing to Company2. The proper way to handle this is to check Use Company Database when you are adding the tables/views to your SmartLists.

I have never updated this globally in SQL, so I am not 100% sure if it will work properly, but you can try changing the Use_Current_Company field to 1 in the SLB10100 table (in the DYNAMICS database) for any SmartLists that should be using the database/company the user is currently logged into. If you do end up doing this in SQL, please write back with your results.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Thanks Victoria, that is a great idea. I will let you know how it works.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

OK, I ran the SQL statement, refreshed the smartlist in maintenance, the re opened smart list. Had some users test out the data and everthing is comming back good. So this was definately a good option when changing from one company to two or more and needing to update all smart lists to use the current company rather than the smartlist company.

this is what I ran

Use Dynamics

GO

Update SLB10100 SET Use_Current_Company = 1

Thanks again Victoria.
Jay

Was this reply helpful? Yes No
Sam Newberry 60 on at

Like (0)

Report

Victoria, Your solution fixes the same problem I am looking at fixing but I am on GP 8 until we finish upgrading later this summer. I don't see table SLB10100 in Dynamics or the Company Database. What suggestion or table should we look at to allow a SmartList in Company A to be available in Company B. Thanks, Sam

Was this reply helpful? Yes No
Victoria Yudin 22,769 on at

Like (0)

Report

Sam,

I just looked at my test GP 8.0 installation and I see SLB10100 in the DYNAMICS database. Are you sure you have SmartList Builder installed?

Was this reply helpful? Yes No
Sam Newberry 60 on at

Like (0)

Report

I can get to the SmartList and it is attached to Analytical Accounting. The version of SmartList says 8.00.73 on the Help About. I guess I'm not sure if SmartList Builder is a different add-on?

What I'm attempting to do is take an existing AA Distribution Query SmartList from Company A and make it available on Company B either by copying the SmartList (if possible) or by allowing multiple companies to see the SmartList Object.

Thanks,
Sam

Was this reply helpful? Yes No
Victoria Yudin 22,769 on at

Like (0)

Report

Sam,

You can check if you have SmartList Builder installed by going to Help > About ... GP, clicking on Options and scrolling through the list of Products. SmartList Builder will be a separately listed product.

I don't know about your SmartList in particular, especially if it is something custom that was created for you. However, in general, SmartList objects (whether in SmartList or SmartList Builder) are not company specific. So they can be seen in all companies and are controlled by Security settings. In GP 8.0 if you have a SmartList object (top level, with the plus sign) that is in one company but not another, you should be able to grant access in the other company within Advanced Security. If you have a SmartList Favorite that is in one company and not another, you can modify the Favorite to make it visible to System, User ID or User Class (anything other than Company) and it will be visible in other companies.

Hope this helps.

Was this reply helpful? Yes No