Forum Discussion
_anomDiebolt_
8 years agoQrew Elite
I am speechless after seeing that formula and hearing you invested that amount of time in developing it.
You are probably thinking I am going to tell you it would take just a few lines of JavaScript to implement it but I think that is obvious. Instead I am going to show you a technique that might be used to shorten your native formula.
One of the challenges that you probably faced is that it is natural to specify the available SLA schedule based on day of the week but you have to calculate the SLA based on the date/time a record is created. So there has to be some logic in your formulas that shifts the SLA schedule to start from the date/time a record is created. This is where you can emulate having an array in the formula language using a delimited string.
To exemplify this consider this partial formula which represents the SLA schedule using seven variables:
However, I think this is just another case where the "no code" / "low code" myth rears its ugly head. I would use script to implement this SLA. In fact, for over ten years I have encouraged QuickBase to all together replace their formula language with script.
You are probably thinking I am going to tell you it would take just a few lines of JavaScript to implement it but I think that is obvious. Instead I am going to show you a technique that might be used to shorten your native formula.
One of the challenges that you probably faced is that it is natural to specify the available SLA schedule based on day of the week but you have to calculate the SLA based on the date/time a record is created. So there has to be some logic in your formulas that shifts the SLA schedule to start from the date/time a record is created. This is where you can emulate having an array in the formula language using a delimited string.
To exemplify this consider this partial formula which represents the SLA schedule using seven variables:
var Text Sun = "08-16";The script emulates a doubly indexed array and indexes into it using Part() to parse out the start and ending times for each day by looking for the ":" and "-" separator characters. Your script can be vastly shortened if you use this "string array" trick.
var Text Mon = "09-16";
var Text Tue = "10-16";
var Text Wed = "11-16";
var Text Thu = "12-16";
var Text Fri = "13-16";
var Text Sat = "14-16";
var Number SLAHours = 30;
var Text Array = List(";", $Sun, $Mon, $Tue, $Wed, $Thu, $Fri, $Sat) & ";";
var Number Length = Length($Array);
var Number DayOfWeek = DayOfWeek(ToDate([Start Date Time]));
var Number SLAStart = Hour(ToTimeOfDay([Start Date Time])) +
Minute(ToTimeOfDay([Start Date Time])) / 60 +
Second(ToTimeOfDay([Start Date Time])) / 3600 +
MSecond(ToTimeOfDay([Start Date Time])) / 3600000;
var Number Shift = 6 * $DayOfWeek;
var Text RotatedArray = NotLeft($Array, $Shift) & Left($Array, $Shift);
var Number SA = ToNumber(Part(Part($RotatedArray, 1, ";"), 1, "-"));
var Number EA = ToNumber(Part(Part($RotatedArray, 1, ";"), 2, "-"));
var Number SB = ToNumber(Part(Part($RotatedArray, 2, ";"), 1, "-"));
var Number EB = ToNumber(Part(Part($RotatedArray, 2, ";"), 2, "-"));
var Number SC = ToNumber(Part(Part($RotatedArray, 3, ";"), 1, "-"));
var Number EC = ToNumber(Part(Part($RotatedArray, 3, ";"), 2, "-"));
var Number SD = ToNumber(Part(Part($RotatedArray, 4, ";"), 1, "-"));
var Number ED = ToNumber(Part(Part($RotatedArray, 4, ";"), 2, "-"));
var Number SE = ToNumber(Part(Part($RotatedArray, 5, ";"), 1, "-"));
var Number EE = ToNumber(Part(Part($RotatedArray, 5, ";"), 2, "-"));
var Number SF = ToNumber(Part(Part($RotatedArray, 6, ";"), 1, "-"));
var Number EF = ToNumber(Part(Part($RotatedArray, 6, ";"), 2, "-"));
var Number SG = ToNumber(Part(Part($RotatedArray, 7, ";"), 1, "-"));
var Number EG = ToNumber(Part(Part($RotatedArray, 7, ";"), 2, "-"));
However, I think this is just another case where the "no code" / "low code" myth rears its ugly head. I would use script to implement this SLA. In fact, for over ten years I have encouraged QuickBase to all together replace their formula language with script.