Description

With a metastasizing number of computers and sales, production and administrative systems integrated into Cyrious and dependent on internal networks and servers, we needed a system to request IT Administration and Cyrious support which could be scheduled and then tracked by all concerned parties. Additionally, we wanted to accumulate times for different kinds of support to better understand the nature and extent of resources needed .And the system needed to be faster than finding someone on the phone and at least as fast as crafting and sending an email.

This presentation is intended as a case study for other users who are considering or embarking on a similar path in the hopes that it will make their implementation faster and smoother. We have included source code and examples for those who want the detail of “how it's done”. Tables and table linkages should apply to all installations. Where variables and UDFs are included, readers should be able to recognize those which would need to be renamed for their application. If there are questions or issues regarding anything in this posting, call or email us for clarification.

Basic Discussion of Approach

We decided to adapt the Cyrious Service Ticket system to this purpose. It has separate Explorers, and tickets can be created and manipulated outside the Estimating and Ordering system. And with a little jerry-rigging the Company and Contacts system could be adapted to our internal use without having to create a new database.

Steps

  1. CREATED A SUBSIDIARY OF OUR COMPANY

The approach as noted above was to create a system which would require a minimum of “clicks” and entries to produce a request. The core requirement was a quick way to select the Support person receiving the request and a similarly easy way to identify the requester. Since Service Tickets assume an external customer, in this system the Primary Salesperson is the one responsible for IT and Systems support and the person receiving the request. The Contact is the one requesting the support. Accordingly, we created a subsidiary of our company which already contained most of our Associates as contacts and permitted us to set the IT Head as the default Primary Salesperson. We explain below why that is important to minimizing clicks and assuring that macro-generated notification emails go to the correct person. With that accomplished we already had the email addresses we needed and a list from which the requester could pick him/her self.

  1. CREATED NEW PRODUCT CATEGORY TO ASSURE SEPARATION FROM NORMAL PRODUCTS

Next, we created a Product Category for the IT Support products and 4 specific products to be ordered, descriptive of the nature of support request being made. Note that this is a core element of establishing an internal system. It is critical to understand what is being ordered and to make the products and selection lists as self-evident to the person ordering as possible.

asg_it_support_products_category.jpg

  1. CREATED LIST VARIABLES FOR SELECTING SUPPORT TYPES TO SPEED ENTRIES WITH DROP DOWN LISTS OF REQUESTS

For each product we created a SupportType variable and an associated list of types of support from which the user could make a more specific selection of the support being requested as shown below.it_support_variables.jpgst_list_variable_setup.jpg

support_types_list.jpg

  1. SET UP THE PRIORITY VARIABLE

To set up the Priority Variable we created 4 priorities in the System Setup/Priority setup page. To better stand out in the Explorer listing, we set the “Hair on Fire” Priority to Font Color red which causes the entire line to show in red when that priority is set for that line item. The Priority setup also has a spin edit field for the “Level” which is very handy for sorting in reports, allowing you to name the priorities whatever you want and sort on the level. The example below shows it at 50. For the final solution we set the levels as 1 - 4. Note also that there is a Ticket Type variable set up in the same way in the System Setup page which is standard in the Explorer Column chooser and an additional useful sort field.

priority_setup.jpg

  1. CREATED THE PRICING FORM

We created a Pricing Form for the User Input. As noted above Cyrious has a Service Ticket Explorer which includes variables which can be used as hooks or filters for grouping and sorting – the ticket type and the priority which ar e included by default in the Product's “General” category. We added the priority variable to the pricing form.. We also added the quantity variable to serve as the entry field for the time estimated to complete the request and a text input variable for adding comments and explanations. We set the default output of the Description variable to be the selection in the Support Tyre list so the user does not need to type anything in the Description line ti give the line item meaning – I.e. minimize clicks. .Additionally. because the line item description is populated automatically, the user does not need to make an entry in the Top Level Service Ticket description line as the latter defaults to the line item description when left blank.

it_support_product_pricing_form.jpg

  1. CREATED LAYOUTS FOR SCREEN, PRINT AND PDF APPLICATIONS

Next we created a single layout to serve as the Summary and Detail layout. We wanted this to look like an internal request “ticket” and not a work order or estimate and employed some of Controls HTML formatting options for it to so appear on the screen and in the printed form as shown below with the code.

Note that the Priority and TicketType variables (we are not using the TicketType in the initial roll-out) requires property statements to display the selection values in CFL.

Declare SupportType := "IT & Admin Support";
//Declare STTicketType := TransDetail.TicketTypeName;
Declare PriorityName := TransDetail.PriorityName;
HTMLFONT(
HTMLRETURN +   HTMLBOLD( "Requested Of:" +  TransHeader.SalesPerson1.FirstName+" " +  TransHeader.SalesPerson1.LastName+
                                                        "
                                                        Requested By:" + TransHeader.ContactName
                                                        + HTMLRETURN +" "+" "+" "+" "
                                                        +"Urgency: "
                                                        +  PriorityName +" "+" "+" "+" "+" "+" "
                                                        //+ HTMLRETURN + "Estimated  Hours To Complete: " )
                                                        //+  DISPLAYVARIABLEVALUE( Quantity) +"  Hrs   "
                                                        // +" "+" "+" "+" "+" "+" "
                                                       +"" )
+  HTMLBOLD( "Support Requested" )+ HTMLRETURN + IT_Cyrious_Support + HTMLRETURN
 +HTMLRETURN
+ HTMLBOLD(  "Explanation of Support Rquested")+ HTMLRETURN
+ Text,
"Arial",2,"Black")
  1. CREATED MACROS FOR NOTIFYING REQUESTORS

SERVICE TICKET CREATED

We then created an email macro which is generated when the Service Ticket is created. Cyrious support needed to add a Service Ticket category to our macro categories and, once that was done, we were able to use the standard Cyrious “On New Service Ticket” macro to send an email, as shown below..

st_macro_setup.jpg

SERVICE TICKET MACROS

For a macro generated completion date we used an established Transaction UDF called “Contracted_Due_Date” and built the Trigger from the UDF how_to_create_a_database_table_change_log_using_a_sql_trigger - TableChangeLogSummary and TableChangeLogDetail. [Editor Note: These are not standard on Cyrious installations but Tech Support will enable it on request. See how_to_use_udf_change_log_tables_for_macros_and_reports The Service Ticket Edited trigger recognizes a UDF change and combined with the code below enabled an Email macro when a Date is first entered in the UDF for the acknowledgement and planned completion date email macro using Cyrious's MergeList UDF selection to embed the date in the email. A second Email macro is triggered if the Plan Date changes. The setup code for both is shown below:

Macro Code To Acknowledge Receipt With Planned Completion Date Macro

originall_plan_date_macro.jpg

Macro Code to Trigger Notification of Completion Date Change

plan_date_changed_macro_code.jpg

  1. CREATED QUICK PRODUCTS

To further simply and direct proper product selection, we created a separate set of quick products for Service Tickets, using that option from the System Setup which permits a different set of Quick Products for Service Tickets and Orders.

new_service_ticket.jpg

  1. SET UP THE PRODUCTION CONTROL AP FOR CAPTURING TIME ON REQUESTS

To track time applied to the Service Tickets, we determined that Cyrious' Production Ap was the most efficient. Ti utilize this ap, we created a station for these tickets called “It & Admin Support” and set the Default Station for each of the 4 products to the It & Admin Support station which assures that all new jobs created are added to the job board when created. We then created a public job board. This permits us to use the phone or browser to clock on to service tickets and capture time spent on them. For the parts we created 3 parts with each representing the type of service – E.g. Admin, Production, or Cyrious and linked them to the selection items in the support type selection. This assures that the correct part is brought in to the line item corresponding to the type of request sought. We linked the service selections to the specific parts in the Part tab of the Product using the inclusion formula as shown in the graphic below:

part_inclusion_formulas.jpg

The Ticket Selections below show how these appear in the Production Control Ap.

  1. service_ticket_job_board.jpg

Below is the result showing 3.3 hours clocked to the request.

time_recorded_on_service_ticket.jpg

  1. Created the Crystal Template For Printed Reports and PDFs

Although we could have used it in a pinch with the code we had created, the standard Cyrious Service Ticket Work Order is designed for external priducts and contains a lot of extraneous fields and information not pertinent to our applicaton. So we created a new, simple template in Cyrious.

The Service ticket template is much simpler than an Order template. The only thing needed is a header and the blob of the Layout Template which is contained in the HTMLShortFormat or HTMLLongFormat field of the TransDetail record.

The only tricky element is that the Cyrious Explorer presents the Service Tickets as line items; so when you have 3 line items in your Service Ticket you will have 3 line items in your Explorer. For this reason they have established two parameters for passing the ID of the selected item when printing from the Explorer or the actual Service Ticket– Order_OrderID and ServiceTicketItem_LineItemID.

This means that you can choose to print either the entire ticket from a right click on the Explorer and Print selection or the entire Service Ticket, depending on which parameter is passed. For our application, to make it simple, we needed only the entire service ticket; so we used a TransDetail table aliased as TDID to receive either the Order_OrderID or the TransDetail.ID and select the correct TransHeader record accordingly. In other words the code locates a TransDetail record of the TransHeader and then pulls a TransHeader table from which all the report variables are derived. To show this more clearly and visually, we have included the Crystal Table layout with the links below.

In the selection formula we created code which passes the TransHeaderID of the ticket to Sequel no matter which variable is populated. The same report then runs from the Explorer Print Menu or the individual Service Ticket print menu. The formula is shown below:

IF {?Order_OrderID} -1 THEN
{TDID.TransHeaderID}in {?Order_OrderID}
ELSE {TDID.ID} = {?ServiceTicketItem_LineItemID}

crystal_libks_for_st_work_order.jpg

Finally, the actual layout is very simple. Users can add and embellish as they see fit for their applications:

st_crystal_template.jpg

Final Product

  1. The Request takes less than 2 minutes to complete and email.

VIEWING REQUESTS FROM THE SERVICE TICKET EXPLORER

The Service Ticket Explorer is a line item explorer. In other words if a request (Sercice Ticket) has more than one line item, they are all shown on the Explorer. If the prorities are different, they will appear in different grups on the Explorer according to their priority. The drawback of this presentation is that many of the right-click options available to Order and Estimae Explorers are not abailable to the Service Ticket Explorer – E,g, right-click ot change status, right-click to access UDFs, etc. This was solved by creating a Dashboard Explorer as explained below.

sorted_service_ticket_explorer.jpg

  1. VIEWING TICKETS FROM A DASHBOARD

Using the Marketing Search List instrument, we also created an Explorer dashboard which lists the request by Ticket number and not line line item number. The steps to this were to create a saved query, attaching that query to the dashboard instrument and then adjusting columns using the Column Chooser and making those selections permaent with the Explorer options “Save Grid Columns.”

Below is the advanced query which returns the tickets in WIP

service_ticket_wip_query.jpg

A SAMPLE SERVICE TICKET DASHBOARD SHOWING TICKETS IN WIP.

Note that the ADJUST USAGE is available in the column chooser and is a hot link which brings up the part where the user can enter time spent on the request directly into the part, saving a right click.

service_ticket_dashboard.jpg

Contributor: Steve Gillispie

Date: 10/1/2017

Version: Control 6.1

You could leave a comment if you were logged in.