- Free
- Plus
- Business
- Enterprise
Formula and aggregation fields allow you to create new information from existing data in the list, or even from other lists!
This feature makes it possible to combine the contents of fields (e.g. first and last names) or to combine texts and labels from references. The calculated texts/labels/numbers work like regular fields, so they can also be used for terms in filters, aggregation in dashboards or as grouping in Kanban. In addition, filters can now be applied within the formulas to include only certain references in the calculation. Last, a text formula can now occupy the title field.
Formulas
When you create a formula field, it applies to every single item in a list. Think of it as applying a formula to an entire column in a normal spreadsheet. The formula takes in information from other fields in the list to create new information (in a normal spreadsheet: taking in data from other columns to calculate something for each row).
The formula field allows you to create formulas using common operators or functions on number or reference fields in your list.
For example, if you wanted to calculate the cost of labour for a task, your formula would look like this:
For instance, in your spaceship building list, each task has an ‘Hourly Wage’ field, a ‘Materials Cost’ field, and an ‘Estimated Time’ field. You want to calculate the estimated cost of each task in the project.
You create an ‘Estimated Cost’ formula field that multiplies ‘Hourly Wage’ by ‘Estimated Time’ and adds ‘Material Cost’. The estimated cost for each task is then displayed on each item in the list!
Guide to using functions and formulas
When you start typing a formula into the formula options field, Zenkit will show you a dropdown of available operators, functions and aggregations you can use.
The first thing you'll be shown is a dropdown list of all number fields in your current list or referenced number fields.
Select a field you want to work with from the dropdown menu or type in a number you want to use.
Add an operator then type in another number or choose from the list again.
When you're done creating your formula, click "Create" to apply it to every item in your list.
How formulas can be applied
Formulas can be applied for a variety of use cases. Create simple calculations out of number fields, aggregations, text field values and much more.
Numbers
Simple calculations can be created out of number fields. All you need is more than one number field from the exact list or a referenced one. Simple calculations can help calculate the total cost of a specific task.
Numbers are also ideal for calculating with fixed figures. This formula can be used to calculate the price including VAT, to name just one example.
In addition to that it is possible to use different number formats. In this case, it would make sense to choose "Currency". However, you can choose from "Integer" and "Decimal" as well.
Aggregations
Even though you can select it from the 'Add Field' dropdown list, an aggregation will create a formula field. An aggregation is essentially a formula-building wizard that makes it easier to build formulas using references.
When you create a new aggregation, it will recognize which reference fields are included in your list, and pull number fields from inside those references.
For example, if you have referenced three tasks from your project tasks list in a main project list, you can use an aggregation field to sum the cost of those tasks so you can calculate the total sum of the project. You could do the same thing with items from a billing list linked to a customer in your customer's list, you can create an aggregation that sums the totals of the invoices into a 'Total lifetime value' field. Every time you add a new invoice to that customer, the total lifetime value will be updated to include it.
Text fields
With Text fields, it is now possible to combine field values. Open the list settings and choose "Fields" from the panel on the left. After having clicked "Add field" choose "Text Formula". You will then be able to choose all fields just like with aggregations and regular formula fields.
As soon as you have added your formula, click "Create" to apply it to your list.
Aggregate field values
Aggregating field values can be used to create a summary of the tasks for customers: The staff member "Bob the Builder" for example is referencing two tasks.
The Text formula "Task Summary" will format these references and define a template that is evaluated for each referenced task. This template basically is another formula that refers to each referenced item (in this case, a task).
The text around your formula can either be edited in HTML, as plain text or in Markdown. To learn more about the different text types click here.
Item Identifier
Every Item in each list has an item identifier by default. With the help of formula fields, it is now possible to visualize individual ID's for your items. This works in a pretty easy way: Click "+ Add" at the very bottom of the item details and then "Custom field after that" scroll to "Item Identifier" and click on it.
You could click "Create" and save it like that. Or to individualize the IDs, even more, you can add more information to that field. You could create individual billing IDs for example.
For example, include the billing year on your ID. In addition, you can decide whether the editor type should be plain text, HTML or Markdown.
Labels
With labels in formulas, you will be able to combine the labels of referenced fields with each other. To do so follow the same steps to add a new field. Scroll to the very bottom of the different options and select "Labels Formula".
You will then be able to personalize your formula. Choose which reference field should be included, and which label field should be part of the formula.
Now the only thing left is to choose the aggregation type for your formula. We offer three different types of aggregations. "Union" combines the labels of all referenced items, "Intersection" selects labels that appear in every referenced item and "Symmetric Difference" selects labels that appear in exactly one referenced item but not more than once.
After having set all the requirements you desire, click "Create".
This innovation enables the combination of content from various items, such as first and last names or other texts and labels from references. These computed text/number/label fields can be used wherever regular fields can be used as well. So you can filter the results, aggregate the values in dashboards or use the computed labels to group your Kanban boards. You can even use a computed text field as the primary field of your list. Additionally, filters can now be applied within formulas, to restrict which references should be used for the calculation.
To learn more about basic field types that don't calculate new information, read our guide to basic fields.