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.