Assume that we have a project with the following data.
Field | Data |
|---|---|
| id | 1234 |
| name | Sample Project: Setting up office space |
| start | June 10, 2011 |
| manager | Mark Duncan |
| budget | 5000 |
| estimatedCost | 4000 |
| actualCost | 3800 |
Based on this data, the table below shows sample formulas, the output of that formula and also provides an explanation.
Examples of the formula field
Formula | Output (In Browser) | Explanation |
|---|---|---|
| ${entity.id * 10} | 12340 | Id is multiplied by 10. |
| entity.id * 10 | entity.id * 10 | Not enclosed in ${} and hence copied to the output verbatim. |
| ${10* 10} | 100 | Formulas need not use variables. |
| <b>${entity.name}</b> | Test Project | You can use html tags. |
| ${entity.name + 100} | ERR! | Cannot perform arithmetic on String type. |
| <a href="http://mywikiserver/${entity.id}">Wiki Link</a> | Wiki Link | If 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, 2011 | Formats the start date as per the user's date format style. |
| ${entity.actualCost > (entity.estimatedCost * .9) ? 'Cost Alert!' : 'Cost Ok'} | Cost Alert! | |
| ${formatCurrency(entity.budget)} | $ 5,000 | Assuming your currency symbol is $ |
Now, lets try creating some formula using custom dields and using some logic :
- Accessing a custom field of Client of a Project on a Task Report
Let us say you have a requirement to flag all tasks in a project where the client is rated 1 as '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 business. The custom field is called 'Rating' and has a formula key '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.rate_01 == 1 ? (High) : ' ' || entity.project.client.rate_01 == 2 ? (Normal): ' ' || entity.project.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. For this, you need the Actual (Finish) date of the task and the deadline of the project. Then, subtract these values and you will get the results. Since, these two fields are dates, you cannot perform 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, the formula will look like
${(entity.project.deadlineCalendar.time.time - entity.actualFinish.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 extra or less days taken by the resources to complete the task due to which the deadline was affected.