1 Reply Latest reply on Oct 21, 2016 3:02 AM by Johan Hedman

    SLA Management

    david.smith

      I am seeking some guidance / sample code on working with SLA deadlines for a Helpdesk System that takes into account a number of variables and then returns the SLA deadline as a timestamp. I am working with FileMaker Pro 15 with a Windows 10 Desktop App and with some restricted functionality delivered to iPad clients via FileMaker GO.

      In the database I have created the following fields:

      ·         “SLA Onsite Level”: Time - Hours  - example 4 hours is denoted by 04:00:00. This field is hard coded for the contract and is a constant for each database entry

      ·         “Call Logged at”: Timestamp. This is a variable field that is automatically populated on creation.

      ·         “Onsite Start SLA”: Time – Actual time that we are contracted to arrive on site – example 09:00:00. This field is hard coded for the contract and is a constant for each database entry

      ·         “Onsite End SLA”: Time – Actual time that we are contracted to finish on site – example 17:30:00. This field is hard coded for the contract and is a constant for each database entry

      I would like to create a calculation that returns a value into a timestamp field called “Onsite SLA Deadline”. This advises us of a countdown of time remaining for us an engineer to arrive on site that uses the following logical calculation:

      If “SLA Onsite level” + “call logged at” is greater than “Onsite End SLA” then calculate ((“Onsite Start SLA” + “SLA Onsite Level”) – (“Onsite End SLA” – “Call logged at”))

      Once I have achieved this then I will be able to measure actual SLA levels versus deadline SLA levels using conditional formatting.

      I would also like to move this then to stage 2 which is to accommodate Weekends – in other words does anyone know how you would exclude Saturdays and Sundays in returning the “Onsite SLA Deadline”? I am sure that Sunday = 1, Monday = 2, etc, but how would I incorporate this into the calculations?

      Any help would be really appreciated.