• 0 Vote(s) - 0 Average
  • 5
  • 4
  • 3
  • 2
  • 1
Thread Modes

Internal Update Step to Copy Data in a Specific Cell
#1
My goal here is to store just the comment left by a technician in one of the AVAILABLE_FIELD_X cells for the given ticket after they complete a specific operation action in the associated workflow. This text is intended to be called in a later workflow step when emailing the requestor regarding the ticket they have open. I understand the most direct method to send this comment would be to pull the requisite data from the AM_ACTION table and store it in a workflow variable, but due to the way I have configured the emails to use mail components to "build" the contents of the email, it would end up being easier to manage having the mail component reference the cell in the SD_REQUEST table rather than have to individually manage each workflow step that needs to show this data in an email. 

After an exhaustive day of trial and error, I've come up with the following SQL query to pull the correct cell from AM_ACTION and update a cell in SD_REQUEST. I have tested it in the MC_MONITORING console and verified it works as designed, however applying this to the Internal Update Step in the workflow has not been successful - I consistent get an error stating "An Error has Occurred. The Wizard Must be Stopped." 

Code:
UPDATE SD_REQUEST
SET SD_REQUEST.AVAILABLE_FIELD_2=AM_ACTION.DESCRIPTION
FROM SD_REQUEST
    CROSS APPLY
        (SELECT TOP 1 AM_ACTION.DESCRIPTION
        FROM AM_ACTION
        WHERE AM_ACTION.REQUEST_ID LIKE '@@ID@@'
            AND AM_ACTION.ACTION_TYPE_ID IN (20)
        ORDER BY AM_ACTION.ACTION_ID DESC) AM_ACTION
WHERE SD_REQUEST.REQUEST_ID LIKE '@@ID@@'

Ideally I'd like to stick with the concept of the above query to keep this compact and consolidated to a single internal update step. I'd like to think I am close to having the right answer since it works conceptually in MC_MONITORING, but I must be missing something obvious. Any and all help in figuring this out would be greatly appreciated.

Thanks
Tony Bozeglav
Systems Administrator
Hyatt Legal Plans, a MetLife Company


#2
(06-06-2019, 02:29 PM)tbozeglav Wrote: My goal here is to store just the comment left by a technician in one of the AVAILABLE_FIELD_X cells for the given ticket after they complete a specific operation action in the associated workflow. This text is intended to be called in a later workflow step when emailing the requestor regarding the ticket they have open. I understand the most direct method to send this comment would be to pull the requisite data from the AM_ACTION table and store it in a workflow variable, but due to the way I have configured the emails to use mail components to "build" the contents of the email, it would end up being easier to manage having the mail component reference the cell in the SD_REQUEST table rather than have to individually manage each workflow step that needs to show this data in an email. 

After an exhaustive day of trial and error, I've come up with the following SQL query to pull the correct cell from AM_ACTION and update a cell in SD_REQUEST. I have tested it in the MC_MONITORING console and verified it works as designed, however applying this to the Internal Update Step in the workflow has not been successful - I consistent get an error stating "An Error has Occurred. The Wizard Must be Stopped." 

Code:
UPDATE SD_REQUEST
SET SD_REQUEST.AVAILABLE_FIELD_2=AM_ACTION.DESCRIPTION
FROM SD_REQUEST
    CROSS APPLY
        (SELECT TOP 1 AM_ACTION.DESCRIPTION
        FROM AM_ACTION
        WHERE AM_ACTION.REQUEST_ID LIKE '@@ID@@'
            AND AM_ACTION.ACTION_TYPE_ID IN (20)
        ORDER BY AM_ACTION.ACTION_ID DESC) AM_ACTION
WHERE SD_REQUEST.REQUEST_ID LIKE '@@ID@@'

Ideally I'd like to stick with the concept of the above query to keep this compact and consolidated to a single internal update step. I'd like to think I am close to having the right answer since it works conceptually in MC_MONITORING, but I must be missing something obvious. Any and all help in figuring this out would be greatly appreciated.

Thanks


Try this code:
Code:
UPDATE    SD_REQUEST
SET    SD_REQUEST.AVAILABLE_FIELD_2 =
    (
    SELECT    TOP 1 AM_ACTION.DESCRIPTION
    FROM    AM_ACTION
    WHERE    AM_ACTION.REQUEST_ID = @@ID@@
        AND AM_ACTION.ACTION_TYPE_ID IN (20)
    ORDER BY AM_ACTION.ACTION_ID DESC
    )
WHERE    SD_REQUEST.REQUEST_ID = @@ID@@

Also, as a heads up, the AVAILABLE_FIELD_X fields have a 100 character limit, so you may want to consider using a larger or custom field for this if you expect to have descriptions that exceed this limit.
Sarah Schumacher
Service Desk Tool Administrator

#3
(06-12-2019, 01:34 PM)sarahschumacher Wrote: Try this code:
Code:
UPDATE    SD_REQUEST
SET    SD_REQUEST.AVAILABLE_FIELD_2 =
    (
    SELECT    TOP 1 AM_ACTION.DESCRIPTION
    FROM    AM_ACTION
    WHERE    AM_ACTION.REQUEST_ID = @@ID@@
        AND AM_ACTION.ACTION_TYPE_ID IN (20)
    ORDER BY AM_ACTION.ACTION_ID DESC
    )
WHERE    SD_REQUEST.REQUEST_ID = @@ID@@

Also, as a heads up, the AVAILABLE_FIELD_X fields have a 100 character limit, so you may want to consider using a larger or custom field for this if you expect to have descriptions that exceed this limit.

That seemed to work! Thanks!

I ended up copying the data to SD_REQUEST.DESCRIPTION to avoid complications with character limits.
Tony Bozeglav
Systems Administrator
Hyatt Legal Plans, a MetLife Company







Users browsing this thread: 1 Guest(s)