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

    Finding the currently assigned user's guid


      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



          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


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



            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,


            • 3. Re: Finding the currently assigned user's guid
              lianne.redding 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


              pm_process_assignment pa on p.pm_current_assignment_guid=pa.pm_guid


              tps_user u on pa.pm_user_guid=u.tps_guid


              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