Data Modeling

Glide Data Modeling

Key Concept: Column-Based Calculations

IMPORTANT: All calculations in Glide are built in the Data Editor by adding computed columns that build off one another. Unlike spreadsheets with cell formulas, Glide uses a column-based computation model.

To build complex calculations:

1. Create intermediate computed columns
2. Chain columns together (one column references another)
3. Build up to your final result step by step

Example: Calculate total with tax

1. Column: Subtotal (Math: Price * Quantity)
2. Column: Tax (Math: Subtotal * 0.08)
3. Column: Total (Math: Subtotal + Tax)

Column Types Overview

Basic Columns (Data Storage)

Computed Columns (Calculations)

AI Columns (Powerful!)

Glide AI columns run inference on your data automatically. Look for opportunities to add these to make apps more useful.

AI Column Ideas by App Type

Integration Columns

Creating Columns via UI

Adding a Column (Keyboard Shortcut - Recommended)

Fastest method: Use keyboard shortcut with browser automation

1. Navigate to the Data Editor (Data tab)
2. Click on the table where you want to add a column
3. Press CMD+SHIFT+ENTER (macOS) or CTRL+SHIFT+ENTER (Windows)
4. Type the column Name
5. Select the Type from dropdown (required - commits the name)
6. Configure type-specific options
7. Click Save

Why this method:

• Much faster than clicking through UI menus
• Works reliably with browser automation
• Direct keyboard access to column creation

Adding a Column (Manual Method)

Alternative UI-based approach:

1. Go to Data Editor (Data tab)
2. Click any column header
3. Select "Add column right"
4. Configure:
   • Name: Column name
   • Type: Select from dropdown (searchable)
   • Type-specific options
5. Click Save

Note: Keyboard shortcut method is preferred for automation workflows.

Column Type Picker

The Type dropdown is organized into categories:

• Basic - Storage types
• Computed - Calculation types
• AI - AI-powered processing
• Integrations - External services

Type in the search box to filter.

Row ID Columns: The Primary Key System

CRITICAL BEST PRACTICE: Add a Row ID column to every table BEFORE creating relations.

What Row ID Columns Are

Row ID columns are Glide's primary key system:

• Unique identifier for each row in a table
• Automatically generated when you add the column
• Never changes for a row (stable identifier)
• Required for reliable relations between tables

How to Add Row ID Column

1. Navigate to the table in Data Editor
2. Add a new column (CMD+SHIFT+ENTER or click column header → "Add column right")
3. Name it "Row ID" (or similar - "ID", "Record ID")
4. Select column type: Basic → Row ID
5. Save
6. The column will appear as the first column in the table

Why Add Row ID First

Before creating relations:

✅ RIGHT workflow:
1. Create tables
2. Add Row ID column to each table
3. Add foreign key columns (text columns to hold IDs)
4. Populate foreign key columns with Row ID values
5. Create relation columns

❌ WRONG workflow:
1. Create tables
2. Try to create relations immediately
3. Relations fail or use unreliable auto-generated $rowID

Row ID vs $rowID

Best practice: Always use explicit Row ID columns that you create, not Glide's auto-generated $rowID values.

Example: Setting Up CRM Tables with Row IDs

Step 1: Create tables and add Row ID to each

Companies table:
  - Add column: "Row ID" (type: Row ID) ← Do this FIRST

Contacts table:
  - Add column: "Row ID" (type: Row ID) ← Do this FIRST

Deals table:
  - Add column: "Row ID" (type: Row ID) ← Do this FIRST

Step 2: Add foreign key columns

Contacts table:
  - Add column: "companyId" (type: Text) ← Will hold Row ID from Companies

Deals table:
  - Add column: "contactId" (type: Text) ← Will hold Row ID from Contacts
  - Add column: "companyId" (type: Text) ← Will hold Row ID from Companies

Step 3: Create relations

Contacts table:
  - Relation: "Company" → Match Contacts.companyId to Companies.Row ID

Companies table:
  - Relation: "Contacts" → Match Companies.Row ID to Contacts.companyId
  - Relation: "Deals" → Match Companies.Row ID to Deals.companyId

Deals table:
  - Relation: "Contact" → Match Deals.contactId to Contacts.Row ID
  - Relation: "Company" → Match Deals.companyId to Companies.Row ID

Result: Reliable, explicit relationships between all tables.

Relation & Lookup Pattern

This is the most common pattern for connecting data:

Prerequisites: Add Row ID Columns First

Before creating any relations:

1. Add a Row ID column to each table involved in the relationship
2. Add foreign key columns (type: Text) to hold the Row ID values
3. Populate the foreign key columns with Row ID values from related tables

See "Row ID Columns: The Primary Key System" section above for detailed guidance.

Step 1: Create Relation Column

• Creates a link between two tables
• Match foreign key column → Row ID column (recommended pattern)
• Can be single or multiple relations
• Example: Match Tasks.projectId (text) → Projects.Row ID (Row ID column)

Step 2: Create Lookup Column

• References the Relation column
• Pulls specific column values from related rows
• Example: Get manager's phone number via Employee->Manager relation

Step 3: Create Rollup Column (if aggregating)

• References the Relation column
• Aggregates values: Count, Sum, Average, Max, Min, etc.
• Example: Count of tasks assigned to employee

IMPORTANT: Rollups should almost always operate on a Relation column, not directly on a table. This ensures the rollup only aggregates related rows, not all rows in the target table. See the computed-columns skill for detailed rollup guidance.

Math Column Syntax

Math columns support:

• Basic arithmetic: +, -, *, /
• Column references: Click to insert column name
• Functions: Check Glide docs for available functions
• Parentheses for order of operations

Example: (Price * Quantity) * (1 + TaxRate)

If-Then-Else Pattern

Structure:

IF [condition]
THEN [value if true]
ELSE [value if false]

Can be nested for multiple conditions:

IF Status = "Overdue"
THEN "!"
ELSE IF Status = "Due Today"
THEN "Today"
ELSE ""

Template Column Syntax

Templates use replacement syntax:

Hello, {Name}! You have {TaskCount} tasks.

Reference any column by wrapping its name in curly braces.

Glide Big Tables vs Native Tables

Native Tables (Glide Tables)

• Built into every app
• Good for small-medium data (up to 25,000 rows)
• Full computed column support
• Cannot be shared between apps

Big Tables

• Separate from apps (team-level)
• Handles large datasets (up to 10 million rows)
• Required for API v2 access
• Can be shared across multiple apps

Big Table Computed Column Limitations

Big Tables have different behavior for computed columns compared to native Glide Tables. These limitations apply when filtering, sorting, or creating rollups on computed columns.

CAN be filtered/sorted/rolled up in Big Tables:

CANNOT be filtered/sorted/rolled up in Big Tables:

Lookup column requirements in Big Tables:

• Must use a single relation (not multi-relation)
• The relation column must be a basic (non-computed) column
• Target table must also be a Big Table
• Target column must be a basic (non-computed) column
• Target column cannot be user-specific

Template column requirements in Big Tables:

• Template string must be static (constant text)
• Dynamic templates (computed template strings) not supported

Other Big Table limits:

These limitations exist because Big Tables use SQL (AlloyDB) for queries, and computed columns must be translatable to SQL expressions.

For more details: Big Tables documentation

Sharing Data Between Apps (Big Tables)

One of Glide's powerful features is linking the same Big Table to multiple apps. This enables:

• Shared data across related apps
• Central data management
• Consistent user experiences

How to Link Shared Tables

1. Go to Data Editor (Data tab)
2. Find Data Sources section in left sidebar
3. Click on your team name (e.g., "SpaceX")
4. A Link Tables panel appears showing:
   • All Big Tables in your team
   • Apps Linked count for each table
   • Last Modified date
   • Searchable table list
5. Check the tables you want to link
6. Click Link Table button

Viewing Linked Tables

The "Apps Linked" column shows how many apps share each table. Click to see which apps use a table.

Use Cases for Shared Tables

Data Architecture Tips

1. Plan table sharing upfront - Decide which data should be centralized
2. Use Big Tables for shared data - Native tables can't be shared
3. Consistent column naming - Makes relations easier across apps
4. Single source of truth - Avoid duplicating data in multiple tables

API Access for Data Operations

For programmatic data operations, use the Glide API v2:

• Base URL: https://api.glideapps.com/
• Works only with Big Tables
• Get API token from: Data Editor > Show API > Copy secret token

See the api skill for detailed API usage.

Best Practices

1. Add Row ID columns first - Add a Row ID column to every table before creating relations
2. Name columns clearly - Use descriptive names that indicate purpose
3. Create intermediate columns - Break complex calculations into steps
4. Use Relations wisely - They're the foundation of data relationships
5. Match foreign keys → Row ID columns - Use explicit Row ID columns, not auto-generated $rowID
6. Consider user-specific columns - For per-user data storage
7. Document your data model - Use the Description field
8. Test calculations - Verify with sample data before going live

Documentation

• Data Editor Overview
• Computed Columns
• Relations
• Big Tables