Our Community has moved! Visit the New Spark Community. Our old Community will be going away soon...

Articles in this section

See more

How To: Set ALL Alerts for ALL Users - TSR

Avatar
Jerad Fuller
Updated
Follow

If you are wanting to mass set all users alert preferences for the Portal Framework and module notifications, please follow the steps below for instructions.

--In all below sections, the 2 is for email only. Set for the type of alert wanted--

0 = None

2 = Email ONLY

4 = Popup ONLY

6 = Email + Popup

8 = Mobile Email ONLY

10 = Email + Mobile Email

12 = Popup + Mobile Email

14 = Email + Popup + Mobile Email

 

1. From the SQL server, take a backup of the Passageways Portal Framework database.

2. Copy the script below in to a new query window. 

NOTE: If you do not see the Production_Portal_PortalFx database naming convention, do a Find & Replace for Production_Portal_PortalFx and replace with your Portal Framework database name. 

3. This section deletes all existing alert preferences in order to set them all. Remove from the script if you do not want to remove existing alerts.


DELETE Production_Portal_PortalFx.dbo.PWUserPreferences WHERE (

PreferenceKey = 'ReportBypassed' OR PreferenceKey = 'ReportStatusChanged' OR PreferenceKey = 'ReportWaiting'

--Announcements

OR PreferenceKey = 'AnnouncementPosted'

--Management Blogs

OR PreferenceKey = 'BlogNotification'

--Form Builder

OR PreferenceKey = 'DistributionCompleted' OR PreferenceKey = 'DistributionSent' OR PreferenceKey = 'FormActivated' OR PreferenceKey = 'FormDeactivated' OR
PreferenceKey = 'FormReadyForActivation' OR PreferenceKey = 'FormSubmitted' OR PreferenceKey = 'FormSubmittedForApproval' OR PreferenceKey = 'SubmissionApproved' OR
PreferenceKey = 'SubmissionApprovedForReview' OR PreferenceKey = 'SubmissionDeclined'

--Conversations

OR PreferenceKey = 'AddPost' OR PreferenceKey = 'AddThread' OR PreferenceKey = 'DeletePost' OR PreferenceKey = 'EditPost' OR
PreferenceKey = 'LockThread' OR PreferenceKey = 'MoveThread' OR PreferenceKey = 'ReportContentNotification' OR PreferenceKey = 'StickyThread'

--Help Desks

OR PreferenceKey = 'MovedWorkOrder' OR PreferenceKey = 'NewWorkOrder' OR PreferenceKey = 'WorkOrderClosed' OR
PreferenceKey = 'WorkOrderCommentAdded' OR PreferenceKey = 'WorkOrderEdited' OR PreferenceKey = 'WorkOrderReopened'

--Products & Services

OR PreferenceKey = 'ProcedureAddedOrRemoved' OR PreferenceKey = 'ProcedureChanged' OR PreferenceKey = 'ProcedurePublished' OR
PreferenceKey = 'ProductDescriptionChanged' OR PreferenceKey = 'ProductPromotionsChanged' OR PreferenceKey = 'ProductSalesTipsChanged'

--Vendor Management

OR PreferenceKey = 'ContractNotification' OR PreferenceKey = 'VendorNotification' OR PreferenceKey = 'VendorReview' OR PreferenceKey = 'ChecklistItem'
OR (PreferenceKey = 'Miscellaneous' and AppID = 'Passageways.Modules.VendorManagement') OR PreferenceKey = 'DocumentReview' OR PreferenceKey = 'ContractExpiration'

--Portal Framework, Workgroups, Misc. 

OR PreferenceKey = 'ChangedPasswordNotification' OR PreferenceKey = 'DeactivatedWorkgroupNotification' OR PreferenceKey = 'DeletedWorkgroupNotification' OR
PreferenceKey = 'DocumentSubscriptionNotification' OR PreferenceKey = 'MiscellaneousNotification' OR PreferenceKey = 'UserCommentsStatusNotification' OR
PreferenceKey = 'UserProfileChangedNotification' OR
PreferenceKey = 'IslandPageChanged' OR
PreferenceKey = 'WorkgroupNotification' OR
PreferenceKey= 'VoiceYourConcernsNotification'

--Calendar
OR PreferenceKey = 'CalendarSubscription' OR PreferenceKey = 'EventAttendence' OR PreferenceKey = 'EventManagement'

--Reservations

OR PreferenceKey = 'ReservationItemCreated'

)

4. Set the users' alert preferences

--If you wish to only do a subset of users, specify them in the Select query below. Existing query will set alert preferences for all activated users.


Declare @userID as varchar(50)
declare c1 cursor for
Select DISTINCT UserID from [Production_Portal_PortalFx].[dbo].[PWUser] where activated = 1

--DO NOT TOUCH THE BELOW SECTION
open c1
fetch next from c1 into @userID

while @@fetch_status=0
begin
--DO NOT TOUCH THE ABOVE SECTION

--In all below sections, the 2 is for email only. Set for the type of alert wanted 0 = None, 2 = Email, 4 = Popup, 6 = Email + Popup.

--Announcements
INSERT INTO Production_Portal_PortalFx.dbo.PWUserPreferences (UserID,AppID,PreferenceKey,PreferenceValue) VALUES (@userID,'Passageways.Modules.Announcements','AnnouncementPosted','2')

--Expense Reports
INSERT INTO Production_Portal_PortalFx.dbo.PWUserPreferences (UserID,AppID,PreferenceKey,PreferenceValue) VALUES (@userID,'Passageways.Modules.ExpenseTracking','ReportBypassed','2')
INSERT INTO Production_Portal_PortalFx.dbo.PWUserPreferences (UserID,AppID,PreferenceKey,PreferenceValue) VALUES (@userID,'Passageways.Modules.ExpenseTracking','ReportStatusChanged','2')
INSERT INTO Production_Portal_PortalFx.dbo.PWUserPreferences (UserID,AppID,PreferenceKey,PreferenceValue) VALUES (@userID,'Passageways.Modules.ExpenseTracking','ReportWaiting','2')

--Form Builder
INSERT INTO Production_Portal_PortalFx.dbo.PWUserPreferences (UserID,AppID,PreferenceKey,PreferenceValue) VALUES (@userID,'Passageways.Modules.FormBuilder','DistributionCompleted','2')
INSERT INTO Production_Portal_PortalFx.dbo.PWUserPreferences (UserID,AppID,PreferenceKey,PreferenceValue) VALUES (@userID,'Passageways.Modules.FormBuilder','DistributionSent','2')
INSERT INTO Production_Portal_PortalFx.dbo.PWUserPreferences (UserID,AppID,PreferenceKey,PreferenceValue) VALUES (@userID,'Passageways.Modules.FormBuilder','FormActivated','2')
INSERT INTO Production_Portal_PortalFx.dbo.PWUserPreferences (UserID,AppID,PreferenceKey,PreferenceValue) VALUES (@userID,'Passageways.Modules.FormBuilder','FormDeactivated','2')
INSERT INTO Production_Portal_PortalFx.dbo.PWUserPreferences (UserID,AppID,PreferenceKey,PreferenceValue) VALUES (@userID,'Passageways.Modules.FormBuilder','FormReadyForActivation','2')
INSERT INTO Production_Portal_PortalFx.dbo.PWUserPreferences (UserID,AppID,PreferenceKey,PreferenceValue) VALUES (@userID,'Passageways.Modules.FormBuilder','FormSubmitted','2')
INSERT INTO Production_Portal_PortalFx.dbo.PWUserPreferences (UserID,AppID,PreferenceKey,PreferenceValue) VALUES (@userID,'Passageways.Modules.FormBuilder','FormSubmittedForApproval','2')
INSERT INTO Production_Portal_PortalFx.dbo.PWUserPreferences (UserID,AppID,PreferenceKey,PreferenceValue) VALUES (@userID,'Passageways.Modules.FormBuilder','SubmissionApproved','2')
INSERT INTO Production_Portal_PortalFx.dbo.PWUserPreferences (UserID,AppID,PreferenceKey,PreferenceValue) VALUES (@userID,'Passageways.Modules.FormBuilder','SubmissionApprovedForReview','2')
INSERT INTO Production_Portal_PortalFx.dbo.PWUserPreferences (UserID,AppID,PreferenceKey,PreferenceValue) VALUES (@userID,'Passageways.Modules.FormBuilder','SubmissionDeclined','2')

--Conversations
INSERT INTO Production_Portal_PortalFx.dbo.PWUserPreferences (UserID,AppID,PreferenceKey,PreferenceValue) VALUES (@userID,'Passageways.Modules.Forums','AddPost','2')
INSERT INTO Production_Portal_PortalFx.dbo.PWUserPreferences (UserID,AppID,PreferenceKey,PreferenceValue) VALUES (@userID,'Passageways.Modules.Forums','AddThread','2')
INSERT INTO Production_Portal_PortalFx.dbo.PWUserPreferences (UserID,AppID,PreferenceKey,PreferenceValue) VALUES (@userID,'Passageways.Modules.Forums','DeletePost','2')
INSERT INTO Production_Portal_PortalFx.dbo.PWUserPreferences (UserID,AppID,PreferenceKey,PreferenceValue) VALUES (@userID,'Passageways.Modules.Forums','EditPost','2')
INSERT INTO Production_Portal_PortalFx.dbo.PWUserPreferences (UserID,AppID,PreferenceKey,PreferenceValue) VALUES (@userID,'Passageways.Modules.Forums','LockThread','2')
INSERT INTO Production_Portal_PortalFx.dbo.PWUserPreferences (UserID,AppID,PreferenceKey,PreferenceValue) VALUES (@userID,'Passageways.Modules.Forums','MoveThread','2')
INSERT INTO Production_Portal_PortalFx.dbo.PWUserPreferences (UserID,AppID,PreferenceKey,PreferenceValue) VALUES (@userID,'Passageways.Modules.Forums','ReportContentNotification','2')
INSERT INTO Production_Portal_PortalFx.dbo.PWUserPreferences (UserID,AppID,PreferenceKey,PreferenceValue) VALUES (@userID,'Passageways.Modules.Forums','StickyThread','2')

--HelpDesk
INSERT INTO Production_Portal_PortalFx.dbo.PWUserPreferences (UserID,AppID,PreferenceKey,PreferenceValue) VALUES (@userID,'Passageways.Modules.HelpDesk','MovedWorkOrder','2')
INSERT INTO Production_Portal_PortalFx.dbo.PWUserPreferences (UserID,AppID,PreferenceKey,PreferenceValue) VALUES (@userID,'Passageways.Modules.HelpDesk','NewWorkOrder','2')
INSERT INTO Production_Portal_PortalFx.dbo.PWUserPreferences (UserID,AppID,PreferenceKey,PreferenceValue) VALUES (@userID,'Passageways.Modules.HelpDesk','WorkOrderClosed','2')
INSERT INTO Production_Portal_PortalFx.dbo.PWUserPreferences (UserID,AppID,PreferenceKey,PreferenceValue) VALUES (@userID,'Passageways.Modules.HelpDesk','WorkOrderCommentAdded','2')
INSERT INTO Production_Portal_PortalFx.dbo.PWUserPreferences (UserID,AppID,PreferenceKey,PreferenceValue) VALUES (@userID,'Passageways.Modules.HelpDesk','WorkOrderEdited','2')
INSERT INTO Production_Portal_PortalFx.dbo.PWUserPreferences (UserID,AppID,PreferenceKey,PreferenceValue) VALUES (@userID,'Passageways.Modules.HelpDesk','WorkOrderReopened','2')

--Products and Services
INSERT INTO Production_Portal_PortalFx.dbo.PWUserPreferences (UserID,AppID,PreferenceKey,PreferenceValue) VALUES (@userID,'Passageways.Modules.Products','ProcedureAddedOrRemoved','2')
INSERT INTO Production_Portal_PortalFx.dbo.PWUserPreferences (UserID,AppID,PreferenceKey,PreferenceValue) VALUES (@userID,'Passageways.Modules.Products','ProcedureChanged','2')
INSERT INTO Production_Portal_PortalFx.dbo.PWUserPreferences (UserID,AppID,PreferenceKey,PreferenceValue) VALUES (@userID,'Passageways.Modules.Products','ProcedurePublished','2')
INSERT INTO Production_Portal_PortalFx.dbo.PWUserPreferences (UserID,AppID,PreferenceKey,PreferenceValue) VALUES (@userID,'Passageways.Modules.Products','ProductDescriptionChanged','2')
INSERT INTO Production_Portal_PortalFx.dbo.PWUserPreferences (UserID,AppID,PreferenceKey,PreferenceValue) VALUES (@userID,'Passageways.Modules.Products','ProductPromotionsChanged','2')
INSERT INTO Production_Portal_PortalFx.dbo.PWUserPreferences (UserID,AppID,PreferenceKey,PreferenceValue) VALUES (@userID,'Passageways.Modules.Products','ProductSalesTipsChanged','2')

--Vendor Management
INSERT INTO Production_Portal_PortalFx.dbo.PWUserPreferences (UserID,AppID,PreferenceKey,PreferenceValue) VALUES (@userID,'Passageways.Modules.VendorManagement','ChecklistItem','2')
INSERT INTO Production_Portal_PortalFx.dbo.PWUserPreferences (UserID,AppID,PreferenceKey,PreferenceValue) VALUES (@userID,'Passageways.Modules.VendorManagement','ContractExpiration','2')
INSERT INTO Production_Portal_PortalFx.dbo.PWUserPreferences (UserID,AppID,PreferenceKey,PreferenceValue) VALUES (@userID,'Passageways.Modules.VendorManagement','DocumentReview','2')
INSERT INTO Production_Portal_PortalFx.dbo.PWUserPreferences (UserID,AppID,PreferenceKey,PreferenceValue) VALUES (@userID,'Passageways.Modules.VendorManagement','Miscellaneous','2')
INSERT INTO Production_Portal_PortalFx.dbo.PWUserPreferences (UserID,AppID,PreferenceKey,PreferenceValue) VALUES (@userID,'Passageways.Modules.VendorManagement','VendorReview','2')

--Portal Framework
INSERT INTO Production_Portal_PortalFx.dbo.PWUserPreferences (UserID,AppID,PreferenceKey,PreferenceValue) VALUES (@userID,'Passageways.Portal','ChangedPasswordNotification','2')
INSERT INTO Production_Portal_PortalFx.dbo.PWUserPreferences (UserID,AppID,PreferenceKey,PreferenceValue) VALUES (@userID,'Passageways.Portal','DeactivatedWorkgroupNotification','2')
INSERT INTO Production_Portal_PortalFx.dbo.PWUserPreferences (UserID,AppID,PreferenceKey,PreferenceValue) VALUES (@userID,'Passageways.Portal','DeletedWorkgroupNotification','2')
INSERT INTO Production_Portal_PortalFx.dbo.PWUserPreferences (UserID,AppID,PreferenceKey,PreferenceValue) VALUES (@userID,'Passageways.Portal','DocumentSubscriptionNotification','2')
INSERT INTO Production_Portal_PortalFx.dbo.PWUserPreferences (UserID,AppID,PreferenceKey,PreferenceValue) VALUES (@userID,'Passageways.Portal','MiscellaneousNotification','2')
INSERT INTO Production_Portal_PortalFx.dbo.PWUserPreferences (UserID,AppID,PreferenceKey,PreferenceValue) VALUES (@userID,'Passageways.Portal','UserCommentsStatusNotification','2')
INSERT INTO Production_Portal_PortalFx.dbo.PWUserPreferences (UserID,AppID,PreferenceKey,PreferenceValue) VALUES (@userID,'Passageways.Portal','UserProfileChangedNotification','2')
INSERT INTO Production_Portal_PortalFx.dbo.PWUserPreferences (UserID,AppID,PreferenceKey,PreferenceValue) VALUES (@userID,'Passageways.Modules.Standard','IslandPageChanged','2')
INSERT INTO Production_Portal_PortalFx.dbo.PWUserPreferences (UserID,AppID,PreferenceKey,PreferenceValue) VALUES (@userID,'Passageways.Modules.Standard','WorkgroupNotification','2')
INSERT INTO Production_Portal_PortalFx.dbo.PWUserPreferences (UserID,AppID,PreferenceKey,PreferenceValue) VALUES (@userID,'Passageways.Modules.Standard','VoiceYourConcernsNotification','2')

--Calendar
INSERT INTO Production_Portal_PortalFx.dbo.PWUserPreferences (UserID,AppID,PreferenceKey,PreferenceValue) VALUES (@userID,'Passageways.Modules.Calendar','CalendarSubscription','2')
INSERT INTO Production_Portal_PortalFx.dbo.PWUserPreferences (UserID,AppID,PreferenceKey,PreferenceValue) VALUES (@userID,'Passageways.Modules.Calendar','EventAttendence','2')
INSERT INTO Production_Portal_PortalFx.dbo.PWUserPreferences (UserID,AppID,PreferenceKey,PreferenceValue) VALUES (@userID,'Passageways.Modules.Calendar','EventManagement','2')

--Reservations
INSERT INTO Production_Portal_PortalFx.dbo.PWUserPreferences (UserID,AppID,PreferenceKey,PreferenceValue) VALUES (@userID,'Passageways.Modules.Reservations','ReservationItemCreated','2')

--Management Blogs

INSERT INTO Production_Portal_PortalFx.dbo.PWUserPreferences (UserID,AppID,PreferenceKey,PreferenceValue) VALUES (@userID,'Passageways.Modules.Blog','BlogNotification','2')

--DO NOT TOUCH THE BELOW SECTION
fetch next from c1 into @userID

end

close c1
deallocate c1
--DO NOT TOUCH THE ABOVE SECTION

Related articles

Comments

0 comments

Please sign in to leave a comment.