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/
1 comment:
Actually I want to create a new user and transfer all the access and security to all companies. Is there any script availale for that?
Thank you
Post a Comment