Microsoft Product Support

  • Subscribe to our RSS feed.
  • Twitter
  • StumbleUpon
  • Reddit
  • Facebook
  • Digg

Monday, 24 October 2011

Copying Smartlists favorites from one user to another

Posted on 08:56 by Unknown
I get this question asked very often and sometimes have to dig up my notes on the subject, so I figured I would just post the SQL script that I use for this.

Smartlist favorites are traditionally stored in the ASIEXP81 table on a user-company basis. It's important to keep this in mind, as you can tailor this script to copy favorites from one user to another in the same company or the same user across various companies. However, this script replicates all favorites, across all companies for one user to another user.

USE DYNAMICS;
GO
-- remove any records for the destination user prior to synchronizing
DELETE FROM ASIEXP81 WHERE USERID = '<DestinationUserID>';

-- execute synchronization
INSERT INTO ASIEXP81
           (ASI_Favorite_Dict_ID
           ,ASI_Favorite_Type
           ,ASI_Favorite_Save_Level
           ,CMPANYID
           ,USRCLASS
           ,USERID
           ,ASI_Favorite_Name
           ,ASI_Field_Dict_ID_1
           ,ASI_Field_Dict_ID_2
           ,ASI_Field_Dict_ID_3
           ,ASI_Field_Dict_ID_4
           ,ASI_Field_List_1
           ,ASI_Field_List_2
           ,ASI_Field_List_3
           ,ASI_Field_List_4
           ,ASI_Search_Type_1
           ,ASI_Search_Type_2
           ,ASI_Search_Type_3
           ,ASI_Search_Type_4
           ,ASI_Match_Case_1
           ,ASI_Match_Case_2
           ,ASI_Match_Case_3
           ,ASI_Match_Case_4
           ,ASI_Start_Account_Number_1_1
           ,ASI_Start_Account_Number_1_2
           ,ASI_Start_Account_Number_1_3
           ,ASI_Start_Account_Number_1_4
           ,ASI_Start_Account_Number_1_5
           ,ASI_Start_Account_Number_1_6
           ,ASI_Start_Account_Number_1_7
           ,ASI_Start_Account_Number_1_8
           ,ASI_Start_Account_Number_1_9
           ,ASI_Start_Account_Number_1_10
           ,ASI_Start_Account_Number_2_1
           ,ASI_Start_Account_Number_2_2
           ,ASI_Start_Account_Number_2_3
           ,ASI_Start_Account_Number_2_4
           ,ASI_Start_Account_Number_2_5
           ,ASI_Start_Account_Number_2_6
           ,ASI_Start_Account_Number_2_7
           ,ASI_Start_Account_Number_2_8
           ,ASI_Start_Account_Number_2_9
           ,ASI_Start_Account_Number_2_10
           ,ASI_Start_Account_Number_3_1
           ,ASI_Start_Account_Number_3_2
           ,ASI_Start_Account_Number_3_3
           ,ASI_Start_Account_Number_3_4
           ,ASI_Start_Account_Number_3_5
           ,ASI_Start_Account_Number_3_6
           ,ASI_Start_Account_Number_3_7
           ,ASI_Start_Account_Number_3_8
           ,ASI_Start_Account_Number_3_9
           ,ASI_Start_Account_Number_3_10
           ,ASI_Start_Account_Number_4_1
           ,ASI_Start_Account_Number_4_2
           ,ASI_Start_Account_Number_4_3
           ,ASI_Start_Account_Number_4_4
           ,ASI_Start_Account_Number_4_5
           ,ASI_Start_Account_Number_4_6
           ,ASI_Start_Account_Number_4_7
           ,ASI_Start_Account_Number_4_8
           ,ASI_Start_Account_Number_4_9
           ,ASI_Start_Account_Number_4_10
           ,ASI_Start_Date_Token_DDL_1
           ,ASI_Start_Date_Token_DDL_2
           ,ASI_Start_Date_Token_DDL_3
           ,ASI_Start_Date_Token_DDL_4
           ,ASI_Start_Date_1
           ,ASI_Start_Date_2
           ,ASI_Start_Date_3
           ,ASI_Start_Date_4
           ,ASI_Start_DDL_1
           ,ASI_Start_DDL_2
           ,ASI_Start_DDL_3
           ,ASI_Start_DDL_4
           ,ASI_String_Start_1
           ,ASI_String_Start_2
           ,ASI_String_Start_3
           ,ASI_String_Start_4
           ,ASI_End_Account_Number_1_1
           ,ASI_End_Account_Number_1_2
           ,ASI_End_Account_Number_1_3
           ,ASI_End_Account_Number_1_4
           ,ASI_End_Account_Number_1_5
           ,ASI_End_Account_Number_1_6
           ,ASI_End_Account_Number_1_7
           ,ASI_End_Account_Number_1_8
           ,ASI_End_Account_Number_1_9
           ,ASI_End_Account_Number_1_10
           ,ASI_End_Account_Number_2_1
           ,ASI_End_Account_Number_2_2
           ,ASI_End_Account_Number_2_3
           ,ASI_End_Account_Number_2_4
           ,ASI_End_Account_Number_2_5
           ,ASI_End_Account_Number_2_6
           ,ASI_End_Account_Number_2_7
           ,ASI_End_Account_Number_2_8
           ,ASI_End_Account_Number_2_9
           ,ASI_End_Account_Number_2_10
           ,ASI_End_Account_Number_3_1
           ,ASI_End_Account_Number_3_2
           ,ASI_End_Account_Number_3_3
           ,ASI_End_Account_Number_3_4
           ,ASI_End_Account_Number_3_5
           ,ASI_End_Account_Number_3_6
           ,ASI_End_Account_Number_3_7
           ,ASI_End_Account_Number_3_8
           ,ASI_End_Account_Number_3_9
           ,ASI_End_Account_Number_3_10
           ,ASI_End_Account_Number_4_1
           ,ASI_End_Account_Number_4_2
           ,ASI_End_Account_Number_4_3
           ,ASI_End_Account_Number_4_4
           ,ASI_End_Account_Number_4_5
           ,ASI_End_Account_Number_4_6
           ,ASI_End_Account_Number_4_7
           ,ASI_End_Account_Number_4_8
           ,ASI_End_Account_Number_4_9
           ,ASI_End_Account_Number_4_10
           ,ASI_End_Date_Token_DDL_1
           ,ASI_End_Date_Token_DDL_2
           ,ASI_End_Date_Token_DDL_3
           ,ASI_End_Date_Token_DDL_4
           ,ASI_End_Date_1
           ,ASI_End_Date_2
           ,ASI_End_Date_3
           ,ASI_End_Date_4
           ,ASI_End_DDL_1
           ,ASI_End_DDL_2
           ,ASI_End_DDL_3
           ,ASI_End_DDL_4
           ,ASI_String_End_1
           ,ASI_String_End_2
           ,ASI_String_End_3
           ,ASI_String_End_4
           ,ASI_Search_From_Str_1
           ,ASI_Search_From_Str_2
           ,ASI_Search_From_Str_3
           ,ASI_Search_From_Str_4
           ,ASI_Search_To_Str_1
           ,ASI_Search_To_Str_2
           ,ASI_Search_To_Str_3
           ,ASI_Search_To_Str_4
           ,ASI_Search_Logic_Type
           ,ASI_Max_Records
           ,ASI_Sort_Dict_ID
           ,ASI_Sort_Field
           ,ASI_Sort_Type
           ,ASI_Field_Comparison_1
           ,ASI_Field_Comparison_2
           ,ASI_Field_Comparison_3
           ,ASI_Field_Comparison_4
           ,ASI_Start_Comp_Field_ID_1
           ,ASI_Start_Comp_Field_ID_2
           ,ASI_Start_Comp_Field_ID_3
           ,ASI_Start_Comp_Field_ID_4
           ,ASI_Start_Comp_Field_Dic_1
           ,ASI_Start_Comp_Field_Dic_2
           ,ASI_Start_Comp_Field_Dic_3
           ,ASI_Start_Comp_Field_Dic_4
           ,ASI_End_Comp_Field_ID_1
           ,ASI_End_Comp_Field_ID_2
           ,ASI_End_Comp_Field_ID_3
           ,ASI_End_Comp_Field_ID_4
           ,ASI_End_Comp_Field_Dict_1
           ,ASI_End_Comp_Field_Dict_2
           ,ASI_End_Comp_Field_Dict_3
           ,ASI_End_Comp_Field_Dict_4)
SELECT ASI_Favorite_Dict_ID
      ,ASI_Favorite_Type
      ,ASI_Favorite_Save_Level
      ,CMPANYID
      ,USRCLASS
      ,'<DestinationUserID>'
      ,ASI_Favorite_Name
      ,ASI_Field_Dict_ID_1
      ,ASI_Field_Dict_ID_2
      ,ASI_Field_Dict_ID_3
      ,ASI_Field_Dict_ID_4
      ,ASI_Field_List_1
      ,ASI_Field_List_2
      ,ASI_Field_List_3
      ,ASI_Field_List_4
      ,ASI_Search_Type_1
      ,ASI_Search_Type_2
      ,ASI_Search_Type_3
      ,ASI_Search_Type_4
      ,ASI_Match_Case_1
      ,ASI_Match_Case_2
      ,ASI_Match_Case_3
      ,ASI_Match_Case_4
      ,ASI_Start_Account_Number_1_1
      ,ASI_Start_Account_Number_1_2
      ,ASI_Start_Account_Number_1_3
      ,ASI_Start_Account_Number_1_4
      ,ASI_Start_Account_Number_1_5
      ,ASI_Start_Account_Number_1_6
      ,ASI_Start_Account_Number_1_7
      ,ASI_Start_Account_Number_1_8
      ,ASI_Start_Account_Number_1_9
      ,ASI_Start_Account_Number_1_10
      ,ASI_Start_Account_Number_2_1
      ,ASI_Start_Account_Number_2_2
      ,ASI_Start_Account_Number_2_3
      ,ASI_Start_Account_Number_2_4
      ,ASI_Start_Account_Number_2_5
      ,ASI_Start_Account_Number_2_6
      ,ASI_Start_Account_Number_2_7
      ,ASI_Start_Account_Number_2_8
      ,ASI_Start_Account_Number_2_9
      ,ASI_Start_Account_Number_2_10
      ,ASI_Start_Account_Number_3_1
      ,ASI_Start_Account_Number_3_2
      ,ASI_Start_Account_Number_3_3
      ,ASI_Start_Account_Number_3_4
      ,ASI_Start_Account_Number_3_5
      ,ASI_Start_Account_Number_3_6
      ,ASI_Start_Account_Number_3_7
      ,ASI_Start_Account_Number_3_8
      ,ASI_Start_Account_Number_3_9
      ,ASI_Start_Account_Number_3_10
      ,ASI_Start_Account_Number_4_1
      ,ASI_Start_Account_Number_4_2
      ,ASI_Start_Account_Number_4_3
      ,ASI_Start_Account_Number_4_4
      ,ASI_Start_Account_Number_4_5
      ,ASI_Start_Account_Number_4_6
      ,ASI_Start_Account_Number_4_7
      ,ASI_Start_Account_Number_4_8
      ,ASI_Start_Account_Number_4_9
      ,ASI_Start_Account_Number_4_10
      ,ASI_Start_Date_Token_DDL_1
      ,ASI_Start_Date_Token_DDL_2
      ,ASI_Start_Date_Token_DDL_3
      ,ASI_Start_Date_Token_DDL_4
      ,ASI_Start_Date_1
      ,ASI_Start_Date_2
      ,ASI_Start_Date_3
      ,ASI_Start_Date_4
      ,ASI_Start_DDL_1
      ,ASI_Start_DDL_2
      ,ASI_Start_DDL_3
      ,ASI_Start_DDL_4
      ,ASI_String_Start_1
      ,ASI_String_Start_2
      ,ASI_String_Start_3
      ,ASI_String_Start_4
      ,ASI_End_Account_Number_1_1
      ,ASI_End_Account_Number_1_2
      ,ASI_End_Account_Number_1_3
      ,ASI_End_Account_Number_1_4
      ,ASI_End_Account_Number_1_5
      ,ASI_End_Account_Number_1_6
      ,ASI_End_Account_Number_1_7
      ,ASI_End_Account_Number_1_8
      ,ASI_End_Account_Number_1_9
      ,ASI_End_Account_Number_1_10
      ,ASI_End_Account_Number_2_1
      ,ASI_End_Account_Number_2_2
      ,ASI_End_Account_Number_2_3
      ,ASI_End_Account_Number_2_4
      ,ASI_End_Account_Number_2_5
      ,ASI_End_Account_Number_2_6
      ,ASI_End_Account_Number_2_7
      ,ASI_End_Account_Number_2_8
      ,ASI_End_Account_Number_2_9
      ,ASI_End_Account_Number_2_10
      ,ASI_End_Account_Number_3_1
      ,ASI_End_Account_Number_3_2
      ,ASI_End_Account_Number_3_3
      ,ASI_End_Account_Number_3_4
      ,ASI_End_Account_Number_3_5
      ,ASI_End_Account_Number_3_6
      ,ASI_End_Account_Number_3_7
      ,ASI_End_Account_Number_3_8
      ,ASI_End_Account_Number_3_9
      ,ASI_End_Account_Number_3_10
      ,ASI_End_Account_Number_4_1
      ,ASI_End_Account_Number_4_2
      ,ASI_End_Account_Number_4_3
      ,ASI_End_Account_Number_4_4
      ,ASI_End_Account_Number_4_5
      ,ASI_End_Account_Number_4_6
      ,ASI_End_Account_Number_4_7
      ,ASI_End_Account_Number_4_8
      ,ASI_End_Account_Number_4_9
      ,ASI_End_Account_Number_4_10
      ,ASI_End_Date_Token_DDL_1
      ,ASI_End_Date_Token_DDL_2
      ,ASI_End_Date_Token_DDL_3
      ,ASI_End_Date_Token_DDL_4
      ,ASI_End_Date_1
      ,ASI_End_Date_2
      ,ASI_End_Date_3
      ,ASI_End_Date_4
      ,ASI_End_DDL_1
      ,ASI_End_DDL_2
      ,ASI_End_DDL_3
      ,ASI_End_DDL_4
      ,ASI_String_End_1
      ,ASI_String_End_2
      ,ASI_String_End_3
      ,ASI_String_End_4
      ,ASI_Search_From_Str_1
      ,ASI_Search_From_Str_2
      ,ASI_Search_From_Str_3
      ,ASI_Search_From_Str_4
      ,ASI_Search_To_Str_1
      ,ASI_Search_To_Str_2
      ,ASI_Search_To_Str_3
      ,ASI_Search_To_Str_4
      ,ASI_Search_Logic_Type
      ,ASI_Max_Records
      ,ASI_Sort_Dict_ID
      ,ASI_Sort_Field
      ,ASI_Sort_Type
      ,ASI_Field_Comparison_1
      ,ASI_Field_Comparison_2
      ,ASI_Field_Comparison_3
      ,ASI_Field_Comparison_4
      ,ASI_Start_Comp_Field_ID_1
      ,ASI_Start_Comp_Field_ID_2
      ,ASI_Start_Comp_Field_ID_3
      ,ASI_Start_Comp_Field_ID_4
      ,ASI_Start_Comp_Field_Dic_1
      ,ASI_Start_Comp_Field_Dic_2
      ,ASI_Start_Comp_Field_Dic_3
      ,ASI_Start_Comp_Field_Dic_4
      ,ASI_End_Comp_Field_ID_1
      ,ASI_End_Comp_Field_ID_2
      ,ASI_End_Comp_Field_ID_3
      ,ASI_End_Comp_Field_ID_4
      ,ASI_End_Comp_Field_Dict_1
      ,ASI_End_Comp_Field_Dict_2
      ,ASI_End_Comp_Field_Dict_3
      ,ASI_End_Comp_Field_Dict_4
FROM ASIEXP81 WHERE USERID = '<SourceUserID>';

GO

In the above script <SourceUserID> and <DestinationUserID> are placeholders for the actual user IDs.

Until next post!

MG.-
Mariano Gomez, MVP
IntellPartners, LLC
http://www.IntellPartners.com/
Email ThisBlogThis!Share to XShare to FacebookShare to Pinterest
Posted in SmartList and SmartList Builder, SQL Scripting | No comments
Newer Post Older Post Home

0 comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Popular Posts

  • Year-to-year Inventory Margin Report using the PIVOT operator in T-SQL
    As of late I have been camping out at the SQL Developer Center's   Transact-SQL Forum  and I have to say, I have learned a great deal fr...
  • Vote for your 2012 GPUG All Star
    The time has come again to vote for the next year's GPUG All Star awards. Surprisingly, I have been nominated to this award along with f...
  • Rejecting duplicate checks during Bank Transactions import with Integration Manager
    One of the interesting things about checkbooks setup in Microsoft Dynamics GP is that you have the ability to prevent duplicate checks from ...
  • Microsoft Dynamics GP Add-In for Microsoft Word not enabling despite several attempts to install
    Just recently, I ran into a situation with the Microsoft Dynamics GP Add-In for Microsoft Word where, after following all the installation s...
  • Changing the metrics properties on the Microsoft Dynamics GP home page - Part 2
    Part 2 of 2 - Changing the metrics properties on the Microsoft Dynamics GP home page. In Part 1 of the series  I showed a couple cool things...
  • Web Client Wednesday - Browser Support
    Last week MVP Mark Polino started a series called Web Client Wednesdays, which is actually a Microsoft Dynamics GP community outreach to st...
  • Extracting data from Microsoft Dynamics GP company databases using SQL Server FOR XML and XMLNAMESPACES
    I truly love what I do. Really! My job takes me just about everywhere around this great country of ours and beyond its borders in the quest ...
  • Windows 8 and the Microsoft Dynamics GP Web Client Series - Part 1
    Windows 8 and the Microsoft Dynamics GP Web Client Series Part 1 This series narrate my personal experiences of installing Microsoft Dynamic...
  • Extender Auto Open and Auto Close options not working in GP 2010
    Just recently, I came across an issue reported by a partner on Extender Auto Open and Auto Close options not working. Extender Auto Open a...
  • Microsoft Dynamics GP 2013 on Windows Azure: The Deployment Process
    Welcome to the third installment on Microsoft Dynamics GP 2013 on Windows Azure. In the previous article I showed how to provision the vario...

Categories

  • Ad Campaigns
  • ADO
  • Adobe Acrobat
  • Analytical Accounting
  • Architecture
  • Around the Blogosphere
  • Article
  • Azure
  • Bank Reconciliation
  • Best of 2009
  • Best of Series
  • Best Practices
  • Bing Maps Enterprise
  • Books
  • Business Alerts
  • Business Analyzer
  • C#
  • Code
  • COM
  • Community
  • Compliance
  • Connect
  • Continuum
  • Convergence
  • Corporate Performance Management
  • CRM
  • Database Maintenance Utility
  • Decisions Conference
  • DEX.INI
  • DEXSQL
  • Dexterity
  • Discussions
  • Drill-Down Builder
  • Dynamics GP 10
  • Dynamics GP 11
  • Dynamics GP 12
  • Dynamics GP 2010
  • Dynamics GP 2010 R2
  • Dynamics GP 2013
  • eConnect
  • EFT
  • Electronic Banking
  • Encumbrance
  • Events
  • Extender
  • Field Services
  • Fixed Assets
  • Forecaster
  • From the Newsgroups
  • FRx
  • Functionality
  • General Ledger
  • GPUG
  • Home Page
  • Human Resources
  • Humor
  • IMHO
  • Installation
  • Integration
  • Integration Manager
  • Internet Explorer
  • Inventory
  • Kinnect
  • Maintenance
  • Management Reporter
  • Manufacturing
  • Menus for Visual Studio Tools
  • Microsoft Office
  • Modifier
  • Multicurrency Management
  • Multitenancy
  • MVP Summit
  • MVPs
  • Named Printers
  • Navigation Pane
  • Notes
  • ODBC
  • Office Web Components
  • OLE Container
  • Online Services
  • OpenXML
  • Partner Connections
  • Payables Management
  • Payroll
  • Performance
  • PO Commitments
  • Printer Compatibility
  • Product Feedback
  • Project Accounting
  • Purchasing
  • Receivables Management
  • RemoteApp
  • Report Writer
  • Reporting
  • Roadmap
  • SafePay
  • Sales Order Processing
  • Season Greetings
  • Security
  • Service Call Management
  • SharePoint
  • SmartList and SmartList Builder
  • SQL Reporting Services
  • SQL Scripting
  • SQL Server
  • Support Debugging Tool
  • Tax Updates
  • Technical Conference
  • The Partner Event
  • The Technology Corner
  • Training
  • Translation
  • Troubleshooting
  • Upgrades
  • VAT
  • VB.NET
  • VBA
  • VBScript
  • Visual Studio 2008
  • Visual Studio Tools
  • Web Client
  • Web Services
  • Windows 7
  • Windows 8
  • Word Templates
  • XBox
  • XBRL

Blog Archive

  • ►  2013 (68)
    • ►  December (2)
    • ►  November (8)
    • ►  October (5)
    • ►  September (5)
    • ►  August (3)
    • ►  July (8)
    • ►  June (5)
    • ►  May (5)
    • ►  April (2)
    • ►  March (11)
    • ►  February (6)
    • ►  January (8)
  • ►  2012 (101)
    • ►  December (8)
    • ►  November (6)
    • ►  October (15)
    • ►  September (16)
    • ►  August (9)
    • ►  July (4)
    • ►  June (4)
    • ►  May (6)
    • ►  April (4)
    • ►  March (11)
    • ►  February (4)
    • ►  January (14)
  • ▼  2011 (158)
    • ►  December (7)
    • ►  November (17)
    • ▼  October (7)
      • MVP Jivtesh Singh shows the power of Business Anal...
      • Mark Polino shows some love for Microsoft Dynamics...
      • Copying Smartlists favorites from one user to another
      • Decisions Fall 2011 Virtual Conference registratio...
      • Printing to screen and PDF file causes default pri...
      • Come join me at GPUG Academy and Summit in November
      • Congrats to MVPs Mark Polino and Siva Venkataraman!
    • ►  September (8)
    • ►  August (8)
    • ►  July (12)
    • ►  June (12)
    • ►  May (13)
    • ►  April (23)
    • ►  March (21)
    • ►  February (10)
    • ►  January (20)
  • ►  2010 (168)
    • ►  December (15)
    • ►  November (11)
    • ►  October (12)
    • ►  September (24)
    • ►  August (13)
    • ►  July (12)
    • ►  June (8)
    • ►  May (17)
    • ►  April (14)
    • ►  March (9)
    • ►  February (16)
    • ►  January (17)
  • ►  2009 (5)
    • ►  December (5)
Powered by Blogger.

About Me

Unknown
View my complete profile