Versions Compared

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

Assume that we have a project with the following data.

Field
Data
idID1234
nameNameSample Project: Setting up office space
startStartJune 10, 2011
managerManagerMark Duncan
budgetBudget5000estimatedCost
Cost (Est)4000actualCost
Cost (Actual)3800

Based on this data, the table below shows sample formulas, the output of that formula and also provides an explanation.

...

Formula
Output (In Browser)
Explanation
${entity.id * 10}12340Id is multiplied by 10.
entity.id * 10entity.id * 10Not enclosed in ${} and hence copied to the output verbatim.
${10* 10}100Formulas need not use variables.
<b>${entity.name}</b>Test ProjectYou can use html tags.
${entity.name + 100}ERR!Cannot perform arithmetic on String type.
<a href="http://mywikiserver/${entity.id}">Wiki Link</a>Wiki LinkIf you use a Wiki, you can create hyperlinks to pages in your Wiki. Notice that we have simply used the HTML Anchor tag to generate a hyperlink.
${entity.abcd}ERR!Formula contains error. No variable entity.abcd exists.
${formatDate(entity.start)}Jan 10, 2011Formats the start date as per the user's date format style.
${entity.actualCost > (entity.estimatedCost * .9) ? 'Cost Alert!' : 'Cost Ok'}Cost Alert! If...then.. logic
${formatCurrency(entity.budget)}$ 5,000Assuming your currency symbol is $

...

Now, lets try creating some formula using custom dields fields and using some logic :

  • Accessing a custom field of attached to Client of a Project on a Task Reportin a formula attached to Task.
    Let us say that you have a requirement to flag all tasks in a project where the custom field of client is rated as 1 as your 'High' priority tasks, 2 as 'Normal' tasks and 3 as 'Low' priority tasks. There is a custom field for Client which rates the client as 1 or 2 or 3 depending on the client's value to my your business. The custom field is called 'Rating' and has a formula key 'client_rate_01'. Now, you need to create a custom formula field on task. Let us name this 'Task Importance'. The formula will look like this :
    ${entity.project.client.client_rate _01 == 1 ? (High) : ' ' || entity.project.client.client_rate _01 == 2 ? (Normal): ' ' || entity.project.client.client_rate _01 == 3 ? (Low) :' '} 

    Now if display 'Task Importance' column in a task report and you will know the importance of the task and can follow up accordingly.

 

  • Date Arithmetic
    Let us assume that one of the projects where you are a PM has completed. Now, you want to compare which of your resources, took more days to complete

    the task due to which the project's deadline was not met.

    their task.
    For this, you need the Finish(Actual) date (

    Finish) date of

    entity.actualFinishCalendar) of the task and the

    deadline

    planned finish date of the

    project. Then, subtract these values and you will get the results.

    task (entity.finishCalendar).
    Since, these two fields are

    dates

    of type date, you cannot perform

    direct

    a direct operation on them.  You need to

    first

    convert them to a number and then perform the

    operations. Create a formula field for task and get the variable for the two fields i.e  Actual (Finish) (entity.actualFinish) date of the task and Project's deadline (entity.project.deadlineCalendar). Then

    operation. So, the formula will look like:
    ${(entity.

    project.deadlineCalendar

    finishCalendar.time.time - entity.

    actualFinish

    actualFinishCalendar.time.time)/86400000}

    When you write ".time.time" it means that the date field is first converted to milliseconds for both the fields, then the subtraction is done. The result is again in milliseconds which needs to be converted again in days and hence, it is divided by 86400000.

    This gives

    The result is the extra or less days taken by the resources to complete the task

    due to which the deadline was affected.

    Define your own health indicators
    Your company may have a different logic on how a budget/schedule status for your task/project is calculated and so you do not wish to use the one provided by us. Lets take an example that you actually want the schedule status to show the status depending on the baseline dates; instead of the projected dates. Then,
      a) You need to create a formula field which will have the below formula
      b) The Output type of the custom field will be Text
      c) Attach the custom field to 'Task' or the entity for which you want to create the custom field.
      d) The field references like entity.projectedFinishCalendar.time.time, entity.baselineFinish.time, etc can be taken from the Field References tab.

    Code Block${set ("label", 'Unknown')} ${set ("label", entity.projectedFinishCalendar.time.time <= entity.baselineFinish.time && entity.percentComplete!=100 ? 'On Time' : label)} ${set ("label", entity.percentComplete!=100 && Date.now().time > entity.baselineFinish.time ? 'Overdue' : label)} ${set ("label", entity.projectedFinishCalendar.time.time > entity.baselineFinish.time && Date.now().time < entity.baselineFinish.time ? 'At Risk' : label)} ${set ("color", 'Neutral')} ${ set ("color", label eq 'On Time' ? '#008000' : color)} ${ set ("color", label eq 'At Risk' ? '#FFA500' : color)} ${ set ("color", label eq 'Overdue' ? '#FF0000' : color)} <span style="display:inline-block;width:10px;height:10px;background-color:${color} ;border-radius:5px;"></span>&#160;${label}

    . The PM can pick this custom field as a column in a task report to see the difference.