/build/static/layout/Breadcrumb_cap_w.png

KACE Product Support Question


Service desk... Auto-Populate field based on department.

08/12/2016 824 views

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


All 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.

Don't be a Stranger!

Sign up today to participate, stay informed, earn points and establish a reputation for yourself!

Sign up! or login

Share

 
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