I want to show all assets assigned to the ticket submitter as a multiple select field in the ticket. I am using the following:

Select query:

select ID, submitter_ID, MACHINE_ID, CUSTOM_FIELD_VALUE0 from HD_TICKET where HD_QUEUE_ID=19

Update query:

update HD_TICKET

    set HD_TICKET.CUSTOM_FIELD_VALUE0 = (select NAME from ASSET where ASSET.OWNER_ID = submitter_ID)

  where 

        (HD_TICKET.ID in (<TICKET_IDS>))

 

I am getting the error:

mysql error: [1242: Subquery returns more than 1 row] in EXECUTE("update HD_TICKET

    set HD_TICKET.CUSTOM_FIELD_VALUE0 = (select NAME from ASSET where ASSET.OWNER_ID = submitter_ID)

  where 

        (HD_TICKET.ID in (24363,24362,24381,24382))")

 

So how can I handle the CUSTOM_FIELD_VALUE0 to grap all the outputs from my update query?

 

Many thanks.

Al

0 Comments   [ + ] Show Comments

Comments

Please log in to comment

Answers

0

A couple of ideas:

1) Try putting DISTINCT before NAME in your Update's subquery.  Assuming it's only pulling multiple matches of the same name, this should work.

2) You could also try using a Select query to populate your multi-select custom field rather than using a ticket rule.  Try this in the custom field's Select Values field:

query: select DISTINCT NAME from ASSET join HD_TICKET on (HD_TICKET.SUBMITTER_ID = ASSET.OWNER_ID)

John

 

Answered 01/08/2013 by: jverbosk
Red Belt

  • I am afraid both did not work ..
  • Unfortunately we do not use the Asset module here, so I'm sorry I can't help more at this point - I'm starting to build out some computer assets, but have not gotten to the point where I have things assigned to users (which is where I would be able to help more here - at this point my ASSET.OWNER_ID values are all zero, so that precludes any useful joins on my side). Perhaps this might help:

    http://www.itninja.com/question/kace-1000-assets-assigned-to-a-user

    John
    • No worries, thanks anyway John.
  • I redid the query - try this in the CUSTOM_1 field, and make sure the Field Type is set to Multiple Select:

    query: SELECT DISTINCT A.NAME FROM ASSET A JOIN HD_TICKET T ON (T.SUBMITTER_ID = A.OWNER_ID)

    John
  • Hi John, have you looked into this any more? That kind of ticket rule would be really handy right now. I just did a helpdesk revamp and brought the Asset field back into existence so the tickets would allign to the equipment or software causing the issue.
  • No, I haven't, sorry. And I'm no longer working for the same company so I no longer have access to the KACE appliances I configured. Hopefully, though, I'll have a lab built out in the near future so I can provide more help.

    John
Please log in to comment
Answer this question or Comment on this question for clarity

Share