I am setting up a new queue on our system and the department that is going to use this queue would like to have a department dropdown so they can choose a department they are opening the ticket for but would like to have a G/L account field that automatically populates based on a department picked from the drop down.... Is this even possible? Not sure if I am overthinking this and the solution is easier than I think or if this is going to be something major.

Any ideas?

Thanks

0 Comments   [ + ] Show Comments

Comments

Please log in to comment

Answers

1

Yes, this is possible. 

Step 1 is to modify the Department asset type to include a field to hold the G/L account number.
Step 2 is to populate the field appropriately. You should be able to do this with an asset import, hopefully.
Step 3 is to create the custom fields in the queue that will hold the Department and the G/L account number.
For the Department field you can use the query: syntax to pull the department names from the Asset table:
query:select ASSET.NAME FROM ASSET WHERE ASSET_TYPE_ID=2 ORDER BY ASSET.NAME ASC

Step 4 is to create a custom rule that will populate the G/L account number based on the value of the Department custom field. This will be more complicated and the values will be dependent on your database. I recommend using a tool like MySQL Workbench to connect to your database and get the necessary values. Your update query will look something like this:
update HD_TICKET
    set HD_TICKET.CUSTOM_FIELD_VALUE1 = 
(SELECT ASSET_DATA_2.FIELD_[NUMBER] as ACCOUNT FROM ASSET
JOIN ASSET_DATA_2 on ASSET_DATA_2.ID = ASSET.ASSET_DATA_ID 
WHERE ASSET.NAME = CUSTOM_FIELD_VALUE0
AND ASSET.ASSET_TYPE_ID = 2)
  where 
        (HD_TICKET.ID in (<TICKET_IDS>))
This update query has a select statement that finds the value of the G/L account in the ASSET_DATA_2 table. This table is where the fields that are defined for the Department asset are stored (assuming your department asset is also ID 2). The FIELD_[NUMBER] will depend on your database. If you look in the ASSET_FIELD_DEFINITION table you will be able to get the ID of the G/L account. So if that field's ID is 57 then the select statement needs to query ASSET_DATA_2.FIELD_57.

This query also assumes that the department name is being stored in the queue's CUSTOM_FIELD_VALUE0 (the first custom field) and the G/L account is going into CUSTOM_FIELD_VALUE1 (the second custom field). You may need to adjust those accordingly.

Answered 08/15/2016 by: chucksteel
Red Belt

  • Great information CHucksteel. I followed your instructions and am fine up to step 3. I created CUSTOM_1 and named it Department. Created is as a single select and added the statement query:select ASSET.NAME FROM ASSET WHERE ASSET_TYPE_ID=2 ORDER BY ASSET.NAME ASC

    When I go to service desk and open a new ticket, the field for department is there and has a drop down list just as expected. I then created a custom_2 and named it G/L# created it as a single select and added the 2nd query you provided my modifying the field number to the correct one for my system:
    update HD_TICKET
    set HD_TICKET.CUSTOM_FIELD_VALUE1 =
    (SELECT ASSET_DATA_2.FIELD_10073 as ACCOUNT FROM ASSET
    JOIN ASSET_DATA_2 on ASSET_DATA_2.ID = ASSET.ASSET_DATA_ID
    WHERE ASSET.NAME = CUSTOM_FIELD_VALUE0
    AND ASSET.ASSET_TYPE_ID = 2)
    where
    (HD_TICKET.ID in (<TICKET_IDS>))

    This did not work and I don't really know if it is an issue with the statement or with the fact that the field is set a single select:
    I get the error " An SQL error occurred in generating the list"
    • The code in step 4 doesn't go in the custom field definition. Rather it is used in a custom ticket rule.You will need to create a custom ticket rule that runs on save and runs that update query.
Please log in to comment
Answer this question or Comment on this question for clarity

Share