Databases inside your notes

A database is a table that lives inside a note. Each column has a type (text, number, date, choice, relation and more), so Filarr knows how to show it, sort it and count it. Use one to track books, clients, invoices or the tasks of a project.

The database is saved in its note: it is encrypted and synced with it, like the rest of the note.

Create a database

1. In a note, type /database, choose Database and press Enter. The Database button of the fixed toolbar, if you show it, opens the same panel.

   

2. The New database panel opens, with the cursor in Name. Type a name, then press Enter.

   Filarr inserts the database where the cursor is, with two columns, Name and Status, and three empty rows. Status offers To do, In progress and Done, and every new row starts at To do. The Database “…” created message offers Undo, which removes the database. To rename it later, click its title.

   

Before creating, the panel sets up the rest, and the Preview on the right shows the database as it will be created:

• Start with: Columns (an empty database, columns of your choice), A template (Tasks, Reading list, Groceries, Budget, Feedback & bugs and more), Import (a note, pasted text or a file: CSV, Excel, PDF, Word, image…) or A folder (one row per file of the vault, see Turn a folder into a database).
• First view: Table, Board, Calendar, Gallery, Timeline, Charts or Form. More views can be added later, on the same data.
• Starting columns: Status, Date, Priority, Owner, Tags, Number, Checkbox and Link. The column the first view needs stays checked, with a lock: the date for a calendar or a timeline, the column that sorts the cards for a board.
• Board columns: for a board, rename, remove or add its columns, and choose whether cards are sorted by Status or by Priority.

When the name suggests a template (“Reading”, “Budget”, “Tasks”…), the panel says so: click Use to start from that template.

The same menu offers Database · Board, Database · Calendar and Database · Gallery: the panel then opens on that layout. Database from a note… creates the database and opens the import right away. Later, Import, in the header of the database, fills an existing database from a note, pasted text or a file: see Import a table.

Track feedback and bugs

The Feedback & bugs template sets up a database for what goes wrong, and what got fixed:

• Type (Bug, Improvement, New feature, Question), Status (To triage, To do, In progress, To verify, Resolved, Dropped), Priority and Area;
• Platform (Desktop, Web, Mobile), Version, Link, Description and Follow-up;
• Reported, Resolved on, and Days to resolve, which computes itself: from Reported to Resolved on, or from the day the entry was added when Reported is empty. An entry resolved before it was added has no delay.

A new entry starts as To triage. Set it to Resolved: Resolved on takes today's date by itself, and is erased if you reopen the entry. For a bug fixed on another day, fill in Resolved on yourself, including in the New entry form, where only the title is required.

Ready-made views: the board by status, Open bugs (bugs neither resolved nor dropped, most severe first), By area, the Resolutions calendar and a chart by area. The suggested areas are generic: rename them, or type a new area in a cell to add it.

Import a table (file, paste, screenshot)

Import reads three kinds of sources, whether you open it from the database header, from Database from a note… or from the New database panel:

• A note: its lists, tasks, tables, and "Key: value" records.
• Pasted text: a list, cells copied from Excel or Google Sheets, a table copied from a web page or from Word (multi-line and merged cells included), a Markdown table, JSON, or a screenshot of a table.
• A file: CSV or TSV (even saved as "ANSI" by Excel), Excel (.xlsx, .xls), LibreOffice (.ods), PDF, Word (.docx), JSON, HTML, Markdown, a SQL database (.sql, .sqlite) or an image (PNG, JPG…). Choose a file… takes it from your device, From the vault… takes a file already stored in Filarr, such as a bank statement or a received invoice. You can also drop the file onto the panel.

What is read:

• A workbook gives one table per sheet. Hidden sheets are not read. Dates, amounts, percentages and checkboxes arrive typed, according to the cell format.
• A PDF is read from the position of its text: right-aligned amounts, labels on two lines, a table that continues on the next page (its repeated header disappears). A scanned PDF has no text: Recognize the text reads it with character recognition.
• An image goes straight through character recognition. Check the preview before importing.

Before importing, the panel lets you choose:

• Which table: a sheet, a page or a table of the document. The largest one is offered first.
• Column names: the detected row (the title of a statement, above it, is left out), another row, or none.
• Rows named after: the column that names the rows, such as the label of a statement rather than its date.
• the columns kept, their type, and a preview of the first rows. Nothing is written before Import.

For a list or pasted text, more boxes appear when they help:

• Split at “—” into columns, or at whichever separator most lines share (:, ;…). When only a few lines have a dash, the box becomes Text after “—” goes to “Additional info”: the title keeps what comes before the dash.
• Shorten long titles: a paragraph keeps its first sentence as its title, and its full text, as written, goes to Details.
• Recognize dates, amounts, #tags, @people, ratings…: each recognized pattern goes to its own column. In the middle of a long sentence, an @person stays in the text; only one at the start or the end refers to someone.

A whole SQL database. A .sql file exported by SQLite, MySQL, phpMyAdmin or PostgreSQL, or a .sqlite file, becomes several linked databases. The preview shows each table and its rows, what each foreign key becomes (a relation to the target table, with its backlink) and each link table (a relation to several rows), then what could not follow. Uncheck the tables you don't want: a relation to a table left out stays a plain value. The databases take the place of a new database, otherwise they are added just below it.

To fill an existing database, each imported column joins the column with the same name. An empty column of the file joins its own too, instead of creating a second one.

The ⋯ button of the header (Database settings) holds two more settings. Show a vault folder… turns the database into a list of files: see Turn a folder into a database. Lightning source fills rows from a catalog of books, anime, TV shows or movies: see Fill rows automatically. Views, filters and sorts are explained in Database views, filters and sorts.

Add rows and fill in cells

• Click New row, at the top right or at the bottom of the table.
• Click a cell to type or choose a value: a date opens a calendar, a Select column opens its list of options, a Rating column fills as you click its stars, a Checkbox ticks in one click.
• Move from cell to cell with the arrow keys.
• To reorder rows, drag the handle that appears in place of the row number when you hover over it. While a sort is on, rows follow the sort and do not move by hand.

The table shows the first 100 rows. A Show … more button at the bottom loads the next ones.

Add and set up columns

1. Click + at the end of the header row. A new column appears, named "Column 3" for example, of type Text, and its settings open.
2. Type its name.
3. Pick its type in the grid.

Click a column's header at any time to open its menu:

• Type changes the type. Filarr keeps the values already typed, even if the new type cannot show all of them.
• Edit property opens the full settings: options of a choice, target of a relation, formula.
• Filter on this column, Sort ascending, Sort descending, Calculate: see Database views, filters and sorts.
• Group by this column, on a Select column only, turns the view into a board with one column per option.
• Hide this column hides it in this view, without deleting its data.
• Insert left, Insert right, Duplicate property, and the two arrows to move the column.
• Delete property deletes the column and its values in every row.

To resize a column, drag the right edge of its header. Double-click that edge to fit the column to its content.

The 19 column types

Type	What it holds
Text	Free text. The first Text column gives each row its title.
Number	A number, which the footer can sum or average. In Edit property, Format shows it in euros, dollars, pounds or as a percentage.
Select	One option from a list you define, each with a color.
Multi-select	Several options from the list.
Checkbox	Checked or not.
Date	A day, picked in a calendar or typed.
Person	Names you type, separated by commas in the table, or one at a time with Enter in the record. They are plain names: no one is notified.
URL	A web address.
Email	An email address, with Send an email.
Phone	A phone number, with Call this number.
Rating	From 1 to 5 stars.
Progress	A percentage from 0 to 100, shown as a bar.
Note	A link to one of your notes.
Vault file	A link to a file of your vault, chosen with Pick a file.
Relation	Links to rows of another database.
Rollup	A calculation across the linked rows.
Formula	A value computed from the other columns.
Created time	When the row was created. Filarr fills it in.
Last edited time	When the row last changed. Filarr fills it in.

The grid of the column menu shows 17 types. Created time and Last edited time are in Edit property, since you never type in them. Relation, Rollup and Formula have their own article: Relations, rollups and formulas.

A Number column separates thousands, so the year 2016 shows as "2,016". For years, use a Text column.

A date that fills itself

A Date column can take today's date when a row moves to a stage: "Resolved on" when the status becomes Resolved, "Read on" when a book becomes Read.

1. Open Edit property on the Date column.
2. Under Fills itself, choose the Select column that triggers it, for example Status.
3. Check the option or options that set the date.

The date is only set when it is empty: a date typed by hand stays. It is erased when the row leaves these options. An import never sets a date. By hand removes the setting.

Options of a Select or Multi-select column

In Edit property, under Options:

• Add an option, then type its name;
• click the colored dot to change its color and its Intensity: Subtle, Medium or Strong;
• click the star to make it the Default value: new rows start with it;
• click × to delete the option.

Work with a row

At the end of each row, small buttons let you:

• create a page for the row, or open it (Create page, Open page);
• Open as a page: show the row as a record on the side;
• Duplicate row, right below the original;
• Delete row. Its sub-items stay: they move up one level.

The record

Open as a page shows every property of the row, including those hidden in the view, marked hidden. The arrows at the top go to the previous or next row. From the record you can also use Graph, Create page, Save as template, Sub-item of and Add a sub-item.

A row and all its connections

Graph, at the top of the record, puts the row at the center of a graph with all its connections: the rows it links to through its relations, and those that link to it, across all your databases. Each database has its color.

• Depth goes from 1 hop (the rows linked directly) to 3 hops (the neighbors of the neighbors, then the next ones).
• Click a dot to read its record. Double-click it, or click Explore from here, to center the graph on it; Back returns to the previous one.
• Open the note opens the note of that dot's database.
• The mouse wheel zooms, dragging the background moves the view, dragging a dot places it elsewhere. With the keyboard: + and - zoom, the arrows move the view, 0 fits the graph, Enter explores the selected dot and Esc clears the selection.

The graph changes nothing. A Query view also shows its result as a graph: see Query.

A page for a row

Create page turns the row into a regular note. At the top of the note, Row properties shows the values of the row, with the name of its database. Write anything below: notes, tasks, images. The title of the page follows the row's first Text column. The next time, the button reads Open page.

Sub-items

Add a sub-item creates a row attached under this one. Sub-item of attaches the row to another. In the table, a parent row has an arrow to fold or unfold its sub-items.

Row templates

1. Open a row as a record and fill in the values you reuse.
2. Click Save as template, type a name, then click Save.
3. Next time, click the small arrow next to New row, at the bottom of the table, and pick the template.

The × next to a template in that menu deletes it.

The schema of your databases

Database schema (Merise), in the ⋯ menu of a database or in the command palette, draws your databases the Merise way:

• Conceptual (MCD): each database is an entity with its columns, each relation an association with its cardinalities (0,1 for a single-row relation, 0,n otherwise). A database that is targeted but missing shows with a dashed border. Drag the boxes: their place is kept on this device. Rearrange goes back to the automatic layout.
• Logical (MLD): one table per database, a foreign key (with a leading #) for a single-row relation, a junction table for a multiple relation. The primary key is underlined.
• SQL: the creation script for SQLite, PostgreSQL or MySQL, with or without the rows. Copy or Download it to open it in another tool.

Opened from a database, the schema first shows This database and its neighbors; Whole vault shows all your databases.

You can also edit the schema:

• New entity creates a database with a Name column, in the note of the database you opened the schema from, or in the open note.
• Draw an association, then drag from one entity to another, or onto itself. Choose the name of the relation, whether each row is linked to one row (0,1) or to several (0,n), and the name of the backlink. The relation and its backlink are created in both databases. With the keyboard, select an entity, then Link to….

A note open in several panes cannot be edited this way: close one, then try again.

Large databases

The whole database is stored in its note, and each change encrypts and uploads the whole note again. Past 256 KB, a gauge in the bar of views shows the weight of the database. Past 1 MB, split it into several databases.

Related articles

• Database views, filters and sorts
• Relations, rollups and formulas
• Fill rows automatically
• Turn a folder into a database
• Build a reading tracker