Dynamic Location Field
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
Answers (1)
Top Answer
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.
Comments:
-
Thanks for the info ChuckSteel. I'll put this in my bag of tricks for next time. For now, we are going to bypass the user-by-location field. We decided to create Distribution Groups by location and will assign these members to the Owner field.
Thanks again! - Jarrettl 5 years ago
