/build/static/layout/Breadcrumb_cap_w.png

Setting up Customize Fields and layout in Kace

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

Answers (9)

Posted by: steelc 12 years ago
Senior Yellow Belt
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.
Posted by: steelc 12 years ago
Senior Yellow Belt
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.
Posted by: quickwhips 12 years ago
Orange Senior Belt
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.
Posted by: MacDude 10 years ago
Fifth Degree Brown Belt
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>

 


Comments:
  • I'm tried using your code, but it didn't work for me. I have K1000 6.4.522 and trying to display the Custom 1 field. - londeaux 7 years ago
Posted by: steelc 12 years ago
Senior Yellow Belt
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.
Posted by: quickwhips 12 years ago
Orange Senior Belt
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.
Posted by: quickwhips 12 years ago
Orange Senior Belt
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) ")
Posted by: quickwhips 12 years ago
Orange Senior Belt
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.
Posted by: steelc 12 years ago
Senior Yellow Belt
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.
Rating comments in this legacy AppDeploy message board thread won't reorder them,
so that the conversation will remain readable.

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