Hello,

I'm looking for a solution for the following problem.

I have a sql command who returns multiple rows - in this case - 3
SELECT *
FROM ASSET_ASSOCIATION
WHERE ASSET_FIELD_ID = 88
AND ASSOCIATED_ASSET_ID = 1234

Could I set this 3 rows into the custom fields from my ticket?

>SQL-update (example, for illustrate)
update HD_TICKET
    set HD_TICKET.CUSTOM_FIELD_VALUE1 = '1',
HD_TICKET.CUSTOM_FIELD_VALUE2 = '2',
HD_TICKET.CUSTOM_FIELD_VALUE3 = '3',
HD_TICKET.CUSTOM_FIELD_VALUE4 = '4'
  where
        (HD_TICKET.ID in (<TICKET_IDS>))

The first three are to be filled with the 3 rows I get from the sql select and the fourth remains empty.
Sometimes I get 3 rows back from my sql select, sometimes I get only 2 rows back.

Is it possible?

Hope you can help, thank you!


Answer Summary:
Cancel
3 Comments   [ + ] Show Comments

Comments

  • I think I have a way to make this work but I have one more question. Do you want the name of the associated assets to appear in the child tickets? If so that is the easiest, but if you want the value from another field in the associated asset it will be more difficult.
  • Oh, I've seen that comment to late. The solution I've posted works, but .. when I open my asset (for example pc111), there is no association to my created ticket.. I think this service desk story is much more work that I thought at the beginning..
  • Would I have the opportunity to chose a asset in the child ticket (manual) and then, for example, I confirm every checkbox in the childticket, the assets in the customfield change the associated room asset? That would be awesome!!
Please log in to comment

Answer Chosen by the Author

2
Solution:

> SQL-update
update HD_TICKET as T
  join HD_STATUS as T5
    on T.HD_QUEUE_ID = T5.HD_QUEUE_ID
  left join HD_TICKET as PARENT
    on PARENT.ID = T.PARENT_ID
  left join ORG1.ASSET as A
    on A.ID = PARENT.ASSET_ID
    set T.CUSTOM_FIELD_VALUE0 = (SELECT NAME FROM ORG1.ASSET
WHERE ASSET.ID =
(
SELECT ASSET_ID FROM ORG1.ASSET_ASSOCIATION
WHERE ASSET_FIELD_ID = 'ID for Printer'
AND ASSOCIATED_ASSET_ID = A.ID
OR ASSET_FIELD_ID = 'ID for PC'
AND ASSOCIATED_ASSET_ID = A.ID
OR ASSET_FIELD_ID = 'ID for monitor'
AND ASSOCIATED_ASSET_ID = A.ID
LIMIT 1
))
  where
        (T.ID in (<TICKET_IDS>))

I have such a ticketrule for every customfield to update the field.

Important is to change the 'LIMIT' at the end of the command - for example ticketrule 2 has the 'LIMIT 1,1' that gives the second row from my select back and set it into the custom field (ticketrule 3 = 'LIMIT 2,1' and so on)

My checklist was automatically filled with the associated assets, from the asset that I've set in the parentticket.

Thanks for your help chucksteel!!
Answered 09/07/2016 by: svmay
Sixth Degree Black Belt

Please log in to comment

Answers

1
I would recommend using separate rules to update each custom field. Here is an example update statment that I use that updates a custom field with data from an asset:
update HD_TICKET    set HD_TICKET.CUSTOM_FIELD_VALUE0 = (SELECT ASSET_DATA_13.FIELD_89 FROM ASSET_DATA_13 WHEREASSET_DATA_13.ID = (SELECT ASSET.ASSET_DATA_ID FROM ASSET WHERE ASSET.NAME = HD_TICKET.CUSTOM_FIELD_VALUE13))  where         (HD_TICKET.ID in (<TICKET_IDS>))
In this case I have a custom field (14) that refers to an asset. When an asset is selected for that custom field the value from asset type 13 field 89 is added to the custom field 1.

Answered 09/06/2016 by: chucksteel
Red Belt

  • Thanks again for your answer chucksteel! Is the asset in your sql command dynamic? Or is it in ervery ticket the same asset?
  • In my case, I want to fill the associated assets from my parent ticket in the custom fields of the child tickets
    • Yes, I saw your other post and thought this might be related. While it is possible to find child tickets by searching for tickets where the parent ID is the ticket that was saved, I'm not sure if that really helps here. Especially since you have a variable number of child tickets based on the room asset. Without being able to create tickets programmatically I'm not sure you can accomplish your goal.
      • I think different currently - I think it's easier with one child ticket. I build the child ticket with customfields. For example; custom field 1 is an easyselection and custom field 2 a chechbox - and again - so can I build a checklist - Now I have only filled the custom fields (easyselection; field1, field3, field5, etc.) with the associated assets - i can live with an big checklist where all my assets are in, instead of a smaller checklist where some assets are missing
      • Ah. In that case you can update the child tickets by using WHERE HD_TICKET.PARENT_ID = <TICKET_IDS> (this assumes one child per parent).

        Updating the multiple custom fields with a variable number of results will be more challenging. Is there a known maximum? If so you could use separate queries to select one at a time using limit statements.
      • I know nothing from a maximum - now i have 24 customfields. That means I have 12 fields to fill with associated asset names and 12 chechboxes to confirm if they moved yet. So how do I get to the associated asset names from the asset in the parent ticket. I don't get it yet
      • Thanks chucksteel!!
Please log in to comment
Answer this question or Comment on this question for clarity
Admin Script Editor
Admin Script Editor is an integrated scripting environment available free here at ITNinja

Share