Tables

A table holds your records. Builders and owners design tables, and see the New table and Import… buttons. Editors, builders and owners enter and change records. Viewers can read them.

Create a table and add fields

1. In the Tables group of the navigation pane, choose New table. A design tab opens.
2. Type a name in the empty row at the bottom of the field grid. Each new name adds a field.
3. Choose the field's Data type.
4. Select the field to set its properties, such as Required or Default value, in the panel beside the grid.
5. Choose Preview changes, read the impact report, then choose Apply.

Nothing changes until you apply it. Discard changes drops your unapplied design changes.

Every table gets an AutoNumber field called id automatically. The database numbers it, so you cannot change its type, move it or delete it. You can rename it.

Each field has a field name (what people see) and an SQL name (used in queries and scripts). The SQL name starts as the field name with spaces replaced by _, so Unit price becomes Unit_price. The table name works the same way: the table Order lines is Order_lines in SQL. SQL names start with a letter, use only letters, digits and _, are at most 63 characters, and are unique within the table regardless of case.

Field types

Locations and areas

A Location field (data type Geo point) holds one point and an Area field (data type Geo area (fence)) holds a boundary such as a quarantine zone or a survey area. Both are stored as standard GeoJSON text, in WGS84 with the longitude first, so scripts, exports and the API can read them directly. Use an ordinary number field for anything about the position, such as GPS accuracy.

• Where values come from. The API and scripts accept GeoJSON. A location also accepts {"lat": 51.50135, "lon": -0.14189} and the text 51.50135, -0.14189, and an import maps text such as 51.50135, -0.14189, POINT(-0.14189 51.50135) or GeoJSON into a Location. A GeoJSON Feature is accepted anywhere a geometry is: its geometry is used.
• What is stored. Coordinates are rounded to 7 decimal places (about 1 cm). Rings are closed, outer rings are stored counter-clockwise and holes clockwise. A location may carry an altitude as a third coordinate; it is kept.
• Limits. Latitude must be from -90 to 90 and longitude from -180 to 180. Every ring needs at least 4 positions (the first repeated at the end). An area may have at most 10,000 positions and 1 MiB of GeoJSON. An area must be a Polygon or MultiPolygon.
• The 180° meridian. Areas that cross the 180° meridian are not supported and are rejected. Draw such an area as a MultiPolygon with one polygon on each side of the meridian.
• No spatial index. Spatial filters and joins scan the table, which is fine for tens of thousands of locations. A spatial join compares every location with every area, so keep both sides modest.
• Changing the type. A Location or Area can be changed to short or long text (a location becomes lat, lon, an area becomes its GeoJSON) and back, as long as the text is valid. Changing to or from any other type, or between Location and Area, is reported as a breaking change because the values cannot be converted.

Enter a location

In the datasheet, a location shows as latitude, longitude with 5 decimals, for example 51.50135, -0.14189. Select the cell and press Enter or F2, or double-click it, to open the map editor:

• Type the Latitude and Longitude in decimal degrees. Show on map moves the map to what you typed.
• Or click the map to place the point, then drag the marker to adjust it.
• Use my location asks the browser for your position, using GPS when the device has it. The editor shows how accurate the fix is, for example Location accuracy: about 12 m. If the table has a number field whose name contains "accuracy", such as GPS accuracy (m), the editor offers to fill it in too.
• Clear empties the field. Save location saves the record; Cancel or Esc leaves it unchanged.

The browser asks once whether AbiData may use your location. If you refuse, or the device cannot get a fix within 20 seconds, the editor says so and you can still type or click. Location needs a secure connection (https://), so it is not offered on a plain http:// address other than localhost.

Draw an area

An area shows as a summary such as Area · 12 points · 3.4 km². Open its cell the same way to draw it:

• Click the map to add corners. The outline closes itself; you need at least 3 corners. The size is shown as you draw.
• Drag a corner to move it. Click a corner to select it, then press Delete or Backspace to remove it. A new corner is added after the selected one.
• Add hole starts a hole inside the outline, for example a lake inside a survey area. Add part starts a separate polygon in the same area.
• Undo (Ctrl+Z) reverses the last change and Clear removes everything.
• Paste GeoJSON reads a Polygon, MultiPolygon, Feature or FeatureCollection, for example one exported from a GIS; choose Use this area to load it.
• Corners lists every corner as latitude and longitude boxes, so an area can be drawn or corrected with the keyboard alone.
• Save area saves the record.

On a phone

In the record sheet on a phone, a location field has a large Use my location button, so a sighting can be recorded with one tap where you stand. The map editors open full screen with large buttons. Set up a number field for GPS accuracy so that each sighting records how precise it is.

Filter by location

Choose Location filter… in the datasheet toolbar. Pick the Location field, then either:

• Within an area: draw the area on the map. Records whose location lies inside it are kept (the edge counts as inside).
• Near a point: type or click a point and set the Distance (metres).

Filter by selection on a location keeps records within 1 m of it.

Maps and privacy

By default maps show a plain latitude/longitude grid and contact no outside server, so they work offline. An administrator can set a map tile server to show streets or satellite images underneath (see Configuration). When one is set, your browser asks that server for the map images of the areas you view.

SQL functions

These functions work in queries, calculated fields and validation rules. Each returns empty (NULL) when an input is not valid, so one bad row cannot break a query.

For example, a calculated field with result type Location and expression geo_centroid(Fence) gives each zone's centre, and geo_distance_m(Spot, geo_point(51.5, -0.14)) < 500 is a validation rule or criterion.

A calculated field that uses these functions cannot be read by stock SQLite tools outside AbiData, because they do not know the functions. The stored values are plain GeoJSON text and read normally.

Required fields, defaults and validation

• Required rejects empty values. Saving a record without one fails with REQUIRED_FIELD.
• Default value is a fixed value such as 0 or 'New', or an expression without fields, such as CURRENT_DATE. It fills the field in new records.
• Validation rule is an expression every row must satisfy, for example Qty > 0.
• Validation text is the message shown when the rule fails (VALIDATION_FAILED).

Tick Hidden in the datasheet to hide a field from the datasheet. Hidden fields still exist and still appear in queries.

An index speeds up searching and sorting. A unique index also rejects a second record with the same value.

• Indexed is a field property with three choices: No, Yes (duplicates OK) and Yes (no duplicates). The last one makes the field unique. The ID field, and fields that belong to a multi-field index, do not offer it.
• Indexes (the button above the field grid) lists every index of the table, including ones over several fields. To add one, choose up to 16 fields in order, tick Unique (no duplicate combinations) if needed, and choose Add index. To remove an index, use its trash button.

Index changes are part of the design draft. They take effect through Preview changes and Apply, like any other change. If the table already holds duplicate values, a new unique index is reported as breaking and cannot be applied until the duplicates are fixed. Deleting a field also deletes every index that includes it. Once a unique index exists, a duplicate fails with DUPLICATE_VALUE. Unique text indexes compare case-insensitively, so abc and ABC are duplicates.

Relationships and lookups

A Lookup field links each record to one record in another table (the parent).

1. Add a field and set its Data type to Lookup.
2. Under Lookup settings, choose the parent table in Look up values from table.
3. Choose the Display field: the parent field to show in place of the record number.
4. In When the linked record is deleted, choose what happens:
   • Block the deletion (RESTRICT): the parent cannot be deleted while records link to it. This fails with LINKED_RECORDS.
   • Delete this record too (CASCADE): deleting the parent deletes the linked records.
   • Clear the link (SET NULL): deleting the parent empties the link.
5. Leave Enforce referential integrity ticked unless you are loading data that is not yet consistent.

In the datasheet, a lookup cell is a combobox that searches the parent table as you type.

Calculated fields

A calculated field holds an expression over the other fields in the same table, such as Quantity * Unit_price. Choose a Result type: Text, Integer, Number, Currency, Yes/No, Date or Date and time. The expression must give the same answer every time, so it cannot read the clock or use random numbers. Calculated fields cannot be edited in the datasheet.

An expression saved by an earlier version that falls outside the expression language still works, and the designer marks it needs attention with the reason. Rewrite it when you next edit the field.

Files and photos

An Attachments field keeps files with a record: for example several phone photos of an invasive-species sighting, a permit PDF, or a specification sheet. Choose Attachments as the field's Data type. In the field's properties, Files per record sets how many files one record can hold, from 1 to 100 (the default is 20). Use 1 for a single document.

• Add files. Select the cell and press Enter or F2, or double-click it. A dialog opens with the files of the record. Drag files onto it, or choose Browse. Each upload shows its progress. Choose Done to keep the changes, or Cancel to throw them away. As with any other field, the record is saved when you press Shift+Enter or leave the row.
• On a phone, the record sheet shows the gallery inline with a large Take photo button, which opens the camera. Browse picks files from the device. A photo is uploaded while you stay on the sheet; Save waits until it is done.
• Manage files. Thumbnails show images; other files show an icon with their name and size. Move earlier and Move later reorder the files (you can also drag a thumbnail onto another), Remove takes a file off the record, and Download saves the original. Choose a thumbnail to open the full-screen viewer, where the arrow keys or the Previous image and Next image buttons move between images and Esc closes it.
• In the grid, a cell shows the number of files and small thumbnails of the first three.
• Limits. The field holds at most Files per record files; the dialog says so and stops accepting more. One file can be at most 25 MiB unless your administrator changed the limit, and the whole workspace has a file storage quota. An upload that is refused shows the reason beside the file: the file is too large or empty, the storage quota is used up, or your role may not upload files. Viewers can see and download files but cannot change them.
• Thumbnails. PNG, JPEG and GIF files have thumbnails. WebP images are shown but have no thumbnail, and HEIC photos are kept as downloads.
• No default, no index. An attachments field cannot have a default value or be indexed. It cannot be converted to or from another type: add a new field instead.
• Deleting. Removing a file from a record does not delete it at once. A file that no record refers to is purged after a grace period of 7 days, and the record's history keeps naming it until then. After that the history shows "file removed".

Files are also available to scripts and over the API.

Enter and edit records

Click a cell, or move with the arrow keys. Press Enter or F2 to edit the cell, or start typing to replace its content. In an Attachments cell, Enter and F2 open the file dialog.

• Tab and Shift+Tab move between cells.
• Shift+Enter saves the record. Esc discards your unsaved changes to the record.
• When you move to another row, AbiData saves the row you left. A half-edited record therefore does not reach other people until you leave it.
• The empty row at the bottom adds a new record. To add records without scrolling there, choose Quick Add in the toolbar or press Mod+Shift+N: a row pinned under the column headers takes the values. Enter saves the record, clears the row and keeps it open for the next one; Esc closes it. Lookup, file and area fields are not in the quick-add row: use the new record row for them.
• To delete records, click a row header to select a record. Shift+click selects a range, and Ctrl+click (⌘ click on Mac) adds one. Then press Delete or choose Delete record in the toolbar. AbiData asks you to confirm.

To keep identifying columns in view on wide tables, open a column's menu and choose Freeze columns up to here: that column and the ones before it stay in place while the rest scroll sideways, with a heavier line marking the edge. Unfreeze all columns undoes it. The setting is saved for you per table.

To work on a block of cells, drag across them, Shift+click, or hold Shift with the arrow keys. Mod+C copies the block as tab-separated text that pastes into spreadsheets. Pasting several cells (from a spreadsheet or from AbiData) opens a preview: it shows the target, whether each value converts for its field, and lets you overwrite the cells or insert the rows as new records. Nothing changes until you choose Paste, and the whole block is saved in one step, so it is applied entirely or not at all. A paste covers up to 1,000 records. Lookup, file and read-only fields are skipped.

To inspect a column, open its menu and choose Column statistics & profiling. The dialog shows, for the records in the current view, the number of rows, how many are empty, how many distinct values there are, and the five most frequent values with bars. Number, currency and date columns also show the minimum, maximum, mean, median and the 25th and 75th percentiles. Very large tables are profiled from their first 20,000 records, and the dialog says so.

Formatting rules

Choose Formatting Rules in the toolbar to colour cells or whole records when a field meets a condition, such as a date that is overdue, a quantity below 5 or an empty field. Each rule names a field, a condition, whether it colours the cell or the whole record, and a colour (danger, warning, success, info or accent). Rules are checked from the top and the first match wins; move them up or down to change the order. A highlighted cell also shows an icon and a hidden text label, so the meaning does not depend on colour. Rules are saved for you in this browser, per table.

Right-click a cell for its actions: Copy, Paste, Edit cell, sorting, Filter by selection, Record history, New record and Delete record. Right-click a column heading to sort it or move it left or right. Mod+C copies the current cell, and Mod+V pastes into it. The context-menu key or Shift+F10 opens the same menu from the keyboard. A cell you are editing keeps your browser's own menu.

See Keyboard shortcuts for more.

Other people's changes appear live. The status bar shows Updates: Live while the connection works.

Conflicts and the merge dialog

Every record has a version number. When you save, AbiData checks that the record has not changed since you started editing it. Nothing is ever overwritten silently.

If someone else saved the record first, AbiData compares field by field, using the values as they were when you started editing as the starting point:

• Different fields. If the other person changed fields you did not touch, or other fields than the ones you changed, your changes are saved on top of theirs. You see a short "Merged with changes made by someone else" message and no dialog.
• Same value. If the other person changed a field you also changed, but to the same value, there is nothing to decide.
• Same field, different values. Only then does the Someone else changed this record dialog open. It lists just those fields, with the value Before you started, Theirs and Mine. Choose Mine or Theirs for each, then choose Save merged. Your other, non-overlapping changes are saved along with your choices. Discard mine drops your edits and shows the other person's version.

Fields you did not change always keep the latest saved value. If several people keep saving the same record while you merge, AbiData tries a few times and then opens the dialog. If the other person deleted the record while you were editing it, your edits cannot be saved and you are told the record no longer exists. The underlying error is CONCURRENT_MODIFICATION.

On a phone, saving from the record sheet works the same way.

Filter and sort

• Filter records searches the text columns for what you type. Press Enter to apply it.
• Filter by selection adds a condition based on the selected cell, for example "Status equals Open". Each condition appears as a chip with a remove button. Clear filter removes all of them.
• Click a column header to sort by it. Click again to reverse the order. Shift+click other headers (or choose Then sort ascending or Then sort descending in the column menu) to sort by several columns: each sorted header shows its priority and direction, such as 1 ▲ and 2 ▼, and a Sorted by bar lists the order. Clear sort removes a column from the sort, and Clear all sorting removes them all. The column menu, which has the column name on its button, also offers Sort ascending, Sort descending and Clear sort.
• To resize a column, drag its edge, or select its header and press Alt+Left or Alt+Right. Column widths and order are remembered in your browser, on this computer.

Filters and sorts apply only to your view. To save a selection for others, create a query.

History

In Design, tick Track history of changes to rows. From then on, every insert, update and delete made through the app is recorded with the user, the time, and the values before and after.

Viewing a record's history

Anyone who can read the table can see a record's history, including the history of deleted records.

• In the datasheet, select a cell in the record and choose History in the toolbar.
• In a form, open the record and choose History in the form toolbar. It is available once the record is saved.

A panel opens with the changes, newest first. Each entry shows who made the change and when, and what changed. A new record lists its initial values and a deleted record lists its last values. An update lists only the fields that changed, as old value → new value. Fields that were later removed from the table appear as "(removed field)". An Attachments field lists the file names; a file that has been purged shows as "file removed". Choose Load older to see earlier entries. Press Esc or choose Close history to close the panel.

If the table does not track history, the panel says so. A builder can turn tracking on in Design. Only changes made after tracking was turned on are recorded.

History is written by the datasheet, by forms and by the records module in scripts. Bulk imports and changes made with db.exec in a script are not recorded.

Changing a design safely

Preview changes lists everything a change would affect: queries, forms, reports and scripts that use the field, rows that cannot be converted to a new type (with sample rows), and rows that break a new rule or index.

• Breaking issues are listed under Breaking. Constraint and foreign-key problems cannot be forced, so fix those rows first.
• Other breaking issues can be applied once you tick I understand this may break the listed objects and choose Apply anyway. Without that confirmation the change is refused with SCHEMA_CHANGE_BLOCKED.
• Warnings are listed separately and do not stop Apply.

Destructive changes take a restore point first. The impact report says "A restore point will be taken first". See Backups.