As of version 2.0, Participants Database has a new type of database field that shows and stores a calculated value based on other values from the record.
There are two different kinds of calculation field:
Numeric Calculation – This field is used to generate a numeric result from an arithmetic calculation of numeric fields and values.
Date Calculation – This field is specifically used to generate the string result of a date calculation from date fields and values. This is to make it possible to search for dates with a string value, such as the name of a month or a year.
Both of these fields save their result to the database so that records can be searched and filtered by their values.
The calculation is set up in the Template configuration for the field by creating a “calculation template.” This template uses Participants Database value tags to represent values from other fields in the record. The values are joined by operators that define the mathematics that will be applied. The final displayed value is formatted using a special format tag.
The Calculation Template goes into the “template” configuration of the field on the Manage Database Fields page.
A Simple Numeric Calculation Example
For an example to help explain the calculation template and how it works, let’s say you want to display the square footage of a room. The user is entering the width and length of the room. To show the square footage, we multiply those two values. The calculation template for the square footage calculation field could be something like this:
[room_length]*[room_width]=[?round_0]In that template, we have two field values and a format tag. The two fields must both be numeric fields of some kind, or they must contain a value that is a number. Between the two tags is the operator, in this case * which indicates multiplication. After the field values, we have the = operator, and then a format tag. A format tag always starts with a ? to show it is a format tag and not a field value tag.
The 0 in the format tag is the number of decimal places to show. In this case, there will be no decimal places, so the value will be rounded to the nearest integer. If the user enters 16.5 and 12.25 for the room dimensions, the field will display “202”.
Let’s try another example which shows how to work with sums. Let’s say the user has 3 scores in their record and we need to show the average score. We could use a template like this:
[score_1]+[score_2]+[score_3]=[?average_2]Breaking that down, we have the three scores getting added together, then in the format tag, we are showing the average value by showing the sum divided by the number of values on the sum. The number 2 in the format tag specifies the number of decimal places to show. In this example, the value will be rounded to two decimal places.
How Calculation Fields Work
There are a few things to understand about how these fields work.
The calculation is performed on the saved values. This means that if the user changes one of the values that is part of the calculation, they will not see the result until the record is saved. The calculated field will not be updated in real time as the user changes values used in the calculation.
If any of the fields has an invalid value (such as a non-numeric value or no value at all) the value won’t be calculated. If you want there to be a fallback value to use in case of an empty value, you can use the source field’s default value setting for that.
The field’s value is only recalculated when the record is changed to avoid unnecessary load on the server. For example, recalculation will be happen when a record is edited by a user or administrator, imported using a CSV, or changed by a mass edit function on the records by an administrator.
If the field’s calculation template is edited, the calculated value for that field in all records will be recalculated. This happens in the background, so even if there are a large number of records, site performance won’t be affected.
Field Value Tags
The field value tag is a placeholder that represents the value of the named field from the record data. For example, if the field is named “number_of_dogs,” use the [number_of_dogs] tag in the template to perform a calculation using that value.
Note that the field’s “Title” cannot be used here, it must be the “Name.”
Also, as mentioned before, the record’s value for that field must be numeric, either a number of some kind or a date (which are represented internally as numbers).
Operators
Operators define how two values will interact to yield a numeric result. These are familiar arithmetic operators like adding, subtracting, multiplying, and dividing.
Values in a calculation template must always be separated by an operator, you cannot place two values next to each other with no operator in between.
Here is a list of operators that you can use in the calculation template:
+Addition-Subtraction*Multiplication/Division=Equals, used to tell the template where the calculation ends and to show the result
These operators act on the value before and after the operator character. If there is more than 1 field value to the left of the operator, the operator acts on the calculated result of those values. For example in the sum template above the second + operator acts on the sum of the two values to the left of it.
Calculation Precedence
The calculation template uses a “left to right precedence” where the precedence of an operation is determined only by its position in the template. This means there are no parentheses, and no precedence based on the operator, which is common in programming languages. Each operator acts on the result of the calculation to the left of it.
A example of how multiple operators and precedence in the calculation template works is something like this…let’s say value_3 = 3, value_4 = 4 and value_5 = 5. Here is the template:
[value_3]*[value_4]+[value_5]
That calculation will first multiply value_3 by value_4, then add value_5, with a result of 12+5 or 17.
If you really wanted to multiply value_3 by the sum of value_4 and value_5, you would rewrite the template as:
[value_4]+[value_5]*[value_3]
Yielding a value of 9*3 or 27. The calculation always proceeds from left to right.
The Equals Operator
The equals operator = is a special case. The operator is used to tell the template where the calculation ends. There can only be a single equals operator in a calculation template, and it must be the last operator in the template. No values can be placed after the = operator.
Optionally, a format tag can be placed after the equals operator.
Format Tags
There are several tags that are used to format the value to be displayed. A format tag always begins with ? to prevent it being confused with a field value.
Numeric and date formats are localized, so it will format dates and numbers according to the standard as determined by the current “Locale” setting.
[?round_n] This tag rounds the result to the number of places specified in n.
[?integer] Truncates the value to an integer, removing the decimal and everything to the right of it with no rounding.
[?average], [?average_n] Takes the result and averages it using the number of items in the sum. The result is rounded to the number of decimals specified in n. If not specified, n is assumed to be 0. The average is calculated as the result of the whole calculation template divided by the number of values in the template that are followed by a + or – operator.
[?years], [?months], [?weeks], [?days] These tags are used to show a result which is a span of time in terms of the number of specified calendrical units. This value is not rounded, it shows the number of whole units in the time span.
[?currency] Formats a number with 2 decimal places. Fills in zeros so there will always be 2 numbers after the decimal point. Does not place a denomination character, you must add it to the template or the field configuration.
[?unformatted] Does not change or format the result of the calculation at all.
Format tags are optional in the template, if it is left out, the value will be unformatted.
Date Calculation Format Tags
When using a Date Calculation field, the result is usually formatted as a date of some kind.
[?date] This formats the result of the date calculation using the configured date format, according to the global WordPress setting.
[?year], [?month], [?week], [?day_of_month], [?day_of_week], [?day_of_year], [?month_day], [?day_month] These are Partial Date format tags, which show only the specified part of the date.
Partial date tags are useful for working with part of a date (such as a month or year) in searches, filtering and sorting.
The Difference Between Date and Numeric Calculation Fields
Simply put, Numeric fields store a number and Date Calc fields store a date as a string. Date Calculation fields are for calculating a date or partial date (which is saved as a searchable string), and Numeric Calculation fields are for mathematically calculating a number.
If you want the result of the calculation to be a date or a partial date, you need to use a Date Calculation field. The [?date] tag is used to show a full date in the globally-defined format. The Partial Date format tags can be useful to show only that part of the date that is relevant, for example to show the day of the week it was. The format tag determines what is stored in the database, so it can be used in searches, filtering and sorting.
You cannot use a Date Calculation field as a value in a numeric calculation unless it is configured to produce a number as its result. If there is a word in the result such as the name of a month, it will not work as you might expect in a numeric calculation.
Date Keys
A Date Key is a special tag that is used to dynamically provide a date, usually a date relative to the current date. Date keys always begin with the # character to distinguish them from field value tags.
Internally, these values are represented as a Unix timestamp, which is the number of seconds before or after January 1, 1970 00:00 UTC. When using date keys in a calculation, they will be a number.
Date keys are values, like field values, and can be used in the same way.
[#current_date] is, of course, the current date, including current the time with the timezone determined by the WordPress configuration.
[#current_week] is (in most cases) 12:00am Monday of the current week.
[#current_month] is 12:00am the first day of the current month.
[#current_year] is the first day of the year.
Time Span Tags
These tags provide a value that corresponds to a specific span of time to be used in a calculation.
[#n_days], [#n_months], [#n_years] these tags provide the value that is the specified number of days, months or years.
For example, if you want to show a date that is 6 months after the person signed up, you could use a template like this:
[date_recorded]+[#6_months]=[?date]
Note that when using the months span tag the time span is not going to result in the same day of the month due to the fact that months do not all have the same number of days. For example, 6 months is calculated as 180 days, so April 15 + 6 months will be October 13, not October 15. The offset will be more or less for different starting dates.
Time span tags can also be used with Date Keys to obtain other relative dates such as [#current_year]-[#1_years] to get the previous year.
Literal Strings and Values
You can also use numeric or string literals in the calculation template, By placing the value in the calculation part of the template. Note that you cannot place a numeric literal as the first term in the template, it must be after a value tag. For example in this template to show the area of a circle, given its radius:
[radius]*[radius]*3.1416=[?round_2]Literal strings can be added before or after the calculation template, for example:
Area of the circle = [radius]*[radius]*3.1416=[?round_2]This would result in a display like this: Area of the circle = 234.45
Literal strings are only allowed outside of the calculation part of the template: in other words before the first value tag and after the last value tag.
Showing Units or Denominations
For some displays, it’s desirable to show the units. You can do this by adding the units as a literal string to the template, for example:
[radius]*[radius]*3.1416=[?round_2]in²Units or denominations can also be added to the field definition using the “data-before” or “data-after” attributes. This has the advantage of providing an HTML wrapper for the character so that it can be styled individually. For example:
Showing a Person’s Age
A common application of the Numeric Calculation field is showing a person’s current age. To do this, you need a date field to hold the birth date (named “birthdate” in this example). The age calculation field calculates the age by subtracting the birth date from the current date.
You can see we are using the [?years] tag to only show the number of whole years it has been since the person’s birth date. Notice that we are using a Numeric Calculation field here, this is because the result is a number: the person’s age, which can now be used to search, filter, or sort records.
Showing a Person’s Birthday
A typical application of the Date Calculation field is to show a person’s birthday. A birthday is often shown as the month and day of the person’s birth date. This is what is called a “partial date” because the year is not included.
The format tag used here will result in a string like this: “April 24” Alternatively, you can use the [?day_month] format tag to result in a string like this: “24 April” if that is preferred. Either way, you can do a search for “April” on the birthday field and get all the records for people who have a birthday in April.
Using Calculation Fields in Search, Sort, and Filtering
It is important to understand what is getting stored in the database by your calculation field. If it is a Numeric Calculation field, searches and filters can use “greater than,” “less than,” and “equals” because the stored value is a number. If it is a Date Calculation field, you should use “~” or “contains” because you’ll be looking for a string or substring.
Note that the strings a Date Calculation field generates are localized to the global locality setting of the website. This could potentially cause problems for multilingual sites because the stored string will use the global locality active at the time the calculation was made.
For sorting, remember that strings are sorted alphabetically and numbers are sorted numerically. If you’re sorting on a Date Calculation field and the stored string begins with a number, the sort will be alphabetical, not numeric, so the results may not be what you expect.