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.

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 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: "[e-mail]"

Setup: Add a Linked Table Field 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

// 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

// 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 to protect related data

6. Debug in context: Use the In-App Debugging when a screen, form, filter, relationship, or action does not bind to the expected table, field, or row.

Related Documentation

• Buzzy Datamodel Overview - Comprehensive guide to all datamodel concepts

• Display Formula - Conditional field and element visibility

• Sub-tables - Detailed guide to one-to-many relationships

• Linked Table Fields - Many-to-many relationship implementation

• Security and Access Control - Protecting related data

• In-App Debugging - Inspect screen, binding, field, action, and runtime issues

• REST API - Programmatic data access

• Async API - Code Widget data operations

• App Examples - Real-world relationship examples

---