User guide
Databases
You want to keep track of something with more shape than a spreadsheet. This is the database program, from a template to a printed report, and what to ask the agent for.
A database in XataWorks is a relational database in the tradition of FileMaker. You keep your records in tables that relate to one another, and you work with them through layouts you design: a form for one record at a time, a list, or a table. Fields can calculate and summarise, value lists keep entries consistent, finds narrow what you see, and scripts and buttons turn a routine into one click. Reports group and total your records, and you can preview them, print them or save them as a PDF.
It is for anyone who keeps track of things that belong together: events and their guests, invoices and their lines, equipment and who has borrowed it. You can design a database yourself, start from a template, or ask the agent to build one and work in it while you watch.
What kind of database this is
The pieces will be familiar if you have used FileMaker, and they fit together the same way:
- Tables and relationships. Each kind of thing has its own table, and relationships connect them, so an event knows its guests and its expenses.
- Layouts. A layout is a saved arrangement of fields for one table. You see it as a form, a list or a table, and a database can have as many layouts as you need.
- Calculation and summary fields that work their values out from other fields, including fields in related records.
- Value lists behind drop-down lists, pop-up menus, checkboxes and radio buttons, and lookups that copy a value in from a related record.
- Portals: a related table’s records shown inside a record, such as the guests listed on an event’s form.
- Finds with the familiar symbols, found sets you can narrow, widen and sort, and scripts you run from a menu or a button.
- Import and export of CSV, tab-separated and Excel files, and sub-summary reports you can preview and print.
Each database is a single SQLite file that XataWorks keeps in its own data folder, not in your Documents folder. Every chat in the app sees the same databases.
Making a database
From the File menu
Choose File ▸ New ▸ Database. The database list opens in the chat’s browser panel with the New Database gallery showing. To get back to the list later, use File ▸ Open ▸ Database… or View ▸ Databases ▸ All Databases; how programs open beside the chat is covered in The office.
Pick Blank database to design everything yourself, or pick a template. A template fills in a suggested name and shows Include sample records, ticked, so the database arrives with records to try things on; untick it for an empty copy of the design. Type a name in Name and choose Create. A database made from a template opens straight at its first layout; a blank one is added to the list for you to open.
The templates
Each template brings its tables, layouts, value lists, calculations and scripts. The dates in its sample records are moved so they look current.
| Template | What it holds |
|---|---|
| Contacts | People and the companies they work for, with categories, birthdays, and each company’s people shown on its form. |
| Projects & Tasks | Projects, their tasks and the team doing them, with due dates, status and how far along each project is, and a script to mark a task done. |
| Inventory | Products with pictures, suppliers, and stock coming in and going out, with reorder alerts and a stock value report by category. |
| Invoices | Customers, products, and invoices whose lines add themselves up, with a printable invoice and a sales report by customer. |
| Events | Events with their guests and expenses, with RSVPs, a headcount, a budget that adds itself up and a guest list to print. |
| Equipment Checkout | Equipment lent to people, with what is out, what is overdue, and a button to check things back in. |
| Time Billing | Clients, projects and the hours worked on them, with billable amounts and an hours-by-client report. |
| Meeting Notes | Meetings with their notes and action items, with what is still open and who owns it. |
Names
A database name starts with a letter and carries on with letters, digits, hyphens or underscores, up to 64 characters. It cannot contain a space, so “Spring Fair” becomes “SpringFair” or “Spring_Fair”. Names are unique regardless of capitals.
Working with records
The four modes
The switch at the top of a database page has four modes. Browse is where you read and edit records. Find is where you type what you are looking for. Layout is where you arrange a layout, and Preview shows the layout as it prints, page by page.
In Browse mode the toolbar has the layout picker, the Form, List and Table views, buttons for the first, previous, next and last record, a Quick find box, Sort… and New Record. The More menu holds the rest: Duplicate Record, Replace Field Contents…, Relookup…, Import Records…, Export Records…, Print…, Delete Record and others. Below the toolbar sit Show All, Modify Last Find, the Scripts menu and Define Database….
Form, List and Table
The same layout can be seen three ways. Form shows one record at a time. List shows the records one after another, each in the layout’s design. Table shows them as rows and columns, like a spreadsheet. Every table also has an automatic layout, listed in the picker as its name followed by (automatic), so you can work in a table before designing anything for it.
Finding records
For a quick search, type in Quick find. For a precise one, switch to Find mode, type criteria into the fields you care about, and choose Perform Find. Plain text finds records whose field has a word starting with what you typed. Several requests in one find widen it, and a request with Omit ticked takes records out. The Symbols menu inserts the rest:
| Symbol | Finds |
|---|---|
| < ≤ > ≥ | Values less than, at most, greater than or at least the value |
| = | An exact word; on its own, an empty field |
| == | The whole field matching exactly |
| … | A range, such as 1…9 (three dots) |
| ! | Duplicate values |
| // | Today’s date, in a date field |
| ? | Invalid values |
| @ | Any one character |
| * | Zero or more characters |
| “” | Literal text, symbols included |
Found sets and sorting
The records a find returns are the found set, and Browse mode shows only those. From Find mode, Constrain Found Set narrows the current set and Extend Found Set adds to it. In the More menu, Omit Record and Omit Multiple… drop records from the set, and Show Omitted Only swaps to the records left out. Show All brings every record back, and Modify Last Find reopens your last request. Sort… orders the found set by one or more fields, each ascending or descending.
Designing a database
Tables and fields
Define Database…, on the database page and below the Browse toolbar, opens a dialog with Tables, Fields and Relationships tabs. On Tables you create, rename and delete tables. On Fields you create fields of these types: Text, Number, Date, Time, Timestamp, Container (a picture or a file), Calculation and Summary.
A field’s Options… have two tabs. Auto-Enter fills a value in for you: a serial number, the date or time a record was created or modified, a value you type under Data, the value from the last record you visited, or a calculated value. It is also where you set a lookup, which copies a value in from a related record; Relookup… copies the values again. Validation sets rules such as Not empty, Unique value, In range and Member of value list, with your own message when a rule fails. Find Invalid Records finds the records that break the rules.
Calculations and summaries
A calculation field has a formula, written in the FileMaker style:
semicolons between a function’s arguments, an ampersand to join
text, and Table::Field for a field in related records.
The functions come in groups: logic (If,
Case, IsEmpty), text, numbers, dates, times,
timestamps, and totals over related records (Sum,
Count, Average, Min,
Max, List), plus Get for things
such as today’s date or the number of records found. In the
Events template, an event’s headcount is
Sum(Guests::Attending), and what is left of its budget is
the budget less the sum of its expenses.
A summary field works across the found set: Total of, Average of, Count of, Minimum, Maximum, Standard Deviation of or Fraction of Total of a field. Summaries are what fill the totals in a report.
Value lists and field controls
Manage Value Lists… holds the lists a field can offer. A list is either custom values you type, such as the Events template’s RSVP list of Invited, Yes, No and Maybe, or the values already in a field. In Layout mode, Format ▸ Field Control… shows a field as an Edit box, Drop-down list, Pop-up menu, Checkbox set, Radio button set or Drop-down calendar.
Relationships and portals
The Relationships tab is a graph of your tables. Add Relationship… connects two of them by matching fields, and can let you create related records through the relationship or delete them along with the record they belong to. Add Occurrence… puts another occurrence of a table on the graph.
A portal is how a layout shows related records. In Layout mode, the Portal tool draws one, and Portal Setup chooses which related records it shows and how many rows. In Browse mode, with a portal row selected, Go to Related Record opens that record and Delete Row removes it.
Layouts
New Layout…, in the layout picker, asks for a table and a style — Standard form, Columnar list, Table or Blank — and then the fields to show, in order. Manage Layouts… reorders, renames, duplicates and deletes them.
In Layout mode you arrange the design with the tools for text, lines, rectangles, ovals, fields, portals and buttons, and the Edit, Arrange, Layouts and Format menus: aligning and grouping, number and date formats, headers and footers, page setup, a grid and rulers. Choose Save Layout to keep your changes and Exit Layout to go back to Browse. Arranging a table’s automatic layout first offers to save it as a layout of its own, with Save and Arrange.
Scripts and buttons
A script is a list of steps that runs in order. The Scripts menu lists your scripts, the first ten with keyboard shortcuts, and Manage Scripts… is where you create, edit, reorder, duplicate, run and delete them. The steps come in categories: control (if, loops, variables, running another script), navigation (going to a layout, record or field, changing mode), found sets (finding, omitting, sorting), records (creating, changing, deleting, importing, exporting) and a few more, such as a custom dialog, printing and saving records as a PDF.
To run a script from a layout, draw a button with the Button tool and choose the script in Format ▸ Button Setup…. The Equipment Checkout template’s Check in script and the Invoices template’s Print invoice script are examples to open and read.
Reports, Preview and printing
A report is a layout with parts. Besides the body, a layout can have headers and footers, grand summaries, and sub-summaries that appear when the records are sorted by a field — a heading above each event’s guests, say, and a total below them. You add parts in Part Setup…, which also sets page breaks, and choose paper, orientation, margins and columns in Page Setup…. The templates’ report scripts each go to the report layout, sort, and enter Preview, so that the sub-summaries fall where they should.
Preview shows the pages as they print. From there, Print… prints the records being browsed, the current record or a blank record, and Save as PDF… writes a PDF. Exit Preview returns to Browse.
Importing and exporting
Import Records… reads a CSV file, a tab-separated file or an Excel workbook, choosing the sheet when there is more than one. You match each column to a field, say whether the first row holds field names, and choose to add new records, update matching records, or update the records in the found set. An Import Summary reports what happened. Export Records… writes the found set’s chosen fields as Comma-separated (.csv), Tab-separated (.tsv) or Excel (.xlsx).
The database’s own page
Choose a database’s name in the list and its own page opens: its tables with how many rows each holds, Define Database…, Manage Value Lists…, and its saved layouts, each a link into Browse mode. The Query box runs a SQL statement when you choose Run; a statement that reads shows its rows, and one that removes data asks first, with Remove.
Snapshots lists the copies of the whole database taken before anything removes data, with when each was taken and who asked for it; Restore puts one back. Delete database… asks you to type the database’s name, and keeps a snapshot, so a deleted database can be brought back from it.
Asking the agent
What you can ask for
The agent works in your databases from any chat. Describe the outcome and it does the steps:
- Build one, blank or from a template, with or without the sample records, and open it beside the chat.
- Change its structure: add tables and fields, relationships, value lists, field options and lookups.
- Change its records: add, edit or remove them, put a picture from the chat into a container field, or restore a snapshot.
- Answer questions about your records, such as how many guests have said yes, without opening anything.
- Work in the open page: go to a layout, find records, sort them, go to a record, and run a script.
- Design: create and change layouts, write and check calculation and summary formulas, write scripts and put them on buttons.
The agent’s changes to a layout can be undone in Layout mode, and Define Database… and Manage Scripts… offer to undo its latest formula or script change.
What it asks first
- Reading your own database runs without asking — describing it, finding, sorting, going to a layout or record, and running a script that only navigates, finds, sorts, previews or prints. If you answered Never allow for one of these on a database, it stays refused.
- A change asks. The main button on the question is Allow changes to “name” in this chat, after which the agent changes that database in this chat without asking again. Behind the arrow are Allow once and an answer that always allows that one action on that database; on the refusing side are Don’t allow, a Never allow answer for the action, and Block all access to database “name”. Allowing the agent to create a database also lets it change that database in this chat.
- Removing anything always asks, even after Allow changes: deleting records, a layout, a script, a value list, a relationship or a whole database, and restoring an older snapshot. A snapshot is taken first. A script that can change or delete records asks as a change, and any deletions it makes take a snapshot.
The question itself, and the answers it offers, are covered in Approvals and permissions.
Good to know
- Database names cannot contain spaces.
- Only the five most recent snapshots of each database are kept; older ones are discarded.
- An import file can be up to 50 MB and 500 columns, and only CSV, tab-separated or Excel. Calculation and summary fields cannot be imported into.
- A file in a container field can be up to 25 MB. The agent can store a picture there but cannot see one; it knows only that it is there.
- The Query box shows at most the first 1,000 rows of a result. A few statements, such as attaching another database file, are refused with the reason.
- A table with no key to edit rows by, or with more than a million records, opens read-only.
- A range in a find uses three dots, as the Symbols menu inserts it.
- In Layout mode, Ctrl+B (Cmd+B on a Mac) leaves Layout mode rather than making text bold.
Back to all user-guide articles.