Clarizen Functions

For an Excel version, scroll down to the bottom of the page.

Abs Function

` Abs (number as Numeric) as Numeric `
Category: Math
Description: Returns the absolute value of a number
Parameters:
number as Numeric Positive or negative numeric value or reference to a numeric field
Returned value as Numeric Returns a numeric value equal to the absolute value of the number entered in the "number" parameter
Example #1 Define a numeric custom field on a work item and set it's default value to
` Abs(\$BaselineWorkVariance/\$WorkVariance) `
Tips
1. Create workflow rules to set standard field values via formulas
2. To create default values based on formulas, be sure to check off the "based on formula" box next to the default value field within the New Custom Field screen

Ceil Function

` Ceil (number as Numeric) as Numeric `
Category: Math
Description: Rounds a number up to the nearest whole integer
Parameters:
number as Numeric Positive or negative numeric value or reference to a numeric field
Returned value as Numeric Returns a numeric value equal to the entered number value rounded to the nearest whole integer
Example #1 Create a Custom Field using the Duration Field Type that will return the total hours spent on a work item rounded up to the nearest whole integer.
` Hours(Ceil(\$ActualEffort/Hours(1))) `
\$ActualEffort\Hours(1) is used to normalize the actual effort in amount of hours (regardless of whether the effort was entered in days, weeks, etc). The Hours() function is then used to convert the effort back to the duration field.
Tips
1. Create workflow rules to set standard field values via formulas

Div Function

` Div (number as Numeric, divisor as Numeric, errorValue as Numeric) as Numeric `
Category: Math
Description: Return number/divisor if divisor<>0 otherwise it will return errorValue
Parameters:
number as Numeric Positive or negative numeric value or reference to a numeric field
divisor as Numeric Positive or negative numeric value or reference to a numeric field
errorValue as Numeric Resulting value if the expression throws a runtime error
Returned value as Numeric Returns a numeric value equal to the "number" divided by the "divisor" parameters, if divisor is equal to "0" the resulting value will be equal to the value set in the "errorValue" parameter

Exp Function

` Exp (number as Numeric) as Numeric `
Category: Math
Description: Returns e raised to the power of the given number
Parameters:
number as Numeric Positive or negative numeric value or reference to a numeric field
Returned value as Numeric Returns e raised to the power of the number given in the "number" parameter
Example #1 Create a Custom Field using the Duration Field Type that will return the exponential value of the total hours spent on a work item.
` Hours(Exp(\$ActualEffort/Hours(1))) `
\$ActualEffort\Hours(1) is used to normalize the actual effort in amount of hours (regardless of whether the effort was entered in days, weeks, etc). The Hours() function is then used to convert the effort back to the duration field.
Tips
1. Functions can be used within other functions to create more complex formulas

Floor Function

` Floor (number as Numeric) as Numeric `
Category: Math
Description: Rounds a number down towards zero to the nearest integer
Parameters:
number as Numeric Numeric value or reference to a numeric field
Returned value as Numeric Returns a numeric value equal to the number specified in the "number" parameter rounded down towards zero to the nearest integer
Example #1 Create a Custom Field using the Duration Field Typethat will return the total hours spent on a work item rounded down to the nearest integer.
` Hours(Floor(\$ActualEffort/Hours(1))) `
\$ActualEffort\Hours(1) is used to normalize the actual effort in amount of hours (regardless of whether the effort was entered in days, weeks, etc). The Hours() function is then used to convert the effort back to the duration field.
Tips
1. Create workflow rules to set standard field values via formulas

Max Function

` Max (arg1 as Object, arg2 as Object, ... as Object) as Object `
Category: Math
Description: Returns the greatest value of the arguments
Parameters:
arg1 as Object Represents any value type, or reference to a field that will be compared to the other "arg" parameters
arg2 as Object Represents any value type, or reference to a field that will be compared to the other "arg" parameters
... as Object Represents any value type, or reference to a field that will be compared to the other "arg" parameters
Returned value as Object Returns the maximum value of the values provided in the "argx" parameters
Example #1 Create a validation rule that will check whether a work item's due date was updated by setting the work item 'Duration' and which exceeds the parent milestone or project's due date. In such a case, throw an error and block updating the appropriate duration of the work item.
The Evaluation Criteria of this validation rule will be:
` IsChanged(\$Duration) && IsChanged (\$DueDate) && Not (IsNULL(\$Milestone)) && (Max(\$DueDate, \$Milestone.DueDate, \$Project.DueDate) = \$DueDate) `
Tips
1. Validation Rules can be used throughout the entire system and will help in regulating data as per your organizational standards

Min Function

` Min (arg1 as Object, arg2 as Object, ... as Object) as Object `
Category: Math
Description: Returns the smallest value of the arguments
Parameters:
arg1 as Object Represents any value type, or reference to a field that will be compared to the other "arg" parameters
arg2 as Object Represents any value type, or reference to a field that will be compared to the other "arg" parameters
... as Object Represents any value type, or reference to a field that will be compared to the other "arg" parameters
Returned value as Object Returns the minimum value of the values provided in the "argx" parameters
Example #1 Create a validation rule that will check whether a work item's start date was updated by setting the work item 'Duration', and begins prior to the parent milestone or project's start date. In such a case, throw an error and block updating the appropriate duration of the work item.
The Evaluation Criteria of this validation rule will be:
` IsChanged(\$Duration) && IsChanged (\$StartDate) && Not (IsNULL(\$Milestone)) && (Min(\$StartDate, \$Milestone.StartDate, \$Project.StartDate) = \$StartDate) `
Tips
1. Validation Rules can be used throughout the entire system and will help in regulating data as per your organizational standards

Mod Function

` Mod (number as Numeric, divisor as Numeric) as Numeric `
Category: Math
Description: Returns the remainder after a number is divided by the divisor
Parameters:
number as Numeric Positive or negative numeric value or reference to a numeric field
divisor as Numeric Positive or negative numeric value or reference to a numeric field
Returned value as Numeric REturns a numeric value equal to the remainder after the "number" parameter is divided by the "divisor" value
Example #1 Example:
` Mod(3,2) `
The Mod function is useful for financial fields

Pow Function

` Pow (number as Numeric, power as Numeric) as Numeric `
Category: Math
Description: Returns number raised to the power
Parameters:
number as Numeric
power as Numeric
Returned value as Numeric

Round Function

` Round (number as Numeric, numberDigits as Numeric) as Numeric `
Category: Math
Description: Rounds a number to the specified number of digits
Parameters:
number as Numeric Positive or negative numeric value or reference to a numeric field
numberDigits as Numeric Numeric value or reference to a numeric field
Returned value as Numeric Returns a number value equal to the number specified in the "number" parameter rounded to the number of digits specified in the "numberDigits" parameter
Example #1 Build a workflow rule that sends an email with work item progress. If you want to get up to 2 decimal places, create the following formula within the body or subject of the email
` Round(\$PercentCompleted,2) `
Tips
1. Other similar functions include Ceil(), Floor(), Truncate()
Related Functions Ceil() Floor() Truncate()

Sqrt Function

` Sqrt (number as Numeric) as Numeric `
Category: Math
Description: Returns the positive square root of a number
Parameters:
number as Numeric Positive or negative numeric value or reference to a numeric field
Returned value as Numeric Returns a numeric value equal to the positive square root of the number provided in the "number" parameter
Example #1 Example:
` Sqrt(4) `
The formula above will return a value of 2
Tips
1. Similar functions include Exp() and Mod()
Related Functions Exp() Mod()

Truncate Function

` Truncate (number as Numeric) as Numeric `
Category: Math
Description: Returns the y integer part of floating-point number, for example Truncate(32.7865) will return 32
Parameters:
number as Numeric Positive or negative numeric value or reference to a numeric field
Returned value as Numeric Returns a numeric value equal to the whole number of value of the number indicated in the "number" parameter, trimming all numbers after the decimal point
Example #1 Build a workflow rule that sends an email with an update of progress. Create the following formula within the body or subject of the email
` Truncate(\$PercentComplete) `
Tips
1. Other similar functions include Ceil(), Floor(), Round()
Related Functions Ceil() Floor() Round()

IsPickVal Function

` IsPickVal (picklistField as ObjectIdentifier, valueLiteral as String) as Boolean `
Category: Pickup
Description: Checks whether the value of a pick list field is equal to the literal value
Parameters:
picklistField as ObjectIdentifier Represents a specific object field of type pick list
valueLiteral as String Represents a picklist value, as text/string of the pick list specified in the "picklistField" parameter
Returned value as Boolean Returns a boolean value, where "TRUE" indicates that the value of the picklist specified in the "picklistField" parameter is equal to the value specified in the "valueLiteral" parameter
Example #1 Build a workflow rule on a work item that will return a TRUE if the state of the work item is indeed set to the defined picklist value. Set the evaluation criteria to
` IsPickval(\$State,’Completed’) `

CurrentObject Function

` CurrentObject () as ObjectIdentifier `
Category: ObjectReference
Description: Returns object that is currently being processed
Returned value as ObjectIdentifier Returns object that is currently being processed
Example #1 To add a hyperlink to the object on an email workflow rule, use the following formula within the email subject or body
` GetHyperlink(CurrentObject()) `
Example #2 Prevent an admin user from updating their own direct manager details by defining a validation rule that will have the following evaluation criteria
` CurrentUser().DirectManager = CurrentObject() `
Tips
1. The CurrentObject() function ca only be used on business rules

CurrentUser Function

` CurrentUser () as Object `
Category: ObjectReference
Description: Returns the current user object
Returned value as Object Returns the current user object
Example #1 Build a validation rule on the User Entity "Add" operation that will prevent non-admin users from inviting new users into the organization. Set the Evaluation Criteria to be:
` Not(CurrentUser().Admin) `
Example #2 Build a validation rule that will prevent the deletion of approved timesheet entries by users that are not set as the approver of the timesheet itself. Build the validation rule on the TimeSheet entity and set its Evaluation Criteria to be:
` (\$State = "Approved")&& (CurrentUser()<> \$ApprovedBy) `
Tips
1. The CurrentUser() function ca only be used on business rules
2. You can get the current user's properties by using the CurrentUser().FieldAPIName syntax, such as CurrentUser().Admin, CurrentUser().DirectManager

FindCustomer Function

` FindCustomer (customer as String) as ObjectIdentifier `
Category: ObjectReference
Description: Returns the customer object, where the "customer" parameter can either be the customer name, an email address of a customer contact or the customer domain name.
Parameters:
customer as String Represents a reference to a customer object, can either be the customer name, an email address of a customer contact, or a customer's domain name
Returned value as ObjectIdentifier Returns a reference to a specific customer object
Example #1 Create an InterAct rule on the Support mailbox that allows your customers to submit email to case tickets. This rule will also allow you to link the Customer to the Issue, so that you can keep track of all Customer bugs, issues, etc.
Using the NewObject action in the rule select the Issue Customer Link to create the link between the newly created issue and the Customer.
In the Entity section enter the Issue (generally speaking this would be NewObject1) and in the Customer section enter the following:
` FindCustomer(EmailObject.From) `
Using the email address of the customer contact, this function will locate the relevant customer.

FindObject Function

` FindObject (entityType as ObjectIdentifier, field as Object, fieldValue as Object) as ObjectIdentifier `
Category: ObjectReference
Description: Returns an object that fits a specific field value criteria. The EntityType parameter refers to the specific object entity type, for example Project. Field parameter refers to the field of interest, and the fieldValue parameter indicates the value of the specified field. If multiple objects fit the criteria, then the last modified object will be returned.
Supported Context: EmailRule, CustomAction
Parameters:
entityType as ObjectIdentifier(Class) Represents the entity type of the desired object (i.e Project). You can access all the available entity types using the values denoted in the "Class" option in the "Pick lists" tab of the formula helper
field as Object Represents the field that will be used to find the desired object (i.e. \$State)
fieldValue as Object Represents the field value of the field specified in the "field" parameter
Returned value as ObjectIdentifier Returns the object that fits the criteria specified in the "fieldValue" parameter for the field specified in the "field" parameter
Example #1 Create a custom action for the creation of a new expense sheet, where the "Project" field of the expense sheet will be filled in based on the "Activity Number" (a custom field on the project level). Where the "Activity Number" is also a User Input variable within the custom action.
Within the custom action, select the New Object Action and the "Expense Sheet" entity. Click on "Another Field" to add the "Project" field and within enter the following:
` FindObject("project", \$C_ActivityNumber, ActivityNumber) `
Example #2 Another example for the FindObject action is to get field values of this object. For example, using the same expense sheet above, you could enter the description of the expense sheet to match the one of the related project.
For this example, in the expense sheet "description" field enter the following:
` FindObject("project", \$C_ActivityNumber, ActivityNumber).Description `
Note If multiple objects fit the criteria, then the last modified object will be returned.

GetFirstUser Function

` GetFirstUser (users as String) as ObjectIdentifier `
Category: ObjectReference
Description: Returns the first user from a string of users, where "users" parameter is a string of users seperated by a comma, each element in the string can either be the login name of a user, an email address of a user, full name of a user, parameter or email CC list.
Parameters:
users as String Represents a string of type text of users seperated by a comma where each element in the string can either be a user's user name, an email address, full name or a reference to a field that returns a string of users (i.e. EmailbOject.Cc)
Returned value as ObjectIdentifier Returns a reference to a user object of the first known user in the list
Example #1 Create an InterAct rule on the Work Item Mailbox that will parse the email's CC list and return the first user in order to assign them as the owner of the task.
Using the NewObject action select the Resource link, in the WorkItem enter CurrentObject() and in the Resource enter the following:
` GetFirstUser(EmailObject.Cc) `

GetImage Function

` GetImage (entityType as String, objectId as ObjectIdentifier) as String `
Category: ObjectReference
Description: Returns an image url for an object specified within the objectId
Parameters:
entityType as String
objectId as ObjectIdentifier
Returned value as String

` GetLink (entityType as String, ref1 as ObjectIdentifier, ref2 as ObjectIdentifier) as ObjectIdentifier `
Category: ObjectReference
Description: Retrieves a link between two objects, where the entity type variable indicates the type of link and the ref1 and ref2 variables indicate the specific objects between which the link exists
Parameters:
entityType as String Represents the link type of the desired link object (i.e. RelatedWork). You can access all the available link types using the values denoted in the "Class" option in the "Pick lists" tab of the formula helper
ref1 as ObjectIdentifier Represents a specific object, or reference to a field that returns a specific object, to which the desired link object is attached
ref2 as ObjectIdentifier Represents a specific object, or reference to a field that returns a specific object, to which the desired link object is attached
Returned value as ObjectIdentifier Returns the a reference to a link between the two objects specified in the "ref1" and "ref2" parameters
Example #1 To get the link that connects the current object (of type work item) to its direct parent:
` GetLink('RealWorkItemHierarchyLink',CurrentObject(),\$Parent) `
Example #2 To find out if there is a dependency between two work items, check if the following is not null:
` GetLink('DependencyLink','T-4','T-13') || GetLink('DependencyLink','T-13','T-4') `
Rate Holder 'JobTitleRateLink' Project Job Title
Attachment 'AttachmentLink' Work Item, Expense, All Issue Types Document
Dependency 'DependencyLink' Work Item Work Item Where ref1 is the predecessor work item, and ref2 is the successor work item
Hierarchy 'WorkItemHierarchyLink' Work Item Work Item Where ref1 is the parent work item, and ref2 is the child work item
Parent 'RealWorkItemHierarchyLink' Work Item Work Item Where ref1 is the hammock (parent) work item, and ref2 is the sub work item (child)
Progress Impact 'ProgressImpactLink' Work Item Work Item Where ref1 is the impacted (parent) work item, and ref2 is the impacting (child) work item
Shortcut 'ShortcutLink' Work Item Work Item Where ref1 refers to the location of the shortcut (parent), and ref2 is the original work item (child)
Issue's Team 'IssueTeamMembers' All Issue Types User
Membership 'MembershipLink' Resource Entity Resource Entity Where ref1 refers to the resource entity object where ref2 resource entity object is a member of
Group Hierarchy 'GroupHierarchyLink' Group Group, User
Related Work 'RelatedWork' All Issue Types Work Item
Stopwatch 'Stopwatch' Work Item User/td>

GetObjectByID Function

` GetObjectByID (entityType as String, sysId as ObjectIdentifier) as ObjectIdentifier `
Category: ObjectReference
Description: Returns a specific object, where the 'entity' parameter denotes the entity type and the 'ID' is the system ID of the object.
Parameters:
entityType as String Represents the entity type of the desired object (i.e Project). You can access all the available entity types using the values denoted in the "Class" option in the "Pick lists" tab of the formula helper
sysId as ObjectIdentifier Represents the system ID of the desired object
Returned value as ObjectIdentifier Returns a reference to the object specified in the "sysID" parameter

GetDomainName Function

` GetDomainName () as String `
Category: Text
Description: Return the Domain of the organization. if Organiztion Domain is not set return clarizen domain.
Returned value as String

HtmlEncode Function

` HtmlEncode (text as String) as String `
Category: Text
Description: Encodes text for use in HTML
Parameters:
text as String
Returned value as String

HtmlToText Function

` HtmlToText (htmlText as String) as String `
Category: Text
Description: Extracts text from htmlText
Parameters:
htmlText as String
Returned value as String

InStr Function

` InStr (str as String, subStr as String, isCaseSensitive as Boolean) as Numeric `
Category: Text
Description: Returns the first position of compared text within the text. If not found returns 0. By default isCaseSensitive is equal to FALSE, if set to TRUE then search is case sensitive
Parameters:
str as String Represents a text string or reference to a field that returns a value of type text
subStr as String Represents the sub-string or partial text content which you need the location of within the text provided in the "str" parameter
isCaseSensitive as Boolean An optional parameter that indicates whether or not the "subStr" value is case sensitive, where "TRUE" is case sensitive. If null the parameter default value is equal to "FALSE"
Returned value as Numeric Returns the character position of the location of the "subStr" parameter within the "str" parameter
Example #1 Build a workflow rules that automatically marks a user as internal if the email address contains a certain domain (e.g. acme.org, acme.co.uk, acme.co.us). Create the workflow on a user entity and set the trigger to run "Every time a record is created or edited", and set the Action to "Update Field". Set the evaluation criteria to be:
` InStr(\$email,"@acme",FALSE) <> 0 `
Tips
1. The function In() will let you search for a couple of substrings within a field
2. The returned position value from this function can be used for Left(), Right(), Mid()
3. Review other useful text related functions
Related Functions Left() Right() Mid() In()

Left Function

` Left (text as String, charsLength as Numeric) as String `
Category: Text
Description: Returns the specific number of characters beginning from the lefthand side of a text string
Parameters:
text as String Represents a text string or reference to a field that returns a value of type text
charsLength as Numeric Positive or negative numeric value or reference to a numeric field, to which you would like to get the text to the left of the character number
Returned value as String Returns a text or string value equal to the text to the left of the character number specified in "charsLength"
Example #1 Create a custom field on a user entity that will return the string of an email address up to the "@" mark, so for example, for the email "joe.smith@acme.com" the formula will return "joe.smith"
` Left(\$email,InStr(\$Email,'@')-1) `

Len Function

` Len (text as String) as Numeric `
Category: Text
Description: Returns the number of characters in a text string
Parameters:
text as String Represents a text string or reference to a field that returns a value of type text
Returned value as Numeric Returns a numeric value equal to the total number of characters in the text specified in the "text" parameter
Example #1 Return the character count within a string
` Len(‘this is a test’) }} The above formula will return the number 14 Create a formula on a User entity that is set to: {{Sample| Right(\$email, Len(\$Email) - InStr(\$Email,'@')) `
The above formula will return the string after the "@" mark. For example, for the email "joe.smith@acme.com", the formula will return "acme.com", since the function Len(\$Email) – InStr(\$Email,’@’) will return the exact number of charachters to the right of the "@"
Tips
1. Review other useful text fields such as Right() and Mid()
Related Functions Right() Mid()

Lower Function

` Lower (text as String) as String `
Category: Text
Description: Changes all letters in the given value to lowercase letters
Parameters:
text as String Represents a text string or reference to a field that returns a value of type text
Returned value as String Returns a text or string value equal to the text entered in the "text" parameter with all letters in lower case
Example #1 Return a string in lower case letters
` Lower('This Is A Test') `
Tips
Related Functions Upper()

Mid Function

` Mid (text as String, startNum as Numeric, charsLength as Numeric) as String `
Category: Text
Description: Returns the specific number of characters beginning from the position of StartNum up to number of characters supplied in CharsLength
Parameters:
text as String Represents a text string or reference to a field that returns a value of type text
startNum as Numeric Positive or negative numeric value or reference to a numeric field, to which you would like to get the text the right of the character number
charsLength as Numeric Numeric value or reference to a numeric field representing the total number of text characters you would like returned
Returned value as String Returns a text or string value equal to the text to the right of the "startNum" character with a total number of characters as defined in the "charsLength" parameter
Example #1 Create a workflow rules that takes 4 characters of a project name starting directly after the location of the "-" character within that string. In such a case, if the name of the project contains the string "Product-NP56-01" then the returned result of that formula will be "NP56".
` Mid(\$Name,InStr(\$Name,"-")+1,4) `
Tips
1. The function InStr() helps you get the start position of a the substring you may be looking for
2. Review other useful related text functions, such as Len(), Right(), Left(), etc.
Related Functions InStr() Len() Right() Left()

ReplaceString Function

` ReplaceString (text as String, find as String, replaceWith as String, isCaseSensitive as Boolean) as String `
Category: Text
Description: Replace all instances of the "find" parameter with the "replace with" parameter within the defined string value. By default isCaseSensitive is equal to FALSE, if set to TRUE then search is case sensitive
Parameters:
text as String Represents a text string or reference to a field that returns a value of type text
find as String Represents a text string or reference to a field that returns a value of type text. Indicates the text value you would like to "find" within the value specified in the "text" parameter
replaceWith as String Represents a text string or reference to a field that returns a value of type text. Indicates the text value you would like to "replace" within the value specified in the "text" parameter instead of the value specified in the "find" parameter
isCaseSensitive as Boolean This optional parameter allows you to decide if the find and replace action will be Case Sensitive. Accepts a value of "TRUE" or "FALSE", if parameter is not entered then by default the parameter value will be "FALSE" meaning it will not be Case Sensitive
Returned value as String Returns a text or string value equal to the text specified in the "text" parameter where the "replaceWith" text replaces the text specified in the "find" parameter
Example #1 Suppose you have a template project with sub-projects, where each sub-project has a prefix in its name (e.g, -Dev, -Marketing), and you would like to replace the with the parent projects name. For example, the parent project is the new product in development, say Widget, so that you would like each milestone to be Widget-Dev, Widget-Marketing.
Build a workflow rule on a Milestone Work Item entity, so that the evaluation criteria is
` Contains(\$Name," ") && Not(IsNull(\$ParentProject)) `
Example #2 Add the "Update Field" option from the "Set Action" drop down, select the "Name" field and set it's value to be
` ReplaceString(\$Name," ",\$ParentProject.Name,FALSE) `

Right Function

` Right (text as String, charsLength as Numeric) as String `
Category: Text
Description: Returns the specific number of characters beginning from the righthand side of a text string
Parameters:
text as String Represents a text string or reference to a field that returns a value of type text
charsLength as Numeric Positive or negative numeric value or reference to a numeric field, to which you would like to get the text to the right of the character number
Returned value as String Returns a text or string value equal to the text to the right of the character number specified in "charsLength"
Example #1 Create a custom formula on a User entity that will return the string after the "@" mark. So for example, for an email "joe.smith@acme.com" the formula will return "acme.com"
` Right(\$email,Len(\$Email)-InStr(\$Email,'@')) `
Tips
1. Review other useful related text functions, such as Len() and Left()
Related Functions Len() Left()

Trim Function

` Trim (text as String) as String `
Category: Text
Description: Removes all spaces from a text string except for single spaces between words
Parameters:
text as String Represents a text string or reference to a field that returns a value of type text
Returned value as String Returns a text string value equal to the text specified in the "text" parameter with all spaces removed except for single spaces between words
Example #1 Create a formula on a description field that will remove spaces from the start and end of a text string. For example, "this is a test " will return "this is a test".
` Trim(\$Description) `
Tips
1. The Trim() function is useful for string comparisons
2. Use the trim function whenever you use Contains() or In() to make sure you are trimming unnecessary spaces from compared strings
Related Functions Contains() In()

Upper Function

` Upper (text as String) as String `
Category: Text
Description: Changes all letters in a given value to uppercase letters
Parameters:
text as String Represents a text string or reference to a field that returns a value of type text
Returned value as String Returns a text or string value equal to the text entered in the "text" parameter with all letters in upper case
Example #1 Return a string in upper case letters
` Upper(‘This is a Test’) `
returns 'THIS IS A TEST'
Tips
Related Functions Lower()

UrlEncode Function

` UrlEncode (text as String) as String `
Category: Text
Description: Encodes text for use in URLs
Parameters:
text as String Text to encode
Returned value as String Returns encoded text for usage in URLs
Example #1 Create a custom action that opens URL with link to Google Maps
` https://maps.google.com/maps?saddr={UrlEncode(CurrentUser().HomeAddress)}&hl=en `

Case Function

Category: Logical
Description: Checks an expression against a series of values. If the expression compared is equal to any value, the corresponding result is returned. If it is not equal to any of the values, the else-result is returned
Parameters:
expression as Object Field or reference to a field of an object
value1/elseResult as Object Represents the value of the field specified in the "Expression" parameter, while "Else Result" represents the action if the previous value is not equal to "TRUE"
result1 as Object Represents the resulting action if "value1" is equal to true
value2/elseResult as Object Represents the second potential value of the field specified in the "Expression" parameter, while "Else Result" represents the action if the previous value is not equal to "TRUE"
... as Object You can enter multiple "Values/Else Results" or "Results" in the same manner as described above
Returned value as Object
Example #1 Setting the due date of an issue to be based off of its severity. In this scenario, the response time for a critical issue is set to 1 working day, 3 working days for a high severity, and 5 working days for all other severity levels.
` Case(ToString(\$Severity),"Critical",DateAdd(\$CreatedOn,Days(1,TRUE)), "High", DateAdd(\$CreatedOn,Days(3,TRUE)), DateAdd(\$CreatedOn,Days(5,TRUE))) `
Example #2 Creating a workflow rule that automatically sets a project sponsor based on the project type>
` Case(ToString(\$ProjectType),"Development","Username1","Advertising","Username2",\$ProjectSponsor) `
Example #3 Creating a workflow rule that extracts the month and the quarter from a given due date field
` Case(ToString(Month(\$DueDate)),"1","Q1-Jan","2","Q1-Feb","3","Q1-Mar", "4","Q2-Apr","5", "Q2-May", "6", "Q2-Jun","7", "Q3-Jul","8", "Q3-Aug", "9", "Q3-Sep", "10", "Q4-Oct", "11", "Q4-Nov", "Q4-Dec") `
Tips
1. To set standard field values based on formulas, create workflow rules>
2. To use pickup fields, or reference to object fields, as expressions, you must first convert the field values into strings using the ToString() function
Related Functions ToString()

Contains Function

` Contains (text as String, value as String, isCaseSensitive as Boolean) as Boolean `
Category: Logical
Description: Checks if a field contains a specified value and returns a TRUE or FALSE value. For isCaseSensitive, enter a value of TRUE or FALSE, default value  is equal to FALSE.
Parameters:
text as String Text as string value, or reference to a field that returns a string value (i.e text fields, or conversion of non-text fields using the ToString function)
value as String Represents the value, or portion of the value, of the field or string specified in the "Text" parameter
isCaseSensitive as Boolean This optional parameter allows you to decide if the expression will be Case Sensitive when triggered. Accepts a value of "TRUE" or "FALSE", if parameter is not entered then by default the parameter value will be "FALSE" meaning it will not be Case Sensitive
Returned value as Boolean Returns a boolean value equal to "TRUE" if the "Text" parameter contains the value specified in the "Value" parameter, or "FALSE" if it does not
Example #1 Build a workflow rules that automatically marks a user as internal if the email domain contains a specific organization (e.g.:acme.org, acme.co.uk, acme.co.us, etc). Create the workflow rule on the User entity and set the Trigger to "Each time a record is created or edited" with an action of "Update Field" (set to FALSE) and set the Evaluation Criteria to be:
` Contains(\$email,"@acme") `
Tips
1. The Contains function can only be used for string and text values.

` HasOverloadedResources (workItem as ObjectIdentifier, overload as Numeric, loadType as String, state1 as ObjectIdentifier, state2 as ObjectIdentifier, ... as ObjectIdentifier) as Boolean `
Category: Logical
Description: Returns "TRUE" when the specified Work Item has at least one resource that has a load greater than the Overload percentage specified for work items in the specified State. Overload is optional and is set to 100 by default. Up to five work item states can be specified, if not entered the query will run for all Active and Draft work items.
Parameters:
workItem as ObjectIdentifier(Work Item) Represents a reference to a work item or field that returns a work item
overload as Numeric Represents a numeric value that denotes the percent that is determined to be overloaded.
loadType as String Indicates whether to analyze load by either "PlannedWork" or "Remaining Effort"
state1 as ObjectIdentifier(State) An optional parameter that represents the state of work items for which you would like to get reported hours, this parameter can be repeated for each work item state, or left as null for work items in state "draft" and "active"
1. Active
2. Cancelled
3. Completed
4. Draft
5. On Hold
state2 as ObjectIdentifier(State) An optional parameter that represents the state of work items for which you would like to get reported hours, this parameter can be repeated for each work item state
1. Active
2. Cancelled
3. Completed
4. Draft
5. On Hold
... as ObjectIdentifier(State) An optional parameter that represents the state of work items for which you would like to get reported hours, this parameter can be repeated for each work item state
1. Active
2. Cancelled
3. Completed
4. Draft
5. On Hold
Returned value as Boolean Returns a boolean value, where "TRUE" represents that the work item specified in the "workItem" parameter has overloaded resources or "FALSE" if it does not
Example #1 Build a scheduled workflow rule running once a week that will check off a custom field check box on a work item if it has any overloaded resources.
This example requires the creation of a new custom field of type "Checkbox", once created, create a scheduled workflow rule and select the update field action that will update the custom checkbox field.
This will only update the work items that are in the state Draft, Active or On Hold.
In the update field action enter the following:
` If(HasOverloadedResources(currentobject(),100 ,"Draft","Active","On Hold"), TRUE, FALSE) `
Tips
1. This function has a parameter from type pick list that may have being customized by your organization. Please refer to the pick list values in the formula helper editor or view the values within the relevant field.

HasPotentialActualWork Function

` HasPotentialActualWork (workItemOrResource as ObjectIdentifier) as Boolean `
Category: Logical
Description: Returns TRUE when specified workitem / resource has actual work, timesheets and/or stopwatch.
Parameters:
workItemOrResource as ObjectIdentifier(Work Item, Human Resource)
Returned value as Boolean

HasRole Function

` HasRole (role as String, obj as ObjectIdentifier, user as ObjectIdentifier) as Boolean `
Category: Logical
Description: Returns a TRUE value if the user included has the specified role in the selected work item
Parameters:
role as String Must be one of the following: "Reviewer", "manager" or "Resource"
obj as ObjectIdentifier(Work Item) Represents a reference to a work item or issue or a field that returns a work item or issue object
user as ObjectIdentifier(User) Represents a reference to a user object or a field that returns a user object
Returned value as Boolean Returns a boolean value, where "TRUE" represents that the user specified in the "user" parameter has the "role" indicated in the object specified in the "obj" parameter
Example #1 Build a workflow rule that will only be triggered if the Current User has a specific role in a work item
Within the Evaluation Criteria enter the following:
` HasRole('Reviewer', CurrentObject(), CurrentUser()) `

If Function

` If (condition as Boolean, truePart as Object, falsePart as Object) as Object `
Category: Logical
<Description: Checks whether a condition is true, and returns one value if TRUE and another value if FALSE . The "IF" condition can be nested and include additional "IF" conditions
Parameters:
condition as Boolean Represents a condition or evaluation criteria that needs to result in a boolean value
truePart as Object Represents the resulting action if the data set in the "condition" parameter results in true
falsePart as Object Represents the resulting action if the data set in the "condition" parameter results in false. You can also enter another if() function within this parameter
Returned value as Object Returns either the data provided in the "truePart" or "falsePart" depending on whether the "condition" parameter evaluates to true or false
Example #1 Set a checkbox custom field, based on Issue Severity
` If(\$Severity = “Critical”, TRUE, FALSE) `
Example #2 Setting the due date of an issue to be based off of its severity. In this scenario, the response time for a critical issue is set to 1 working day, 3 working days for a high severity, and 5 working days for all other severity levels.
` If(\$Severity = “Critical”, DateAdd(\$CreatedOn,Days(1,TRUE)),If(\$Severity= “High”, DateAdd(\$CreatedOn,Days(3,TRUE)),DateAdd(\$CreatedOn,Days(5,TRUE)))) `
Tips
1. To set standard field values based on formulas, create workflow rules

In Function

` In (field as Object, value1 as Object, value2 as Object, ... as Object) as Boolean `
Category: Logical
Description: Returns true if a specified field value is equal to any of the supplied values
Parameters:
field as Object Represents a specific object field
value1 as Object Represents a potential value of the field specified in the "field" parameter
value2 as Object Optional parameter that represents a potential value of the field specified in the "field" parameter
... as Object Optional parameters that represent potential values of the field specified in the "field" parameter
Returned value as Boolean Returns a boolean value, where "TRUE" represents that the "field" specified has one of the values specified in the "valuex" parameters
Example #1 Create a workflow rule that will update the external user flag to false in case the user's email is one of the following domains: acme.co.us, acme.org, acme.com, acme.co.uk, etc. This will automatically set the 'external user; flag to FALSE, in case the email domain includes anything with acme.
` Not(In(\$Email,"@acme.")) `
Example #2 Automatically set a user's default availability for new projects based on their job title. For example, if the user is a developer, DBA or QA engineer, they should only spend 70% of time on project related activities (as the remaining 30% is spent on maintenance work). Create a workflow that will update the 'Default Project Availability' field as follows:
` If(In(\$JobTitle.Name, "Developer", "DBA","QA Engineer"), 70,100) `
Tips
1. To set standard field values based on formulas, create workflow rules

IsChanged Function

` IsChanged (field as Object) as Boolean `
Category: Logical
Description: Returns TRUE if the given field was updated in current session
Parameters:
field as Object Represents a specific object field
Returned value as Boolean Returns a boolean value, where "TRUE" indicates that the field value of the "field" parameter has changed
Example #1 To run a business rule each time an issue state is updated (from any value to any value), set the evalution criters to be:
` IsChanged(\$State) `
Example #2 To run a business rule that will automatically change the 'Issue State' from "Submitted" to "In Work" once it is assigned to a project, Use the "Update Field"action for the 'State' field and set the Evaluation Criteria to be:
` isChanged(\$PlannedFor) && Not(ISNULL(\$PlannedFor)) && (\$State = 'Submitted2') `
Tips
1. To set standard field values based on formulas, create workflow rules
2. You can also use the GetPreviousValue() function to compare the currently entered value of any field with its previous value
Note to reference the Issue State picklist value "Submitted" use the string "Submitted2"
Related Functions GetPreviousValue()

IsChangeType Function

` IsChangeType () as Boolean `
Category: Logical
Description: Returns TRUE if the current object has gone through the "change type" operation
Supported Context: WorkflowRule
Returned value as Boolean

IsEditable Function

` IsEditable (field as Object) as Boolean `
Category: Logical
Description: Returns TRUE if the field can be edited (based on the entity state)
Supported Context: WorkflowRule
Parameters:
field as Object Represents a specific object field
Returned value as Boolean Returns a boolean value, where "TRUE" indicates that the field specified in the "field" parameter is editable or "FALSE" if the field is read-only
Example #1 To run a validation rule that will block updating specific fields from being editable on a work item once it has been marked to complete, set the 'Evaluation Criteria' to be
` NOT(IsEditable(\$DueDate)) `
This function will return FALSE in the case where the work item is completed, since the due date cannot be updated on a completed work item
Tips
1. To set standard field values based on formulas, create workflow rules
Note this function is only available for Business Rules

IsFinancial Function

` IsFinancial (field as Object) as Boolean `
Category: Logical
Description: Returns TRUE if the given field requires financial permissions
Parameters:
field as Object Represents a specific object field
Returned value as Boolean Returns a boolean value, where "TRUE" indicates that the field specified in the "field" parameter is a financial field

IsFollower Function

` IsFollower (entity as ObjectIdentifier, user as ObjectIdentifier) as Boolean `
Category: Logical
Description: Returns TRUE if a user follows the entity<
Parameters:
entity as ObjectIdentifier (User, Topic, Work Item, Case, Expense Sheet, Expense Entry, Customer, Document)
user as ObjectIdentifier(User)
Returned value as Boolean

IsFromTemplate Function

` IsFromTemplate () as Boolean `
Category: Logical
Description: Returns TRUE if current object was created as part of a template. The function only applies to item creation.
Returned value as Boolean

IsLeaf Function

` IsLeaf (workItem as ObjectIdentifier) as Boolean `
Category: Logical
Description: Returns a value of TRUE if the work item specified is a leaf object, i.e. an object with no children
Parameters:
workItem as ObjectIdentifier(Work Item) Represents a specific work item or field that references a specific work item
Returned value as Boolean Returns a boolean value, where "TRUE" indicates that the work item specified in the "workItem" parameter is a leaf object (i.e. an object with no children or sub-object)
Example #1 Build a scheduled workflow rule that sends an email to a work item manager if the work item is a leaf and the Start Date has past with no assigned resources.
In the evaluation criteria enter the following:
` IsLeaf(currentobject()) && \$StartDate < Today() && \$ResourcesCount = 0 `

IsManuallySet Function

` IsManuallySet (field as Object) as Boolean `
Category: Logical
Description: Returns TRUE if value of the specified field is changed manually, if the specified field is changed by the system will return FALSE
Parameters:
field as Object Represents a specific object field
Returned value as Boolean Returns a boolean value, where "TRUE" indicates that the field specified in the "field" parameter is manually set and therefore not the automatically calculated field value
Example #1 To build a business rule that automatically updates the 'Start Date' of a work item if its predecessors were completed ahead of schedule, and the work item does not have a manually set start date, use the "Reschedule" action within a workflow for the 'Start Date' and set its Evaluation Criteria to be:
` \$Executable && Not(IsManuallySet(\$StartDate)) && (\$StartDate > Now()) `
Example #2 To build a workflow rule that adds a comment to a projects 'Comment Field' if the project status is manually set to "off track", set the Evaluation Criteria to be:
` IsManuallySet(\$TrackStatus) && \$TrackStatus = "Off Track" `
Example #3 And select the "Update Field" action, setting the 'Additional Comments' field to:
` "state changed manually to: " +ToString(\$TRackStatus)+ "\n" + GetPreviousValue(\$AdditionalComments) `
Tips
1. To set standard field values based on formulas, create workflow rules
2. To set a carriage return in a string, use the "\n" string
Note this function is only available for Business Rules

IsMemberOf Function

` IsMemberOf (group as ObjectIdentifier, user as ObjectIdentifier) as Boolean `
Category: Logical
Description: Returns a TRUE value if the specified user is a member of the selected group
Parameters:
group as ObjectIdentifier(UserGroup, DiscussionGroup) Represents a specific group or reference to a field that returns a group object
user as ObjectIdentifier(User) Represents a specific user or reference to a field that returns a user object
Returned value as Boolean Returns a boolean value, where "TRUE" indicates that the user specified in the "user" parameter is a member of the group specified in the "group" parameter
Example #1 Build a workflow rule that will send an email to a resource once they are assigned to a task, assuming that resource is part of your group.
` IsMemberOf('US Marketing', CurrentUser()) `

IsNew Function

` IsNew () as Boolean `
Category: Logical
Description: Indicates whether the object has just been added
Returned value as Boolean

IsNull Function

` IsNull (value as Object) as Boolean `
Category: Logical
Description: Returns TRUE if a given field name or value is NULL
Parameters:
value as Object Represents a specific object field
Returned value as Boolean Returns a boolean value, where "TRUE" indicates that the field specified in the "value" parameter is null
Example #1 Set a validation rule for Issues that won't allow the 'Issue State' field to be set to "In Work" if the 'target project' is not defined. To evaluation criteria should be:
` IsChanged(\$State) && (\$State = "In Work") && IsNULL(\$PlannedFor) `
Example #2 Set a project sponsor to be NULL if the 'project type' is marked as internal. Create a workflow rule that will set the following evaluation criteria:
` \$ProjectType = "Internal" `
To update the field within the workflow rule, add an "Update Field" action, select the 'Project Sponsor' field and set it to NULL.
Tips
1. To set standard field values based on formulas, create workflow rules
2. To set a field of any type to NULL, simply write NULL

IsTeamMember Function

` IsTeamMember (user as ObjectIdentifier) as Boolean `
Category: Logical
Description: Returns a value of TRUE if the specified user is a member of the work item or case pool
Parameters:
user as ObjectIdentifier(User) Represents a specific user or reference to a field that returns a user object
Returned value as Boolean Returns a boolean value, where "TRUE" indicates that the user specified in the "user" parameter is a team member of the current object (either work item or issue)
Example #1 Build a custom action that will only be available if the Current User is a team member of the selected work item or Issue.
Within the Evaluation Criteria enter the following:
` IsTeamMember(CurrentUser()) `

IsTriggeredBy Function

` IsTriggeredBy (trigger as String) as Boolean `
Category: Logical
Description: Returns true if current operation was triggered by specified trigger
Parameters:
trigger as String

New - Business rule was fired as a result of a new object being created
NewFromTemplate - Business rule was fired as a result of a new project or milestone being created from a template
NewTemplate - Business rule was fired as a result of a template being created
ChangeType - Business rule was fired as a result of an object change type action
Import - Business rule was fired as a result of import from MS project
CustomAction - Business rule was fired as a result of a custom action
Interact - Business rule was fired as a result of an email received by InterAct mailbox
Login - Business rule is fired as a result of a user logging in
Counters
NewFromObject - Business rule is fired as a result of a copying an object
API - Business rule is fired if triggered from an API call
Mobile - Business rule is fired when triggered from a mobile app (as opposed to web browser)
Trackit - Business rule is fired when email is tracked with Trackit
Other Actions - Default behavior

Returned value as Boolean Returns true if current operation was triggered by specified trigger
Example #1 To serve as a notification to organization administrators whenever a new template is created, Users can create a workflow rule that’s triggered when a new project is created from template
` IsTriggeredBy('NewTemplate') `

Not Function

` Not (logical as Boolean) as Boolean `
Category: Logical
Description: Changes FALSE to TRUE or TRUE to FALSE for the given value
Parameters:
logical as Boolean Represents a boolean value or a field or expression that returns a boolean value
Returned value as Boolean Returns a boolean value that changes FALSE to TRUE or TRUE to FALSE for the value given in the "logical" parameter
Example #1 Build a workflow rule that will automatically mark an invited user as external if the email domain does not contain "@acme". Set the Evaluation Criteria to:
` Not(In(\$Email,"@acme.")) `
Example #2 Build a validation rules that prevents clearing the value of the 'Target Project' field (assuming it was previously filled). Set the Evaluation Criteria to be"
` IsNull(\$PlannedFor) && Not(IsNull(GetPreviousValue(\$PlannedFor))) `
Example #3 To check if a user is marked as "External", or to check the value of any boolean field, use the following formula:
` Not(\$ExternalUser) `
Tips
1. The && expression is used for "and" within formula's while || is used for "or"

Date Function

` Date (year as Numeric, month as Numeric, day as Numeric) as DateTime `
Category: Date
Description: Creates a date from year, month and day values entered
Parameters:
year as Numeric Numeric value representing a year, or reference to a field that will return a year value
month as Numeric Numeric value representing a month (1-12), or reference to a field that will return a month value
day as Numeric Numeric value representing a day (1-31), or reference to a field that will retun a day value
Returned value as DateTime Returns a date value using the specified "Year", "Month", and "Day" parameters
Example #1 Create a date Custom Field that will automatically set the corporate fiscal year of the objects created date (e.g. May 1st). Set the formula to be:
` Date(Year(\$CreatedOn),5,1) `
Tips
1. To update standard date/date time fields, use workflow rules
2. Review other useful date and duration functions such as DateAdd(), ToDate(), etc.

` DateAdd (dateTime as DateTime, duration as Duration) as DateTime `
Category: Date
Description: Adds a duration value to a date and returns date
Parameters:
dateTime as DateTime Represents the original date as a date or reference to a field that will return a date value
duration as Duration Represents a duration or reference to a field that will return a duration value (i.e. 4d or 20h)
Returned value as DateTime Returns a date value equal to the "dateTime" parameter plus the "duration" parameter
Example #1 Create a Date Custom Field that will forecast the realistic due date of a work item, based on another duration custom field called Buffer, that will include both the original due date of the work item as well as the defined buffer. Set the formula to be:
` DateAdd(\$DueDate,\$Buffer) `
Tips
1. To update standard date/date time fields, use workflow rules
2. Review other useful date and duration functions such as DateSubstract(), ToDate(), etc.
Related Functions DateSubstract() ToDate()

` DateAddCalendarFixedPeriod (dateTime as DateTime, period as String, numberOfUnits as Numeric) as DateTime `
Category: Date
Description: Returns a Date value, where the Date parameter is the original date, period is a frame of time that can be either "d", "w", "m", "q", "y", and units indicates the number for the duration, such as 3 months from today. This function does not take into account non-working days or calendar exceptions.
Parameters:
dateTime as DateTime Represents the original date as a date or reference to a field that will return a date value
period as String Represents the frame of time that can be either "d", "w", "m", "q", or "y" corresponding to day, week, month, quarter, or year. This will be used along with the "numberOfUnits" parameter to add the desired value to the original date specified in the "dateTime" parameter
numberOfUnits as Numeric Represents the number of units that you would like to add to the "dateTime" parameter, must be of type number or reference to a field that will return a numeric value. This will be used along with the "period" parameter to add the desired value to the original date specified in the "dateTime" parameter
Returned value as DateTime Returns a date value equal to the original date specified in the "dateTime" parameter plus the "numberOfUnits"+"period" parameters
Example #1 Create a Custom Action that will shift the start date of a project by "x" amount of weeks, where "x" is defined by the end user upon the running of the custom action.
First create a User Input variable of type Duration in the custom action called "moveby"
Then select the "Reschedule Action" and select "start date". Within the formula enter the following:
` DateAddCalendarFixedPeriod(\$StartDate,"w",moveby) `
Note This function does not take into account non-working days or calendar exceptions

DateDiff Function

` DateDiff (dateTime1 as DateTime, dateTime2 as DateTime, interval as String) as Numeric `
Category: Date
Description: Returns the intervals between two dates in number. The 'interval' parameter is a string that should contain one of the following: “y” (for years), “m” (for months), "d" (for days) or "w" (for weeks)
Parameters:
dateTime1 as DateTime Represents the first date value as a date or reference to a field that will return a date value
dateTime2 as DateTime Represents the second date value as a date or reference to a field that will return a date value
interval as String Represents the desired interval value between the two dates specified in the "dateTime1" and "dateTime2" parameters, must be equal to either "d", "w", "m", or "y" corresponding to day, week, month, or year
Returned value as Numeric Returns a duration value equal to the difference between the two dates indicated in the "dateTime1" and "dateTime2" parameters using the duration type indicated in the "interval" parameter
Example #1 Create a numeric custom field that will ALWAYS define the length of a project in regular calendar weeks (rather than Clarizen's calendar weeks that might be based on personal calendars). Set the formula to be:
` DateDiff(\$DueDate,\$StartDate,"w") `
Another example that will get the delay of the due date of a work item, so if a work item has been moved by 7 days will return 7: DateDiff(\$DueDate, GetPreviousValue(\$DueDate),"d")
Tips
1. Use the DateSubtract() function to return a duration field type that will take into consideration your Clarizen calendar settings
Related Functions DateSubtract()

DateSubtract Function

` DateSubtract (dateTime1 as DateTime, dateTime2 as DateTime, isWorking as Boolean) as Duration `
Category: Date
Description: Subtracts two dates or datetime parameters (can be fields) and returns duration value. If IsWorking (optional parameter, default is FALSE) set to TRUE returns only the working duration.
Parameters:
dateTime1 as DateTime Represents the first date value as a date or reference to a field that will return a date value
dateTime2 as DateTime Represents the second date value as a date or reference to a field that will return a date value
isWorking as Boolean An optional parameter that allows you to return only work days or calendar days. Enter a value of "TRUE" for working days, or "FALSE" for total calendar days
Returned value as Duration Returns a duration value equal to the difference between the two dates indicated in the "dateTime1" and "dateTime2" parameters
Example #1 Create a duration custom field that will define the buffer a work item has between the committed due date and the planned due date. Set the formula to be:
` Datesubtract(\$CommittedDate,\$DueDate) `
Tips
1. To update the standard date/date time fields use workflow rules
2. View other useful date and duration functions such as DateAdd(), ToDate(), etc.
Note Duration = DateTime1 – DateTime2 e.g. DateSubtract(‘19/2/12’, ‘15/2/12’, TRUE) = 4 d

DateTime Function

` DateTime (year as Numeric, month as Numeric, day as Numeric, hour as Numeric, minute as Numeric, second as Numeric) as DateTime `
Category: Date
Description: Creates a timestamp from a year, month, day, hour, min and second
Parameters:
year as Numeric Numeric value representing a year, or reference to a field that will return a year value
month as Numeric Numeric value representing a month (1-12), or reference to a field that will return a month value
day as Numeric Numeric value representing a day (1-31), or reference to a field that will return a year value
hour as Numeric Numeric value representing an hour (0-23), or reference to a field that will return an hour value
minute as Numeric Numeric value representing a minute (00-59), or reference to a field that will return a minute value
second as Numeric Numeric value representing a second (00-59), or reference to a field that will return a second value
Returned value as DateTime Returns a date time value using the specified "year", "month", "day", "hour", "minute", and "second" parameters
Example #1 Create a date time field representing May 1st 2011 19:30
` DateTime(2011,5,1,19,30,00) `
Example #2 Define a workflow rule that automatically sets a task's due date to be the end of the following day of the start date Choose the "Reschedule" action and set the start date to be the following:
` DateTime(Year(\$StartDate),Month(\$StartDate),Day(\$StartDate)+1,16,00,00) `
So that if a task's start date is January 2nd 2011, then the due date of that task will be January 3rd at 16:00
Tips
1. To update the standard date/date time fields, use workflow rules
2. Review other date and duration useful functions, such as DateAdd(), ToDate(), etc

DateTimeValue Function

` DateTimeValue (text as String, format as String) as DateTime `
Category: Date
Description: Returns a timestamp value from a text given in the following format: YYYY-MM-DD hh:mm:ss
Parameters:
text as String Represents a text value as a string, or reference to a field that will return a string value given in the following format: YYYY-MM-DD hh:mm:ss
format as String
Returned value as DateTime Returns a date time value using the text specified in the "text" parameter
Example #1 Create a custom field that will return a set Date Time Value, or one based off of another custom or standard field
` DateTimeValue(“2011-05-01 19:30:00”) `

DateValue Function

` DateValue (expression as String, format as String) as Date `
Category: Date
Description: Returns a date value from a string given in the following format: YYYY-MM-DD
Parameters:
expression as String Represents a text value as a string, or reference to a field that will return a string value given in the following format: YYYY-MM-DD
format as String
Returned value as Date Returns a date value using the text specified in the "expression" parameter
Example #1 Create a custom field that will return a set Date Value, or one based off of another custom or standard field
` DateValue(“2011-05-01”) `

Day Function

` Day (dateTime as DateTime) as Numeric `
Category: Date
Description: Returns the day number of the month of a given date or date time value
Parameters:
dateTime as DateTime Represents a date value or a field that will return a date value
Returned value as Numeric Returns a numeric value representing the day number in the month of the date value given in the "dateTime" parameter
Example #1 Define a validation rule that will prevent defining a due date for a work item on the 13th of every month :). Set the evaluation criteria to be:
` Day(\$DueDate) = 13 `
Tips
1. This function can be useful for Date() and DateTime()
2. To update standard date/date time fields, use workflow rules
3. Review other date and duration useful functions, such as DateAdd(), ToDate(), etc
Related Functions Date() DateTime()

Days Function

` Days (number as Numeric, isWorking as Boolean) as Duration `
Category: Date
Description: Returns duration value in days. If IsWorking (optional parameter, default is FALSE) set to TRUE returns only the working duration.
Parameters:
number as Numeric Numeric value or reference to a numeric field
isWorking as Boolean An optional parameter that allows you to return only work days or calendar days. Enter a value of "TRUE" for working days, or "FALSE" for total calendar days
Returned value as Duration Returns a duration value in days based on the "number" parameter entered either as working hours or calendar hours as determined in the "isWorking" parameter
Example #1 Create a formula based custom field that will supply a rough extimation (in days) for the resolution of an issue, based on other fields that specify that complexity and priority of the issue. Set within the formula that days function, that will automatically set the due date to 7 working days.
` Days(7,TRUE) `
Tips
1. To update standard date/date time fields, use workflow rules
2. Review other date and duration useful functions, such as DateAdd(), ToDate(), etc

GetAssignedWorkingTime Function

` GetAssignedWorkingTime (user as ObjectIdentifier, dateTime1 as DateTime, dateTime2 as DateTime, loadType as String, state1 as ObjectIdentifier, state2 as ObjectIdentifier, ... as ObjectIdentifier) as Duration `
Category: Date
Description: Returns a duration value indicating the working time for the specified User in the given time range for work items in the indicated states. Where the loadType indicates whether to analyze the load by either "PlannedWork" or "RemainingEffort". Up to five work item states can be specified, if not entered the query will run for all Active and Draft work items.
Parameters:
user as ObjectIdentifier(User) Represents a reference to a user object or a field that returns a user object
dateTime1 as DateTime Represents a reference to a from date or field that returns a date value
dateTime2 as DateTime Represents a reference to a to date or field that returns a date value
state1 as ObjectIdentifier(State) An optional parameter that represents the state of work items for which you would like to get reported hours, this parameter can be repeated for each work item state, or left as null for work items in state 'draft' and 'active'
1. Active
2. Cancelled
3. Completed
4. Draft
5. On Hold
state2 as ObjectIdentifier(State) An optional parameter that represents the state of work items for which you would like to get reported hours, this parameter can be repeated for each work item state
1. Active
2. Cancelled
3. Completed
4. Draft
5. On Hold
... as ObjectIdentifier(State) An optional parameter that represents the state of work items for which you would like to get reported hours, this parameter can be repeated for each work item state
1. Active
2. Cancelled
3. Completed
4. Draft
5. On Hold
Returned value as Duration Returns a duration value indicating the working time for the specified User in the given time range for work items in the indicated states
Example #1 Create a scheduled workflow rule that will run at the start of a new iteration and calculate the working time for a given user for the next two weeks. This duration value should fill in a custom field called "User Availability" to allow for quick and easy assignment using Agile project management methodologies
` GetAssignedWorkingTime('joe.user',today(),today()+days(14),'PlannedWork') `
Tips
1. This function has a parameter from type pick list that may have being customized by your organization. Please refer to the pick list values in the formula helper editor or view the values within the relevant field.
Related Functions Today()

GetEndWorkday Function

` GetEndWorkday (dateTime as DateTime, user as ObjectIdentifier) as DateTime `
Category: Date
Description: Returns an end time of working day on specified dateTime according the calendar settings of the specified user. If the user was not specified the returned value is based on organizational calendar.
Parameters:
dateTime as DateTime
user as ObjectIdentifier(User)
Returned value as DateTime

GetEstimatedDueDate Function

` GetEstimatedDueDate (workItem as ObjectIdentifier, user as ObjectIdentifier) as DateTime `
Category: Date
Description: Returns the estimated due date of a specified work item, based on the actual and estimated work item progress and duration. The "user" parameter is optional, and if not entered will return the estimated due date of the work item as a whole.
Parameters:
workItem as ObjectIdentifier(Work Item) Represents a specific work item or field that references a specific work item, for which you would like to get an estimated due date on
user as ObjectIdentifier(User) This is an optional parameter that represents a specific user in your organization, if entered, the function will check the estimated due date based on the specified user's calendar
Returned value as DateTime Returns a date value equal to the estimated due date of the work item specified in the "workItem" parameter for the specified user, if no user is entered in the "user" parameter will return the estimated due date of the work item as a whole

GetStartWorkday Function

` GetStartWorkday (dateTime as DateTime, user as ObjectIdentifier) as DateTime `
Category: Date
Description: Returns an start time of working day on specified dateTime according the calendar settings of the specified user. If user was not specified the returned value is based on organizational calendar.
Parameters:
dateTime as DateTime
user as ObjectIdentifier(User)
Returned value as DateTime

GetSumOfTimesheetReportHoursForFixedPeriod Function

` GetSumOfTimesheetReportHoursForFixedPeriod (user as ObjectIdentifier, period as String, state1 as ObjectIdentifier, state2 as ObjectIdentifier, ... as ObjectIdentifier) as Duration `
Category: Date
Description: Returns the the sum total duration of reported timesheets for the specified user. Where the "user" argument can be a specific user or a reference to user field, and the "period" is either "m" for current month, "w" for current week, or "d" for current day. The "state" argument is optional and by default will return reported hours for timesheets in all states.
Parameters:
user as ObjectIdentifier(User) Represents a reference to a user object or a field that returns a user object
period as String Represents the frame of time for which you would like to know the total reported hours. Value can be either "d", "w", "m" corresponding to day, week, month, quarter, or year
state1 as ObjectIdentifier(State) An optional parameter that represents the state of work items for which you would like to get reported hours, this parameter can be repeated for each work item state, or left as null for work items in state "draft" and "active"
1. Active
2. Cancelled
3. Completed
4. Draft
5. On Hold
state2 as ObjectIdentifier(State) An optional parameter that represents the state of work items for which you would like to get reported hours, this parameter can be repeated for each work item state
1. Active
2. Cancelled
3. Completed
4. Draft
5. On Hold
... as ObjectIdentifier(State) An optional parameter that represents the state of work items for which you would like to get reported hours, this parameter can be repeated for each work item state
1. Active
2. Cancelled
3. Completed
4. Draft
5. On Hold
Returned value as Duration Returns a duration value equal to the total number of reported hours for the user specified in the "user" parameter for the given "period" for the work items in the given states specified in the "statex" parameters
Example #1

Create a custom field on the User entity that will calculate the total timesheet hours reported for the current week. This will allow both the user and the managers to view total reported hours.

To implement this, first create a custom field on a user of type "duration", and set it to be updated by the API.

Next, create a workflow rule on the Timesheet entity, that will run every time a record is created or edited.
In the Set Actions section, select the "Update Field" action, and then find the custom field you created by first clicking on the "Reported By Fields" option.
Now enter the following:

` GetSumOfTimesheetReportHoursForFixedPeriod(currentuser(),"w") `
This formula can also be changed to track the reported timesheet hours for a given month or day, or for timesheets that have only been approved.
Tips
1. This function has a parameter from type pick list that may have being customized by your organization. Please refer to the pick list values in the formula helper editor or view the values within the relevant field.

GetSumOfTimesheetReportHoursForGivenPeriod Function

` GetSumOfTimesheetReportHoursForGivenPeriod (user as ObjectIdentifier, from as DateTime, to as DateTime, state1 as ObjectIdentifier, state2 as ObjectIdentifier, ... as ObjectIdentifier) as Duration `
Category: Date
Description: Returns the the sum total duration of reported timesheets for the specified user. Where the "user" argument can be a specific user or a reference to user field, and "dateTime1" and "dateTime2" arguments are used to determine the period. The "state" argument is optional and by default will return reported hours for timesheets in all states.
Parameters:
user as ObjectIdentifier(User) Represents a reference to a user object or a field that returns a user object
from as DateTime Represents a reference to a from date or field that returns a date value
to as DateTime Represents a reference to a to date or field that returns a date value
state1 as ObjectIdentifier(State) An optional parameter that represents the state of work items for which you would like to get reported hours, this parameter can be repeated for each work item state, or left as null for work items in state "draft" and "active"
1. Active
2. Cancelled
3. Completed
4. Draft
5. On Hold
state2 as ObjectIdentifier(State) An optional parameter that represents the state of work items for which you would like to get reported hours, this parameter can be repeated for each work item state
1. Active
2. Cancelled
3. Completed
4. Draft
5. On Hold
... as ObjectIdentifier(State) An optional parameter that represents the state of work items for which you would like to get reported hours, this parameter can be repeated for each work item state
1. Active
2. Cancelled
3. Completed
4. Draft
5. On Hold
Returned value as Duration Returns a duration value equal to the total number of reported hours for the user specified in the "user" parameter for the date range specified in the "from" and "to" parameters for the work items in the given states specified in the "statex" parameters
Example #1 Create a custom field on the User entity that will calculate the total timesheet hours reported for a specific date range. This will allow both the user and the managers to view total reported hours say for the duration of a specific work item.
To implement this, first create a custom field on a user of type "duration", and set it to be updated by the API.
Next, create a workflow rule on the Timesheet entity, that will run every time a record is created or edited, and in the evaluation criteria make sure to enter the desired work item that this should be tracked for.
In the Set Actions section, select the "Update Field" action, and then find the custom field you created by first clicking on the "Reported By Fields" option.
Now enter the following:
` GetSumOfTimesheetReportHoursForGivenPeriod(currentuser(),\$WorkItem.StartDate,\$WorkItem.DueDate) `
Tips
1. This function has a parameter from type pick list that may have being customized by your organization. Please refer to the pick list values in the formula helper editor or view the values within the relevant field.

GetTimeZone Function

` GetTimeZone (obj as ObjectIdentifier) as ObjectIdentifier `
Category: Date
Description: Returns the time zone of the organization, a user or a user group. Valid inputs for the obj parameter are a user entity and a user group entity. If no entity is defined, the time zone returned is that of the organization
Parameters:
obj as ObjectIdentifier(User, UserGroup) An optional field that represents a reference to either a user or a user group or a field that returns a user or a user group. If null this parameter will reference the organization
Returned value as ObjectIdentifier Returns the time zone set on the object specified in the "obj" parameter
Example #1 To get the time zone of the current user
` GetTimeZone(CurrentUser()) `

GetWorkingTimeForFixedPeriod Function

` GetWorkingTimeForFixedPeriod (user as ObjectIdentifier, period as String) as Duration `
Category: Date
Description: Returns the Working Time for a user for the selected period. Where the "user" argument can be a specific user or a reference to user field, and the "period" is either "y" for current year, "q" for current quarter, "m" for current month, or "w" for current week.
Parameters:
user as ObjectIdentifier(User) Represents a reference to a user object or a field that returns a user object
period as String Represents the frame of time for which you would like to know the total working hours. Value can be either "d", "w", "m" corresponding to day, week, month
Returned value as Duration Returns a duration value equal to the total number of working hours for the user specified in the "user" parameter for the given "period"
Example #1 Calculate the Bill to Burn KPI on the user entity.
Within a workflow rule, select the "Update Field" action and select the custom field Bill to Burn KPI (needs to be created by your organization first).
` \$C_BilltoBurn/(GetWorkingTimeForFixedPeriod(CurrentUser(), 'w')/Hours(1)) `

GetWorkingTimeForGivenPeriod Function

` GetWorkingTimeForGivenPeriod (user as ObjectIdentifier, dateTime1 as DateTime, dateTime2 as DateTime) as Duration `
Category: Date
Description: Returns the Working Time for a user for a specific period of time. Where the "user" argument can be a specific user or a reference to user field. "dateTime1" and "dateTime2" arguments are used to determine the period of interest.
Parameters:
user as ObjectIdentifier(User) Represents a reference to a user object or a field that returns a user object
dateTime1 as DateTime Represents a reference to a from date or field that returns a date value
dateTime2 as DateTime Represents a reference to a to date or field that returns a date value
Returned value as Duration Returns a duration value equal to the total number of working hours for the user specified in the "user" parameter for the date range specified in the "dateTime1" and "dateTime2" parameters
Example #1 Calculate the Bill to Burn KPI on the user entity.
Within a workflow rule, select the "Update Field" action and select the custom field Bill to Burn KPI (needs to be created by your organization first).
` \$C_BilltoBurn/(GetWorkingTimeForGivenPeriod(CurrentUser(), 06/05/2011, 07/15/2011)/Hours(1)) `

Hours Function

` Hours (number as Numeric, isWorking as Boolean) as Duration `
Category: Date
Description: Returns duration value in hours. If IsWorking (optional parameter, default is FALSE) set to TRUE returns only the working duration.
Parameters:
number as Numeric Numeric value or reference to a numeric field
isWorking as Boolean An optional parameter that indicates whether the return value will show calendar hours or working hours, where "TRUE" will return working hours and "FALSE" calendar hours. If null, value will default to "FALSE"
Returned value as Duration Returns a duration value in hours equal to the number specified in the "number" parameter either as working hours or calendar hours as determined in the "isWorking" parameter
Example #1 Add a formula based custom field that will supply a rough estimation (in hours) for the resolution of an issue, based on other fields that specify that complexity and priority of the issue. Set within the formula the hours function to automatically set the duration to 7 hours.
` Hours(7,TRUE) `
Tips
1. To update standard date/date time fields, use workflow rules
2. Review other date and duration useful functions, such as DateAdd(), ToDate(), etc

IsResourceAvailableForFixedPeriod Function

` IsResourceAvailableForFixedPeriod (user as ObjectIdentifier, period as String, overload as Numeric, loadType as String, state1 as ObjectIdentifier, state2 as ObjectIdentifier, ... as ObjectIdentifier) as Boolean `
Category: Date
Description: Returns "FALSE" in the case that the specified User has a load greater than the Overload percentage during the specified Period for work items in the given states. Period is an optional variable that can be set to either 'y', 'm', 'q', or 'w', where 'w' is the default value. Up to five work item states can be specified, if not entered the query will run for all Active and Draft work items.
Parameters:
user as ObjectIdentifier(User) Represents a reference to a user object or a field that returns a user object
period as String An optional variable that represents the frame of time for which you would like to know the resource availability. Value can be either "d", "w", "m" corresponding to day, week, month. If null then the parameter value defaults to "w"
overload as Numeric Represents a numeric value that denotes the percent that is determined to be overloaded.
loadType as String Indicates whether to analyze load by either "PlannedWork" or "Remaining Effort"
state1 as ObjectIdentifier(State) An optional parameter that represents the state of work items for which you would like to get reported hours, this parameter can be repeated for each work item state, or left as null for work items in state "draft" and "active"
1. Active
2. Cancelled
3. Completed
4. Draft
5. On Hold
state2 as ObjectIdentifier(State) An optional parameter that represents the state of work items for which you would like to get reported hours, this parameter can be repeated for each work item state
1. Active
2. Cancelled
3. Completed
4. Draft
5. On Hold
... as ObjectIdentifier(State) An optional parameter that represents the state of work items for which you would like to get reported hours, this parameter can be repeated for each work item state
1. Active
2. Cancelled
3. Completed
4. Draft
5. On Hold
Returned value as Boolean Returns a boolean value, where "TRUE" indicates that the user specified in the "user" parameter is available during the time frame specified in the "period" parameter
Example #1 Create a Scheduled Workflow Rule on the User that will run repeatedly every Monday morning, and send an email to a user's direct manager if the user is overloaded in the current week.
This will only check for resource availability on tasks that are "Active", "Draft", or "On Hold"
In the Evaluation Criteria enter the following:
` IsResourceAvailableForFixedPeriod(currentuser(),"w",100, "Active", "Draft", "On Hold") `
Tips
1. This function has a parameter from type pick list that may have being customized by your organization. Please refer to the pick list values in the formula helper editor or view the values within the relevant field.

IsResourceAvailableForGivenPeriod Function

` IsResourceAvailableForGivenPeriod (user as ObjectIdentifier, dateTime1 as DateTime, dateTime2 as DateTime, overload as Numeric, loadType as String, state1 as ObjectIdentifier, state2 as ObjectIdentifier, ... as ObjectIdentifier) as Boolean `
Category: Date
Description: Returns "FALSE" in the case that the specified user has a load greater than the Overload percentage specified between the time period of dateTime1 and dateTime2 for work items in the given states. Overload is an optional parameter and is set to 100 by default. Up to five work item states can be specified, if not entered the query will run for all Active and Draft work items.
Parameters:
user as ObjectIdentifier(User) Represents a reference to a user object or a field that returns a user object
dateTime1 as DateTime Represents a reference to a from date or field that returns a date value
dateTime2 as DateTime Represents a reference to a to date or field that returns a date value
overload as Numeric Represents a numeric value that denotes the percent that is determined to be overloaded.
loadType as String Indicates whether to analyze load by either "PlannedWork" or "Remaining Effort"
state1 as ObjectIdentifier(State) An optional parameter that represents the state of work items for which you would like to get reported hours, this parameter can be repeated for each work item state, or left as null for work items in state "draft" and "active"
1. Active
2. Cancelled
3. Completed
4. Draft
5. On Hold
state2 as ObjectIdentifier(State) An optional parameter that represents the state of work items for which you would like to get reported hours, this parameter can be repeated for each work item state
1. Active
2. Cancelled
3. Completed
4. Draft
5. On Hold
... as ObjectIdentifier(State) An optional parameter that represents the state of work items for which you would like to get reported hours, this parameter can be repeated for each work item state
1. Active
2. Cancelled
3. Completed
4. Draft
5. On Hold
Returned value as Boolean Returns a boolean value, where "TRUE" indicates that the user specified in the "user" parameter is avaiable during the time frame specified in the "period" parameter
Example #1 Create a workflow rule on the Resource Assigned Link that will send an email to the project manager if a user that has been assigned to a task is overloaded.
This workflow rule will only capture work items that are in the "Draft" and "Active" state, as the state parameters are not entered.
In the evaluation criteria enter the following:
` IsResourceAvailableForGivenPeriod(\$Resource,\$WorkItem.StartDate,\$WorkItem.DueDate,100) `
Tips
1. This function has a parameter from type pick list that may have being customized by your organization. Please refer to the pick list values in the formula helper editor or view the values within the relevant field.

Minutes Function

` Minutes (number as Numeric) as Duration `
Category: Date
Description: Returns duration value in minutes
Parameters:
number as Numeric Numeric value or reference to a numeric field
Returned value as Duration Returns a duration value in minutes equal to the number specified in the "number" parameter
Example #1 Set a workflow rule that will automatically update the duration of tasks that are marked to be of certain type to be
` Minutes(30) `
Tips
1. To update standard date/date time fields, use workflow rules
2. Review other date and duration useful functions, such as DateAdd(), ToDate(), etc

Month Function

` Month (dateTime as DateTime) as Numeric `
Category: Date
Description: Returns the number of any given month, 1 (January) and 12 (December)
Parameters:
dateTime as DateTime Represents a date value or a field that will return a date value
Returned value as Numeric Returns a numeric value representing the month number in the year of the date value given in the "dateTime" parameter
Example #1 Create a custom field that will return the month in which a project is due
` Month(\$DueDate) `
Example #2 Create a custom picklist field that will define the quarters in which projects will be delivered
` If(In(Month(\$DueDate),1,2,3),"Q1",If(In(Month(\$DueDate),4,5,6),"Q2",If(In(Month(\$DueDate),7,8,9),"Q3","Q4"))) `
Tips
1. To update standard date/date time fields, use workflow rules
2. Review other date and duration useful functions, such as DateAdd(), ToDate(), etc

Months Function

` Months (number as Numeric) as Duration `
Category: Date
Description: Returns duration value in months
Parameters:
number as Numeric Numeric value or reference to a numeric field
Returned value as Duration Returns a duration value in months based on the "number" parameter entered
Example #1 Create a workflow rules that will automatically update the duration of tasks that are marked to be of a certain type
` Months(2) `
Tips
1. To update standard date/date time fields, use workflow rules
2. Review other date and duration useful functions, such as DateAdd(), ToDate(), etc

Now Function

` Now () as DateTime `
Category: Date
Description: Returns the current date and time in the UTC timezone.
Returned value as DateTime

OrganizationNow Function

` OrganizationNow () as DateTime `
Category: Date
Description: Returns the current date and time in the organization's time zone
Returned value as DateTime

Seconds Function

` Seconds (number as Numeric) as Duration `
Category: Date
Description: Returns duration value in seconds
Parameters:
number as Numeric Numeric value or reference to a numeric field
Returned value as Duration Returns a duration value in seconds based on the "number" parameter entered
Example #1 Build a numeric custom field that will measure the response time interval of a certain task, based on its duration.
` \$ActualDuration/Seconds(90) `
Tips
1. To update standard date/date time fields, use workflow rules
2. Review other date and duration useful functions, such as DateAdd(), ToDate(), etc

Today Function

` Today () as Date`
Category: Date
Description: Returns today's date at 00:00 converted into UTC. For example, if your Organization time zone is UTC +2, the Today() function will return yesterday's date (22:00 UTC the day before)
Returned value as Date

ToOrganizationalDateTime Function

` ToOrganizationalDateTime (dateTime as DateTime) as DateTime `
Category: Date
Description: Converts a DateTime argument to the timezone of the Organization
Parameters:
dateTime as DateTime Represents a date time value or reference to a field that will return a date time
Returned value as DateTime Returns a date time value equal to the date time entered in the "dateTime" parameter converted to the organization time based on the organization time zone
Example #1 Return the organization time when using the Today() function
Within a workflow rule, when trying to return a date time using the function Today(), use the ToOrganizationalDateTime function to ensure that it returns the current date time as per the organizations timezone.
` ToOrganizationalDateTime(Today()) `
Related Functions Today() ToOrganizationalDateTime()

ToTimeZone Function

` ToTimeZone (dateTime as DateTime, fromTimeZone as ObjectIdentifier, toTimeZone as ObjectIdentifier) as DateTime `
Category: Date
Description: Converts the given date time parameter from the 'from' timezone to the 'to' time zone
Parameters:
dateTime as DateTime Represents a date time value or reference to a field that will return a date time
fromTimeZone as ObjectIdentifier(Time Zone) Represents the current time zone of the value indicated in the "dateTime" parameter, or reference to a field that returns a time zone. Accepts a time zone code which can be accessed from within the "pick list" tab of the formula helper (i.e. 'Greenwich Standard Time')
1. Afghanistan Standard Time
3. Arab Standard Time
4. Arabian Standard Time
5. Arabic Standard Time
6. Argentina Standard Time
7. Atlantic Standard Time
8. AUS Central Standard Time
9. AUS Eastern Standard Time
10. Azerbaijan Standard Time
11. Azores Standard Time
13. Cape Verde Standard Time
14. Caucasus Standard Time
15. Cen. Australia Standard Time
16. Central America Standard Time
17. Central Asia Standard Time
18. Central Brazilian Standard Time
19. Central Europe Standard Time
20. Central European Standard Time
21. Central Pacific Standard Time
22. Central Standard Time
23. Central Standard Time (Mexico)
24. China Standard Time
25. Dateline Standard Time
26. E. Africa Standard Time
27. E. Australia Standard Time
28. E. Europe Standard Time
29. E. South America Standard Time
30. Eastern Standard Time
31. Egypt Standard Time
32. Ekaterinburg Standard Time
33. Fiji Standard Time
34. FLE Standard Time
35. Georgian Standard Time
36. GMT Standard Time
37. Greenland Standard Time
38. Greenwich Standard Time
39. GTB Standard Time
40. Hawaiian Standard Time
41. India Standard Time
42. Iran Standard Time
43. Israel Standard Time
44. Jordan Standard Time
45. Kamchatka Standard Time
46. Korea Standard Time
47. Mauritius Standard Time
48. Mid-Atlantic Standard Time
49. Middle East Standard Time
50. Montevideo Standard Time
51. Morocco Standard Time
52. Mountain Standard Time
53. Mountain Standard Time (Mexico)
54. Myanmar Standard Time
55. N. Central Asia Standard Time
56. Namibia Standard Time
57. Nepal Standard Time
58. New Zealand Standard Time
59. Newfoundland Standard Time
60. North Asia East Standard Time
61. North Asia Standard Time
62. Pacific SA Standard Time
63. Pacific Standard Time
64. Pacific Standard Time (Mexico)
65. Pakistan Standard Time
66. Paraguay Standard Time
67. Romance Standard Time
68. Russian Standard Time
69. SA Eastern Standard Time
70. SA Pacific Standard Time
71. SA Western Standard Time
72. Samoa Standard Time
73. SE Asia Standard Time
74. Singapore Standard Time
75. South Africa Standard Time
76. Sri Lanka Standard Time
77. Taipei Standard Time
78. Tasmania Standard Time
79. Tokyo Standard Time
80. Tonga Standard Time
81. Ulaanbaatar Standard Time
82. US Eastern Standard Time
83. US Mountain Standard Time
84. UTC
85. Venezuela Standard Time
87. W. Australia Standard Time
88. W. Central Africa Standard Time
89. W. Europe Standard Time
90. West Asia Standard Time
91. West Pacific Standard Time
92. Yakutsk Standard Time
toTimeZone as ObjectIdentifier(Time Zone) Represents the time zone to which you would like to convert the value indicated in the "dateTime" parameter, or reference to a field that returns a time zone. Accepts a time zone code which can be accessed from within the "pick list" tab of the formula helper (i.e. 'Greenwich Standard Time')
1. Afghanistan Standard Time
3. Arab Standard Time
4. Arabian Standard Time
5. Arabic Standard Time
6. Argentina Standard Time
7. Atlantic Standard Time
8. AUS Central Standard Time
9. AUS Eastern Standard Time
10. Azerbaijan Standard Time
11. Azores Standard Time
13. Cape Verde Standard Time
14. Caucasus Standard Time
15. Cen. Australia Standard Time
16. Central America Standard Time
17. Central Asia Standard Time
18. Central Brazilian Standard Time
19. Central Europe Standard Time
20. Central European Standard Time
21. Central Pacific Standard Time
22. Central Standard Time
23. Central Standard Time (Mexico)
24. China Standard Time
25. Dateline Standard Time
26. E. Africa Standard Time
27. E. Australia Standard Time
28. E. Europe Standard Time
29. E. South America Standard Time
30. Eastern Standard Time
31. Egypt Standard Time
32. Ekaterinburg Standard Time
33. Fiji Standard Time
34. FLE Standard Time
35. Georgian Standard Time
36. GMT Standard Time
37. Greenland Standard Time
38. Greenwich Standard Time
39. GTB Standard Time
40. Hawaiian Standard Time
41. India Standard Time
42. Iran Standard Time
43. Israel Standard Time
44. Jordan Standard Time
45. Kamchatka Standard Time
46. Korea Standard Time
47. Mauritius Standard Time
48. Mid-Atlantic Standard Time
49. Middle East Standard Time
50. Montevideo Standard Time
51. Morocco Standard Time
52. Mountain Standard Time
53. Mountain Standard Time (Mexico)
54. Myanmar Standard Time
55. N. Central Asia Standard Time
56. Namibia Standard Time
57. Nepal Standard Time
58. New Zealand Standard Time
59. Newfoundland Standard Time
60. North Asia East Standard Time
61. North Asia Standard Time
62. Pacific SA Standard Time
63. Pacific Standard Time
64. Pacific Standard Time (Mexico)
65. Pakistan Standard Time
66. Paraguay Standard Time
67. Romance Standard Time
68. Russian Standard Time
69. SA Eastern Standard Time
70. SA Pacific Standard Time
71. SA Western Standard Time
72. Samoa Standard Time
73. SE Asia Standard Time
74. Singapore Standard Time
75. South Africa Standard Time
76. Sri Lanka Standard Time
77. Taipei Standard Time
78. Tasmania Standard Time
79. Tokyo Standard Time
80. Tonga Standard Time
81. Ulaanbaatar Standard Time
82. US Eastern Standard Time
83. US Mountain Standard Time
84. UTC
85. Venezuela Standard Time
87. W. Australia Standard Time
88. W. Central Africa Standard Time
89. W. Europe Standard Time
90. West Asia Standard Time
91. West Pacific Standard Time
92. Yakutsk Standard Time
Returned value as DateTime Returns a date time value equal to the date time entered in the "dateTime" parameter converted to the time zone indicated in the "toTimeZone" parameter
Example #1 Convert current time to the current user's time zone
` ToTimeZone(Now(), 'UTC', GetTimeZone(CurrentUser())) `
Tips
1. This function has a parameter from type pick list that may have being customized by your organization. Please refer to the pick list values in the formula helper editor or view the values within the relevant field.

Week Function

` Week (date as DateTime) as Numeric `
Category: Date
Description: Returns a numeric value that represents the week of the year
Parameters:
date as DateTime
Returned value as Numeric

Weeks Function

` Weeks (number as Numeric, isWorking as Boolean) as Duration `
Category: Date
Description: Returns duration value in weeks. If IsWorking (optional parameter, default is FALSE) set to TRUE returns only the working duration.
Parameters:
number as Numeric Numeric value or reference to a numeric field
isWorking as Boolean An optional parameter that allows you to return only work days or calendar days. Enter a value of "TRUE" for working weeks, or "FALSE" for total calendar weeks
Returned value as Duration Returns a duration value in weeks based on the "number" parameter entered either as working weeks or calendar weeks as determined in the "isWorking" parameter
Example #1 Build a date custom field for an issue that will define the maximum tolerated response date, which, in this case, is 2 working weeks for a critical bug and 4 working weeks for other other severity levels
` If(\$severity= "critical", \$CreatedOn + weeks(2,TRUE), \$CreatedOn+weeks(4,TRUE)) `
Example #3 ToDate
Tips
1. To update standard date/date time fields, use workflow rules
2. Review other date and duration useful functions, such as DateAdd(), ToDate(), etc

Year Function

` Year (dateTime as DateTime) as Numeric `
Category: Date
Description: Returns the number of any given year
Parameters:
dateTime as DateTime Represents a date value or a field that will return a date value
Returned value as Numeric Returns a numeric value representing the year number in the date value given in the "dateTime" parameter
Example #1 Create a numeric custom field for a project entity that will categorize the projects by the year they are scheduled to be delivered in
` Year(\$DueDate) `
Tips
1. Review other date and duration useful functions, such as DateDiff(), DateAdd(), and DateSubtract()

Years Function

` Years (number as Numeric) as Duration `
Category: Date
Description: Returns duration value in years
Parameters:
number as Numeric Numeric value or reference to a numeric field
Returned value as Duration Returns a duration value in years based on the "number" parameter entered
Example #1 Build a workflow rule that automatically sets the due date of a Request (of type Idea) to be 1 business year from the creation date. Set the evaluation criteria to be
` \$RequestType = "Idea" `
Example #2 Use the "Update Field" action for the 'Due Date' field and set its formula to be
` \$CreatedOn+ Years(1) `
Tips
1. If you would like that field to be updated for 1 calendar year, use the formula \$CreatedOn+ Days(365)
Related Functions Days()

ToDate Function

` ToDate (dateTime as DateTime) as DateTime `
Category: Convert
Description: Converts a DateTime value to Date type value by removing time value in the argument provided
Parameters:
dateTime as DateTime Represents a date time value or reference to a field that will return a date time
Returned value as DateTime Returns a date value equal to the date time value specified in the "dateTime" parameter
Example #1 Convert a Date and Time value into a date for a Custom Field within work items
` ToDate(DateTimeValue(“2011-05-01 19:30:00”)) `
Tips
Related Functions DateTimeValue() DateValue() DateTime()

ToNumber Function

` ToNumber (value as String) as Numeric `
Category: Convert
Description: Converts a text value to a number. Returns a run-time exception if cannot convert
Parameters:
value as String Represents a text or string number value or reference to a field that returns a string number value
Returned value as Numeric Returns a numeric value equal to the text or string number value entered in the "value" parameter. Returns a run-time exception if cannot convert (i.e. text/string entered in "value" contains characters that are not numeric)
Example #1 Convert a text field with string values to number values
` ToNumber("567") `
Tips
1. To prevent run time errors, use the Catch() exception
2. To convert picklist values of number types to numbers for formula calculations use the ToNumber(ToString(\$picklistfield))
Related Functions Catch()

ToString Function

` ToString (value as Object, format as String) as String `
Category: Convert
Description: Converts any value to a string
Parameters:
value as Object Represents a numeric or pick list value, or reference to a field that will return a numeric or pick list value
format as String
Returned value as String Returns a text or string value equal to the value entered in the "value" parameter
Example #1 Create a formula field on work items that returns a string representing the Due Date in the format of YYYY-MM-DD HH:MM:SS
` ToString(\$DueDate) `
Example #2 Create a formula field that is set on work items that will return the system ID of the 'Parent Project' (e.g."P-1")
` ToString(\$ParentProject) `
Tips
1. To prevent run time errors, use the Catch() exception
2. To convert picklist values to strings for formula calculations use ToString(\$PicklistField)
Related Functions Catch()

Catch Function

` Catch (expression as Object, errorValue as Object) as Object `
Category: Common
Description: Returns a value of the expression or errorValue in case that expression throws a runtime error
Parameters:
expression as Object Field or reference to a field of an object
errorValue as Object Resulting value if the expression throws a runtime error
Returned value as Object Returns the desired error value if the expression throws a runtime error, else returns "null"
Example #1 Create a custom numeric field that automatically converts the last 5 characters of a work item name into a number value. In the case where the custom number field was manually inputted incorrectly (i.e. does not contain numeric characters), set the errorValue to "-999".
` Catch(ToNumber(Right(\$Name,5),-999) `
Tips
1. Be sure to check off the "Field value is based on a formula" to create Custom Fields set by formulas
2. Using this function can help you in preventing run time errors in business rules. This is especially important since run time error will cause any add, update or delete operations that triggers these business rule to fail

GetCustomActionUrl Function

` GetCustomActionUrl (customAction as ObjectIdentifier, targetObject as String, param2 as String, param3 as String, param4 as String, param5 as String, param6 as String, param7 as String, param8 as String, param9 as String, ... as String) as String `
Category: Common
Description: Generates a URL string that represents a custom action with parameters
Parameters:
customAction as ObjectIdentifier(Custom Operation)
targetObject as String
param2 as String
param3 as String
param4 as String
param5 as String
param6 as String
param7 as String
param8 as String
param9 as String
... as String
Returned value as String
Example 1

GetDocumentFileName Function

` GetDocumentFileName (document as ObjectIdentifier) as String `
Category: Common
Description: Returns the name of the specified file
Parameters:
document as ObjectIdentifier(Document)
Returned value as String

GetHiddenRuntimeParameter Function

` GetHiddenRuntimeParameter (paramValue0 as Object, paramValue1 as Object, paramValue2 as Object, paramValue3 as Object, paramValue4 as Object, ... as Object) as String `
Category: Common
Description: Returns a string representing a runtime parameter of any type that will be hidden by a custom action.
Parameters:
paramValue0 as Object
paramValue1 as Object
paramValue2 as Object
paramValue3 as Object
paramValue4 as Object
... as Object
Returned value as String

` GetHyperLink (referenceField/className as String, objectId as String) as String `
Category: Common
Description: Creates and returns a hyperlink to an object.
Parameters:
referenceField/className as String An optional field that represents a reference to a class name (i.e.currentobject() or \$Document)
objectId as String An optional field that represents a specific object's system ID (i.e. P-101)
Returned value as String Returns a URL to the specified object
Example #1 Create a workflow rule that automatically sends an email to the work item manager when the work items actual effort and remaining effort exceed the set work. The email will contain a hyperlink to that work item. Within the email body, insert a message that looks as follows: "items task1 updated efforts (14h) from the team exceed the original estimated effort (5h)", where task1 is a hyperlink to that task.
` Item {GetHyperLink(CurrentObject())}updated efforts( {\$ActualEffort+\$RemainingEffort} ) from the team exceed the original estimated efforts( {\$work} ) `
Example #2 Create a workflow rule on the "Attachment" link class that will automatically send an email to the work item's manager whenever a document is added to a work item. Within the email body supply the hyperlink to both the document as well as the work item itself.
` Document: {GetHyperLink(\$Document)} was added to {GetHyperLink(\$Entity)} `
Tips
1. If you would like to place hard coded values into the hyperlink (e.g. certain users, projects, etc) you can use the GetHyperlink() function as follows: GetHyperlink('Project','P-001')
2. To create automatic emails within workflow rules, select the “Send Email” option from within the Select Action List drop down
Note This function is only available for business rules

GetModuleViewUrl Function

` GetModuleViewUrl (entityType as String, viewId as ObjectIdentifier, expirationDate as DateTime, register as Boolean) as String `
Category: Common
Description: Returns a module view url for for a specific item type
Parameters:
entityType as String
viewId as ObjectIdentifier(SubsystemView)
expirationDate as DateTime
register as Boolean
Returned value as String

GetObjectViewUrl Function

` GetObjectViewUrl (objectId as ObjectIdentifier, viewId as ObjectIdentifier, expirationDate as DateTime, register as Boolean) as String `
Category: Common
Description: Returns an object details view url for for a specific item
Parameters:
objectId as ObjectIdentifier(Work Item, ResourceEntity, Organization, Document, Comment, Timesheet, Customer, ContactPerson, Expense Sheet, Expense Entry, Case, DiscussionMessage, Topic)
viewId as ObjectIdentifier(ObjectDetailsView)
expirationDate as DateTime
register as Boolean
Returned value as String

GetPreviousValue Function

` GetPreviousValue (field as Object) as Object `
Category: Common
Description: Returns the pervious value of a given field prior to last change
Parameters:
field as Object Represents the field of a specific object for which you would like to see the previous value
Returned value as Object Returns the previous value of the field given in the "field" parameter prior to the last change
Example #1 Set a workflow rule that sends an email automatically to the work item's manager whenever the work item's percent completion increases by more than 25% (for example: an update from 20% to 50% would trigger the workflow, while an increase from 20% to 25% will not).
` \$PercentCompleted - GetPreviousValue(\$PercentCompleted) >= 25 `
Example #2 Define a validation rule for a bug that will prevent clearing the 'To be resolved' field (if it was previously defined) and the bug is marked as 'reported by customer'. The Evaluation Criteria of the validation rule to:
` isNull(\$PlannedFor) && Not(IsNull(GetPreviousValue(\$PlannedFor))) && \$ReportedbyCustomer `
Tips
1. The GetPreviousValue() function cannot be used within formulas for Rescheduling actions, since the rescheduling is a queued job that happens after the object is already updated
Note This function is only available for business rules

GetReportUrl Function

` GetReportUrl (report as ObjectIdentifier, param1 as String, param2 as String, param3 as String, param4 as String, param5 as String, param6 as String, param7 as String, param8 as String, param9 as String, ... as String) as String `
Category: Common
Description: Generates a URL string that represents a report with parameters
Parameters:
report as ObjectIdentifier(SystemQuery, UserDefinedQuery)
param1 as String
param2 as String
param3 as String
param4 as String
param5 as String
param6 as String
param7 as String
param8 as String
param9 as String
... as String
Returned value as String

GetRuntimeParameter Function

` GetRuntimeParameter (paramValue0 as Object, paramValue1 as Object, paramValue2 as Object, paramValue3 as Object, paramValue4 as Object, ... as Object) as String `
Category: Common
Description: Returns a string representing one runtime parameter of any field type and operator type.
Parameters:
paramValue0 as Object
paramValue1 as Object
paramValue2 as Object
paramValue3 as Object
paramValue4 as Object
... as Object
Returned value as String

GetSessionId Function

` GetSessionId () as String `
Category: Common
Description: Returns the current user's session ID to be used in outbound calls, API calls, and integrations with external systems
Returned value as String Returns a current user's session ID
Example #1 Get a Clarizen URL with an embedded session ID to be used as a token in order to skip the login
` https://app.clarizen.com/Clarizen/Pages/MainPage/Project.aspx? po=6.25263536.21&id={GetSessionId()} `

GetSystemSetting Function

` GetSystemSetting (settingName as String) as Object `
Category: Common
Description: Returns the value for the entered system setting, where the value type is determined by the selected system setting.
Parameters:
settingName as String Represents a reference to a system setting, to access the system settings names click on the "pick lists" tab in the formula function helper
Returned value as Object Returns the value of the system setting specified in the "settingName" parameter
Example #1 Set a workflow rule that will send an email to the Organization support user when a new object is added to the system. The evaluation criteria of this rule should include the Get System Settings function to verify that the system is enabled to send alerts via email.
` GetSystemSettings('Send Alerts via Email') `

` GetWidgetHyperLink (objectId as ObjectIdentifier, format as String, expirationDate as DateTime, urlOnly as Boolean, register as Boolean) as String `
Category: Common
Description: Returns a widget URL for a single project or report specified within the "objectID" variable, where the "format" variable determines whether a 'roadmap' or 'gantt' type widget URL is returned, the expiration date for the widget (can be set to NULL if not applicable), and the "urlOnly" parameter is set to TRUE for only the URL or FALSE for the full HTML hyperlink.
Parameters:
objectId as ObjectIdentifier(Project, UserDefinedQuery, SystemQuery) Represents a reference to a project or a field that returns a project object
format as String Represents the type of Widget you would like to receive, where the value can either be "gantt" or "roadmap"
expirationDate as DateTime Represents a date or a field that returns a date value for the expiration of the widget
urlOnly as Boolean Represents the return data, enter "TRUE" if you would like to see only the widget URL or "FALSE" to receive the full widget HTML hyperlink
register as Boolean An optional parameter that represents whether or not you would like the widget to be visible in the widget repository, enter "TRUE" to access from widget repository or "FALSE" if not accessible. If null this parameter will default to "TRUE"
Returned value as String Returns the widget URL or HTML hyperlink for the desired object specified in the "objectID" parameter
Example #1 Create a workflow rule that will automatically send an email to project stakeholders with the a link to the project roadmap each time a new project is set to Active.
In the email body of the Send Email action enter the following:
` GetWidgetHyperLink(currentobject(),"roadmap", NULL ,TRUE) `

`GetWidgetHyperLinkWithPassword(objectId as String,format as String,expirationDate as DateTime,urlOnly as Boolean,password as Boolean)`
Category: Common
Description: Returns a widget URL for a single project or report specified within the "objectID" variable, where the "format" variable determines whether a 'roadmap' or 'gantt' type widget URL is returned, the expiration date for the widget (can be set to NULL if not applicable), and the "urlOnly" parameter is set to TRUE for only the URL or FALSE for the full HTML hyperlink./td>
Parameters:
objectId as string Represents a reference to a user object or a field that returns a user object. Normally this will be a resource on a project.
format as string Gantt, Roadmap other
expirationDate as dateTime Represents a reference to a from date as DateTime(year,month,day,hour,minute,second)

e.g. DateTime(Year(Today()),1,1,08,00,00)

or field that returns a date value e.g. Start Date

urlOnly as Boolean Boolean (TRUE/ FALSE)
password as Boolean Boolean (TRUE/ FALSE)
Example #1 Create a Workflow Rule that sends an email to the Project Sponsor with a link to the Project Gantt.
` GetWidgetHyperLinkWithPassword(CurrentObject,'Gantt',\$DueDate,true,true) `
Tips
1. Create workflow rules to set standard field values via formulas
• N/A

` HyperLink (url as String, title as String) as String `
Category: Common
Description: Returns a hyperlink based on the specified url and the title
Parameters:
url as String
title as String
Returned value as String

IsCustomActionAvailable Function

` IsCustomActionAvailable (customAction as ObjectIdentifier, targetObject as ObjectIdentifier, user as ObjectIdentifier) as Boolean `
Category: Common
Description: Returns TRUE if the specified custom action is enabled and the specified user has permission to execute it
Parameters:
customAction as ObjectIdentifier(Custom Operation)
user as ObjectIdentifier(User)
Returned value as Boolean

` IsIPAddressInRange (ipAddress as String, ipRange as String) as Boolean `
Category: Common
Description: Returns TRUE if the ipAddress is within the ipRange. The ipAddress's format is 'x.x.x.x' and the ipRange's format is 'x.x.x.x-y.y.y.y,x.x.x.x-y.y.y.y'.
Parameters:
ipRange as String
Returned value as Boolean

` NumberOfAvailableLicenses (userType as ObjectIdentifier) as Numeric `
Category: Common
Parameters:
1. Email
2. Full
3. Limited
4. None
5. Social
6. TeamMember
7. Trial
Returned value as Numeric

` ParseHyperLink (hyperlink as String) as String `
Category: Common
Description: Parses a specified hyperlink and returns a url part of it.
Parameters:
Returned value as String

Currency Function

` Currency (number as Numeric, currency as String) as Currency `
Category: Currency
Description: Creates a currency with the amount specified in the number value, where currencyName (optional parameter) specifies a text currency value (such as 'usd'). If not entered, the organization's default base currency will be taken. CurrencyName is only relevant when multi-currency support is enabled
Parameters:
number as Numeric Positive or negative numeric value
currency as String An optional parameter that accepts the Currency type that you would like the "Number" parameter to be represented as. Accepts three letter currency code (such as "USD"). If not entered, the default organizational currency type will be used
Returned value as Currency Returns a currency value equal to the "number" parameter using the currency type specified in the "currency" parameter
Example #1 Create a workflow rule that automatically updates the 'Expense Approver' field to be the corporate CPA in cases where the expense exceeds a certain fixed amount (e.g. \$1600).
` \$Total > Currency (1600,'usd') `
Tips
1. To update a field within a workflow select the "Update Field" from the Set Action List
2. It is recommended to use this function while comparing base currencies

CurrencyExchange Function

` CurrencyExchange (currency as Currency, targetCurrency as ObjectIdentifier, date as DateTime, project as ObjectIdentifier) as Currency `
Category: Currency
Description: Converts a currency amount to a specified currency, where the date parameter indicates the exchange rate date, if no specific date then a "Null" value can be entered. The project parameter indicates either a specific project exchange rate or the exchange rate date set on the specified project, if no specific project then a "Null" value can be entered.
Parameters:
currency as Currency A currency amount, or reference to a field that returns a currency value
targetCurrency as ObjectIdentifier(CurrencyType) Represents the desired target currency type. Accepts three letter currency code (such as "USD")
1. AED
2. AFN
3. ALL
4. AMD
5. ANG
6. AOA
7. ARS
8. AUD
9. AWG
10. AZN
11. BAM
12. BBD
13. BDT
14. BGN
15. BHD
16. BIF
17. BMD
18. BND
19. BOB
20. BRL
21. BSD
22. BTN
23. BWP
24. BYR
25. BZD
27. CDF
28. CHF
29. CLF
30. CLP
31. CNY
32. COP
33. COU
34. CRC
35. CUP
36. CVE
37. CZK
38. DJF
39. DKK
40. DOP
41. DZD
42. EEK
43. EGP
44. ERN
45. ETB
46. EUR
47. FJD
48. FKP
49. GBP
50. GEL
51. GHS
52. GIP
53. GMD
54. GNF
55. GTQ
56. GYD
57. HKD
58. HNL
59. HRK
60. HTG
61. HUF
62. IDR
63. ILS
64. INR
65. IQD
66. IRR
67. ISK
68. JMD
69. JOD
70. JPY
71. KES
72. KGS
73. KHR
74. KMF
75. KPW
76. KRW
77. KWD
78. KYD
79. KZT
80. LAK
81. LBP
82. LKR
83. LRD
84. LSL
85. LTL
86. LVL
87. LYD
89. MDL
90. MGA
91. MKD
92. MMK
93. MNT
94. MOP
95. MRO
96. MUR
97. MVR
98. MWK
99. MXN
100. MYR
101. MZN
103. NGN
104. NIO
105. NOK
106. NPR
107. NZD
108. OMR
109. PAB
110. PEN
111. PGK
112. PHP
113. PKR
114. PLN
115. PYG
116. QAR
117. RON
118. RSD
119. RUB
120. RUR
121. RWF
122. SAR
123. SBD
124. SCR
125. SDG
126. SEK
127. SGD
128. SHP
129. SKK
130. SLL
131. SOS
132. SRD
133. STD
134. SYP
135. SZL
136. THB
137. TJS
138. TMM
139. TND
140. TOP
141. TRY
142. TTD
143. TWD
144. TZS
145. UAH
146. UGX
147. USD
148. UYU
149. UZS
150. VEF
151. VND
152. VUV
153. WST
154. XAF
155. XCD
156. XOF
157. XPF
158. YER
159. ZAR
160. ZMK
161. ZWD
date as DateTime Represents the value of the desired exchange rate date, value must be of type date or reference to a field of type date. If no specific exchange rate date then enter a value of "Null"
project as ObjectIdentifier(Project) Represents the value of the desired exchange rate or exchange rate date as set in a specific project, value must be a relation to a specific project object. If no specific project exchange rate then enter a value of "Null"
Returned value as Currency Returns the currency value equal to the amount inputted in the "Currency" parameter converted to the currency specified in the "Target Currency" parameter using the exchange rate defined in either the "Date" or "Project" parameters
Example #1 Create a workflow rule that will automatically convert the Actual Cost of a work item from 'USD' to 'EUR', based on the project exchange rate, and enter the 'EUR' value into a custom field for billing purposes.
In the Workflow rule select the run time to be every time a record is created or edited and set the evaluation criteria to: IsChanged(\$ActualCost)
Select the "Update Field" action and choose the custom field mentioned above. In the formula enter the following:
` CurrencyExchange(\$ActualCost,'EUR',null,\$Project) `
In the example above the organization's base currency is in USD
Tips
1. To use a specific date for the exchange rate in the above workflow rule, enter the date or reference to a field in the date parameter, and keep the project parameter as "null". CurrencyExchange(\$ActualCost,'EUR',\$Startdate, null)
2. If both the date and project parameters are entered then the workflow rule will result in a run-time error
3. If both the date and project parameters are entered then the exchange rate date will be the current date
4. This function has a parameter from type pick list that may have being customized by your organization. Please refer to the pick list values in the formula helper editor or view the values within the relevant field.
Note This function is only available when the organization is set to use multi-currency

GetAssignedProjectCapacityForFixedPeriod Function

`GetAssignedProjectCapacityForFixedPeriod(user as ObjectIdentifier, project as ObjectIdentifier, period as String)`
Category: Resource Utilization
Description: Returns a duration value indicating the Assigned Project Capacity for the specified User in the given time range for a specified project. This should be the same number that is displayed in the Resource Planning.
Parameters:
user as ObjectIdentifier(User) Represents a reference to a user object or a field that returns a user object. Normally this will be a resource on a project.
project as ObjectIdentifier(project) Represents a project object for which you would like to know the resource’s assigned capacity.
period as String An optional variable that represents the frame of time for which you would like to know the resource availability. Value can be either "d", "w", "m" corresponding to current day, week, month. If null then the parameter value defaults to "w"
Example #1

Create 3 Custom Fields on Resource Link:

Create a Workflow Rule on Resource Link every time the link is Edited

This will only check for Users that are "Active"
In the Evaluation Criteria enter the following:

In the Actions, select Update Field: GetAssignedProjectCapacityForFixedPeriod(Resource, WorkItem, period as String)

Tips
1. Use this value in conjunction with a user’s rate to calculate projected cost or revenue from billing for a particular project.

GetAssignedProjectCapacityForGivenPeriod Function

`GetAssignedProjectCapacityForGivenPeriod(user as ObjectIdentifier, project as ObjectIdentifier, dateTime1 as DateTime, dateTime2 as DateTime)`
Category: Resource Utilization
Description: Returns a duration value indicating the Assigned Project Capacity for the specified User in the given time range for a specified project. This should be the same number that is displayed in the Resource Planning.
Parameters:
user as ObjectIdentifier(User)

Represents a reference to a user object or a field that returns a user object. Normally this will be a resource on a project.

project as ObjectIdentifier(project)

Represents a project object for which you would like to know the resource’s assigned capacity.

dateTime1

Represents a reference to a from date as DateTime(year,month,day,hour,minute,second)

e.g. DateTime(Year(Today()),1,1,08,00,00)

or field that returns a date value

e.g. Start Date

dateTime2

Represents a reference to a to date as DateTime(year,month,day,hour,minute,second)

e.g. DateTime(Year(Today()),12,31,08,00,00)

or field that returns a date value

e.g. Due Date

Example #1

Create a Custom Action “Quick Quote” on a new Project –

In the Evaluation Criteria enter the following:

IsResourceAvailableForFixedPeriod(currentuser(),"w",100, "Active", "Draft", "On Hold")

Actions

Create a Project with no tasks

Set its Duration to 6 months

Add Resources to the project and set their monthly % Assigned Project Capacity

Run the Custom Action

Tips

Use this value in conjunction with a user’s rate to calculate projected cost or revenue from billing for a particular project.

GetAssignedProjectWorkForFixedPeriod Function

`GetAssignedProjectCapacityForGivenPeriod(user as ObjectIdentifier, project as ObjectIdentifier, dateTime1 as DateTime, dateTime2 as DateTime)`
Category:

Resource Utilization

Description:

Returns a duration value indicating the Assigned Project Work for the specified User in the current day, week or month time range for a specified project. This should correspond to the value that can be seen in the project’s Resource Planning panel and is a sum of all assigned work for a user on all tasks in the project for that time period.

Supported Context:

Parameters:
user as ObjectIdentifier(User)

Represents a reference to a user object or a field that returns a user object. Normally this will be a resource on a project.

project as ObjectIdentifier(project)

Represents a project object for which you would like to know the resource’s assigned capacity.

dateTime1

Represents a reference to a from date as DateTime(year,month,day,hour,minute,second)

e.g. DateTime(Year(Today()),1,1,08,00,00)

or field that returns a date value

e.g. Start Date

dateTime2

Represents a reference to a to date as DateTime(year,month,day,hour,minute,second)

e.g. DateTime(Year(Today()),12,31,08,00,00)

or field that returns a date value

e.g. Due Date

Example #1

Create a Scheduled Workflow Rule on the User that will run repeatedly every Monday morning, and send an email to a user's direct manager if the user is overloaded in the current week.
This will only check for resource availability on tasks that are "Active", "Draft", or "On Hold"
In the Evaluation Criteria enter the following:

IsResourceAvailableForFixedPeriod(currentuser(),"w",100, "Active", "Draft", "On Hold")

Tips

Use this value in conjunction with a user’s rate to calculate projected cost or revenue from billing for a particular project.

GetAssignedProjectWorkForGivenPeriod Function

`GetAssignedProjectCapacityForGivenPeriod(user as ObjectIdentifier, project as ObjectIdentifier, dateTime1 as DateTime, dateTime2 as DateTime) `
Category:  Resource Utilization

Description:

Returns a duration value indicating the Assigned Project Capacity for the specified User in the given time range for a specified project. This should correspond to the value that can be seen in the project’s Resource Planning panel.

Supported Context:

Parameters:

user as ObjectIdentifier(User)

Represents a reference to a user object or a field that returns a user object. Normally this will be a resource on a project.

project as ObjectIdentifier(project)

Represents a project object for which you would like to know the resource’s assigned capacity.

dateTime1

Represents a reference to a from date as DateTime(year,month,day,hour,minute,second)

e.g. DateTime(Year(Today()),1,1,08,00,00)

or field that returns a date value

e.g. Start Date

dateTime2

Represents a reference to a to date as DateTime(year,month,day,hour,minute,second)

e.g. DateTime(Year(Today()),12,31,08,00,00)

or field that returns a date value

e.g. Due Date

Example #1

Create a Scheduled Workflow Rule on the User that will run repeatedly every Monday morning, and send an email to a user's direct manager if the user is overloaded in the current week.
This will only check for resource availability on tasks that are "Active", "Draft", or "On Hold"
In the Evaluation Criteria enter the following:

IsResourceAvailableForFixedPeriod(currentuser(),"w",100, "Active", "Draft", "On Hold")

Tips

Use this value in conjunction with a user’s rate to calculate projected cost or revenue from billing for a particular project.

` GetTaskAssignmentForFixedPeriod (user as ObjectIdentifier, task as ObjectIdentifier, period as String, loadType as String, state0 as String…) `
Category:

Resource Utilization

Description:

Returns a duration value indicating the Assigned Task work for the specified User in the current day, week or month time range. This should correspond to the value that can be seen in the project’s Resource Planning panel, or Resource Load screen.

Supported Context:

Parameters:

user as ObjectIdentifier(User)

Represents a reference to a user object or a field that returns a user object. Normally this will be the resource on a task.

Represents a task object for which you would like to know the resource’s assigned work.

period ‘d’ for Current Day, ‘w’ for Current Week, ‘m’ for Current Month

‘Planned Work’ or ‘Remaining Effort’

Example #1

Create a Scheduled Workflow Rule on the User that will run repeatedly every Monday morning, and send an email to a user's direct manager if the user is overloaded in the current week.
This will only check for resource availability on tasks that are "Active", "Draft", or "On Hold"
In the Evaluation Criteria enter the following:

IsResourceAvailableForFixedPeriod(currentuser(),"w",100, "Active", "Draft", "On Hold")

Tips

Use this value in conjunction with a user’s rate to calculate projected cost or revenue from billing for a particular task.

` GetTaskAssignmentForGivenPeriod (user as ObjectIdentifier, task as ObjectIdentifier, dateTime1 as DateTime, dateTime2 as DateTime, loadType as String, state0 as String…) `

Category:

Resource Utilization

Description:

Returns a duration value indicating the Assigned Task work for the specified User in the specified time range. This should correspond to the value that can be seen in the project’s Resource Planning panel, or Resource Load screen.

Supported Context:

Parameters:

user as ObjectIdentifier(User)

Represents a reference to a user object or a field that returns a user object. Normally this will be the resource on a task.

Represents a task object for which you would like to know the resource’s assigned work.

dateTime1

Represents a reference to a from date as DateTime(year,month,day,hour,minute,second)

e.g. DateTime(Year(Today()),1,1,08,00,00)

or field that returns a date value

e.g. Start Date

dateTime2

Represents a reference to a to date as DateTime(year,month,day,hour,minute,second)

e.g. DateTime(Year(Today()),12,31,08,00,00)

or field that returns a date value

e.g. Due Date

Example #1

Create a Scheduled Workflow Rule on the User that will run repeatedly every Monday morning, and send an email to a user's direct manager if the user is overloaded in the current week.
This will only check for resource availability on tasks that are "Active", "Draft", or "On Hold"
In the Evaluation Criteria enter the following:

IsResourceAvailableForFixedPeriod(currentuser(),"w",100, "Active", "Draft", "On Hold")

Tips

Use this value in conjunction with a user’s rate to calculate projected cost or revenue from billing for a particular project.

GetTotalFinancialSummaryForFixedPeriod Function

` GetTotalFinancialSummaryForFixedPeriod (work item as ObjectIdentifier, field as String,period as String)`
Category: Financial Planning
Description:

Returns a currency value for a specific currency field (Budget Cost, Budget Revenue etc.. for the specified work item in the given time range for a specified project. This should be the same amount that is displayed in Financial Planning summary view

Supported Context:

Parameters:
workitem as ObjectIdentifier(workitem) Represents a reference to a work item. Normally this will be a project.
field as string The API name of the field in 'quotes' that you want to summarize for the time period
period

‘d’ for Current Day, ‘w’ for Current Week, ‘m’ for Current Month, 'q' for Current Fiscal Quarter , 'y' for Current Fiscal Year

Example #1

Create a Scheduled Workflow Rule on a Project that will run repeatedly every Monday morning, and send an email to the Project manager letting them know how much revenue the project is expected to make this week:

`GetTotalFinancialSummaryForFixedPeriod(CurrentObject(),'PlannedRevenue','w')`
Tips

The API name of the field you want to summarize should be in Quotes and without "\$".

GetTotalFinancialSummaryForGivenPeriod Function

`GetTotalFinancialSummaryForGivenPeriod(work item as ObjectIdentifier, field as String,from as DateTime,to as DateTime)`
Category:

Financial Planning

Description:

Returns a currency value for a specific currency field (Budget Cost, Budget Revenue etc.. for the specified work item in the given time range for a specified project.

Parameters:
workitem as ObjectIdentifier(workitem)

Represents a reference to a work item. Normally this will be a project.

field as string

The API name of the field in 'quotes' that you want to summarize for the time period

dateTime1

Represents a reference to a from date as DateTime(year,month,day,hour,minute,second)

e.g. DateTime(Year(Today()),1,1,08,00,00)

or field that returns a date value

e.g. Start Date

dateTime2

Represents a reference to a to date as DateTime(year,month,day,hour,minute,second)

e.g. DateTime(Year(Today()),12,31,08,00,00)

or field that returns a date value

e.g. Due Date

Example #1

1. Create a custom Currency field "Bonus" on Project.

2. Create a Workflow Rule on "Non-Labor Time Phase data" which runs

Every time a record is created or edited

and

Runs only when the following conditions are met:

`isNew() || isChanged(\$PlannedBudget)`

3. Set Action: Update Field  \$RelatedLink.WorkItem.Project.C_TotalBonus

`ProjectGetTotalFinancialSummaryForGivenPeriod (\$RelatedLink.WorkItem.Project, 'PlannedBudget', \$RelatedLink.WorkItem.Project.StartDate, \$RelatedLink.WorkItem.Project.DueDate) * 0.25`
Tips

The API name of the field you want to summarize should be in Quotes and without "\$".

GetFinancialDataForFixedPeriod Function

`GetFinancialDataForFixedPeriod(work item as ObjectIdentifier, nlr as ObjectIdentifier, field as String, period as string)`
Category: Financial Planning
Description: Returns a Currency value indicating the aggregated financial value of a specific financial resource time-phase field, for the specified Non-Labor Resource in the given time range for a specified project. This should be the same number that is displayed in the Financial Planning screen.
Parameters: work item as ObjectIdentifier(Object)
nlr as ObjectIdentifier(nlr)

Represents a project object for which you would like to know the resource’s assigned capacity.

field as string The name of the field you want to sum financial data from
period as string 'd’ for Current Day, ‘w’ for Current Week, ‘m’ for Current Month, 'q' for Current Fiscal Quarter , 'y' for Current Fiscal Year
Example #1

GetFinancialDataForGivenPeriod Function

`GetFinancialDataForGivenPeriod(work item as ObjectIdentifier, nlr as ObjectIdentifier, field as String, from as DateTime, to as DateTime)`
Category: Financial Planning
Description: Returns a Currency value indicating the aggregated financial value of a specific financial resource time-phase field, for the specified Non-Labor Resource in the given time range for a specified project. This should be the same number that is displayed in the Financial Planning screen.
Parameters:
workitem as ObjectIdentifier(workitem) Represents a reference to a work item object or a field that returns a work item object.
nlr as ObjectIdentifier(nlr) Represents a Non-Labor Resource for which you would like to know the resource’s assigned capacity.
dateTime1

Represents a reference to a from date as DateTime(year,month,day,hour,minute,second)

e.g. DateTime(Year(Today()),1,1,08,00,00)

or field that returns a date value

e.g. Start Date

field as string The name of the field you want to sum financial data from
dateTime2

Represents a reference to a to date as DateTime(year,month,day,hour,minute,second)

e.g. DateTime(Year(Today()),12,31,08,00,00)

or field that returns a date value

e.g. Due Date

Example #1
Tips

Excel format

0 out of 1 found this helpful
Have more questions? Submit a request

• Ariel Rak

GetLink function documentation (and others) need to be updated with the new multi manager functionality.

• Boris Krutiy

Ariel,

I will be sure to let our documentation team know. Thank you

• Ariel Rak

I am pretty sure there used to be somewhere that described all the toString format options, but I can't find that. Please add explanations to the toString description.

• Andrew Miller

Great that your starting to expose some of the Resource Planning functionality in functions. This will be very useful for generating forecast reports. Hope it works!

• Andrew Miller

The description for the 4 new financial functions at the bottom aren't correct. They seem to be referring to the Task Assignment functions

• Iris Anavi

The new Financial Functions at the bottom of the page are incorrect and are duplicating the Task assignment functions, can this be corrected ?

• David Goulden

Yes. We will be correcting this. Thanks for pointing this out.

• Patrick Harrell

Needed to Yellow-Highlight projects that hadn't been updated in >= 5 days.
Just used this help page for the DateDiff() function.
The text was clear. Example was helpful. And I accomplished what I needed for formatting.

Formula is: DateDiff(Today(),\$LastUpdatedOn,"d")>=5
Changed Formatting to Background-Yellow if result was TRUE.

Thank you Clarizen!