K1000 Service Desk - Unexpected Behavior with Query in Custom Field
In an attempt to adjust my Time Tracking queue to make future administration a bit easier - i.e. remove the need to manually enter manager's email addresses for users to select, as I'm doing here:
I used a suggestion by jdornan to use a query in a custom field:
His original suggested query for that question was:
query: select full_name from USER
For my purposes, I wanted to restrict the users listed to those in my "managers" label and used the following query:
QUERY: SELECT U.FULL_NAME FROM USER U JOIN USER_LABEL_JT UL ON (U.ID = UL.USER_ID) JOIN LABEL L ON (UL.LABEL_ID = L.ID) WHERE L.NAME = 'managers' ORDER BY U.FULL_NAME
I tested and the query above populates the single-select field correctly, but on ticket save the GUI only has the first name selected (it actually created extra entries for "First Name" and "Last Name" in the single-select field - the originals are still present). If a query is subsequently run in the MySQL Query Browser to list the ticket's field data (i.e. SELECT * FROM HD_TICKET T WHERE T.ID = #), it shows the full name (i.e. Smith, John), unlike the GUI. But if the ticket is saved again, the query browser shows the same data in the custom field as the GUI (i.e. just the first name - John).
It seems as though the custom field query runs each time the ticket is saved, and does not retain the selection made during the initial ticket creation. This makes sense that the query would run each time, but does not make sense that the data would change - perhaps the single select is causing this, or perhaps the selection's data needs copied to another field in order to retain it?
I retested with a multiple select field type and the same thing happened - but, it selects both new split entries and a query on the ticket field data shows the correct full name (i.e. Smith, John), unlike the single select which can only pull one field (i.e. John). This approach would be workable, but the single-select field type would be prefereable.
So my questions are:
1) Is this expected behavior?
2) Is this simply due to the FULL_NAME data having a space in it?
3) Is there any ways to get this working the way I intend? (i.e. as a single-select field with a persistent selection)
I haven't played with trying to output the selection into another custom field, as I wanted to run this by someone before digging any deeper.