Summary:

An issue may arise whereby GlobalLibraryList and GlobalLibraryFilterBox are deleted from CRM through an error in customisation. This can result in a SQL error when carrying out a mail merge from the Documents tab fo the Company entity. The error returned will refer to Libr_HasAttachments, which is not a valid field in current version of Sage CRM.



More information:

The following SQL scripts will reinstate GlobablLibraryList and GlobalLibraryFilterBox. This script is written for Sage CRM installs upgraded from v7.1 or earlier; copies of Sage CRM 7.2, that use SQL Identities, will not have to have the ID fields specified.

 

-- Reinstate GlobalLibraryFilterBox

declare @globalLibraryFilterBox_ID int
declare @custom_screens_id int
select @globalLibraryFilterBox_ID = CObj_TableId from Custom_ScreenObjects where cobj_name = N'GlobalLibraryFilterBox'
declare @current_date datetime
select @current_date = GETDATE()

exec @custom_screens_id = eware_get_Identity_Id @Table_Name = 'Custom_Screens'
insert into custom_screens (SeaP_SearchEntryPropsId, SeaP_SearchBoxName, SeaP_Order, SeaP_ColName, SeaP_Newline, SeaP_RowSpan, SeaP_ColSpan, SeaP_CreatedBy, SeaP_CreatedDate, SeaP_UpdatedBy, SeaP_UpdatedDate, SeaP_TimeStamp, SeaP_CreateScript, SeaP_CustomEditsIDFK, SeaP_ScreenObjectsIDFK)
values(@custom_screens_id, 'GlobalLibraryFilterBox', 1, 'libr_filename', '0', '1', '1', '1', @current_date, 1, @current_date, @current_date, null, (select ColP_ColPropsId from Custom_Edits where ColP_ColName = N'libr_filename' and ColP_Entity = N'Library'), @globalLibraryFilterBox_ID)

exec @custom_screens_id = eware_get_Identity_Id @Table_Name = 'Custom_Screens'
insert into custom_screens (SeaP_SearchEntryPropsId, SeaP_SearchBoxName, SeaP_Order, SeaP_ColName, SeaP_Newline, SeaP_RowSpan, SeaP_ColSpan, SeaP_CreatedBy, SeaP_CreatedDate, SeaP_UpdatedBy, SeaP_UpdatedDate, SeaP_TimeStamp, SeaP_CreateScript, SeaP_CustomEditsIDFK, SeaP_ScreenObjectsIDFK)
values(@custom_screens_id, 'GlobalLibraryFilterBox', 2, 'libr_channelid', '1', '1', '1', '1', @current_date, 1, @current_date, @current_date, null, (select ColP_ColPropsId from Custom_Edits where ColP_ColName = N'libr_channelid' and ColP_Entity = N'Library'), @globalLibraryFilterBox_ID)

exec @custom_screens_id = eware_get_Identity_Id @Table_Name = 'Custom_Screens'
insert into custom_screens (SeaP_SearchEntryPropsId, SeaP_SearchBoxName, SeaP_Order, SeaP_ColName, SeaP_Newline, SeaP_RowSpan, SeaP_ColSpan, SeaP_CreatedBy, SeaP_CreatedDate, SeaP_UpdatedBy, SeaP_UpdatedDate, SeaP_TimeStamp, SeaP_CreateScript, SeaP_CustomEditsIDFK, SeaP_ScreenObjectsIDFK)
values(@custom_screens_id, 'GlobalLibraryFilterBox', 3, 'libr_status', '1', '1', '1', '1', @current_date, 1, @current_date, @current_date, null, (select ColP_ColPropsId from Custom_Edits where ColP_ColName = N'libr_status' and ColP_Entity = N'Library'), @globalLibraryFilterBox_ID)

exec @custom_screens_id = eware_get_Identity_Id @Table_Name = 'Custom_Screens'
insert into custom_screens (SeaP_SearchEntryPropsId, SeaP_SearchBoxName, SeaP_Order, SeaP_ColName, SeaP_Newline, SeaP_RowSpan, SeaP_ColSpan, SeaP_CreatedBy, SeaP_CreatedDate, SeaP_UpdatedBy, SeaP_UpdatedDate, SeaP_TimeStamp, SeaP_CreateScript, SeaP_CustomEditsIDFK, SeaP_ScreenObjectsIDFK)
values(@custom_screens_id, 'GlobalLibraryFilterBox', 4, 'libr_active', '1', '1', '1', '1', @current_date, 1, @current_date, @current_date, 'Defaultvalue = ''Y'';', (select ColP_ColPropsId from Custom_Edits where ColP_ColName = N'libr_active' and ColP_Entity = N'Library'), @globalLibraryFilterBox_ID)

exec @custom_screens_id = eware_get_Identity_Id @Table_Name = 'Custom_Screens'
insert into custom_screens (SeaP_SearchEntryPropsId, SeaP_SearchBoxName, SeaP_Order, SeaP_ColName, SeaP_Newline, SeaP_RowSpan, SeaP_ColSpan, SeaP_CreatedBy, SeaP_CreatedDate, SeaP_UpdatedBy, SeaP_UpdatedDate, SeaP_TimeStamp, SeaP_CreateScript, SeaP_CustomEditsIDFK, SeaP_ScreenObjectsIDFK)
values(@custom_screens_id, 'GlobalLibraryFilterBox', 5, 'libr_type', '1', '1', '1', '1', @current_date, 1, @current_date, @current_date, null, (select ColP_ColPropsId from Custom_Edits where ColP_ColName = N'libr_type' and ColP_Entity = N'Library'), @globalLibraryFilterBox_ID)

exec @custom_screens_id = eware_get_Identity_Id @Table_Name = 'Custom_Screens'
insert into custom_screens (SeaP_SearchEntryPropsId, SeaP_SearchBoxName, SeaP_Order, SeaP_ColName, SeaP_Newline, SeaP_RowSpan, SeaP_ColSpan, SeaP_CreatedBy, SeaP_CreatedDate, SeaP_UpdatedBy, SeaP_UpdatedDate, SeaP_TimeStamp, SeaP_CreateScript, SeaP_CustomEditsIDFK, SeaP_ScreenObjectsIDFK)
values(@custom_screens_id, 'GlobalLibraryFilterBox', 6, 'libr_category', '1', '1', '1', '1', @current_date, 1, @current_date, @current_date, null, (select ColP_ColPropsId from Custom_Edits where ColP_ColName = N'libr_category' and ColP_Entity = N'Library'), @globalLibraryFilterBox_ID)

exec @custom_screens_id = eware_get_Identity_Id @Table_Name = 'Custom_Screens'
insert into custom_screens (SeaP_SearchEntryPropsId, SeaP_SearchBoxName, SeaP_Order, SeaP_ColName, SeaP_Newline, SeaP_RowSpan, SeaP_ColSpan, SeaP_CreatedBy, SeaP_CreatedDate, SeaP_UpdatedBy, SeaP_UpdatedDate, SeaP_TimeStamp, SeaP_CreateScript, SeaP_CustomEditsIDFK, SeaP_ScreenObjectsIDFK)
values(@custom_screens_id, 'GlobalLibraryFilterBox', 7, 'libr_language', '1', '1', '1', '1', @current_date, 1, @current_date, @current_date, 'DefaultValue=CurrentUser.user_language;', (select ColP_ColPropsId from Custom_Edits where ColP_ColName = N'libr_language' and ColP_Entity = N'Library'), @globalLibraryFilterBox_ID)

exec @custom_screens_id = eware_get_Identity_Id @Table_Name = 'Custom_Screens'
insert into custom_screens (SeaP_SearchEntryPropsId, SeaP_SearchBoxName, SeaP_Order, SeaP_ColName, SeaP_Newline, SeaP_RowSpan, SeaP_ColSpan, SeaP_CreatedBy, SeaP_CreatedDate, SeaP_UpdatedBy, SeaP_UpdatedDate, SeaP_TimeStamp, SeaP_CreateScript, SeaP_CustomEditsIDFK, SeaP_ScreenObjectsIDFK)
values(@custom_screens_id, 'GlobalLibraryFilterBox', 8, 'libr_entity', '1', '1', '1', '1', @current_date, 1, @current_date, @current_date, null, (select ColP_ColPropsId from Custom_Edits where ColP_ColName = N'libr_filename' and ColP_Entity = N'Library'), @globalLibraryFilterBox_ID)
go

-- END reinstate GlobalLibraryFilterBox

-- Reinstate GlobalLibraryList

update Custom_ScreenObjects set CObj_AllowDelete = N'N', CObj_Deleted = null where CObj_Name = N'GlobalLibraryList'
go

declare @globalLibraryList_id int
select @globalLibraryList_id = CObj_TableId from Custom_ScreenObjects where cobj_name = N'GlobalLibraryList'

declare @custom_screens_id int
declare @current_date datetime
select @current_date = GETDATE()

-- Already present on customer's database
-- exec @custom_screens_id = eware_get_Identity_Id @Table_Name = 'Custom_Lists'
-- insert into Custom_Lists (GriP_GridPropsId, GriP_GridName, GriP_Order, GriP_ColName, GriP_AllowOrderBy, GriP_OrderByDesc, GriP_Alignment, GriP_Jump, GriP_CreatedBy, GriP_CreatedDate, GriP_UpdatedBy, GriP_UpdatedDate, GriP_TimeStamp, GriP_CustomAction, GriP_CustomIDField, GriP_ViewMode, GriP_CustomEditsIDFK, GriP_ScreenObjectsIDFK)
-- values(@custom_screens_id, 'GlobalLibraryList', 0, 'Libr_hasattachments', null, null, N'CENTER', N'Library', -1, @current_date, null, @current_date, @current_date, N'library', N'libr_libraryid', N'ATTACHMENT', null, @globalLibraryList_id)

exec @custom_screens_id = eware_get_Identity_Id @Table_Name = 'Custom_Lists'
insert into Custom_Lists (GriP_GridPropsId, GriP_GridName, GriP_Order, GriP_ColName, GriP_AllowOrderBy, GriP_OrderByDesc, GriP_Alignment, GriP_Jump, GriP_CreatedBy, GriP_CreatedDate, GriP_UpdatedBy, GriP_UpdatedDate, GriP_TimeStamp, GriP_CustomAction, GriP_CustomIDField, GriP_ViewMode, GriP_CustomEditsIDFK, GriP_ScreenObjectsIDFK)
values(@custom_screens_id, 'GlobalLibraryList', 1, 'Libr_UpdatedDate', N'Y', N'Y', null, null, -1, @current_date, -1, @current_date, @current_date, null, null, null, (select ColP_ColPropsId from Custom_Edits where ColP_ColName = N'Libr_UpdatedDate'), @globalLibraryList_id)

exec @custom_screens_id = eware_get_Identity_Id @Table_Name = 'Custom_Lists'
insert into Custom_Lists (GriP_GridPropsId, GriP_GridName, GriP_Order, GriP_ColName, GriP_AllowOrderBy, GriP_OrderByDesc, GriP_Alignment, GriP_Jump, GriP_CreatedBy, GriP_CreatedDate, GriP_UpdatedBy, GriP_UpdatedDate, GriP_TimeStamp, GriP_CustomAction, GriP_CustomIDField, GriP_ViewMode, GriP_CustomEditsIDFK, GriP_ScreenObjectsIDFK)
values(@custom_screens_id, 'GlobalLibraryList', 2, 'Libr_FileName', N'Y', null, null, N'Library', -1, @current_date, -1, @current_date, @current_date, null, null, null, (select ColP_ColPropsId from Custom_Edits where ColP_ColName = N'Libr_FileName'), @globalLibraryList_id)

exec @custom_screens_id = eware_get_Identity_Id @Table_Name = 'Custom_Lists'
insert into Custom_Lists (GriP_GridPropsId, GriP_GridName, GriP_Order, GriP_ColName, GriP_AllowOrderBy, GriP_OrderByDesc, GriP_Alignment, GriP_Jump, GriP_CreatedBy, GriP_CreatedDate, GriP_UpdatedBy, GriP_UpdatedDate, GriP_TimeStamp, GriP_CustomAction, GriP_CustomIDField, GriP_ViewMode, GriP_CustomEditsIDFK, GriP_ScreenObjectsIDFK)
values(@custom_screens_id, 'GlobalLibraryList', 3, 'Libr_Type', null, null, null, null, -1, @current_date, -1, @current_date, @current_date, null, null, null, (select ColP_ColPropsId from Custom_Edits where ColP_ColName = N'Libr_Type'), @globalLibraryList_id)

exec @custom_screens_id = eware_get_Identity_Id @Table_Name = 'Custom_Lists'
insert into Custom_Lists (GriP_GridPropsId, GriP_GridName, GriP_Order, GriP_ColName, GriP_AllowOrderBy, GriP_OrderByDesc, GriP_Alignment, GriP_Jump, GriP_CreatedBy, GriP_CreatedDate, GriP_UpdatedBy, GriP_UpdatedDate, GriP_TimeStamp, GriP_CustomAction, GriP_CustomIDField, GriP_ViewMode, GriP_CustomEditsIDFK, GriP_ScreenObjectsIDFK)
values(@custom_screens_id, 'GlobalLibraryList', 4, 'Libr_Category', null, null, null, null, -1, @current_date, -1, @current_date, @current_date, null, null, null, (select ColP_ColPropsId from Custom_Edits where ColP_ColName = N'Libr_Category'), @globalLibraryList_id)

exec @custom_screens_id = eware_get_Identity_Id @Table_Name = 'Custom_Lists'
insert into Custom_Lists (GriP_GridPropsId, GriP_GridName, GriP_Order, GriP_ColName, GriP_AllowOrderBy, GriP_OrderByDesc, GriP_Alignment, GriP_Jump, GriP_CreatedBy, GriP_CreatedDate, GriP_UpdatedBy, GriP_UpdatedDate, GriP_TimeStamp, GriP_CustomAction, GriP_CustomIDField, GriP_ViewMode, GriP_CustomEditsIDFK, GriP_ScreenObjectsIDFK)
values(@custom_screens_id, 'GlobalLibraryList', 5, 'Libr_UserId', null, null, null, null, -1, @current_date, -1, @current_date, @current_date, null, null, null, (select ColP_ColPropsId from Custom_Edits where ColP_ColName = N'Libr_UserId'), @globalLibraryList_id)

exec @custom_screens_id = eware_get_Identity_Id @Table_Name = 'Custom_Lists'
insert into Custom_Lists (GriP_GridPropsId, GriP_GridName, GriP_Order, GriP_ColName, GriP_AllowOrderBy, GriP_OrderByDesc, GriP_Alignment, GriP_Jump, GriP_CreatedBy, GriP_CreatedDate, GriP_UpdatedBy, GriP_UpdatedDate, GriP_TimeStamp, GriP_CustomAction, GriP_CustomIDField, GriP_ViewMode, GriP_CustomEditsIDFK, GriP_ScreenObjectsIDFK)
values(@custom_screens_id, 'GlobalLibraryList', 6, 'Libr_Note', null, null, null, null, -1, @current_date, -1, @current_date, @current_date, null, null, null, (select ColP_ColPropsId from Custom_Edits where ColP_ColName = N'Libr_Note'), @globalLibraryList_id)

exec @custom_screens_id = eware_get_Identity_Id @Table_Name = 'Custom_Lists'
insert into Custom_Lists (GriP_GridPropsId, GriP_GridName, GriP_Order, GriP_ColName, GriP_AllowOrderBy, GriP_OrderByDesc, GriP_Alignment, GriP_Jump, GriP_CreatedBy, GriP_CreatedDate, GriP_UpdatedBy, GriP_UpdatedDate, GriP_TimeStamp, GriP_CustomAction, GriP_CustomIDField, GriP_ViewMode, GriP_CustomEditsIDFK, GriP_ScreenObjectsIDFK)
values(@custom_screens_id, 'GlobalLibraryList', 7, 'Libr_Status', N'Y', null, N'CENTER', null, -1, @current_date, -1, @current_date, @current_date, null, null, null, (select ColP_ColPropsId from Custom_Edits where ColP_ColName = N'Libr_Status'), @globalLibraryList_id)
go

-- END reinstate GlobalLibraryList

-- Rebuild views on next logon

drop view vsentinel
go