Table of Contents
Abs Function
Abs (number as Numeric) as Numeric
| Category: | Math |
| Description: | Returns the absolute value of a number |
| Supported Context: | FormulaField And BusinessRules |
| 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 |
|
| Additional Links |
Ceil Function
Ceil (number as Numeric) as Numeric
| Category: | Math |
| Description: | Rounds a number up to the nearest whole integer |
| Supported Context: | FormulaField And BusinessRules |
| 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 | Create workflow rules to set standard field values via formulas |
| Additional Links |
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 |
| Supported Context: | FormulaField And BusinessRules |
| 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 |
| Supported Context: | FormulaField And BusinessRules |
| 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 | Functions can be used within other functions to create more complex formulas |
| Additional Links |
Floor Function
Floor (number as Numeric) as Numeric
| Category: | Math |
| Description: | Rounds a number down towards zero to the nearest integer |
| Supported Context: | FormulaField And BusinessRules |
| 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 Type that 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. Example: $C_value = 5.4, Floor($C_value) > returns 5 Example: $C_value = 99.9999, Floor($C_value) > returns 999 |
| Tips | Create workflow rules to set standard field values via formulas |
| Additional Links |
Max Function
Max (arg1 as Object, arg2 as Object, ... as Object) as Object
| Category: | Math |
| Description: | Returns the greatest value of the arguments |
| Supported Context: | FormulaField And BusinessRules |
| 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 | 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 |
| Supported Context: | FormulaField And BusinessRules |
| 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 | 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 |
| Supported Context: | FormulaField And BusinessRules |
| 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 |
| Supported Context: | FormulaField And BusinessRules |
| 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 |
| Supported Context: | FormulaField And BusinessRules |
| 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 | Other similar functions include Ceil(), Floor(), Truncate() |
| Related Functions |
Sqrt Function
Sqrt (number as Numeric) as Numeric
| Category: | Math |
| Description: | Returns the positive square root of a number |
| Supported Context: | FormulaField And BusinessRules |
| 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 Example: $C_positive_value = 9 Sqrt($C_positive_value) > returns 3 Example: $C_negative_value = -9 Sqrt($C_negative_value) > returns 3 |
| Tips | Similar functions include Exp() and Mod() |
| Related Functions |
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 |
| Supported Context: | FormulaField And BusinessRules |
| 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 | Other similar functions include Ceil(), Floor(), Round() |
| Related Functions |
IsPickVal Function
IsPickVal (picklistField as ObjectIdentifier, valueLiteral as String, exact as Boolean) as Boolean
| Category: | Pickup |
| Description: | Checks whether the value of a pick list field is equal to the literal value. An optional "exact" parameter with options: TRUE/FALSE is for Multi-Select Picklists to evaluate if a set of values e.g. "a, b" are contained in the list of selected values e.g. "a, b, c, d" (exact parameter = False) or are the the only values (an exact match) "a, b", using exact parameter=true. |
| Supported Context: | FormulaField And BusinessRules |
| 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 |
| exact as Boolean | Optional: Defines if a set of values are contained in the list of selected values. Example: If "a, b" in a list that includes "a, b, c, d", the exact parameter = False, but if the list includes only "a, b", its an exact match, parameter=true. |
| 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 |
| Supported Context: | FormulaField And BusinessRules |
| 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 | The CurrentObject() function can only be used on business rules |
CurrentUser Function
CurrentUser () as Object
| Category: | ObjectReference |
| Description: | Returns the current user object |
| Supported Context: | BusinessRules |
| 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 |
|
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. |
| Supported Context: | BusinessRules |
| 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. |
| Supported Context: | BusinessRules |
| 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 |
| Supported Context: | FormulaField And BusinessRules |
| Parameters: | |
| entityType as String | |
| objectId as ObjectIdentifier | |
| Returned value as String | |
| Example #1 | {GetImage('CustomIcon','Roadmap') where CustomIcon is an existing organizational picklist level field and Roadmap is one if it's picklist options Useful when building custom pages such as Portal Home. This function can be used to pull the background image where the image is stored as the icon of a picklist value. |
GetLink Function
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 | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Supported Context: | BusinessRules | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| 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') | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Additional Information |
|
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. |
| Supported Context: | BusinessRules |
| 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 |
| Example #1 | GetObjectByID('Project','P-7302') Common entities that can be used: Customer, Contact Person, File, Issue, Report, Resource, Request, User, Work Item |
GetDomainName Function
GetDomainName () as String
| Category: | Text |
| Description: | Return the Domain of the organization. if Organiztion Domain is not set return clarizen domain. |
| Supported Context: | BusinessRules |
| Returned value as String | |
| Example #1 | GetDomainName() As an example, can be used in an InterAct rule to construct a return email address for support. Although you can hard-code an email in this example, using this function covers the use case of the domain name changing. See a screenshot of a worklow action: https://www.screencast.com/t/R1iXFf8NGK |
HtmlEncode Function
HtmlEncode (text as String) as String
| Category: | Text |
| Description: | Encodes text for use in HTML |
| Supported Context: | FormulaField And BusinessRules |
| Parameters: | |
| text as String | |
| Returned value as String | |
HtmlToText Function
HtmlToText (htmlText as String) as String
| Category: | Text |
| Description: | Use this function when working with HTML formatted content, most commonly used with Interact rules as most email is sent in HTML format. Use this function to convert HTML formatted content to user-friendly plain text content, removing HTML tags and references, and leaving just the text content. |
| Example #1 | HtmlToText(emailobject.htmlbody) See an example here https://www.screencast.com/t/1EJFa35IZUp |
| Supported Context: | BusinessRules |
| 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 |
| Supported Context: | FormulaField And BusinessRules |
| 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 |
|
| Related Functions |
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 |
| Supported Context: | FormulaField And BusinessRules |
| 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 |
| Supported Context: | FormulaField And BusinessRules |
| 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 | Review other useful text fields such as Right() and Mid() |
| Related Functions |
Lower Function
Lower (text as String) as String
| Category: | Text |
| Description: | Changes all letters in the given value to lowercase letters |
| Supported Context: | FormulaField And BusinessRules |
| 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 | See also Upper() function |
| Related Functions |
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 |
| Supported Context: | FormulaField And BusinessRules |
| 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 |
|
| Related Functions |
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 |
| Supported Context: | FormulaField And BusinessRules |
| 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 |
| Supported Context: | FormulaField And BusinessRules |
| 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 | Review other useful related text functions, such as Len() and Left() |
| Related Functions |
Trim Function
Trim (text as String) as String
| Category: | Text |
| Description: | Removes all spaces from a text string except for single spaces between words |
| Supported Context: | FormulaField And BusinessRules |
| 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 |
|
| Related Functions |
Upper Function
Upper (text as String) as String
| Category: | Text |
| Description: | Changes all letters in a given value to uppercase letters |
| Supported Context: | FormulaField And BusinessRules |
| 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 | See also Lower() function |
| Related Functions |
UrlEncode Function
UrlEncode (text as String) as String
| Category: | Text |
| Description: | Encodes text for use in URLs |
| Supported Context: | FormulaField And BusinessRules |
| 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 |
|
| Related Functions | |
| Additional Links |
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. |
| Supported Context: | FormulaField And BusinessRules |
| 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") |
| Notes | The Contains function can only be used for string and text values. The function does not work for strings containing a + character followed by text, so 'text +' works but 'text + more text' does not. A workaround is to use ToString() around the text. Example: Contains(ToString($Name), "some text", FALSE). |
HasOverloadedResources Function
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. |
| Supported Context: | BusinessRules |
| 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"
|
| 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
|
| ... 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
|
| 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 | 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. |
| Supported Context: | BusinessRules |
| 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 |
| Supported Context: | BusinessRules |
| Parameters: | |
| role as String | Must be one of the following: "Reviewer", "manager" or "Resource". Note: “Reviewer” returns TRUE for managers and resources as well since managers and resources have the reviewer role. |
| 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()) To reveal only reviewers, filter out managers and resources, like this: HasRole(Reviewer) = TRUE and HasRole(Manager) = FALSE and HasRole(Resource) = FALSE) |
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 |
| Supported Context: | FormulaField And BusinessRules |
| 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 | To set standard field values based on formulas, create workflow rules |
| Additional Links |
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 |
| Supported Context: | FormulaField And BusinessRules |
| 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 | 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 |
| Supported Context: | BusinessRules |
| 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 |
|
| Note | to reference the Issue State picklist value "Submitted" use the string "Submitted2" |
| Related Functions | |
| Additional Links |
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 | |
| Example #1 | IsChangeType() Rarely used function to determine if a Work Item object or Case object has changed type via the 'Change Type' ribbon action. An example use would be to notify a Project Manager if any work item changed type, for example, from Milestone to Task. |
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 | 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 |
| Supported Context: | BusinessRules |
| 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 |
| Example #1 | IsFinancial($PlannedBudget) returns > TRUE IsFinancial($ProjectType) returns > FALSE |
IsFollower Function
IsFollower (entity as ObjectIdentifier, user as ObjectIdentifier) as Boolean
| Category: | Logical |
| Description: | Returns TRUE if a user follows the entity< |
| Supported Context: | BusinessRules |
| Parameters: | |
| entity as ObjectIdentifier (User, Topic, Work Item, Case, Expense Sheet, Expense Entry, Customer, Document) | |
| user as ObjectIdentifier(User) | |
| Returned value as Boolean | |
| Example #1 | IsFollower('EnhancementRequest','Dev placeholder') Common entities that can be used: Customer, Contact Person, File, Issue, Report, Resource, Request, User, Work Item |
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. |
| Supported Context: | BusinessRules |
| Returned value as Boolean | |
| Example #1 | IsFromTemplate() Useful to determine if a Work Item was created from a template or not |
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 |
| Supported Context: | BusinessRules |
| 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 |
| Supported Context: | BusinessRules |
| 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 |
|
| Note | this function is only available for Business Rules |
| Additional Links |
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 or profile. Note that if you have a groups and/or profiles with the same name, a value of TRUE will be returned if the selected user is a member of any one of the matching records. |
| Supported Context: | BusinessRules |
| 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()) |
IsMemberOfGroup Function
IsMemberOfGroup (group as ObjectIdentifier) as Boolean
| Category: | Logical |
| Description: | This function checks for membership of groups only. This includes User and Discussion Groups. |
| Supported Context: | BusinessRules |
| Parameters: | |
| group as ObjectIdentifier(UserGroup, DiscussionGroup) | Represents a specific group or reference to a field that returns a group object |
| Example #1 | IsMemberOfGroup('Human Resources','example.user@email.com') |
IsMemberOfProfile Function
IsMemberOfProfile (group as ObjectIdentifier) as Boolean
| Category: | Logical |
| Description: | This function specifically checks for membership of profiles only |
| Supported Context: | BusinessRules |
| Parameters: | |
| Example #1 | IsMemberOfProfile('Default Profile','example.user@email.com') |
IsNew Function
IsNew () as Boolean
| Category: | Logical |
| Description: | This function is typically used in workflow rules, custom actions, and validation rules in the "Set Evaluation Criteria" section to determine if an object was just created or existed before the business rule ran. |
| Example #1 | For a validation rule to allow only the Assigned To user to edit existing cases, the validation rule would look as follows: https://www.screencast.com/t/g0xMNvUez Example 1: IsNew() Example 2: IsNew($SYSID) |
| Supported Context: | BusinessRules |
| 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 |
| Supported Context: | FormulaField And BusinessRules |
| 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 |
|
| Additional Links |
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 |
| Supported Context: | BusinessRules |
| 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 |
| Supported Context: | BusinessRules |
| 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') |
IsEmptyOrZero
IsEmptyOrZero (value as Field) as Boolean
| Category: | Logical |
| Description: | Returns TRUE if a given field name or value is NULL or equals zero |
| Supported Context: | Formula Column or Highlight in Reports Field |
| Parameters: | |
| value as Field | Represents a specific value of a field |
| Returned value as Boolean | Returns a boolean value, where "TRUE" indicates that the field specified in the "value" parameter is null or equals zero |
| Example #1 | Check if the reported actuals are empty or zero (not reported): IsEmptyOrZero($CapacityOfUserMonthly.LoadForMonthly.LinkObject.ActualApproved)
|
Not Function
Not (logical as Boolean) as Boolean
| Category: | Logical |
| Description: | Changes FALSE to TRUE or TRUE to FALSE for the given value |
| Supported Context: | FormulaField And BusinessRules |
| 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 | 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 |
| Supported Context: | FormulaField And BusinessRules |
| 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 |
|
| Related Functions | |
| Additional Links |
DateAdd Function
DateAdd (dateTime as DateTime, duration as Duration) as DateTime
| Category: | Date |
| Description: | Adds a duration value to a date and returns date |
| Supported Context: | FormulaField And BusinessRules |
| 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 |
|
| Related Functions |
DateAddCalendarFixedPeriod Function
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. |
| Supported Context: | FormulaField And BusinessRules |
| 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) |
| Supported Context: | FormulaField And BusinessRules |
| 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 | Use the DateSubtract() function to return a duration field type that will take into consideration your Clarizen calendar settings |
| Related Functions | |
| Additional Links |
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. |
| Supported Context: | FormulaField And BusinessRules |
| 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 |
|
| Note | Duration = DateTime1 – DateTime2 e.g. DateSubtract(‘19/2/12’, ‘15/2/12’, TRUE) = 4 d |
| Related Functions | |
| Additional Links |
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 |
| Supported Context: | FormulaField And BusinessRules |
| 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 |
|
| Related Functions |
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 |
| Supported Context: | FormulaField And BusinessRules |
| 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 |
| Supported Context: | FormulaField And BusinessRules |
| 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 |
| Supported Context: | FormulaField And BusinessRules |
| 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 |
|
| Related Functions |
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. |
| Supported Context: | FormulaField And BusinessRules |
| 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 |
|
| Related Functions |
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. |
| Supported Context: | BusinessRules |
| 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 |
| loadType as String | loadType indicates whether to analyze the load by either 'PlannedWork' or 'RemainingEffort' |
| 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'
|
| 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
|
| ... 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
|
| 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 | GetAssignedWorkingTime('joe.user',today(),today()+days(14),'PlannedWork') |
| Tips | 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 |
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. |
| Supported Context: | BusinessRules |
| 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. |
| Supported Context: | BusinessRules |
| 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. |
| Supported Context: | BusinessRules |
| 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. |
| Supported Context: | BusinessRules |
| 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"
|
| 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
|
| ... 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
|
| 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 show 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 taht will run every time a record is created or edited.
In the Set Actions section, select the 'Update Field' action, adn 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 the timesheets that have been approved. |
| Tips | 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. |
| Supported Context: | BusinessRules |
| 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"
|
| 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
|
| ... 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
|
| 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 | GetSumOfTimesheetReportHoursForGivenPeriod(currentuser(),$WorkItem.StartDate,$WorkItem.DueDate) |
| Tips | 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 |
| Supported Context: | FormulaField And BusinessRules |
| 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. |
| Supported Context: | BusinessRules |
| 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. |
| Supported Context: | FormulaField And BusinessRules |
| 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. |
| Supported Context: | FormulaField And BusinessRules |
| 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 |
|
| Related Functions |
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. |
| Supported Context: | BusinessRules |
| 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"
|
| 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
|
| ... 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
|
| 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 | 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. |
| Supported Context: | BusinessRules |
| 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"
|
| 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
|
| ... 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
|
| 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 | IsResourceAvailableForGivenPeriod($Resource,$WorkItem.StartDate,$WorkItem.DueDate,100) |
| Tips | 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 |
| Supported Context: | FormulaField And BusinessRules |
| 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 |
|
| Related Functions |
Month Function
Month (dateTime as DateTime) as Numeric
| Category: | Date |
| Description: | Returns the number of any given month, 1 (January) and 12 (December) |
| Supported Context: | FormulaField And BusinessRules |
| 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 |
|
| Related Functions | |
| Additional Links |
Months Function
Months (number as Numeric) as Duration
| Category: | Date |
| Description: | Returns duration value in months |
| Supported Context: | FormulaField And BusinessRules |
| 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 |
|
| Related Functions |
Now Function
Now () as DateTime
| Category: | Date |
| Description: | Returns the current date and time in the UTC timezone. |
| Supported Context: | BusinessRules |
| Returned value as DateTime | |
| Example #1 | This function is very useful as parameters in other functions. In addition, it is used commonly when comparing the current date and time with another datetime field or capturing the current time when a specific workflow ran. For example, when creating a Custom Action for approval, use this function to log the time the approval was completed. |
OrganizationNow Function
OrganizationNow () as DateTime
| Category: | Date |
| Description: | Returns the current date and time in the organization's time zone |
| Supported Context: | BusinessRules |
| Returned value as DateTime | |
| Example #1 | This function is very useful as parameters in other functions. In addition, it is used commonly when comparing the current date and time with another datetime field or capturing the current time when a specific workflow ran. For example, when creating a Custom Action for approval, use this function to log the time the approval was completed. Use together with ToDate(OrganizationNow()) to get the date/time in the organization's time zone (DateTime type) and convert it into the date type. Note: Use ToDate() and not Today() for such purposes. |
Seconds Function
Seconds (number as Numeric) as Duration
| Category: | Date |
| Description: | Returns duration value in seconds |
| Supported Context: | FormulaField And BusinessRules |
| 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 |
|
| Related Functions | |
| Additional Links |
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) |
| Supported Context: | BusinessRules |
| Returned value as Date | |
| Example #1 | This function is very useful as parameters in other functions. In addition, it is used commonly when comparing the current date and time with another datetime field or capturing the current time when a specific workflow ran. For example, when creating a Custom Action for approval,use this function to log the time the approval was completed. In addition, this function can be used in report formula fields. |
ToOrganizationalDateTime Function
ToOrganizationalDateTime (dateTime as DateTime) as DateTime
| Category: | Date |
| Description: | Converts a DateTime argument to the timezone of the Organization |
| Supported Context: | FormulaField And BusinessRules |
| 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 Now() function Within a workflow rule, when trying to return a date time using the function Now(), use the ToOrganizationalDateTime function to ensure that it returns the current date time as per the organizations timezone. ToOrganizationalDateTime(Now()) |
| Related Functions |
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 |
| Supported Context: | FormulaField And BusinessRules |
| 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')
|
| 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')
|
| 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 | 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 |
| Supported Context: | FormulaField And BusinessRules |
| 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. |
| Supported Context: | FormulaField And BusinessRules |
| 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 #2 | DateAdd |
| Example #3 | ToDate |
| Tips |
|
| Additional Links |
Year Function
Year (dateTime as DateTime) as Numeric
| Category: | Date |
| Description: | Returns the number of any given year |
| Supported Context: | FormulaField And BusinessRules |
| 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 | Review other date and duration useful functions, such as DateDiff(), DateAdd(), and DateSubtract() |
| Related Functions | |
| Additional Links |
Years Function
Years (number as Numeric) as Duration
| Category: | Date |
| Description: | Returns duration value in years |
| Supported Context: | FormulaField And BusinessRules |
| 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 | If you would like that field to be updated for 1 calendar year, use the formula $CreatedOn+ Days(365) |
| Related Functions |
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 |
| Supported Context: | FormulaField And BusinessRules |
| 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 | See also DateTimeValue(), DateValue() and DateTime() |
| Related Functions |
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 |
| Supported Context: | FormulaField And BusinessRules |
| 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 |
|
| Related Functions |
ToString Function
ToString (value as Object, format as String) as String
| Category: | Convert |
| Description: | Converts any value to a string |
| Supported Context: | FormulaField And BusinessRules |
| 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 |
|
| Related Functions |
JsonObject Function
JsonObject() – (object identifier as ID) as Numeric
| Category: | Convert |
| Description: | Returns JSON format data of a specific object |
| Supported Context: | Custom Panel and Custom Page |
| Parameters: | objectId,fields |
| objectId as Numeric | Positive or negative numeric value or reference to a numeric field |
| fields as text API names | Returns JSON formatted data of the field/s provided |
| Example | To see an example, go to http://www.clarizen.com/resource/jsonobject/ |
| Additional links |
|
JsonObjects Function
JsonObjects() – (Relation as API Name, Field as API name, Filter as field API names and filter text)
| Category: | Convert |
| Description: | Returns JSON format data of a collection of objects. The collection can be filtered. |
| Supported Context: | Custom Panel and Custom Page |
| Parameters: | Relation API name, fields, filter |
| Relation as API name: | Relation API name |
| Fields as text API names | Returns JSON formatted data of the field/s provided |
| Filter as text names | Returns field data of the related items (TargetObject) collection filtered by a formula criteria |
| Example | To see an example, go to http://www.clarizen.com/resource/jsonobjects/ |
| Tips |
|
| Additional links |
|
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 |
| Supported Context: | FormulaField And BusinessRules |
| 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 |
|
| Additional Links |
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 |
| Supported Context: | FormulaField And BusinessRules |
| 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 | GetCustomActionUrl('Data Loader',GetRuntimeParameter(CurrentObject())) Use Formula Options > Objects tab > Custom Actions to select the Custom Action you want to call and then send paramaters to the custom action if it needs them. Very useful when building custom actions that 'call' a secondary custom action (dependant forms) |
GetCustomPanelUrl Function
GetCustomPanelUrl (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 panel with parameters |
| Supported Context: | FormulaField And BusinessRules |
| 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 |
| Supported Context: | BusinessRules |
| Parameters: | |
| document as ObjectIdentifier(Document) | |
| Returned value as String | |
GetFileURL Function
GetFileURL
| Category: | Common |
| Description: | Returns the URL of the specified file |
| Supported Context: | BusinessRules |
| Parameters: | file:ObjectIdentifier, expirationDate:DateTime |
| document as ObjectIdentifier(Document) | |
| Returned value as String | |
| Example #1 | {GetFileUrl('D-5601',Date(3999,12,30))} The object ID of the file can be found using Formula Options > Objects tab > File. You may search by the name of the file. |
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. |
| Supported Context: | FormulaField And BusinessRules |
| Parameters: | |
| paramValue0 as Object | |
| paramValue1 as Object | |
| paramValue2 as Object | |
| paramValue3 as Object | |
| paramValue4 as Object | |
| ... as Object | |
| Returned value as String | |
| Example #1 | GetCustomActionURL('New Project',GetRunTimeParameter(CurrentObject()), GetHiddenRunTimeParameter('First Variable')) This is mainly used as a way to pass data to a custom action where, when passed, that field in no longer visible on the form. This is helpful when you want to pass data in the background. |
GetHyperLink Function
GetHyperLink (referenceField/className as String, objectId as String) as String
| Category: | Common |
| Description: | Creates and returns a hyperlink to an object. |
| Supported Context: | BusinessRules |
| 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 |
|
| 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 |
| Supported Context: | BusinessRules |
| Parameters: | |
| entityType as String | This is the object you are working with (Project, Task, Request, etc). For example, if you are working with Request, this would be 'EnhancementRequest'. The single quotes are required as it needs to be as String. |
| viewId as ObjectIdentifier(SubsystemView) | This is the View ID that needs to be referenced. To reference the ID, you need to click on Formula Options > Object tab > View. You will see three options:• Module View - Allows you to pick the Module level view •Object Details view - Allows you to pick the module details level view (e.g. Project details or Request details view) • Timesheet View - Allows you to select a Timesheet view |
| expirationDate as DateTime | Examples: • No expiration = NULL • Expires in 2 weeks = Today()+weeks(2)• Expires five days after Due Date = $DueDate+Days(5) |
| register as Boolean | Register allows you to specify if you want a list created of shared Module View links to create an audit trail: Not wanted = FASLE Wanted = TRUE |
| Returned value as String | By default the GetModuleViewUrl will be provided in the form of a long URL in text format. You can use the HyperLink() function to show it as a link with a desired caption/title. |
GetNextCounterValue Function
GetNextCounterValue (entityType as String) as String
| Category: | Common |
| Description: | Increments the specified counter and returns the new value |
| Supported Context: | BusinessRules |
| Parameters: | |
| entityType as String | |
| 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 |
| Supported Context: | BusinessRules |
| 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 |
| Supported Context: | BusinessRules |
| 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 | 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 |
| Additional Links |
GetObjectUrl Function
GetObjectUrl (report as ObjectIdentifier, param1 as String
| Category: | Common |
| Description: | Returns a URL string for an an Object |
| Supported Context: | FormulaField And BusinessRules |
| Parameters: | |
| report as ObjectIdentifier(SystemQuery, UserDefinedQuery) | |
| param1 as String | |
| ... as String | |
| Returned value as String | |
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 |
| Note | This function works only for reports that are viewable in Clarizen. It will not work for reports that can only be downloaded in Excel and not viewed in Clarizen. |
| Supported Context: | FormulaField And BusinessRules |
| Parameters: | |
| report as ObjectIdentifier(SystemQuery, UserDefinedQuery) | |
| ... 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. |
| Supported Context: | FormulaField And BusinessRules |
| 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 |
| Supported Context: | BusinessRules |
| 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. |
| Supported Context: | BusinessRules |
| 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 Function
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. |
| Supported Context: | BusinessRules |
| 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 Function
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> |
| Supported Context: | BusinessRules |
| 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 | Create workflow rules to set standard field values via formulas |
| Additional Links |
HyperLink Function
HyperLink (url as String, title as String) as String
| Category: | Common |
| Description: | Returns a hyperlink based on the specified url and the title |
| Supported Context: | FormulaField And BusinessRules |
| Parameters: | |
| url as String | |
| title as String | |
| Returned value as String | |
| 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. Use Cases: There is another system I use to track account details (CRMxyz) I want a URL that takes this account ID and creates dynamic URL on a Project record that points to my CRM record I've created a custom field $C_CRMxyz_Account_ID where a Project Manager can input the ID How to do it: Create a formula field with the following: If(IsNull($C_CRMxyz_Account_ID), "", HyperLink("https://www.CRMxyz.com/" + $C_CRMxyz_Account_ID, "Link to Account")) Very useful function for creating dynamic hyperlink(URLs) especially in formula fields. Important Note: GetHyperlink is a much better option for returning the URL to a specific Clarizen record. |
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 |
| Supported Context: | BusinessRules |
| Parameters: | |
| customAction as ObjectIdentifier(Custom Operation) | |
| targetObject as ObjectIdentifier(Project, Work Item, GenericTask, User, ResourceEntity, Organization, Dependency, WorkItemHierarchyLink, Milestone, Regular Human Resource, Human Resource, Attachment, Discussion, JobTitle, Timesheet, Customer, ContactPerson, CustomerLink, Expense Sheet, Expense Entry, Case, Issue, Risk, Bug, EnhancementRequest, Related Work, Team Member, Skill, UserGroup, Group, GroupMemberLink, CaseCustomerLink, ShortcutLink, ProgressImpactLink, Reviewer, Rate Holder, SkillLink, GroupHierarchyLink, DiscussionGroup, GroupLink, GroupProjectLink, GroupTaskLink, GroupCustomerLink, GroupCaseLink, DiscussionMessage, DiscussionPost, DiscussionReply, Topic) | |
| user as ObjectIdentifier(User) | |
| Returned value as Boolean | |
IsIPAddressInRange Function
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'. |
| Supported Context: | FormulaField And BusinessRules |
| Parameters: | |
| ipAddress as String | |
| ipRange as String | |
| Returned value as Boolean | |
| Example #1 | Check if a user's last login IP is within a specific network range: IsIPAddressInRange($LastLoginIPAddress,'12.250.199.12-12.250.199.30') |
NumberOfAvailableLicenses Function
NumberOfAvailableLicenses (userType as ObjectIdentifier) as Numeric
| Category: | Common |
| Description: | Returns number of available licenses specified by licenseType |
| Supported Context: | BusinessRules |
| Parameters: | |
| userType as ObjectIdentifier(LicenseType) |
|
| Returned value as Numeric | |
| Example #1 | Examples: NumberOfAvailableLicenses('Full') NumberOfAvailableLicenses('Social') |
ParseHyperLink Function
ParseHyperLink (hyperlink as String) as String
| Category: | Common |
| Description: | Parses a specified hyperlink and returns a url part of it. |
| Supported Context: | FormulaField And BusinessRules |
| Parameters: | |
| hyperlink as String | |
| Returned value as String | |
PermissionsRole Function
PermissionsRole (role as String) as String
| Category: | Common |
| Description: | Add or remove permission access levels for work items, cases and customers, to users, groups or profiles based on business rules. This is achieved using the Permissions Definition link when creating custom actions and workflows. |
| Supported Context: | FormulaField And BusinessRules |
| Parameters: | |
| role as String | |
| Returned value as String | 'editor', 'viewer' |
| Example #1 |
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 |
| Supported Context: | FormulaField And BusinessRules |
| 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 |
|
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. |
| Supported Context: | FormulaField And BusinessRules |
| 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")
|
| 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 |
|
| Note | This function is only available when the organization is set to use multi-currency |
| Additional Links |
GetCurrencyType Function
Currency (Field Identifier as String) as String
| Category: | Currency |
| Description: | Takes a currency field of any item and returns the currency type tricode e.g. “USD” , ”JPY” , “CAD” , “EUR”)
|
| Supported Context: | Conditional Formatting, Formula Field And Business Rules |
| Parameters: | |
| currency as Field Identifier | A mandatory parameter that accepts the Currency field for an object that you want to see the currency type. |
| Returned value as Currency Tricode String | Returns the currency type tricode e.g. “USD” , ”JPY” , “CAD” , “EUR” |
| Example #1 | Create a validation rule that blocks adding a Resource to a Project when their Billing Rate Currency does not match the Cost Currency Type of the Project. Note: 'Not equals' in the code example below should be replaced with the correct not equals symbol that could not be inserted as it affects the code of this page. $GetCurrencyType($Resource.RevenueRegularRate) not equals $WorkItem.CostCurrencyType |
| Tips |
|
GetBillingRate Function
GetBillingRate(date as Date,currency as String,sourceObject as ObjectIdentifier,isRegular as Boolean)
| Category: | Currency |
| Description: | Gives you the Billing Rate for a particular Job Title, User, Assigned Resource on a particular date |
| Supported Context: | Formula Field And Business Rules |
| date as Date | The Date you want the Rate for. Fully supported by date effective rates, this enables you easily access future or historic rates. |
| currency as String | A parameter that accepts the Currency type that you would like the currency field’s amount to be shown in as. Accepts three letter currency code (such as "USD"). |
| sourceObject as Object Identifier | This will normally be a Job Title, User, or Assigned Resource on a Project/Milestone/Task |
| IsRegular as Boolean | TRUE will return the Regular Hourly Rate. FALSE will return the Overtime Hourly Rate |
| Returned value as Currency Tricode String | Returns a hourly billing rate in the currency type specified in the "currency" parameter |
| Example | Create an Organization Custom Action “Quick Quote” to generate you a high level price for the type of resource needed. Where type of Resource is represented by Job Title. |
GetCostRate Function
GetCostRate(date as Date,currency as String,sourceObject as ObjectIdentifier,isRegular as Boolean)
| Category: | Currency |
| Description: | Gives you the Cost Rate for a particular Job Title, User, Assigned Resource on a particular date |
| Supported Context: | Formula Field And Business Rules |
| date as Date | The Date you want the Rate for. Fully supported by date effective rates, this enables you easily access future or historic rates. |
| currency as String | A parameter that accepts the Currency type that you would like the currency field’s amount to be shown in as. Accepts three letter currency code (such as "USD"). |
| sourceObject as Object Identifier | This will normally be a Job Title, User, or Assigned Resource on a Project/Milestone/Task |
| IsRegular as Boolean | TRUE will return the Regular Hourly Rate. FALSE will return the Overtime Hourly Rate |
| Returned value as Currency Tricode String | Returns a hourly cost rate in the currency type specified in the "currency" parameter |
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. |
| Supported Context: | Business Rules |
| 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 | 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. |
| Supported Context: | Business Rules |
| 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: | Business Rules |
| 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: | Business Rules |
| 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 Function
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: | Business Rules |
| 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. |
| task as ObjectIdentifier(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 |
| loadType | ‘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 Function
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: | Business Rules |
| 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. |
| task as ObjectIdentifier(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[,start year as Numeric, start month as Numeric, periods count as Numeric])
| Category: | Financial Planning |
| Description: | Returns a Currency value indicating the aggregated financial value of a specific financial resource time-phase field, for the specified Resource in the fixed time range for a specified project. Example business questions: Calculate Budget Cost for current fiscal month Calculate Forecast Cost for next fiscal quarter Calculate Budget Revenue for next fiscal year |
| Supported Context: | Business Rules |
| Parameters: | |
| workitem as ObjectIdentifier(workitem) | Represents a reference to a work item. Normally this will be a project. |
| field as string | Note: because the currency fields you will be referencing may not be directly referenceable using the Formula Options helper, you will need to manually input the field’s API name in the formula. The field API name can be seen in the Configure screen. Examples: “PlannedBudget”, “ForecastRevenue”. Full example below. |
| period | Calendar Periods: ‘d’ for Current Day ‘w’ for Current Week ‘m’ for Current Month 'q' for Current Quarter 'y' for Current Year Fiscal Periods ‘fm’ for Fiscal Month 'fq' for Fiscal Quarter 'fy' for Fiscal Year |
| Optional Parameters | Note: Start year and month will be according to whether you defined calendar or fiscal period in the previous parameter |
| Start year as numeric | The calendar or fiscal year you want to start aggregating from. If not set, will use Current Year. |
| Start Month as Numeric | The calendar or fiscal month you want to start aggregating from. 1 to 12 If not set, will use Month 1 Only used if you have set a Start Year. |
| Periods count as Numeric | The amount of quantities to aggregated data from. The period used is the period (day, week, month, quarter, year) defined in the 3rd parameter. |
| Example #1 | Calendar Period: Calculate Budget Revenue for current calendar month GetTotalFinancialSummaryForFixedPeriod(CurrentObject(),'PlannedRevenue','m') Calculate Budget Cost for a calendar year, starting in June 2019 (ending in June 2020) GetTotalFinancialSummaryForFixedPeriod(CurrentObject(),'PlannedBudget','y',Year(today()+1) Calculate Budget Cost for a calendar year, starting in June 2019 (ending in June 2020) GetTotalFinancialSummaryForFixedPeriod(CurrentObject(),'PlannedBudget','y',2019,06) Calculate Forecast Revenue for 10 Years period, starting from Jan 2020 GetTotalFinancialSummaryForFixedPeriod(CurrentObject(),'ForecastRevenue,'y',2020,01,10) Fiscal Period: Calculate Budget Cost for Fiscal Year 2019 GetTotalFinancialSummaryForFixedPeriod(CurrentObject(),'PlannedBudget','fy',2019) Calculate Next 3 Fiscal Years Budget Cost for Starting in Fiscal Year 2019 GetTotalFinancialSummaryForFixedPeriod(CurrentObject(),'PlannedBudget','fy',2019,1,3) |
| 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 indicating the aggregated financial value of a specific financial resource time-phase field, for the specified Non-Labor Resource in a date range between 2 specific dates for a specified project. Example business questions: 1. Calculate Budget Cost from today until the end of the current year 2. Calculate Forecast Cost from next month until the end of the project 3. Calculate Budget Revenue for next fiscal year |
| Supported Context: | Business Rules |
| 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. Note: because the currency fields you will be referencing may not be directly referenceable using the Formula Options helper, you will need to manually input the field’s API name in the formula. The field API name can be seen in the Configure screen Examples: “PlannedBudget”, “ForecastRevenue”. Full example below. |
| 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 | isNew() || isChanged($PlannedBudget) 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 [, start year as Numeric, start month as Numeric, periods count as Numeric])
| Category: | Financial Planning |
| Description: | Returns a currency value for a specific currency field (Budget Cost, Budget Revenue etc.. for the specified Non-Labor Resource, for the a specified work item in the given time range. This should be the same amount that is displayed in Financial Planning view. {GetFinancialDataForFixedPeriod(work item,nlr,field,period,start year,start month,periods count)} |
| Supported Context: | Business Rules |
| Parameters: | work item as ObjectIdentifier(Object) |
| nlr as ObjectIdentifier(nlr) | Represents a non-labor resource object for which you would like to calculate financial timephase data. |
| field as string | The API name of the field in 'quotes' that you want to summarize for the time period. Note: Because the currency fields you will be referencing may not be directly referenceable using the Formula Options helper, you will need to manually input the field’s API name in the formula. The field API name can be seen in the Configure screen. Examples: “PlannedBudget”, “ForecastRevenue”. Full example below. |
| period as string | Calendar Periods: ‘d’ for Current Day ‘w’ for Current Week ‘m’ for Current Month 'q' for Current Quarter 'y' for Current Year Fiscal Periods ‘fm’ for Fiscal Month 'fq' for Fiscal Quarter 'fy' for Fiscal Year |
| Optional Parameters | Note: Start year and month will be according to whether you defined calendar or fiscal period in the previous parameter |
| Start year as numeric | The calendar or fiscal year you want to start aggregating from. |
| Start Month as Numeric | The calendar or fiscal month you want to start aggregating from. 1 to 12 If not set, will use Month 1 Only used if you have set a Start Year |
| Periods count as Numeric | The amount of quantities to aggregated data from. The period used is the period (day, week, month, quarter, year) defined in the 3rd parameter. If not set, will use 1 Period Only used if you have set a Start Year and Start Month |
| Example #1 | Calendar Period: GetFinancialDataForFixedPeriod(CurrentObject(),'NLR-101','PlannedBudget','m') Fiscal Period: (CurrentObject(),’GetObjectByExternalID(‘NonLaborResource’,’Hotel’),'ForecastCost','fy',Year(today())+1,01) |
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. |
| Supported Context: | Business Rules |
| 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 object for which you would like to calculate financial timephase data |
| 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 API name of the field in 'quotes' that you want to summarize for the time period. Note: Because the currency fields you will be referencing may not be directly referenceable using the Formula Options helper, you will need to manually input the field’s API name in the formula. The field API name can be seen in the Configure screen. Examples: “PlannedBudget”, “ForecastRevenue”. Full example below. |
| 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 | GetFinancialDataForGivenPeriod(CurrentObject(),’NLR-101’,'PlannedBudget',DateTime(2018,01,01,08,00,00),DateTime(2018,12,31,20,00,00)) |
GetLink function documentation (and others) need to be updated with the new multi manager functionality.
Ariel,
I will be sure to let our documentation team know. Thank you
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.
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!
The description for the 4 new financial functions at the bottom aren't correct. They seem to be referring to the Task Assignment functions
The new Financial Functions at the bottom of the page are incorrect and are duplicating the Task assignment functions, can this be corrected ?
Yes. We will be correcting this. Thanks for pointing this out.
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!
Hi Clarizen Team,
What function will allow to pull resource capacity for specific period (custom field) that can be used further in custom reports? It is impossible to use capacity field configured in Clarizen out f box that is available only in Resource Utilization report.
Thanks in advance for your assistance.
Hi - you can look at using: GetWorkingTimeForFixedPeriod Function or GetWorkingTimeForGivenPeriod Function.
"GetSumOfTimesheetReportHoursForFixedPeriod " and "GetSumOfTimesheetReportHoursForGivenPeriod" Functions' State parameters are not correctly described. As currently described, they reference Work Item States where as they actually relate to Timesheet States (Un Submitted, Submitted and Approved).
for the "IsChanged Function", is this only triggered by direct changes to a filed or are indirect changes also included? I have rule leveraging this function that is only running on direct changes to the field and not on indirect changes. I am hoping that it should be running for both.