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 |
| Cost (Est) | 4000 |
| Cost (Actual) | 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! | If...then.. logic |
| ${formatCurrency(entity.budget)} | $ 5,000 | Assuming your currency symbol is $ |
Now, lets try creating some formula using custom fields and using some logic :
...
...
Your company may have a different logic on how a budget/schedule status for your task/project is calculated. 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. To create such a field, do the following:
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 class="label label-outline" style=" |
...
border-color: ${color}; color: ${color} |
...
; |
...
">${label}</span> |