Link tables with references and child rows

A Reference field points to one item of another table: an order's Customer points to a customer. A References field points to several, such as an order's Products. The base's builders add one on the table's Fields page in Builder and pick the table under Points to one item of.

A reference to the base's users

A reference to the base's users, such as a task's Assignee or a deal's Owner, offers only the base's own people: the users with an admin or staff role. It does not offer a user who has only an external role, such as a customer who signs in to the portal (Open a customer portal). To offer those users too, turn on Allow outside users in the field's box: "The field also offers users who have only an outside role, such as a customer's login in the portal." A contact's Signs in as has it on.

• Without Allow outside users, saving an outside user in the field is refused with the field's title, such as "Assignee takes only the base's own people", from the item's page, an import, the API or the assistant alike.
• An item that already names an outside user keeps that user until someone changes the field.
• A filter on the field still offers every user.

Show more than a name in a picker

A reference's picker can show more than a name: under Show beside each label, in the field's box, pick up to three fields of the table it points to. The picker then shows each item as its label and the values of those fields, such as "JOB-0024 · B 123 ABC", a job's number with its car's plate. You can also ask the assistant: "Show each job's plate in the job picker".

• The chosen item keeps its label only.
• You can pick short text, long text, integer, decimal, yes / no, single choice, multiple choice, date and date & time fields.
• A field that someone may not read does not show to them.

A reference's When the referenced item is deleted, in the field's box, chooses what happens when the item it points at is deleted:

• Refuse the delete, the default: the customer cannot be deleted while an order points at it. Deleting Carol Baker in the shop says "This customer cannot be deleted: 3 orders refer to it.", with Orders, 3, and View all, which opens the orders list filtered to them. Someone who cannot see those orders is only told that other items refer to it.
• Clear this field, for a reference that is not required: the delete goes through and the field is emptied on every item that pointed at it, as a product's Category is when its category is deleted.

The item a reference points at lists the items that point at it, on its page: a customer's page lists the customer's orders (Open an item’s page: comments, history, Follow and Duplicate). Three settings in the reference field's box control this list:

• Show a list on the page of the linked item: the linked item's page shows the list of the items that point at it through this field. This setting is on by default. If you turn it off, the linked item's page does not show this list, and it shows its other lists as before.
• Title of the list: the heading of the list on the linked item's page, such as "Required by". If you leave it empty, the page names the list with the table's plural and the field's title, such as "Orders · Customer".
• Show as a timeline: the list on the linked item's page shows as a timeline, each item a bar from its start day to its end day, such as a project's stages on the project's page (Show items on a timeline (Gantt chart)).

The assistant writes a title when it adds a reference field, and turns a list off when you ask it: "Do not show the orders on the customer's page".

A reference's picker can offer only the items that fit the rest of the form: in the example shop, an order line's Product offers only the products of the order's price list that are not retired. Until the order has a price list, the picker says what to pick first, such as "Pick the order's price list first", instead of a list. Ask the assistant for such a picker in words: "On an order line, offer only the products of the order's price list". The field's box then reads "Narrowed by a rule the assistant wrote", or the sentence the picker shows until that first field is picked, which you can change there.

Add an item from a picker with + New

Where you may add the items a picker offers, the picker has + New: the new item's form opens in a box over yours. Save it, with its usual message, and it is picked, with everything you typed in your form still there. From a picker that offers only what fits, the new item comes filled in with what the choice depends on: a contact made from a deal's Contact picker, in the Deals app, has the deal's company.

Limits and details

• A narrowed picker only narrows what each person may see: someone who may not see a product is not offered it, whatever the rule.
• Saving an item that is not one of the choices, from a script, an import or an AI client, is refused with "Not one of the choices", marked on the field or on the line.
• What an item already holds stays: an old order whose product was retired since still shows it, and saves, until someone picks another.
• A picker offers its items even from a table whose list is switched off, such as the shop's price lists, picked on each order.
• The assistant and an AI client ask the same question: "Which products may this order's line take?" gets the picker's answer.

A Child rows field holds lines edited inside the item's form: an order's Items in the order, each with a product, a quantity and a price. The base's builders add the field: under Rows of, pick the table of the lines; it needs a required reference back to this one, which Linked by names, and only tables with one are listed.

To add a line to an order:

1. Open an order and click Edit.
2. Click Add item. The new line starts with its defaults, a Quantity of 1: pick its Product, and its Unit price fills in from the order's price list. Change the quantity if you need, or type another price.
3. As you type, the line's Amount and the order's Total move with it, before you save: the form works out every computed field, lookup and rollup over what it holds, the new lines included.
4. Click Save. A line with a mistake is marked on the line itself.

Choose how the lines show on the item's page

The edit form shows every line and every field of each. On the order's page, when you read it, the lines' grid offers the grid views of Order lines, its default view first: pick one, and its columns, its sort and its filters apply to the lines. The base remembers your choice for that rows field, so it holds after a reload and on every other order, and it is yours only. To offer another choice, add a grid view on Order lines' Views page in Builder.

Keep the lines in the order you put them in

Child rows can keep the order you put them in, as an invoice's or a quote's lines do. Switch on Keep the order rows are put in in the child rows field's box and save the table: the lines already there keep the order they were added in. On the form, each line then has a handle: drag it up or down, or use its move up and move down buttons from the keyboard, and click Save. The lines keep that order after a reload, on the item's page, in its print templates and in an export. A script, an import or an AI client puts the lines in the order it sends them, and a line added on its own goes last. In the example shop, the lines of orders and invoices keep their order.

Limits and details

• The table of the lines is a child table: its items live in their parent, and its views are not in the sidebar.
• A child table made from here gets its default view, as any table does, and its views stay on its Views page in Builder and at its own list page.
• A child table has no forms, print templates, automations or workflows: its rows are its parent's, and so are these, so its pages in Builder leave them out.

A Files field takes documents and pictures: drop them on the field, or click choose. The item's page links to each file, as an order's Attachments link to its invoice: a picture opens in a window over the page, with Download and Open in new tab. On a phone, Take a photo beside the field opens the camera, and the photo is added to the field.

A Files field can also be a signature, such as a job's Customer signature. The customer then signs on the screen, on a phone, a tablet or a computer, and the base keeps the signature with the item. The base's builders switch it on.

1. In Builder, open the table's Fields page, click a Files field, or add one, and switch on Signature. Click Done, then Save table.
2. Open an item and click Edit. The field shows a pad. Sign on it with a finger, a pen or the mouse. Click Clear to start again.
3. Click Keep signature. The base keeps the signature as a picture in the field. Then click Save. A signature you drew and did not keep is kept when you click Save, and in an action's dialog (such as Delivered) when you click the action's own button.

Limits and details

• Only a Files field can be a signature.
• The field holds one signature. When you sign again, the new signature replaces the old one.
• The item's page and its print templates show the signature as a picture.

You can also ask the assistant: "Add a Customer signature to the jobs." Through an API token, the assistant and the AI clients that you connect, the field is set as any files field, with one file only.

A table that extends another keeps one person or company once, though they are several things to your business: a customer, a supplier, one of your staff. Keep them in a table such as Contacts, with what every contact has: a name, an email, a city. Then make each relation a table that extends Contacts, such as Customers with Loyalty points and Suppliers with Terms. A customer is then also a contact, with the same ID: you change Ana Lopez's email once, and her customer and her supplier show the new email. The base's builders set it up.

1. In Builder, make the table that holds the relation, such as Customers, and open its General page.
2. Under Extends, select the table, such as Contacts, and save. You can set or remove it only while the table has no items; when it has items, the choice is locked and says why.
3. On the Fields page, add only what the relation adds, such as Loyalty points. A field cannot take the key of a field of Contacts: "Contacts already has a field with this key". The Label field can be a field of Contacts, so a customer is named by the contact's name.

The forms and the lists of Customers show the fields of both tables, those of Contacts first. A new customer's form lets you type a new contact's name and email, or Pick an existing contact, such as a supplier who now also buys from you. Search, an import, an export, print templates, automations and the base's code see a customer with both tables' fields too. The workflows, checks and custom actions of Customers can also use the fields of Contacts, and their pages in Builder offer them. An import into Customers takes the columns of both tables, and each row makes a contact and its customer.

Limits and details

• A contact can be a customer only once: "This contact is already in Customers".
• A role that may read customers sees their contacts too, and their contact fields, through each customer it may read: a seller who reads Customers and not Suppliers finds Ana in Contacts and in Customers, and not in Suppliers. In a relation, a field of Contacts that either table hides from a role stays hidden: if Suppliers hides the email from a buyer, the buyer does not see the email in Suppliers. If the role may also read Contacts by the rules of Contacts, Contacts shows what its own rules let the role see.
• A rule of a role on Customers can name a field of Contacts. A rule on Staff such as "User is Me" names the contact's user, so each user of the role sees only their own Staff item.
• When someone changes a contact, the open lists of Customers and of the contact's other relations show the change at once.
• A reference can offer only the contacts of one relation, such as an order's Contact that offers only customers. Ask the assistant for it: "On an order, offer only the contacts that are customers".
• Deleting a customer keeps the contact, and the contact's other relations. To delete a contact that is also a customer or a supplier, click Delete and confirm as for any item. The base then names the items that go with the contact, such as "Deleting this contact also deletes 1 customer and 1 supplier.", and asks you again. Confirm, and they all go. If you may not delete one of them, nothing is deleted.
• Extending goes one level deep: a table that extends another cannot itself be extended, and a table that others extend cannot extend a table. While Customers extends Contacts, Contacts cannot be deleted: "Customers extends this table".

You can also ask the assistant: "Keep each customer and supplier as a contact". Through an API token and the AI clients that you connect, a table's extends holds the table it extends, and a new customer can name the contact it extends. In the base's code, base.create("customers", {…}, {extends: contactId}) makes an existing contact a customer.