Formulae
Contents
If the Formulaic check box is ticked then the value of this property will be calculated by a formulae.
The syntax for the formulae is based on MySQL, with one major exception, MySQL uses the column name in its formulae, most users of Exprodo DB would prefer to use the property name. In order to do this there is a special syntax, if the property name is in [ ], such as [durationInMinutes] then Exprodo DB will convert the property name to the correct column name.This also allows the user to specify [biskit.property] in order to access properties of related Biskits.
When creating formulae use all the standard number calculations * / + - , as well as setting up logic to determine the correct answer.
For instance there are two main logical options if and case:
Logic |
Description |
|---|---|
if (expr1, expr2, expr3) |
If expr1 is true then expr2 will be returned otherwise expr3 is returned. (You cannot use the if statement) |
case when expr1 then option1 when expr2 then option2 else option3 end |
If expr1 is true do option1, otherwise if expr2 is true do option2, otherwise do option3. Case statements can be nested one inside the other. |
An expression can include < > != (is not null) = () as well as AND or OR for example:
[costPerHour] > 1000 OR ([costPerHour] < 500 AND [costPerHour] > 275)
When dealing with strings there are a number of functions that can be used, here is a subset of the more useful ones:
Function |
Description |
|---|---|
length(str) |
Return the length of the string |
concat(str1,str2) |
Return str 1 and str2 concatenated together |
replace(str1,str2,str3) |
In str1, find where str2 occurs, and replace it with str3 |
substr(str,pos) |
Return the string that starts at the pos character of str. |
When dealing with dates there are also many functions some of the more useful are:
Function |
Description |
|---|---|
datediff(date1, date2) |
Returns the difference in whole days between two dates. |
unix_timestamp(date) |
Returns the number of seconds since midnight 1/1/70. |
timediff(date1, date2) |
Returns the difference between two times as a time string, |
time_to_sec(time) |
Returns the conversion of a time string to seconds, |
Using time_to_sec() in conjunction with timediff() allows a calculation of the time in seconds between two times. i.e. time_to_sec(timediff(date1,date2))
Here is an example of a formulae using a nested case and datediff().
In this example the value of a cost property of a booking is determined depending on the status of a booking.
If the booking is denied, or cancelled with more than seven days to go then do not charge. If the booking goes ahead then charge the rate found in the property costPerHour in the Project Resource Settings for that project multiplying by the duration in minutes of the booking divided by 60 to give hours. If the booking was cancelled less than two days ago charge 50% otherwise charge 20%.
case
when [status] = 'Denied' then 0
when [status] = 'Approved' or [status] = 'Requested' then [projectResourceSettings.costPerHour] * [durationInMinutes] / 60
else
case
when datediff(start_date, [cancelled]) >= 7 then 0
when datediff(start_date, [cancelled]) >= 2 then 0.2*[projectResourceSettings.costPerHour] * [durationInMinutes] / 60
else 0.5*[projectResourceSettings.costPerHour] * [durationInMinutes] / 60
end
end
There are a number of issues with using formulae in Biskits. As the formulae has to be converted from what is written in Exprodo DB to MySql there are currently some functions that will not work.
1.Any function that requires a string constant as a parameter. These constants do not currently convert from Exprodo DB to MySQL cleanly.
For example: timestampdiff(unit,datetime_expr1,datetime_expr2) where unit is one of FRAC_SECOND (microseconds), SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, QUARTER, or YEAR. Unit does not convert properly.
2.The if statement does not work so use the if() function instead.
3.Use 'T' and 'F' for the value of Boolean properties.
4.When checking for null use "is not null" not != null as this is always False.