Skip to content

Formulas

Formulas on Dbfolder

You can use formulas to create dynamic values in your database.

The formula is a javascript code that will be executed when the row is loaded.

How to Use

The code can be written in the column settings. The generated values can be added to the notes' metadata by enabling persist formula output in the column settings.

Exposed Variables

Common variables that you can use in your formula are:

  • db : the database object with predefined functions that can be used in the formula
  • config : the table's config information

To use an exposed variable, use the ${} syntax. For example, to get a value of the config, you can use ${config.pagination_size}.

Column Formula

If you are using a column formula, you can also use the following variables:

  • row : the row object

In case you are using a footer formula, you can also use the following variables:

  • values : An Array of cell values in the column

Options Formula

The options formula is a special case. It is used to generate the options for a column with the type select or tag. The formula must return an array of objects with the following structure:

{
    value: "value",
    label: "label"
    color: "color" // HSL, RGB, HEX, or color name but with string representation
}

Due the nature of the formula, the variables are called directly instead of using the ${} syntax. For example, to get a value of the db, you can use db.js.myOptionsFormula().

You can also use the following variables: - column : Object with the column information

Exposed Functions

The root object db has the following functions:

  • js : execute a javascript function that you previously defined in the js folder of your table in the database or plugin global settings. (I.E.: db.js.myFunction( arg1, arg2))
  • dataview: expose the dataview API. (see Dataview API)
  • rollup: expose the rollup functions of the dbfolder plugin. (see Rollup documentation)
  • Internal functions of the plugin (see API DatabaseFnType)

Javascript file structure

To add a javascript file to the js folder, it must be a .js file and have the following structure:

function optionalFunction( arg1, arg2){
    // do something
    return value
}

// Your main function inside the file (arguments are optional)
function myFunction(arg1, arg2) {
  // do something
  // You can use another functions defined in the file
  return result;
}

// expose the main function to the formula
module.exports = myFunction;

Examples library

We will continue adding examples under this Link where you can find some examples of column and footer formulas.

If you consider that you have a good example, please feel free to add it to the repository via a pull request.

Luxon

Our date library is Luxon. So you can use all the functions of the library in your formulas.

Luxon Examples

If you have a column ID named "Date" you can add time:

  • ${row.Date.plus({years: 1}).toFormat("DD")} adds 1 year
  • ${row.Date.plus({months: 1}).toFormat("DD")} adds 1 month
  • ${row.Date.plus({weeks: 1}).toFormat("DD")} adds 1 week
  • ${row.Date.plus({days: 1}).toFormat("DD")} adds 1 day

Change the date format using 1st January 2000 1pm British Standard time as an example.

  • ${row.Date.toFormat("y")} or "yyyy" shows the year eg: 2000
  • ${row.Date.toFormat("yy")} shows the last 2 digits of the year eg: 00
  • ${row.Date.toFormat("L")} shows the month number eg: 1
  • ${row.Date.toFormat("LLL")} shows the month short name eg: Jan
  • ${row.Date.toFormat("LLLL")} shows the month full name eg: January
  • ${row.Date.toFormat("d")} shows the day number eg: 1
  • ${row.Date.toFormat("D")} shows day/month/year eg: 01/01/2000
  • ${row.Date.toFormat("DD")} shows day/month(short name)/year eg: 01/Jan/2000
  • ${row.Date.toFormat("DDD")} shows day/month(full name)/year eg: 01/January/2000
  • ${row.Date.toFormat("DDDD")} shows day(name),day/month(full name)/year eg: Saturday/January/2000
  • ${row.Date.toFormat("h")} shows 12 hour time eg: 1
  • ${row.Date.toFormat("H")} shows 24 hour time eg: 13
  • ${row.Date.toFormat("m")} shows minute time eg: 00
  • ${row.Date.toFormat("f")} shows day/month/year/ 24 hour time eg: 01/01/2000 13:00
  • ${row.Date.toFormat("ff"}) shows day/month(short name)/year/ 24 hour time eg: 01/Jan/2000 13:00
  • ${row.Date.toFormat("fff")} shows day/month(full name)/year/ 24 hour time timezone eg: 01/January/2000 13:00 BST
  • ${row.Date.toFormat("ffff")} shows day(name), day/month(full name)/year/ 24 hour time, full timezone eg: Saturday, 01 January 2000, 13:00, British Summer Time

If you replace f with F then seconds will be included.