Service desk... Auto-Populate field based on department.
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
Answers (1)
Please log in to answer
Posted by:
chucksteel
7 years ago
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.
Comments:
-
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" - raul102801 7 years ago-
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. - chucksteel 7 years ago
