VIA Advanced Patterns

This guide covers advanced patterns, real-world scenarios, and best practices for using the VIA clause in AXEL relationship traversal. These patterns are essential for building complex analytics that span multiple related tables.

Bracket Identifiers for Special Field Names

When your data model includes field names with spaces, special characters, or reserved words, use bracket notation to explicitly identify them in your VIA paths.

Syntax

VIA([field with spaces], [another field])

Example: Fields with Spaces

Consider a data model where field names use natural language:

Data Model:

Sales Records (has field "assigned customer")
  └── Customer Details (has field "home region")
        └── Geographic Regions (has field "region name")

To traverse this path, wrap each field name in brackets:

Query:

RELATED(
  [Geographic Regions][region name],
  VIA([assigned customer], [home region])
)

How it works:

1. Starting from Sales Records, AXEL follows the assigned customer field
2. From Customer Details, it follows the home region field
3. Finally, it retrieves region name from Geographic Regions

Mixed Bracket and Simple Names

You can mix bracket notation with simple field names in the same query. Use brackets only where needed:

ADDCOLUMNS(
  [Sales Records],
  "Region", RELATED([Regions][name], VIA([assigned customer], region)),
  "Customer", RELATED([Customers][full name], VIA([assigned customer]))
)

Real-World Scenario: E-Commerce Analytics

This comprehensive example demonstrates how multi-hop VIA traversal enables powerful cross-table analytics for an e-commerce platform.

Data Model

The e-commerce system has the following interconnected tables:

• Orders: amount, date, product reference, customer reference
• Products: name, price, stock, category reference
• Categories: name, discount rate, department reference
• Departments: name, budget, manager
• Customers: name, email, region reference
• Regions: name, sales manager

This creates two main relationship chains:

1. Orders → Products → Categories → Departments (3 hops for product lineage)
2. Orders → Customers → Regions (2 hops for customer geography)

Complete Order Analysis

This query enriches each order with information from all related tables in a single pass:

ADDCOLUMNS(
  [Orders],

  -- Product Information (1 hop)
  "Product", RELATED([Products][name], VIA(product)),
  "Unit Price", RELATED([Products][price], VIA(product)),

  -- Category Information (2 hops)
  "Category", RELATED([Categories][name], VIA(product, category)),

  -- Department Information (3 hops)
  "Department", RELATED([Departments][name], VIA(product, category, department)),

  -- Customer Information (1 hop)
  "Customer", RELATED([Customers][name], VIA(customer)),

  -- Region Information (2 hops via customer)
  "Region", RELATED([Regions][name], VIA(customer, region))
)

Why this is powerful: A single ADDCOLUMNS expression retrieves data from 6 different tables, following two separate relationship chains from the Orders table. This would typically require multiple JOINs in SQL.

Revenue by Department Report

Group orders by their product's department (3 hops away) to create executive-level summaries:

SUMMARIZE(
  [Orders],
  RELATED([Departments][name], VIA(product, category, department)),
  "Department Revenue", SUMX([Orders], [amount]),
  "Order Count", COUNTX([Orders], 1),
  "Avg Order Value", AVGX([Orders], [amount])
)

Result:

Best Practices

1. Be Explicit with VIA

Always specify VIA when the foreign key field name differs from the target table name. This makes your code self-documenting and prevents errors:

-- Good: Explicit about which field to use
RELATED([Customers][name], VIA(assigned_customer))

-- Risky: Relies on automatic matching
RELATED([Customers][name])

Why it matters: Auto-matching works when field names exactly match table names (case-insensitive), but explicit VIA ensures clarity and prevents bugs when field names change.

2. Order Matters in Multi-Hop

The fields in VIA must follow the actual relationship chain in your data model. AXEL traverses them in order:

-- Correct: order → product → category
VIA(product, category)

-- Wrong: order → category (no direct relationship exists)
VIA(category)  -- Error: Orders has no 'category' field!

3. Test Each Hop Separately

When debugging complex multi-hop queries, verify each level of the path independently:

-- First, verify 1-hop works
RELATED([Products][name], VIA(product))

-- Then verify 2-hop
RELATED([Categories][name], VIA(product, category))

-- Finally, full 3-hop
RELATED([Departments][name], VIA(product, category, department))

This incremental approach helps identify exactly where a path breaks.

4. Use Meaningful Field Names

When designing your forms and tables, use descriptive relation field names that make VIA paths readable:

Good: customer, product, assigned_region, primary_category
Poor: ref1, link, id, fk_field

Good naming makes your AXEL formulas self-documenting:

-- Readable: clear what data you're fetching
VIA(customer, region)

-- Confusing: what does ref1 point to?
VIA(ref1, ref2)

Multi-Hop RELATEDTABLE

VIA works with RELATEDTABLE for one-to-many relationships across multiple hops. This retrieves all related records, not just a single value:

-- Get all orders for products in a specific category
SUMX(
  RELATEDTABLE([Orders], VIA(product, category)),
  [amount]
)

Use case: When sitting in a Categories context, this retrieves all Orders for all Products in that category, then sums their amounts.

Performance Considerations

Multi-hop traversals are optimized internally, but keep these tips in mind:

1. Fewer hops = faster queries - Design your data model to minimize relationship depth when possible
2. Cache intermediate results - Use LET to store RELATED results if you need them multiple times
3. Filter early - Apply FILTER before multi-hop operations to reduce the dataset being traversed

Next Steps

• VIA Clause Basics - Core syntax and simple examples
• JOIN and DEFAULT Options - Handling missing relationships
• Cross-Table Operations - FILTER and SUMMARIZE with RELATED