4 Replies Latest reply on Jan 14, 2019 9:49 AM by rspringe

    One to Many field in Xtraction

    rspringe Apprentice

      I would like to the top most record from Xtraction based on a join.  Table A drives the query while it relies on data from Table B.  For each record in Table A, there could be multiple records in Table B that coincide with Table A.  So in Table B, I created  a field using the following SQL statement:  (SELECT top 1 act_log.CALL_REQ_ID FROM act_log INNER JOIN act_type ON act_log.type = act_type.code INNER JOIN call_req ON act_log.call_req_id = call_req.persid WHERE (call_req.persid = 'ACTIVITY_LAST_LOG_COMMENT.CALL_REQ_ID') and act_log.type='LOG' order by time_stamp desc).  There are other fields that I have made available in TABLE B.  When I leverage one of the those, the value is blank even though I should data.  Can anyone help me with this?

        • 1. Re: One to Many field in Xtraction
          GreggSmith SupportEmployee

          Even though the logic is incorrect, in reviewing your query, it appears you are trying to return information about the last LOG activity record for each ticket in Call_Req.  Is that correct?

          • 2. Re: One to Many field in Xtraction
            rspringe Apprentice

            Hey Gregg, Happy New Year.  Yes. I am trying to get the just the last log comment per ticket.

            • 3. Re: One to Many field in Xtraction
              GreggSmith SupportEmployee

              Okay, in the Incident view (or whichever view you looking to add this into),

               

              • create a new table with ID, Text, and Alias of LAST_LOG_COMMENT_LINK
                • This Table field should contain the following:

              (SELECT call_req_id, MAX(id) max_id FROM dbo.act_log WHERE type = 'LOG' GROUP BY call_req_id)

                • Map INCIDENT_FK; Expression = LAST_LOG_COMMENT_LINK.CALL_REQ_ID
                • Map LAST_LOG_COMMENT_FK; Expression = LAST_LOG_LINK.MAX_ID
              • create a new table with ID, Text, and Alias of LAST_LOG_COMMENT
                • The Table field should be ACT_LOG
                • Map LAST_LOG_COMMENT_PK; Expression = LAST_LOG_COMMENT.ID
                • Map LAST_LOG_COMMENT; Expression = CONVERT(NVARCHAR(MAX), LAST_LOG_COMMENT.DESCRIPTION)
                • Map any other act_log fields you need - Analyst that logged the comment, date/time of comment, etc.
              • join INCIDENT as LEFT OUTER JOIN to LAST_LOG_COMMENT_LINK
              • join LAST_LOG_COMMENT_:LINK as LEFT OUTER JOIN to LAST_LOG_COMMENT
              • 4. Re: One to Many field in Xtraction
                rspringe Apprentice

                Thanks Gregg,  This solution worked.