3 Replies Latest reply on Jan 26, 2018 11:57 AM by Christopher.Bakken

    Adding fields to Survey Metric View

    phan10ms Apprentice

      I'm trying to add a ResolvedBy field to this list view but I noticed that there isn't an Incident relationship to the Survey Metric View business object, but it looks like it's pulling other information from incident.  Looks like it's a table view in the database.  How is this done?

       

        • 1. Re: Adding fields to Survey Metric View
          Christopher.Bakken SupportEmployee

          The views are hardcoded at the DB level and cannot be altered. You won't be able to add or remove anything from them as there isn't a mechanism to drive the changes in the GUI to the query generating the view.

           

          For the sake of information, this is the query for the views. SurveyMetricView is actually a union of independent Incident and ServiceReq views.

           

          CREATE VIEW [dbo].[SurveyMetricView]

          AS

          SELECT * FROM IncidentSurveyMetricView

          UNION

          SELECT * FROM ServiceReqSurveyMetricView

          GO

           

          ----

          CREATE VIEW [dbo].[ServiceReqSurveyMetricView]

          AS

          SELECT ServiceReq.ClosedDateTime ClosedDateTime

          ,ServiceReq.Service Service

          ,ServiceReq.OWNER OWNER

          ,ServiceReq.OwnerTeam OwnerTeam

          ,ServiceReq.OrganizationUnitID OrganizationUnitID

          ,ServiceReq.ServiceReqNumber ID

          ,FRS_SurveyAnswer2.Category Category

          ,FRS_SurveyAnswer2.Category_de Category_de

          ,FRS_SurveyAnswer2.Category_fr Category_fr

          ,FRS_SurveyAnswer2.NumericValue NumericValue

          ,ServiceReq.RecId BORecId

          ,FRS_SurveyAnswer2.SurveyAnswerId RecId

          ,CI3.OWNER CIOwner

          ,CASE

          WHEN FRS_SurveySession1.IdentifierType = 'serviceReq'

          THEN 'Service Request'

          ELSE FRS_SurveySession1.IdentifierType

          END BOType

          FROM dbo.ServiceReq

          JOIN dbo.FRS_SurveySession FRS_SurveySession1 ON (ServiceReq.RecId = FRS_SurveySession1.Identifier)

          JOIN dbo.FRS_SurveyAnswer FRS_SurveyAnswer2 ON (FRS_SurveySession1.SurveySessionId = FRS_SurveyAnswer2.SessionId)

          JOIN dbo.CI CI3 ON (

          ServiceReq.Service_Valid = CI3.RecId

          AND CI3.CIType = 'Service'

          )

          GO

           

           

          ----

          CREATE VIEW [dbo].[IncidentSurveyMetricView]

          AS

          SELECT Incident.ClosedDateTime ClosedDateTime

          ,Incident.Service Service

          ,Incident.OWNER OWNER

          ,Incident.OwnerTeam OwnerTeam

          ,Incident.OrganizationUnitID OrganizationUnitID

          ,Incident.IncidentNumber ID

          ,FRS_SurveyAnswer2.Category Category

          ,FRS_SurveyAnswer2.Category_de Category_de

          ,FRS_SurveyAnswer2.Category_fr Category_fr

          ,FRS_SurveyAnswer2.NumericValue NumericValue

          ,Incident.RecId BORecId

          ,FRS_SurveyAnswer2.SurveyAnswerId RecId

          ,CI3.OWNER CIOwner

          ,FRS_SurveySession1.IdentifierType BOType

          FROM dbo.Incident

          JOIN dbo.FRS_SurveySession FRS_SurveySession1 ON (Incident.RecId = FRS_SurveySession1.Identifier)

          JOIN dbo.FRS_SurveyAnswer FRS_SurveyAnswer2 ON (FRS_SurveySession1.SurveySessionId = FRS_SurveyAnswer2.SessionId)

          JOIN dbo.CI CI3 ON (

          Incident.Service_Valid = CI3.RecId

          AND CI3.CIType = 'Service'

          )

          • 2. Re: Adding fields to Survey Metric View
            AnthonyDowns Specialist

            Hi Chris,

             

            Would it be possible to update a Heat reference to this view after changes were made? I can alter the views to include "ResolvedBy" but the problem is Heat doesn't notice this extra field on the BO.

             

            It could come in handy to be able to update from SQL and have the changes reflected in Heat.

            • 3. Re: Adding fields to Survey Metric View
              Christopher.Bakken SupportEmployee

              I doubt it. It's not something that was ever expected to be changed.