Copying Smartlists favorites from one user to another
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.
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/
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/
Comments
Thank you
Kind Regards,
Geoff James
Glad I could help!
MG.-
Thanks in advance.
INSERT INTO [DYNAMICS].[dbo].[ASIEXP81]
[ASI_Favorite_Dict_ID]
,[ASI_Favorite_Type]
,[ASI_Favorite_Save_Level]
,[CMPANYID]
,[USRCLASS]
,[USERID]
,[ASI_Favorite_Name]
----(reduced)-----
,[ASI_End_Comp_Field_Dict_1]
,[ASI_End_Comp_Field_Dict_2]
,[ASI_End_Comp_Field_Dict_3]
,[ASI_End_Comp_Field_Dict_4]
,[DEX_ROW_ID]
SELECT [ASI_Favorite_Dict_ID]
,[ASI_Favorite_Type]
,[ASI_Favorite_Save_Level]
,[CMPANYID]
,[USRCLASS]
,[USERID]
,[ASI_Favorite_Name]
----(reduced)-----
,[ASI_End_Comp_Field_Dict_1]
,[ASI_End_Comp_Field_Dict_2]
,[ASI_End_Comp_Field_Dict_3]
,[ASI_End_Comp_Field_Dict_4]
,[DEX_ROW_ID]
FROM [DYN-Restore].[dbo].[ASIEXP81] WHERE [ASI_Favorite_Dict_ID] = ‘XXX’