I am trying to find out how to setup up our ticket system so when a user name is selected it will automatically fill out some custom files such as phone number, location, and some other user information. How do I make it so when I pick the submitter in the submitter field it then will fill in the location and phone number automatically in Custom_1 field or Custom_2 field. Is this possible to do this from the customize fields and layout? If so can someone get me started or a link to how to begin this journey of discovery?
0 Comments   [ + ] Show Comments

Comments

Please log in to comment

Rating comments in this legacy AppDeploy message board thread won't reorder them,
so that the conversation will remain readable.

Answers

0
We have custom fields for location and phone number and the data is populated via a rule. I have the default value for the field set to "Set on Save" and the rule only runs if the ticket is saved with that in the field. The SQL to update the field is:

update HD_TICKET as T
set T.CUSTOM_FIELD_VALUE5 = (select WORK_PHONE FROM USER where ID = SUBMITTER_ID) where
T.ID = <TICKET_IDS>;

This is for the phone number which is custom field 6. The SQL table starts the numbering of the fields at 0 so the column to be updated is CUSTOM_FIELD_VALUE5.

Once the ticket is saved, the value gets updated in the field. I don't believe there is a way to make it update the field on the fly, only on ticket save.
Answered 02/01/2012 by: steelc
Senior Yellow Belt

Please log in to comment
0
Ok I'm starting to understand what I need to do but how do you set the default value to set on save? Are you just making a text field then? Thanks for all the help so far.
Answered 02/01/2012 by: quickwhips
Orange Belt

Please log in to comment
2
Yes, the field is just a text field and Set on Save is the default value. That's the convention I have developed for any custom fields that a rule will enter the information. I found that if I left the field blank users would enter something and the rule wouldn't always work as I intended.
Answered 02/02/2012 by: steelc
Senior Yellow Belt

Please log in to comment
0
I tried this today and I am getting an error. Below is the SQL query and below that is the error I'm getting.

update HD_TICKET as T
set T.CUSTOM_FIELD_VALUE0 = (select LOCATION FROM USER where ID = T.SUBMITTER_ID) where
T.ID = <TICKET_IDS>


Error below
09:00> Starting: Fri, 03 Feb 2012 13:09:00 -0500
09:00> Executing Select Query...
09:00> mysql error: [1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '<TICKET_IDS> and (HD_TICKET.ID = 2191)' at line 3] in EXECUTE("update HD_TICKET as T
set T.CUSTOM_FIELD_VALUE0 = (select LOCATION FROM USER where ID = T.SUBMITTER_ID) where
T.ID = <TICKET_IDS> and (HD_TICKET.ID = 2191) ")
Answered 02/03/2012 by: quickwhips
Orange Belt

Please log in to comment
2
The only difference that I see is that you have ID = T.SUBMITTER_ID and I just have ID = SUBMITTER_ID in the sub select. What is your select statement? Be sure that it includes a select for the ticket ID.
Answered 02/03/2012 by: steelc
Senior Yellow Belt

Please log in to comment
0
The select statement is select LOCATION FROM USER where ID = T.SUBMITTER_ID

I listed all i have in the rule in the forum post above.
Answered 02/03/2012 by: quickwhips
Orange Belt

Please log in to comment
0
I don't think the issue is with the column you're selecting but the row. I don't think that the sub select statement knows what T.SUBMITTER_ID is (because T is defined in the outer update statement).

A rule has two parts, the select statement and the update statement. You posted the update statement but I don't see the select query that determines which tickets to act on.
Answered 02/03/2012 by: steelc
Senior Yellow Belt

Please log in to comment
1
Thanks for all the help steelc. Its working now I'm really new to this and after I realized that I was putting it in the select field and not the update field. After that its work now.
Answered 02/03/2012 by: quickwhips
Orange Belt

Please log in to comment
0

This seems to work. Be aware that my queue is ID=12 and the Custom Field for Location in that queue is the second field in the GUI, but CUSTOM_FIELD_1 in the database. 

SELECT QUERY:

SELECT T.ID AS ID, U.USER_NAME, C.DESCRIPTION, C.ID
FROM HD_TICKET TJOIN USER U ON (U.ID = HD_TICKET.SUBMITTER_ID) 
JOIN HD_TICKET_CHANGE C ON (C.HD_TICKET_ID = T.ID)
WHERE (T.HD_QUEUE_ID = 12  and C.DESCRIPTION LIKE 'TICKET CREATED%' and C.ID=<CHANGE_ID>)

 

UPDATE QUERY: 

UPDATE HD_TICKET T
JOIN USER U ON (U.ID = T.SUBMITTER_ID)
JOIN HD_TICKET_CHANGE C ON (C.HD_TICKET_ID = T.ID)
SET T.CUSTOM_FIELD_VALUE1 = U.LOCATION, C.DESCRIPTION = "Location Set"
WHERE C.ID=<TICKET_IDS>

 

Answered 03/27/2014 by: MacDude
Fourth Degree Brown Belt

Please log in to comment
Answer this question or Comment on this question for clarity