KACE Product Support

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.

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.

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.

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>

 

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.

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.

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) ")

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.

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.