/build/static/layout/Breadcrumb_cap_w.png

Dell K1000 - Service Desk - Select command set

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!


Asked 8 years ago 1455 views
3 Comments   [ + ] Show 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. - chucksteel 8 years ago
  • 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.. - svmay 8 years ago
  • 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!! - svmay 8 years ago

Answers (2)

Answer Summary:
Posted by: svmay 8 years ago
Red Belt
2

Top Answer

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!!
Posted by: chucksteel 8 years ago
Red Belt
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.

Comments:
  • Thanks again for your answer chucksteel! Is the asset in your sql command dynamic? Or is it in ervery ticket the same asset? - svmay 8 years ago
  • In my case, I want to fill the associated assets from my parent ticket in the custom fields of the child tickets - svmay 8 years ago
    • 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. - chucksteel 8 years ago
      • 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 - svmay 8 years ago
      • 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. - chucksteel 8 years ago
      • 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 - svmay 8 years ago
      • Thanks chucksteel!! - svmay 8 years ago
 
This website uses cookies. By continuing to use this site and/or clicking the "Accept" button you are providing consent Quest Software and its affiliates do NOT sell the Personal Data you provide to us either when you register on our websites or when you do business with us. For more information about our Privacy Policy and our data protection efforts, please visit GDPR-HQ