Databases Overview
Learn about Sapience Databases, how they work, what they’re great for, and how the build on the power of the rest of the platform.
Overview
A Sapience Database is a place to keep structured information: clients, deals, invoices, products, candidates, board actions, anything you would normally track in a spreadsheet. It looks and feels like a spreadsheet. Underneath, it is built from the rest of Sapience, and that is what makes it more useful than one.
- Every column has a type. A Currency column only holds money, an Email column only holds valid email addresses, and a Rating column only holds stars. Bad values are refused, so your data stays clean.
- Tables link to each other. An invoice can point at the product it sold, even when the product lives in a different database, and pull in that product's price automatically.
- Rows can be your real work. A row can be one of your existing files, notes, tasks or goals, linked rather than copied, so the database becomes an organised view over things you already have.
- It is shared the Sapience way. You share a database like you share a project, and Sapience checks access on every row, every time.
- Strangers can add to it. A form gives you a public link that turns someone's answers into a new, correctly typed row.
What A Database Is Made Of
Part | What it is | Example |
Database | The container. You name it, share it, tag it and organise it like a project | Sales |
Table | One list inside the database, like a sheet in a workbook. A database can hold several | Invoices, Archive |
Row | One record. Every row has a Name, always the first column | INV-001 |
Column | One typed field that every row in the table shares | Amount (Currency) |
View | A saved way of looking at a table: its filters, sort, grouping and visible columns | Unpaid this month |
Form | A public page that adds a row to one table each time someone submits it | Customer Sign-up |
What Databases Are Great For
Use | How a database helps |
Client or contact list | One row per client, with typed Email, Phone and URL columns that refuse typos |
Deal pipeline | A Select column for the stage, grouped with subtotals so you can see the value at each stage |
Invoices and products | Invoices link to products and look up their prices, so a price change shows everywhere at once |
Document register | A File column holds the actual documents, with Sapience's AI summary of each one beside it |
Board actions or project tracker | Link your existing tasks and goals as rows, then add owner, due date and status columns around them |
Sign-ups, enquiries and applications | Publish a form and every response arrives as a new row, files included, with an email to you |
Start With The Demo Database
Every Sapience user can open the Demo Database. It is a sample business, in the style of a small trading company, that shows what a finished database looks like:
Table | What it holds |
Categories | Product categories, such as Beverages and Dairy |
Suppliers | Who supplies each product |
Products | Each product, linked to its category and supplier |
Customers | The company's customers |
Employees | The sales team |
Orders | Each order, linked to its customer and salesperson |
Order Details | The lines of each order, linked to the products sold |
It uses a wide range of column types, links between its tables, lookups that pull values across those links, and a saved view on every table. It also includes a sample Customer Sign-up form on the Customers table, so you can see how a form is put together.
The Demo Database is read-only. You can browse, filter, group and export it, but you can't change it, and it has no New form button for you. To try things out, create your own database.
Typed Columns
When you add a column you choose its Type. The types are grouped like this:
Group | Types |
Basic | Text, Number, Checkbox, Date, Date and time, Person |
Choice | Select, Multi-select |
Relational | Reference, Lookup, File |
Validated text | Email, URL, Phone |
Numeric | Currency, Percent, Rating (up to 10 stars), Duration |
Each type has its own editor and formatting in the grid. If you type a value the column can't hold, such as an invalid email address, the cell goes back to its previous value and a short message tells you why. A column's type is set when you create it. To store the same information as a different type, add a new column.
How Databases Build On The Rest Of Sapience
Your files, notes, tasks and goals can be rows
Link existing item adds one of your existing Sapience files, notes, tasks or goals to a table as a row. Nothing is copied: the row points at the real item, which stays where it is. You can then add your own columns around it, such as an owner, a status or a review date. Removing the row only unlinks the item. It is never deleted from Sapience.
Files live in File columns
A File column holds real Sapience files. Double-click a cell to pick an existing file or upload a new one, and tick Allow multiple files if a row may hold several. Click a file's chip to open it in the normal file viewer. Turn on Show AI summary and the column shows Sapience's summary of each file beside it, so you can scan a register of contracts or reports without opening each one.
Tables link across databases
A Reference column links a row to rows in another table, in this database or any other database you can read. A Lookup column then reads a value through that link, such as a product's price, and stays current on its own.
Sharing and access work the way they do everywhere else
Click Share to share a database, the same way you share a project or a file. People with write access can add and edit rows, columns, tables, views and forms. People with read access can browse, filter, group and export.
Sapience checks access row by row. If a row points at something a viewer is not allowed to open, such as a colleague's private file, they see that the row exists, but its values stay hidden. The header shows how many rows you can see.
Forms collect rows from anyone
A form publishes a public link. Visitors fill it in without a Sapience account, and each response becomes one new row with the right types, uploaded files included. You get an email for each response, and a Source column shows which rows came from which form.
Databases appear in the Everything view
The Everything view lists a Databases group at the top of its tree. Expand a database to see its views, tables and forms, and click any of them to open it.
Organise a database like anything else
You can rename, tag, move or copy, share and delete a database, much as you do a project. Deleting a database asks you to confirm first, because its rows, views and columns are removed permanently.
Working With The Grid
The grid on the Databases screen is built for real work with numbers:
- + Row opens an editor with every value of a new row, and a quick-add link under the last row adds one instantly. Open any row to edit all its values in one place.
- Group rows by dragging a column header into the grouping bar, for subtotals and a grand total.
- Pivot summarises the numeric columns.
- Filter, sort and save views so you can come back to the same slice of a table.
- Export Excel and Export CSV download what you see.
The grid loads the whole table, up to 5,000 rows, so totals describe the table rather than one page of it. If a table is larger, the header says so.
What Happens Behind The Scenes
- A database is a container, like a project. That is why sharing, tagging, moving, copying and deleting work the same way they do across Sapience.
- A row is a link to a Sapience item. When you add a new row, Sapience creates a small record stored inside the database. When you link an existing file, note, task or goal, the row points at that item instead.
- Every value is checked against its column's type when it is saved, whether you type it in the grid or a visitor submits a form.
- Links are kept consistent. Sapience won't delete a row that other rows still link to without telling you first. If a whole table is deleted, the links that pointed at it show as Deleted rather than vanishing.
- Access is checked on every read. Each viewer sees only the rows and values they are allowed to see.
Who Can Do What
You are… | You can… |
The owner | Do everything: build tables, columns, views and forms, edit rows, share, organise and delete the database |
Shared with write access | Add and edit rows, columns, tables and views, and build and publish forms |
Shared with read access | Open, filter, sort, group and export. No editing |
Anyone, on the Demo Database | Browse it, read-only |
Where To Go Next
Article | Read it to… |
Organising A Database Into Tables | Create a database, add tables and set up typed columns |
Linking Tables With Reference And Lookup Columns | Connect tables and pull values across them |
Totals, Grouping And Pivot In Database Grids | Get subtotals, pivots, saved views and exports |
Collecting Responses With Database Forms | Publish a form and turn responses into rows |
Summary
A Sapience Database is a typed, linked, shareable set of tables. Rows can be your existing files, notes, tasks and goals, tables can link to each other across databases, and a form can fill a table from a public link. Open the Demo Database to see one finished, then create your own at `/#/databases`.