[capink]

The formulas action is a template based solution for doing calculations based on values from multiple books (somewhat similar to excel formulas). Since it is template based, you can also control the formatting of the final report. Similar to the single field action, you can define the formulas at runtime, or have them predefined.

The formulas action comes with the following functions: sum, min, max, median, mean, mode, multiply_by. These functions work on a list of values e.g sum('1,2,3,4'). In addition to the supplied functions, you can define your own using the plugin's module editor, or using calibre's template function editor. More on this below.

Two additional functions are included which make it possible to fetch values from different books: from_selection, from_search.

Here is an example of how use formulas:

program:
    col = '#my_col';
    vals = from_selection(col);
    s = sum(vals)

This will print the result of the sum in a pop-up final report. We can take this further by defining more than one formula and adding some formatting to the final report:

program:
    col = '#my_col';
    vals = from_selection(col);
    s = sum(vals);
    avg = mean(vals);
    strcat('<b><u>Calculations for column ', col, ':</u></b><ul><li>', 'Sum: ', s, '</li><li>', 'Average: ', avg, '</li></ul>')

You can see how the final report looks in the attached screenshot.

In the above examples we got the value of #my_col for all selected books. We can replace it with values from a search instead of selections, using from_search function:

    vals = from_search(col, 'tags:true');

Notes

• Only general program mode is supported.
• In addition to functions defined by the formulas action, calibre template language has arithmetic functions like: add, substract, multiply, divide .... etc, they can be used together.
  
  Update: As of calibre 5.13, template language supports several operator types including arithmetic operators. So people using calibre version >= 5.13 can replace this:
  
  

  program:
  add(1,2)

  with:
  
  

  program:
  1 + 2

  See this post for more details.
  
• If you want to have line breaks in the report, you can use the <br> tag. If you want multiple adjacent spaces you can use '&nbsp;' for each space.
• For performance reasons, whenever in the formula editor, the from_selection and from_search functions return a fixed list ('1,2,3,4,5'). Otherwise, the editor would be very slow while waiting for the full results from these two functions. This will be reflected in the result you see in the editor's preview box.
• All the numerical functions like sum, mean ... etc will discard non-numerical values in lists. If you want, you can convert these values to a number using the re_non_numerical function (an additional function defined by formulas). For example, the mean function will disregard all columns that does not have values (represented as 'None' in the list). If you want these to be counted, do the following:
  
  

  program:
      col = '#my_col';
      vals = from_selection(col);
      new_vals = re_non_numerical(vals, 0);
      avg = mean(new_vals)

  This way the mean function should count the undefined columns.
  

Defining your own functions
You can define your own function using the plugin's module editor. Here is an example of how the mean function is defined:

from calibre_plugins.action_chains.actions.formulas import FormulasFunction
import statistics

class MeanFormula(FormulasFunction):
    
    name = 'mean'
    arg_count = 1

    def run(self, iterable):
        iterable = self.to_numerical_iterable(iterable)
        return str(statistics.mean(iterable))

There are some caveats you have to observe:

• In template functions, all args are of string type. So, you have to unpack (if in an iterable) and convert to whatever datatype you want. When you return the result of your function, convert back to string.
• Even None values are strings in the form of 'None'. So you have to account of that.

You can also use calibre's template function editor to define the function. If it makes sense to use your function outside this plugin, it might be more suitable to define it in calibre's template function editor