Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Dynamics 365 Community / Forums / Microsoft Dynamics GP (Archived) / How can you do a Smart...
Microsoft Dynamics GP (Archived)

How can you do a SmartList bulk company change

(0) ShareShare
ReportReport
Posted on by Community Member Microsoft Employee

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

  • Victoria Yudin Profile Picture
    Victoria Yudin 22,766 on at
    Re: Re: How can you do a SmartList bulk company change

    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.

  • Sam Newberry Profile Picture
    Sam Newberry 60 on at
    Re: How can you do a SmartList bulk company change
    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
  • Victoria Yudin Profile Picture
    Victoria Yudin 22,766 on at
    Re: Re: Re: How can you do a SmartList bulk company change

    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? 

  • Sam Newberry Profile Picture
    Sam Newberry 60 on at
    Re: Re: How can you do a SmartList bulk company change
    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
  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    Re: Re: How can you do a SmartList bulk company change

    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
  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    Re: Re: How can you do a SmartList bulk company change

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

  • Victoria Yudin Profile Picture
    Victoria Yudin 22,766 on at
    Re: How can you do a SmartList bulk company change

    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.

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

December Spotlight Star - Muhammad Affan

Congratulations to a top community star!

Top 10 leaders for November!

Congratulations to our November super stars!

Tips for Writing Effective Suggested Answers

Best practices for providing successful forum answers ✍️

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 291,280 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 230,214 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans