More calculation examples

This section provides some more example calculations. In these examples, dataObject is used to represent the object on which the calculation is being performed. Remember to leave the first two lines of the calculation as they are when you open the Edit Formula dialog:

1
2
import System
static def GetAttributeValue(dataObject):

Many of these examples use attributes that won't be in your database. To use these examples you will either need to create the required attributes or change the examples to use attributes in your database.

Comparing the current user and raise user

1
2
3
4
5
6
7
import System
static def GetAttributeValue(Incident):
    if Incident.RaiseUser == null:
        return 'Raise User is null'
    if Incident.GetCurrentUserName().ToLower() == Incident.RaiseUser.Name.ToLower():
        return 'raised by current user'
    return 'CurrentUser=' + Incident.GetCurrentUserName() + ' RaiseUser=' + Incident.RaiseUser.Name

The first if statement tests if the RaiseUser for the Incident is null, and returns Raise User is null if this is true.

If that if statement is not true, the second if statement runs and tests to see if the current user is the same as the raise user. If it is, then it returns raised by current user.

.ToLower() is used in this line to overcome case-sensitivity issues, by making both strings lower case.

If neither of the if statements are true, the final line runs and returns the separate CurrentUser and RaiseUser values.

Calculating a user's age

1
2
3
age = DateTime.Now.Year - dataObject.DateOfBirth.Year
age-- if DateTime.Now.Month < dataObject.DateOfBirth.Month or (DateTime.Now.Month == dataObject.DateOfBirth.Month and DateTime.Now.Day < dataObject.DateOfBirth.Day)
return age

This calculation has a variable called age, which is determined by subtracting the DateOfBirth year for the user from the current year. An additional year is subtracted if the user has yet to have their birthday this year (if the current month is less than the birth month, or if the current month is the same as the birth month, but the current day is earlier than the birth day).
age-- means "subtract 1 from age".
a single = sign means "make this equal to", two == signs means "this is equal to"

Calculating a user's length of service

1
2
3
4
5
end = DateTime.Now
end = dataObject.EndDate if dataObject.EndDate != null
los = end.Year - dataObject.StartDate.Year
    los-- if end.Month < dataObject.StartDate.Month or (end.Month == dataObject.StartDate.Month and end.Day < dataObject.StartDate.Day)
return los

The end variable is set to the current date, unless the user record has an EndDate (EndDate is not null), in which case, it is set to the user's EndDate. The los variable is then calculated by subtracting the StartDate from the end variable; an extra year is subtracted if the EndDate is earlier in the year than the StartDate.
!= means "is not equal to"

Calculating the most recent date in a collection

In this calculation, we have a collection of Orders associated with the dataObject, and we want to find out what the latest OrderDate is for any of the items in the collection. (You could just use the Max(" ") Framework Collection function, but this example introduces some useful concepts.)

1
2
3
4
5
lastOrderDate = null
for order in dataObject.Orders:
    if lastOrderDate == null or lastOrderDate < order.OrderDate:
        lastOrderDate = order.OrderDate
        return lastOrderDate

In the first line, the lastOrderDate variable is set to null. (Remember that a single = sign means "make this equal to", and two == signs means "this is equal to".)
The second line creates a loop around the dataObject.Orders collection using the variable order. The indented part of the calculation is run against each of the objects in the collection sequentially.
Note that the third line has a single indent to indicate that it belongs to the second line. The third line says that if the variable lastOrderDate is null (which it is the first time because we set it to null in line 1), or if it is less than this object's value for OrderDate, then...
on the fourth line, it sets the variable lastOrderDate to be the value of OrderDate for current object in the collection.
Then the calculation returns to the third line for the next object in the collection and tests again to see if the current value of lastOrderDate is null (which it won't be now) or if it is less than the value of OrderDate for this next object in the collection. If it is less than the value of OrderDate for this object, then lastOrderDate is set to this new value.
When the calculation has considered every object in the collection, the final line of the calculation returns the value it has set for lastOrderDate.

Summing calculation results across all of the objects in a collection

In previous examples, we have learned how to perform arithmetic calculations, and also how to loop a calculation around all of the objects in a collection. In this example, we combine both of these techniques to sum the results of calculations performed on all objects in a collection.

This calculation determines the total cost for a collection of objects, where each object in the collection has a unit cost for the item and a value for the quantity of items. The cost of each object in the collection is therefore the result of multiplying the quantity of items by the unit cost for the item. The total cost is then determined by adding all of the object costs together. The calculation does this by determining the cost of the first object, then adding this to the cost of the second object, and so on for all of the objects in the calculation.

1
2
3
4
cost = 0
for item in dataObject.Items:
    cost = cost + (item.Quantity * item.UnitCost)
        return cost

In the first line, the cost variable is set to 0.
The second line creates a loop around the dataObject.Items collection using the variable item.
The third (indented) line sets the variable cost to be equal to its current value plus the result of multiplying the Quantity attribute by the UnitCost attribute. The third line then repeats for all of the objects in the collection - each time the calculation loops to a new object in the collection, it adds the new object's cost to the total.
When the calculation has considered every object in the collection, the final line of the calculation returns the value it has set for cost.

Calculations on collections of collections

This calculation is an extension of the previous example. It determines the total value for a collection of orders, where each order comprises a number of items. In this example, the cost is the sum of a collection of collections

1
2
3
4
5
cost = 0
    for order in dataObject.Orders:
        for item in order.Items:
            cost = cost + (item.UnitCost * item.Quantity)
        return cost

In the first line, the cost variable is set to 0.
The second line (indented) creates a loop around the dataObject.Orders collection using the variable order.
The third line (indented twice) creates a loop around the dataObject.Items collection underneath the dataObject.Orders collection using the variable item.
The fourth line (indented three times) sets the variable cost to be equal to its current value plus the result of multiplying the Quantity attribute by the UnitCost attribute. The fourth line then repeats for all of the objects in the dataObject.Items collection - each time the calculation loops to a new object in the collection, it adds the new object's cost to the total.
When the calculation has considered every object in the first Order's sub-collection of Items, it moves on to the second Order's sub-collection of Items and repeats the fourth line for each object in the second sub-collection of Items.
When the calculation has considered every object in the top dataObject.Items collection, the final line of the calculation returns the value it has set for cost.

Generating text that includes data from your database

This example describes how to create a text string that includes data from your database.

1
return String.Format("Hello {0}, this was last updated by {1}", dataObject.RaiseUser.Title, dataObject.LastUpdateUser.Title)

The String.Format(" ", {0}, {1}. ...) function returns a string where the parameters {0}, {1}, and so on define where to get the data from for use in the string. Note that the parameters start at {0}. In the example calculation, {0} is replaced by the name of the Raise User (dataObject.RaiseUser.Title), and {1} is replaced by the name of the last user to update the record (dataObject.LastUpdateUser.Title).
For example, if the incident was logged for Gill Bond and was last saved by Jane Green, the string would read: Hello Gill Bond, this was last updated by Jane Green.