Velo Tutorial: Calculating and Displaying Collection Data

There are many instances when you want to display data that does not come directly from one of your collections. Instead, you want to calculate values based on the data stored in a collection and display those calculated values. Sometimes you want to calculate data for each item you're displaying and other times you want to calculate aggregated values based on a number of items. In this article, we demonstrate how to display such data using a variety of different page elements.

Per-Item Calculations

There are several ways to calculate and display data on a per-item basis. Here, we will explain how to do so using a data hook.

To generate a template for a hook using the sidebar:

1. Find your collection in the CMS section of the sidebar and mouse over it.
2. Click the Show More icon and select Add data hooks. 
3. Select the hooks you want to create, then click Add & Edit Code. Templates for each of the hooks you choose are automatically generated and added to a file named data.js in your site's backend code.

The idea is to create an afterQuery hook that intercepts the data from your collection before it reaches your page code. The hook runs for each item that is retrieved by a dataset or query. The hook performs the desired calculations and adds the calculated data to each item before it gets to the page. Then, in your page code, you can get the calculated data and apply it to your page's elements. 

For example, let's say you have a collection named Employees that contains information about employees. The collection might store an employee's first name and last name in separate fields. It also might store an employee's date of birth. But for display purposes, you want to show employee names together in one element and employee ages instead of dates of birth.

To get started, you write an afterQuery hook for the Employees collection:

1
// In file backend/data.js
2

3
export function Employees_afterQuery(item, context) {
4
  item.fullName = item.lastName + ", " + item.firstName;
5
  item.age = ageFromDob(item.dob);
6

7
  return item;
8
}

The hook receives the item from the collection and adds two new properties to it. First, it adds a fullName property that concatenates the employee's name in a "lastName, firstName" format. Second, it adds an age property that calculates an employee's age based on the date stored in the dob field.

The hook uses the following function, which can also be included in the data.js file:

1
function ageFromDob(dob) {
2
  const now = new Date();
3
  const days = now.getDate() - dob.getDate();
4
  const months = now.getMonth() - dob.getMonth();
5
  let age = now.getFullYear() - dob.getFullYear();
6

7
  if (months < 0 || (months === 0 && days < 0)) {
8
    age--;
9
  }
10

11
  return age.toString();
12
}

A page that retrieves items from the Employees collection using a dataset or query will now have the new properties that you've added to each item. The next step is to apply the values from those properties to the page's elements.

In the following examples, we will assume that the page receives its data using a dataset. Generally, this means that you can connect your page elements to any non-calculated data as you would normally do. However, since your calculated data is not part of the database schema, you will have to "connect" the data to page elements using code. If you use a query instead of a dataset to retrieve your page's information, you can use a similar approach to apply the calculated data to your page elements.

Text Elements

To apply calculated data to a text element, you set the value of the text element's text property to the value of one of the item's calculated properties in the dataset's onReady() function.

If your page's dataset contains more than one item and you allow your site visitors to click through multiple items using previous and next buttons, you also need to set the value of the text element's text property in the dataset's onCurrentIndexChanged function.

You can connect all the other elements on your page to their corresponding non-calculated fields as you would normally do.

For example, assuming the employee data mentioned above, you connect non-calculated fields, such as address and job title, to page elements using their connect panels. However, you leave the elements that will show the calculated data, the employee name and age, unconnected. Those connections are dealt with in the following code.

1
$w.onReady(function () {
2
  $w("#dataset1").onReady(() => {
3
    populateCalculatedFields();
4
  } );
5
 
6
  $w("#dataset1").onCurrentIndexChanged( (index) => {
7
    populateCalculatedFields();
8
  } );
9
} );
10

11
function populateCalculatedFields() {
12
  const currentItem = $w("#dataset1").getCurrentItem();
13
  $w("#textFullName").text = currentItem.fullName;
14
  $w("#textAge").text = currentItem.age;
15
}

Here, the populateCalculatedFields() function takes the calculated data from the dataset and applies it to the corresponding page elements. It is called when the dataset is first ready and then every time the current item is changed.

Table

To apply calculated data to a table, you reset the table's columns using its columns property to include columns for each item's calculated values. You then set the data of the table using the table's rows property.

You begin by connecting the table to a dataset as you would normally do. Then, use the table's Manage Table panel to delete any columns that you don't want to be shown in your table because you're going to calculate their data.

For example, assuming the employee data mentioned above, after you connect your table to the dataset, you will delete the First Name, Last, Name, and DOB columns. The columns that are remaining will receive their data from the dataset. The columns that will display your calculated data are created in the following code.

1
$w.onReady(function () {
2
  const currentColumns = $w("#employeeTable").columns;
3
  const calculatedColumns = [
4
    {
5
      "id": "colFullName",
6
      "dataPath": "fullName",
7
      "label": "Name",
8
      "type": "string",
9
    },
10
    {
11
      "id": "colAge",
12
      "dataPath": "age",
13
      "label": "Age",
14
      "type": "string",
15
    }
16
  ];
17
  $w("#employeeTable").columns = calculatedColumns.concat(currentColumns);

Here, the code begins by getting the current columns from the table using the columns property. Remember, those will correspond to the fields from the dataset, minus the columns we deleted.

Next, it creates an array of two new columns that will hold the calculated data.

Then, it puts both sets of columns together and uses that to reset the table's columns property.

Repeater

To apply calculated data to a repeater, you set the values of the corresponding elements using the repeater's onItemReady() function.

You can connect all the other elements in your repeater to their corresponding non-calculated fields as you would normally do.

For example, assuming the employee data mentioned above, you connect non-calculated fields, such as address and job title, to elements in your repeater using their connect panels. However, you leave the elements that will show the calculated data, the employee name and age, unconnected. Those connections are dealt with in the following code.

1
$w.onReady(function () { 
2
  $w("#employeeRepeater").onItemReady( ($item, itemData, index) => {
3
    $item("#repeaterFullName").text = itemData.fullName;
4
    $item("#repeaterAge").text = itemData.age;
5
  } );
6
} );

Here, the repeater onItemReady() function takes the calculated data from the dataset and applies it to the corresponding elements in the repeater items. It is called for each item in the repeater as they are created.

Aggregations

If you want to display aggregated values, using a data hook is not necessarily the best option since hooks work on a per item basis. Instead, it is usually better to perform your aggregation calculations in your page code. 

For example, let's say you have a collection named Expenses that contains information about monthly expenses. The collection might store the month, type, and amount of each individual expense. There can be multiple items for each month. Instead of displaying all the expense items individually, you want to just display a total for each month.

We can use the following code to populate a table with the aggregated expense data:

1
import wixData from 'wix-data';
2
import {monthSort} from 'public/utils';
3

4
$w.onReady(function () {
5
  $w("#aggTable").columns = [
6
    {
7
      "id": "col1",
8
      "dataPath": "month",
9
      "label": "Month",
10
      "type": "string"
11
    },
12
    {
13
      "id": "col2",
14
      "dataPath": "total",
15
      "label": "Total Expenses",
16
      "visible": true,
17
      "type": "number"
18
    }
19
  ];
20

21
  wixData.query("Expenses")
22
    .ascending("month")
23
    .limit(1000) // include a limit if you have more than 50 items
24
    .find()
25
    .then( (result) => {
26
      const months = result.items.map(x => x.month)
27
        .filter((obj, index, self) => index === self.indexOf(obj))
28
        .sort(monthSort);
29
      const aggregated = months.map(x => {
30
        return {
31
          month: x,
32
          total: result.items.filter(obj => obj.month === x)
33
                  .map(z => z.amount)
34
                  .reduce((sum, current) => sum + current)
35
        };
36
    } );
37
   
38
    $w("#aggTable").rows = aggregated;
39
  } );
40
} );

The code begins by setting the columns of a table. There will be two columns, one for the name of the month and another for the total expenses during that month.

Next, the code performs a query on the expenses collection. Once it receives the results of the query, it uses standard JavaScript array functions to calculate the total expenditures for each month.

In this particular case, the code first extracts a sorted list of the months found in the collection. It uses the following function from a public file to sort the months list:

1
const months = ["January", "February", "March", "April", 
2
    "May", "June", "July", "August", 
3
    "September", "October", "November", "December"];
4

5
export function monthSort(a, b) {
6
  return months.indexOf(a) - months.indexOf(b);
7
}

Then, for each of those months, it creates an object with two key:value pairs. The first pair corresponds to the name of the month. The second pair corresponds to the total expenditures for that month.

Finally, the objects that were just created are set to be the row data for the table.