I have this update in a ticket rule -

update HD_TICKET T
    JOIN ASSET A ON A.ID=T.ASSET_ID
        set CUSTOM_FIELD_VALUE17 = (SELECT group_concat(DISTINCT ASSET.NAME) FROM ASSET WHERE ASSET.ID IN (SELECT ASSET_ID FROM ASSET_ASSOCIATION WHERE ASSET_FIELD_ID='10054' AND ASSOCIATED_ASSET_ID = T.ASSET_ID))
    WHERE T.HD_QUEUE_ID=27
     AND (T.ID in (<TICKET_IDS>))

When I run the (SELECT group_concat. . ." statement, separately in work bench I get exactly what I was expecting. When used in a ticket rule, it is setting the field to 0. No errors noted in the rule screen, it simply reads the usual, selected 1 row, updated 1 row. The sub query does return more than one row. Because of this I have IN in place of = prior to the sub query.

From what I find via the Google, it is better to use a JOIN in this instance but I haven't be able to change the select with sub query to a functional JOIN.

Thoughts / Suggestions would be most appreciated. Thank you
2 Comments   [ + ] Show Comments

Comments

  • I'm trying to get a better understanding of what you are trying to accomplish. It looks like you want to set a custom field to the name of the asset which is associated with the asset selected on the ticket, is that correct?
    • Hi Chuck,

      I’ve managed to create a series of connected logical assets. In brief, top asset (A) can have 0 or many sub-assets (B). Each B asset has several connected assets, via field type. This works well so far. Because the HD_TICKET Asset field (ASSET_ID) searches ALL asset types, I moved the selection of the top level asset to a custom_field. When the user selects the top level asset, and clicks Apply/Save, a ticket rule looks up the asset ID and sets it in the HD_TICKET.ASSET_ID field.

      In this ticket rule, I am looking up any/all of the sub- assets tied to the top level asset, then setting the results in a multi-select custom_field. The group_concat works well to format the results in a comma separated output that fits nicely in the multi-select field. Connected assets connect via the asset_assocation table and can be targeted via the asset_field_id number.

      Thus, CUSTOM_FIELD_VALUE17 is the multi_select field, I’m trying to drop the grouped asset.names in. I’m grabbing those names based on HD_TICKET.ASSET_ID by looking at the ASSET_ASSOCIATION between the two.
      • Interesting. I don't know that you can populate a multi-select field at run time with a rule. Those values are part of the field definition, as far as I know.
  • Well, if you set the field type but don’t provide a data set, you can drop your data in with a ticket rule, as long as it’s properly formatted. The initial result is all of the items are selected, but the ticket owner can then select however many they choose. It’s not a User settable/visible field so general appearance isn’t of concern. I have a couple of queues where this is setup and working as desired. This is the first one where I’m looking across multiple tables to establish the data.

    At this point I’ve had to temporarily set this aside and continue with other parts of the queue.
Please log in to comment

There are no answers at this time

Answers

Answer this question or Comment on this question for clarity

Share