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:


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.



Answer Summary:
0 Comments   [ + ] Show Comments


Please log in to comment



Interesting concept.  I wasn't able to recreate the "error" you are getting.  This might be a silly question, but on Ticket Layout - Permissions, what do you have?  I set mine as User Modify instead of User Create or whatever else.

I took the exact code you sent into a Custom Field, Field Type "Single Select", code, Default "Check AD" (copy from the one above it).  Then I went to Ticket Layout, next to Custom_2 field, label none, Required "required", and Permissions "User Modify".

I mean, I pull the full name as "Smith, John" from AD so it shouldn't change, and it should be surrounded by "" be default, unless there is a rogue character somewhere.

Answered 12/06/2012 by: gcarpenter
Green Belt

  • Thanks for testing! I just tried with User Modify instead of User Create (as I couldn't remember exactly what I had it set to previously) and it does the same thing (creates a new entry in the single select field with just the first name). Out of curiosity, did you try saving the ticket again (i.e. add a comment, hit save, etc) - this is where I'm seeing the field change (particularly via the MySQL Query Browser).

    In case it matters, I'm still running 5.3.53053. Not sure what else to do with it at this point beyond upgrading to 5.4 and/or trying to output the result into another custom field.

  • Interesting result John. I'm out of office until Monday but ill look into it and see if I can recreate as well. I'm on 5.4 so it may be a difference in versions but at least we will know.
  • jdornan - Thanks, I appreciate your help!

    No huge rush on this, as I'll be at Konference all next week and this change would require adjustments to existing time tracking tickets, reports and report schedules (and my boss is talking about actually getting a "real" HR system in place to handle this type of stuff), but I'd like to at least know if this approach is feasible - I'm all about proof-of-concept, as who knows where else it will become useful.

  • Understood, enjoy the konference!
  • Yes sir. Does it post the change in the notes section above the message contents (e.g. Changed Manager from "Smith, John" to "John")?

    I want to say the code is fine, it really looks flawless. The problem might be in your "managers" label.

    Have you looked at what information you are pulling from AD? The manager you are testing might have a jacked up AD entry, or you might not be pulling cn and samaccountname from different things (cn =~ sn, givenname). Mine is going to some default "name".

    The only way I can imagine only the first name is showing up is if there is a problem with Kace and formatting, or if there is a mix up between cn (common name) and givenname. That's all I have, man!
  • No, nothing is listed in the change notes on ticket save related to the field data changing. I think the custom field query is "updating" it in the background, which might explain why.

    The managers label is manual - I select the users and assign them to it manually, so there's not much risk of having a problem there (as with Smart labels).

    AD imports are working fine - the Full Name User attribute on the K1000 is pulling from the "name" LDAP attribute, the same as yours. And the behavior is consistent for all users selected.

    Again, I feel this may be due to a space being in the Full Name field causing the names to be spli - in other words, it feels like a bug to me (or a misunderstanding on my part of how queries in custom fields are supposed to work). Since this isn't critical, I'll retry things later after upgrading to 5.4 and see if it persists.

  • Yeah, sounds like it. I'd hate to redo the LDAP pull just because of this one issue, but you might pull CN into a custom_field 8 or something and instead of having the FULL_NAME, use the CUSTOM_FIELD and see if it works. Heck, you copy the "name" call into the custom_field and see if it does the same thing. LOL.

    I'm on hold with KACE regarding an issue as we speak. About a dozen computers stopped checking in with full information and now duplicate computers and report a little information as possible. I checked WMIs and nothing. I might ask a question about it, but I'd rather ask KACE directly since it seems like a glitch.
  • Finally had a chance to try tonight. I was not able to reproduce this on 5.4
  • Thanks for the update - I'll retest after I've moved to 5.4 and advise.

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