/build/static/layout/Breadcrumb_cap_w.png
04/17/2019 173 views

I'm trying to create a custom ticket field for the submitter's location (mapped to physicalDeliveryOfficeName). I would like this field to be dynamic so when the submitter is selected this custom field automatically populates with the correct location information. I can do the following, but this is not dynamic:
query: SELECT NAME FROM USER JOIN ASSET on USER.LOCATION_ID = ASSET.ID where USER.USER_NAME = 'first.last'
This also works:
query: SELECT NAME FROM USER JOIN ASSET on USER.LOCATION_ID = ASSET.ID where USER.ID = '120'

We are already importing the user's location properly and I can pull all of the locations, but I only want a single location based off of the selected user's Active Directory information (physicalDeliveryOfficeName).

Thanks for any help that's provided!

KACE SMA v9.0.270

0 Comments   [ + ] Show comments

Comments


All Answers

0

The SMA doesn't support dynamic fields like that, unfortunately. However, it sounds like you want to set a field based on the user, and that can be done using a custom ticket rule. Here is how I accomplished this on my appliance.

Create your custom field in the service desk and set the default value to "Set on Save"

Create a new custom ticket rule, put anything you want in the wizard, we're just going to erase it.

For the SQL select statement, enter the following:

select HD_TICKET.ID     
from HD_TICKET
WHERE
HD_TICKET.CUSTOM_FIELD_VALUE10 = 'Set on Save'

Check the box for Run Update Query

Enter this for the SQL statement:

update HD_TICKET as T
    set T.CUSTOM_FIELD_VALUE10 = (
select ASSET.NAME FROM USER
JOIN ASSET on ASSET.ID = USER.LOCATION_ID
where USER.ID = SUBMITTER_ID ) where 
        T.ID = <TICKET_IDS>;

Set the rule to run on ticket save.

Note that my custom field in the service desk is field 11, which maps to CUSTOM_FIELD_VALUE10 in the database (the database columns start at 0). You may have to adjust that based on your custom field.


Answered 04/19/2019 by: chucksteel
Red Belt

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