Edit many rows at once

A sheet is a table that a person edits like a spreadsheet. In a browser with JavaScript, the sheet is an editing grid, and one save writes every change. Without JavaScript, the sheet is a read-only list, and each row links to a form that edits that one row.

Use a sheet when a person works down a list. Entering a mark for every student in a class through a form is one page render per student. A sheet makes it one page and one save.

Declare a sheet

page maintain_members "Maintain members" {
  route:  /members/maintain
  access: board

  sheet of members "Members" {
    columns:   last_name, first_name, email, fee_group, active
    where:     left_on is empty
    search:    last_name
    filter:    fee_group
    sort:      last_name
    page_size: 50
    actions:   save, add, delete
    row -> member
  }
}

page member "Member" {
  route:  /members/{member}
  access: board

  form of members "Member" {
    fields: last_name, first_name, email, fee_group, active
  }
}

Every property except actions: is a report’s, spelled the same way and meaning the same thing. A sheet’s rows are the listing’s rows, from the same query.

Write the three required lines

columns:, page_size:, and row -> have no default in a sheet. The compiler reports each missing line.

• columns:. A report can default to every column. In a sheet, that default makes every column of the table editable, including columns that the author never named.
• page_size:. This is the number of rows per page of the list that a reader without JavaScript gets. It does not limit a save, and it does not limit the grid.
• row -> <page>. This is the only way a reader without JavaScript edits a row. The target page must hold a form of the sheet’s own table, and its route must carry the row. The compiler refuses a target that only shows the row, and a form that creates a new row.

The rule is general: a read region can default, a write region cannot.

Know what each reader gets

The engine sends one page, and the browser decides which half the reader sees.

• With JavaScript, the grid replaces the list. The grid loads the rows 200 at a time while the reader scrolls, so it holds every row of the list and has no pager.
• Without JavaScript, the reader gets exactly a report: the table, the search box, the filters, the sort links, and the pager. No cell is editable. The identity cell of each row links to the row -> page.
• On a phone, the grid does not mount. Below 40rem and on a coarse pointer, such as a touch screen, the reader gets the list with its row links. The engine asks this once, when the page loads.

Write help: on a column when a cell needs an explanation, for example max_comments int "Maximum comments" { help: "Empty = unlimited." }. In the grid, the column’s header then has a help button with a question-mark icon, and pressing it shows the text below the header. A screen reader reads the text when the button gets focus. The list without JavaScript does not show help:. The form on the row -> page shows it between the label and the input.

A row link is drawn only when the reader can open the target page. The engine checks the target page’s access rule, not each row. If the target page has a stricter row filter than the sheet, a reader can see a row in the sheet and get a 404 from its link.

One list decides which cells are editable

A sheet has no second list of editable columns. Whether a cell is editable is derived.

A cell is read-only when the engine owns the column, when the column is derived, or when the reader’s role does not grant the column. So you can put a slug or a count in columns: as read-only context that tells somebody which row they are on.

A save that names a read-only column does not write it. The engine decides that on the server, so a crafted request cannot write the column either.

What one save does

One save is one transaction. If any cell fails a check, nothing is written. The grid keeps every edit on the screen and shows each message on its cell.

The grid sends only the rows that somebody changed. The engine then compares each sent row with the stored row inside the transaction and writes only the rows that really changed. An unchanged row is not a statement, so it does not move updated_at and does not run an update hook.

Two people on one screen

Each row carries a stamp of the values that the grid loaded. If a row changed after the grid loaded it, the save is refused and names the rows that moved.

A sheet is the screen two people open at once. A silent overwrite erases somebody’s work with no trace and no way to notice, so it is not the default here.

Let people remove rows

Add delete to actions:.

actions: save, delete

To remove rows, select them and press Delete rows, or right-click and choose Delete rows. The grid marks the rows. Undo removes the mark. Nothing is removed until the save. The save removes the marked rows first, in the same transaction as the cell changes.

Two behaviors surprise most authors:

• A marked row is not checked at all. An empty required cell in that row does not stop the row from being removed.
• A marked row whose values changed is refused, not removed. The row stamp guards a removal too. Removing a row that somebody else just edited destroys their work, so the save is refused and names the row.

If another table still references the row, the save is refused. The message says the row is in use and names it, and nothing at all is written.

A before delete hook and an after delete hook run for each removed row, exactly as they do for a form. The slug of a removed row stays reserved, so a new row cannot take the address that the removed row had.

Let people create rows

Add add to actions:.

actions: save, add

To add a row, press Add row, or right-click and choose Insert row. A paste that reaches past the last row also adds rows. One save can create any number of new rows. The engine creates them last, in the order they appear, in the same transaction as every cell change. Either every new row lands, or none does.

A new row starts with each column’s default:. The engine also gives it the default lifecycle state and the application’s scope column. A set on the region fills its column too. A row created in a sheet is therefore as complete as a row created in a form. A read-only cell, such as a slug or a count, stays empty until the save.

Two behaviors surprise most authors:

• A new row that still holds only its defaults creates nothing. The grid does not send it, and it does not stop a save. The engine compares each cell with the default that the row started with, so a date column with default: today does not create a row on its own.
• A new row with one cell filled in is checked, not ignored. That row is a row somebody started, so every rule of the table applies to it. If it is refused, nothing is written, and each message shows on the cell it belongs to. A message about the row as a whole names it, for example “New row 3”.

Two new rows with the same unique value are refused on the later row.

An empty sheet with add shows its empty: text above the grid and still offers the grid, so that somebody can create the first row. A filter that matched nothing shows the engine’s own sentence instead.

Give every required column a way to be filled

A sheet with add must be able to fill every required column of its table. Satisfy each one in one of four ways:

• name the column in columns:, where the reader can write it;
• give the column a default:;
• write a set for the column on the region;
• let the engine own the column, as it owns slug and the audit columns.

A sheet that leaves one required column unsatisfiable is refused when the tenant is opened. The message names the column and all four repairs. Without that refusal, every row somebody added would be refused at the moment they saved it.

A login id is the common case, and set is the answer:

sheet of notes "Maintain notes" {
  columns:   title, released, folder
  page_size: 25
  actions:   save, add
  row -> note

  // `owner` is `int required`, has no default, and is in no `columns:`.
  set owner = :user.id
}

A before insert hook and an after insert hook run for each created row, exactly as they do for a form. The before hook runs inside the transaction, and what it changes is checked again against every rule the row already passed. The after hook runs after the commit.

A sheet whose where: reads a route parameter cannot use add, and the engine refuses it when the tenant is opened. A new row has no cell for the column that the condition is about, so every added row would be refused. Create the row in a form on its own page instead.

Fill a sheet from a spreadsheet

You write no DSL for the grid. There is no property that turns it on, and none that turns it off. Arrow keys move between cells, typing starts an edit, and a range works as it does in Excel. Regions lists every key.

To copy a column out of Excel into a sheet:

1. Copy the cells in Excel.
2. Click the first cell of the target range in the grid.
3. Press Ctrl+V. On a Mac, press Command+V.
4. Read the line under the grid. It reports how many cells were filled and how many rows were added.
5. Correct the cells that show a message, then save.

If the sheet offers add, a paste past the last row adds rows. If it does not, the rows past the edge are not pasted. Columns past the last column are never pasted, and a paste never writes a read-only cell.

A dropdown cell holds a label, such as a member’s fee group. Copy it, and you get the label. Paste a label back, and the grid matches it to an option exactly, including upper and lower case. If no label matches, the grid tries the stored value. If neither matches, the cell keeps the value it had, and the line under the grid tells you. It names one such cell, for example Status has no option called Nope. For more cells, it gives the number, for example 3 cells matched no option and kept their value. Correct those cells before you save.

To continue a value down a column, select the source cells and drag the small square at the corner of the selection. A whole number, an amount, and an ISO date continue a constant step. One date steps one day, and Task 1 becomes Task 2. A datetime, a checkbox, a dropdown, and text without a trailing number repeat instead.

Ctrl+Z undoes the last edit, paste, fill, clear, added row, or removal mark. Press it again to go further back, up to 200 steps. Ctrl+Y puts a step back.

Work on more rows than fit on the screen

Scroll. The grid loads the next 200 rows before you reach the last loaded row, and your edits stay where they are.

A save carries only the rows you changed, so a long list and a short edit are still one small request. If you change more rows than one save can carry, the grid says so before it sends anything. Save in several steps.

A row that another person moves into a part of the list that the grid already loaded appears after the next reload.

Sort and filter with unsaved edits

A header click sorts on the server, through the list’s own sort link. The filter form reloads the list too. Both reload the grid.

If the grid holds unsaved edits, it asks before it follows either one. It also asks before the reader leaves the page. Save first, then sort or filter.

Read the message after a save

The message appears above the grid. It is built from up to three counts, always in one order: rows changed, rows added, rows deleted. A count of zero is left out, so a save that only created a row reports only that. A save that changed nothing says so.

Limits to plan around

• One save carries at most 200 rows, or 50 rows when a before hook runs on each row. The count is the changed rows plus the removed rows plus the new rows. A before delete hook counts only when the sheet offers delete, and a before insert hook only when it offers add. A save over the limit is refused before anything is written.
• Two rows cannot swap a unique value in one save. Clear one, save, then set the other.
• A dropdown cell of a ref column offers at most 200 rows of the target table. A label past that limit cannot be chosen or pasted.

Status: actions: save, actions: add, and actions: delete all work, in Chromium and WebKit, against the real engine. An img column in columns: is accepted by the compiler and refused when the tenant is opened, because it has no editor yet. Edit a picture in a form on the row’s own page. The grid has not been tested with a screen reader yet. See Project status.