> For the complete documentation index, see [llms.txt](https://docs.buzzy.buzz/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://docs.buzzy.buzz/the-building-blocks/datatables-fields-and-data/datatable-to-datatable-relationships.md).

# Datatable to Datatable Relationships

Relationships allow you to connect datatables together, creating powerful data models that reflect real-world associations. When users view records, they can automatically see related data from connected datatables. This guide shows you how to create and work with different types of relationships in Buzzy.

## Overview

Buzzy supports several types of relationships between datatables:

1. **One-to-Many (1:M)**: One parent record has multiple child records
2. **Many-to-Many (N:M)**: Multiple records in one datatable relate to multiple records in another
3. **Hierarchical**: Multi-level parent-child relationships

For a comprehensive overview of all relationship types, see our [Buzzy Datamodel Overview](/the-building-blocks/datatables-fields-and-data/buzzy-datamodel-overview.md).

## Current Datatable Field Types

The current Buzzy Workspace field picker supports these field types. Use this list when planning relationships, screen bindings, forms, filters, and API payloads.

| Field type             | Use for                                | Notes                                                                                    |
| ---------------------- | -------------------------------------- | ---------------------------------------------------------------------------------------- |
| Text                   | Short or long text values              | Text can be configured as plain text, email, URL, phone, or password input.              |
| Rich Text              | Formatted text                         | Use when users need structured notes or formatted content.                               |
| Number                 | Numeric values                         | Number fields can be configured as plain number, slider, or currency.                    |
| Date/Time              | Dates, times, months, years, or events | Date/time options control the date mode and formatting.                                  |
| Images                 | One or more image attachments          | Can be configured as single image when the app needs one primary image.                  |
| Files                  | File attachments                       | Use for documents or non-image uploads.                                                  |
| Selection              | Single-choice option lists             | Good for status, category, type, and workflow-stage values.                              |
| Toggle                 | Boolean true/false values              | Use for yes/no, enabled/disabled, approved/unapproved.                                   |
| Checkboxes             | Multiple-choice option lists           | Stores multiple selected values.                                                         |
| Rating                 | Rating input                           | Typically rendered as stars or another configured icon set.                              |
| Sub Table              | One-to-many child records              | Creates a parent-to-child relationship. Child rows are scoped to the parent row.         |
| Linked Table           | Reference to rows in another datatable | Use for selecting related records, such as assignees, customers, products, or locations. |
| Location               | Address or location data               | Use when screens need maps, geospatial filters, or location capture.                     |
| Embedded Video         | Video URL/embed content                | Use for video media attached to a row.                                                   |
| Author                 | Row creator metadata                   | System metadata for who created a row.                                                   |
| Submitted              | Row submission timestamp metadata      | System metadata for when a row was created.                                              |
| Viewers                | Row-level viewer selection             | Controls additional users who can view a row, depending on access settings.              |
| Teams                  | Team selection                         | Useful for routing, permissions, workflow ownership, and formulas.                       |
| Team Viewers           | Team-based viewer selection            | Use when visibility is granted to teams rather than individual users.                    |
| Formula                | JSONata-calculated value               | Calculates and renders a derived value from row/form/context data.                       |
| Button                 | Row or form action trigger             | Use with Buzzy actions, workflow rules, links, state changes, and submit behavior.       |
| Code Widget            | Custom UI/code field                   | Use when a screen needs a custom embedded interface backed by Buzzy APIs.                |
| Blood Pressure Monitor | Bluetooth health-device input          | Use only for supported device-specific healthcare workflows.                             |

In Builder MCP data-model artifacts, some labels are normalized, for example `sub-table`, `link-table`, `location-field`, `date-only`, `date-time`, `single-image`, and `jsonata`. The builder translates these into the corresponding Buzzy runtime field behavior.

## Relationship Types

### 1. Sub-table Relationships (1:M)

Sub-tables create one-to-many relationships where child records are automatically linked to their parent via `embeddingRowID`.

```
Parent Datatable (e.g., Projects)
├── _id: "proj_001"
├── Name: "Website Redesign"
└── Child Records (Sub-table)
    ├── Task 1
    │   ├── _id: "task_001"
    │   ├── embeddingRowID: "proj_001" ← Automatic link to parent
    │   └── Title: "Design Homepage"
    └── Task 2
        ├── _id: "task_002"
        ├── embeddingRowID: "proj_001" ← Automatic link to parent
        └── Title: "Develop Backend"
```

**Setup**: Add a [Sub-table field](/the-building-blocks/datatables-fields-and-data/advanced-fields/sub-tables.md) to the parent datatable pointing to the child datatable.

### 2. Linked Table Field Relationships (N:M)

Linked Table Fields create many-to-many relationships by referencing records in another datatable.

```
Tasks ←→ Users (Many-to-Many)

Task 1
├── _id: "task_001"
├── Title: "Design Homepage"
└── Assignee (Linked Table Field): "user_123" ← References Users datatable

Task 2
├── _id: "task_002"
├── Title: "Develop Backend"
└── Assignee (Linked Table Field): "user_123" ← Same user, different task

User
├── _id: "user_123"
├── Name: "Alice Designer"
└── Email: "alice@example.com"
```

**Setup**: Add a [Linked Table Field](/the-building-blocks/datatables-fields-and-data/advanced-fields/linked-table-field.md) to reference records from another datatable.

## Practical Tutorial: Location Hierarchy

Let's create a practical example with a location-based hierarchy system.

### Step 1: Create the Parent Datatable (Locations)

1. Create a new datatable named "Locations"
2. Set "Who can View Rows" to enable appropriate viewers
3. Add these fields:
   * **TEXT Field**: "Place Name"
   * **LOCATION Field**: "Address"

### Step 2: Create the Child Datatable (Items)

1. Create a new datatable named "Items"
2. Add these fields:
   * **TEXT Field**: "Description"
   * **LINKED TABLE FIELD**: "Parent Location"
     * Configure to reference: Locations datatable, Place Name field

### Step 3: Configure Sorting and Filtering

1. In Items datatable properties:
   * Set Sort Field 1 to "Parent Location"
2. In Locations datatable properties:
   * Set Sort Field 1 to "Place Name"
3. In Locations datatable Advanced Properties, add this rule:

   ```
   When row is filtered, then target Datatable ID: Items, broadcast Field ID: Place Name
   ```

### Step 4: Test the Relationship

1. Add sample locations:
   * Place Name: "Downtown Office", Address: "123 Main St, City"
   * Place Name: "Warehouse", Address: "456 Industrial Blvd, City"
2. Add sample items:
   * Description: "Conference Room Equipment", Parent Location: "Downtown Office"
   * Description: "Office Supplies", Parent Location: "Downtown Office"
   * Description: "Inventory Storage", Parent Location: "Warehouse"
3. Test filtering:
   * Click the filter button on a location row
   * The Items datatable should automatically filter to show only items for that location

## Advanced Relationship Patterns

### Multi-Level Hierarchies

You can create complex hierarchies by chaining relationships:

```
Organization
└── Departments (Sub-table)
    └── Projects (Sub-table)
        └── Tasks (Sub-table)
            └── Assignee (Linked Table Field) → Users
```

### Cross-Reference Relationships

Create many-to-many relationships with junction datatables:

```
Students ←→ Enrollments ←→ Courses

Student
├── _id: "student_001"
└── Name: "John Doe"

Enrollment (Junction)
├── _id: "enroll_001"
├── Student (Linked Table Field): "student_001"
├── Course (Linked Table Field): "course_001"
└── Grade: "A"

Course
├── _id: "course_001"
└── Title: "Introduction to Programming"
```

## Displaying Related Data

When creating views and screens, you can display:

1. **Child data**: Use sub-table fields to show related child records
2. **Linked data**: Include fields from linked datatables
3. **Parent data**: Reference parent fields for breadcrumb navigation

Example: In a Task view, you can show:

* Task details (current record)
* Project name (parent via embeddingRowID)
* Organization name (grandparent)
* Assignee details (linked table data)

## Working with Relationships via API

### REST API Examples

```javascript
// Get all child records for a parent
GET /api/microappdata?microappId=tasks&embeddingRowID=proj_001

// Create a child record with automatic parent linking
POST /api/insertmicroapprow
{
  "microappId": "tasks",
  "data": {
    "embeddingRowID": "proj_001",
    "title": "New Task",
    "assigneeId": "user_123"
  }
}
```

### Async API Examples

```javascript
// Get related records in a Code Widget
const childTasks = await buzzyAPI.getMicroAppData({
  microappId: 'tasks',
  embeddingRowID: currentProject._id
});

// Create related record
await buzzyAPI.insertMicroAppRow({
  microappId: 'tasks',
  data: {
    embeddingRowID: currentProject._id,
    title: 'New Task',
    assigneeId: selectedUser._id
  }
});
```

## Best Practices

1. **Plan your relationships**: Design your datamodel before implementation
2. **Use consistent naming**: Follow clear conventions for relationship fields
3. **Test performance**: Verify performance with realistic data volumes
4. **Document relationships**: Keep track of how datatables connect
5. **Consider security**: Use [security controls](/the-building-blocks/datatables-fields-and-data/security-and-access-control.md) to protect related data
6. **Debug in context**: Use the [In-App Debugging](/troubleshooting/in-app-debugging.md) when a screen, form, filter, relationship, or action does not bind to the expected table, field, or row.

## Related Documentation

* [Buzzy Datamodel Overview](/the-building-blocks/datatables-fields-and-data/buzzy-datamodel-overview.md) - Comprehensive guide to all datamodel concepts
* [Display Formula](/the-building-blocks/datatables-fields-and-data/display-formula.md) - Conditional field and element visibility
* [Sub-tables](/the-building-blocks/datatables-fields-and-data/advanced-fields/sub-tables.md) - Detailed guide to one-to-many relationships
* [Linked Table Fields](/the-building-blocks/datatables-fields-and-data/advanced-fields/linked-table-field.md) - Many-to-many relationship implementation
* [Security and Access Control](/the-building-blocks/datatables-fields-and-data/security-and-access-control.md) - Protecting related data
* [In-App Debugging](/troubleshooting/in-app-debugging.md) - Inspect screen, binding, field, action, and runtime issues
* [REST API](/developing-and-extending-buzzy/buzzy-rest-api/rest-api.md) - Programmatic data access
* [Async API](/the-building-blocks/code-widget-custom-code/new-async-api-+-react-html-components.md) - Code Widget data operations
* [App Examples](/working-with-buzzy/buzzy-app-examples.md) - Real-world relationship examples

***