Our users have long told us how much they love our custom fields - whether it is for tracking key dates, picking from lists of options to make reporting easier or just being able to do away with those pesky spreadsheets for tracking lots of details on sales, projects, tickets and retainers, our custom fields let you shape Accelo to match just what your business needs.
However, there's always been this problem - the fields weren't that smart. You could make it easier for people to update them with status progressions, and with the introduction of Triggers late last year you could have them updated based on certain rules, but if you wanted to have an easy way to say "Make the Due date of a Ticket 4 days after the Start date" and know it would always be thus even if the start date was later changed, well, you were out of luck.
Until now: We're excited to announce that we've taught Accelo math, and now you can take advantage of it using our new Formula custom field type.
Formulas, available for all Premium editions of Accelo, are a lot like regular custom fields: they show up on view screens, you can use them in filters for quick reporting (including saved reports), and export the info for more detailed analysis. The big difference though is that they're actually calculated via a formula (configurable by you!), which means the outputted value can't be edited, and can always be trusted. To start with, we're releasing two types of formulas.
For example, you've always wanted to be able to track the difference (in days) between two date fields. Maybe they're Custom Date Fields, or maybe they're built-in fields like the Commenced and Completed dates. Now you can create a new Formula (number) field which will automatically calculate the difference. Any time a field in the formula changes, the formula result will re-calculate - so your data will always be up-to-date.
To build the formula, we must first set the initial field, e.g., below, I am using the Completed date.
I'll then select the minus operation to subtract a field - the Commenced date.
This will give me a complete formula of Completed minus Commenced. The formula knows that the output type will be a number, and will now let me save it:
Note that we could continue adding more operations in the formula - including the ability to multiply or divide by additional fields/variables.
The result will now be visible when looking at a project that has the required variables set - the Commenced and Completed dates in this case:
A simple example of this would be to start with a date field and then append a number field to it. The number could come from an existing custom field, formula field, or just be a fixed number for this formula. This can be helpful in setting benchmark or SLA target dates:
Note in the screenshot above there's an option to "Use 0 is empty". This means that in cases where the field "SLA grace days" has no value, the formula will use 0 instead (and effectively return the Due date + 0 days). This allows the formula field to evaluate - instead of returning empty and therefore not showing up.
In addition to these two main formula types, we've also included a couple of extra power moves.
"Inception" Formulas: a Formula that derives from a Formula
Since Formula fields are extension fields like any other, we've made it possible to include the output stored in another formula field when you're creating a new one! This way, you can have a formula which could be "Due Date" based on the number of days in an SLA field value, and then set an "Escalation" date/time field to be a certain date before or after the Due Date field!
Formula fields lock down input fields
When a formula field uses one (or more) fields as part of its calculations, we have checks in place to make sure you can't delete those input fields while the formula field is still around. Makes sense that you can't go around deleting an input and expecting the output to be happy!
Triggers + Formulas = Automation Domination!
The functionality of Triggers - which are often used to update statuses, send notification emails, and even update other fields - work in harmony with formulas. You can use formula fields in your trigger rules, which means if a formula field changes in value (either because of manual user update or through another trigger taking effect) and another trigger ruleset is then satisfied, the trigger will run.
There's also a TODAY option
In addition to the ability to bring in other fields to make up your calculations, you can also use a TODAY options which will automatically recalculate overnight (in your own timezone). This way, you can have a formula like "Number of days overdue" which subtracts TODAY from the Due Date and have a simple numeric field to use in your filter and reporting interfaces!
