4 Replies Latest reply on Aug 27, 2010 8:10 AM by karenpeacock

    Finding the currently assigned user's guid

    Rookie

      In the im_incident table there is a field for the currently assigned analst - usr_currentassignedanalyst. This field reflects the actual name of an analyst in the tps_user.tps_title field. I need to locate a reference to the most recent current assigned analyst's guid in tps_user for incidents currently assinged to that analyst, but using usr_currentassignedanalyst from im_incident isn't an option, as there may be multiple analysts with the same name being assigned to the tps_user table from our company's active directory - I know it's unlikely, but my boss says if it's possible then we need to code for it.  Is there a reference to the currently assigned user's guid in tps_user that can be located within the incident schema? If so, would i need to include any other field value comparisons or aggregate functions to isolate that guid?

       

      Thanks in advance.

        • 1. Re: Finding the currently assigned user's guid
          karenpeacock SupportEmployee

          Hi

           

          Was it a sql query you were looking for here when you say "code for it"?  What are you trying to achieve?  I think you are looking for something like this perhaps:

           

          SELECT     tps_user.tps_guid
          FROM         im_incident AS im_incident INNER JOIN
                                 pm_process_assignment AS pm_process_assignment ON im_incident.im_current_assignment_guid = pm_process_assignment.pm_guid INNER JOIN
                                 tps_user AS tps_user ON pm_process_assignment.pm_user_guid = tps_user.tps_guid
          WHERE     (im_incident.im_id = 14)

           

          This is obviously selecting the user's guid for the current assignment on incident 14 but you get the idea hopefully.

           

          Best wishes

          Karen

          • 2. Re: Finding the currently assigned user's guid
            Rookie

            Karen,

             

            Thank you for your reply.

            Yes it is an sql query for which i am coding.

             

            Would pm_process_assignment.pm_user_guid reflect the curent analyst if the incident has been reassigned?

             

            The nature of the query i am writing requires me to return incidents assigned to a particular analyst in which that analyst has billable time in contrast to incidents not assigned to that analyst in which that analyst has billable time. I can work out the logic of the query, I just need to be able to consistently locate the guid of the analyst that has been most recently assigned to an incident.

             

            Thank you,

            Charles

            • 3. Re: Finding the currently assigned user's guid
              Apprentice

              This may help - it's sql i use when a person leaves the company to give me a list of their current assigned incidents - you'd just need to add in the criteria which relates to the billable time

               

              select i.im_id, * from im_incident i join pm_process p on i.pm_guid=p.pm_guid

              join

              pm_process_assignment pa on p.pm_current_assignment_guid=pa.pm_guid

              join

              tps_user u on pa.pm_user_guid=u.tps_guid

              where

              u.tps_title like '%UserName%'

               

               

              • 4. Re: Finding the currently assigned user's guid
                karenpeacock SupportEmployee

                Hi Charles

                 

                Yes the im_incident.im_current_assignment_guid will hold the guid of the current assignment and so as long as this is linked to pm_process_assignment you will have the current assignment details.

                 

                Best wishes

                Karen