6 Replies Latest reply on Mar 25, 2013 11:01 AM by TerryNorling

    Help Desk Service Ticket Database Question

    TerryNorling

      Title

      Help Desk Service Ticket Database Question

      Post

           I created a service ticket DB and am having an issue with time formulas. In the attached screen shots (there are three screen shots divided by a black line all converted into a single jpg for ease of viewing). Chain of events and input methods and the results I want to happen:

           1. New Ticket is opened (this auto fills in the "Date Entered and HD Ticket Open" time

           2. Once the help desk repsonds the ticket they enter the "HD Response Time" 

           3. What I want is the "HD SLA Response Met" to auto filli in "Yes" or "No". What is happening is 60 is being inserted each time. (we have a 60 Minute SLA responce time agreement with our users, 60 mins or less we fullfilled our SLA 60 min or over we did not)

           4. The rest of the line items such as Service Restoral Data and below I have working correct. 

           Any help in tis issue will be greatly appreciated. 

      Service_Ticket_DB.jpg

        • 1. Re: Help Desk Service Ticket Database Question
          philmodjunk

               Why does the user need to edit this field?

               It would appear that you have defined this field as a calculation field. The contents of a calcualtion field cannot be directly edited and thus you get this error message.

               Your options are to:

               Don't try to edit this field, either edit a different field that becomes part of this calculation's expression or don't try to modify it at all.

               Make this field a text field with an auto-entered calculation instead.

          • 2. Re: Help Desk Service Ticket Database Question
            TerryNorling

                 PhilModJunk,

                 I am trying to make the field as a calculation field. The "drop" down menu was left over from when it was a manual entry, but our ISO auditor does not like it being manual. She wants it the field to be Automated. I captured the Drop down options to help decribe what I am trying to achieve. Ie, Help Desk response less then 60 mins the HD SLA Response Met: would = Yes

                  

            • 3. Re: Help Desk Service Ticket Database Question
              philmodjunk

                   Then you should remove the drop down formatting and not permit access to the field. Then you will not get this error message.

                   Your calculation, on the other hand, should be:

                   If ( HD Response Time - Help Desk Ticket Time < 3600 ; "Yes" ; "No" )

                   You can use Case instead of If if you prefer.

                   The difference between two time fields will give a result in seconds so you compare the result to 3600 seconds instead of 60 minutes.

              • 4. Re: Help Desk Service Ticket Database Question
                TerryNorling

                     PhilModJunk,

                      

                     Your soltuion fixed my problem but creates a different issue. Say the ticket is "Opened" at 14:00 and the help desk responds at 14:40, Good to go your solution works perfectly. Same thing if they do not respond for over an hour. I get the correct response (No). The issue is, if someone puts in a time before the ticket time. IE: Ticket opened at 14:00, the the time entered for response is 03:00. The auto answer says "Yes". I suspect it is because the answer is correct numerically just not really with regards to the clock. Does that make sense? 

                     Thanks for all your help by the way.
                      


                      

                • 5. Re: Help Desk Service Ticket Database Question
                  philmodjunk

                       why would the response time be earlier than the open time?

                       If this time represents 03:00 of the next day, you should use time stamp fields to record the date and the time for each of these two events. TimeStamp fields can be subtracted just like time fields and the result produced is still in seconds, but the change in date enables the calculation to still correctly compute the elapsed time when midnight falls between the two recorded time values.

                  • 6. Re: Help Desk Service Ticket Database Question
                    TerryNorling

                         Phil,

                         You are the man! Thanks for all your help!