How do I configure a custom ticket field to show a value for a phone number?
I have a request of
my Boss to make the Call Back Number field in the New Ticket Form have a default value of a 7 or 10
digit number. Right now I have it as text but some of the Tech are using
the last 4 digits of the phone number instead of putting in the whole number.
Is there a way to configure that one field so they have to fill out the whole
phone number and not just part of it. I think I need
something in the CUSTOM_1 Select Values, but I’m not sure how to accomplish
this.
1 Comment
[ + ] Show comment
Answers (3)
Answer Summary:
Please log in to answer
Posted by:
chucksteel
7 years ago
Top Answer
You could use a rule to validate the phone number field. A query like this should identify tickets that don't have a phone number matching 123-123-1234 format:
SELECT HD_TICKET.ID, CUSTOM_FIELD_VALUE1 as PHONENUMBER,OWNER.EMAIL as OWNEREMAILFROM HD_TICKET JOIN USER OWNER on OWNER.ID = HD_TICKET.OWNER_IDWHERE CUSTOM_FIELD_VALUE1 NOT REGEXP '[0-9]{3}-[0-9]{3}-[0-9]{4}'
If you schedule this rule to run on ticket save you can have it send an email to OWNEREMAIL to inform the owner that the phone number isn't set correctly. If you want to be really annoying you can schedule the rule to run every 15 minutes and they'll get the email until they update the ticket.
Note that I didn't test the query above but I'm reasonably certain it will work.
Comments:
-
How do I get this to run on save? I'll give it a try - DebbieF 7 years ago
-
In Service Desk, Configuration, Rules select the appropriate queue and then click Choose Action, New (SQL). The query above goes in the select statement. Check the box for Email each recipient in query results. Column containing email addresses is OWNEREMAIL. Uncheck the update query box. Under frequency select on ticket save. - chucksteel 7 years ago
-
When I select "View Ticket Search Results" I get this error:
There were syntax errors in your query.
mysqli error: [1064: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'HD_TICKET JOIN USER OWNER on OWNER.ID = HD_TICKET.OWNER_IDWHERE CUSTOM_FIELD_VAL' at line 1] in EXECUTE("SELECT HD_TICKET.ID, CUSTOM_FIELD_VALUE1 as PHONENUMBER,OWNER.EMAIL as OWNEREMAILFROM HD_TICKET JOIN USER OWNER on OWNER.ID = HD_TICKET.OWNER_IDWHERE CUSTOM_FIELD_VALUE1 NOT REGEXP '[0-9]{3}-[0-9]{3}-[0-9]{4}' and HD_TICKET.HD_QUEUE_ID = '1' ") - DebbieF 7 years ago -
The formatting here removed a couple of spaces that were carriage returns. Let's see if this looks better:
SELECT HD_TICKET.ID, CUSTOM_FIELD_VALUE1 as PHONENUMBER,OWNER.EMAIL as OWNEREMAIL
FROM HD_TICKET
JOIN USER OWNER on OWNER.ID = HD_TICKET.OWNER_ID
WHERE CUSTOM_FIELD_VALUE1 NOT REGEXP '[0-9]{3}-[0-9]{3}-[0-9]{4}' - chucksteel 7 years ago -
I now get this message:
There was an error retrieving the data for this page.
Please refresh the page. If the error persists, try resetting the default page layout.
Maybe it means it's correct?!? :-) - DebbieF 7 years ago -
To be honest I never use the View Ticket Search Results link. I save the rule and then test it using a ticket. I recommend setting up a separate queue for testing rules like this. - chucksteel 7 years ago
-
I will do that, thank you. This is a start, I'll let you know if it works for us or not.
If I want to use a 7 digit phone number I can just modify this by deleting the first [0-9]{3}- portion of the query right? - DebbieF 7 years ago
-
Yes, that should work. - chucksteel 7 years ago
Posted by:
MAXintosh
7 years ago
DebbifF,
You have been provided with a very technical solution to your question. I have another, very simple, solution that may work. What if you were to change the label name to read: 10 Digit Call Back Number (or something equivalent)
If you put in the label the number of digits you're requesting that may urge users to provide their numbers in that format. My experience has been I receive the same level of 'work' whether we force or request users to fill out a form using a particular format. If it is forced, users will find a way to just not fill out the field or put junk data into it. If you leave it open, but provide instructions, it yields similar results.
Just something to think about.
Wish you luck!
Posted by:
csninja
7 years ago
Depending on what all you pull from Active Directory you could use a query such as:
query: select distinct(WORK_PHONE) from USER
If the field was set to Single Select, it would list all the numbers and allow you select it from the drop down after typing the last 4 digits.
I don't have much experience with them, but I suppose a rule could be created to force the set 7-10 character length if desired instead.
I have the submitters as the Techs, we don't have this setup for the end user to use yet, so the Tech or Helpdesk Personnel needs to manually put in the phone number. I was hoping for the field to not allow them to proceed unless they put in a 7 or 10 digit number. ___-___-____ or ___-____ and they fill in the number. I could pull it from Active Directory but I don’t know what I’m doing as far as ldap labels. - DebbieF 7 years ago