PrevPrev Go to previous topic
NextNext Go to next topic
Last Post 9/5/2011 7:25 PM by  Dennis Odri
Custom SQL to mark client actioned jobs to unread.
 0 Replies
AddThis - Bookmarking and Sharing Button
Sort:
You are not authorized to post a reply.
Author Messages
Dennis Odri
HelpMaster tech support
Helpdesk leader
Helpdesk leader
Posts:45
Points:81


--
9/5/2011 7:25 PM

    Some users have expressed the desire for all jobs that were last actioned by Clients to revert to the unread state in Workgroup Explorer to make jobs requiring attention easier to find in their Open Jobs queue.

    Currently HelpMaster will only set an existing read job to the unread state if the Action Template used to add a clients response from Email Manager or the Web module has the 'Assign To' field selected. This feature request has been logged for inclusion in a future feature release but as a work-around a Priority Manager profile can be created with custom SQL to set every recent job updated by a client back to the unread state as follows;

    1. Create a new Priority Manager profile,
    2. Give it a name,
    3. Select the "Custom SQL" option,
    4. Paste in the following SQL.  Note, the SQL will only "look" for open jobs that have been actioned by clients only in the last 5 minutes,

      SELECT DISTINCT tblHelpdeskJobs.pkID
      FROM tblHelpdeskJobs INNER JOIN
       tblLink_Jobs_Clients ON tblHelpdeskJobs.pkID = tblLink_Jobs_Clients.JobLink LEFT OUTER JOIN qryActionsLinkedToJobs_LastActionOnly ON qryActionsLinkedToJobs_LastActionOnly.ActionJobPKID=tblHelpdeskJobs.pkID
      WHERE (tblLink_Jobs_Clients.IsPrimaryClient = 1) AND
      (DateAdd(minute, 5, LastActionedDate) >= getUTCDate()) AND (dbo.tblHelpdeskJobs.StatusLink <> 2) AND (ActionedBy NOT LIKE '%Priority Manager Service%') AND qryActionsLinkedToJobs_LastActionOnly.ActionedByLink NOT IN (Select tblStaff.ClientLink FROM tblStaff) AND (Properties & 1) = 1

       
    5. On the Actions To Perform screen, select the "Execute this SQL" and paste in the following not including the [Click "Insert Job# tag"] which is a button you need to press on this screen to insert the job variable;

    Update tblHelpdeskJobs Set Properties = Properties-1 Where (Properties & 1) = 1 AND tblHelpdeskJobs.PKID = [Click "Insert Job# tag"]

    1. In the "Frequency" section set the profile to run every '5 Minutes' and the recurrence to "Perform all actions EVERY TIME this profile is run for all jobs found by this profile,
    2. Enable the Priority Manager profile and let it do it's thing,
    3. All jobs actioned by clients will now be reverted to the 'unread' state.

    Done!

    You are not authorized to post a reply.


    New version - HelpMaster v24 now available! by Rod Weir in Announcements with 0 replies A new major release of HelpMaster is now available! Read about some of the new features and improvements here:  https://docs.helpmasterpr...-f...

    New version - HelpMaster v23 now available! by Rod Weir in Announcements with 0 replies A new major release of HelpMaster is now available! Read about some of the new features and improvements here:  https://docs.helpmasterpro.com...

    March 2023 v22.7 maintenance release now available by Rod Weir in Announcements with 0 replies A maintenance release for v22.7 HelpMaster is now available! The maintenance release fixes a number of issues that have been reported since the ini...

    July 2022 release now available - New version released! by Rod Weir in Announcements with 0 replies A new feature release of HelpMaster is now available!  v22.7.22 For a list of the major new features of v22, see the new documentation site ht...

    June 2022 v22.2 maintenance release now available by Rod Weir in Announcements with 0 replies A maintenance release for v22.2 HelpMaster is now available! The maintenance release fixes a number of issues that have been reported since the ini...

    RE: Use PowerBI for HelpMaster dashboards by Rod Weir in Reports and reporting Hi SR, Thanks for the post. Yes, the PowerBI reports for HelpMaster have been well received. Download the latest version, from here. https://www...

    RE: Use PowerBI for HelpMaster dashboards by Scenic_Rim_IT in Reports and reporting Hi Rod - just wondering how PowerBI reports are going? Any chance you have a beta/pre-release version we can help test for you?...

    New version - HelpMaster v24 now available! by Rod Weir in Announcements A new major release of HelpMaster is now available! Read about some of the new features and improvements here:  https://docs.helpmasterpr...-f...

    New version - HelpMaster v23 now available! by Rod Weir in Announcements A new major release of HelpMaster is now available! Read about some of the new features and improvements here:  https://docs.helpmasterpro.com...

    March 2023 v22.7 maintenance release now available by Rod Weir in Announcements A maintenance release for v22.7 HelpMaster is now available! The maintenance release fixes a number of issues that have been reported since the ini...

    July 2022 release now available - New version released! by Rod Weir in Announcements A new feature release of HelpMaster is now available!  v22.7.22 For a list of the major new features of v22, see the new documentation site ht...

    June 2022 v22.2 maintenance release now available by Rod Weir in Announcements A maintenance release for v22.2 HelpMaster is now available! The maintenance release fixes a number of issues that have been reported since the ini...

    February 2022 release now available - New version released! by Rod Weir in Announcements A new major release of HelpMaster is now available! For a list of the major new features of v22, see the new documentation site https://docs.helpma...

    November 2021 release now available - New Major Version! by Rod Weir in Announcements A new major release of HelpMaster is now available! For a list of the major new features of v22, see the new documentation site https://docs.helpma...

    May 2021 release now available - New Major Version! by Rod Weir in Announcements A new major release of HelpMaster is now available! For a list of the major new features of v21, see the new documentation site https://docs.helpma...