Skip to content · Zum Inhalt

Advanced Table Linking and Cross-Record Validation

Introduction

Business logic frequently depends on data from multiple related tables - validating customer credit requires examining both customer master data and outstanding ledger entries, calculating inventory availability needs current stock levels minus pending sales commitments, pricing calculations reference customer agreements and item cost structures. Source references in QUALIA Rule Engine define these cross-table relationships and make related data accessible for conditions and actions.

Basic source references link directly related tables (Customer to Sales Header via customer number), while advanced patterns involve multi-hop relationships (Sales Header → Sales Line → Item), aggregations across filtered record sets, conditional linking, and complex data structures spanning master data, transactions, and ledger entries.

Improper table linking leads to incorrect validation logic, missing data in conditions, performance degradation from inefficient joins, and rules that fail when expected relationships don't exist. Understanding table relationship patterns, filter optimization, and data availability timing prevents these issues.

Advanced linking capabilities:

Multi-level relationship chains (3+ table hops)

Conditional source references (different links based on conditions)

Filtered relationship subsets (only specific related records)

Self-referencing patterns (record comparing to others in same table)

Many-to-many relationship handling

Temporal filtering (date-based record selection)

Part 1: Multi-Level Relationship Chains

Three-Table Relationships

Accessing data three or more levels removed requires chained source references.

Validation Set: Sales Line - Vendor Lead Time Check - OnValidate

Rule 1: Validate Delivery Promise Against Vendor Lead TimeTable: Sales Line (37)

Source References:

1. Sales Header (36)
   Link via: [37:3] = [36:3]
   // Sales Line → Sales Header (document relationship)

2. Item (27)
   Link via: [37:6] = [27:1]
   // Sales Line → Item (item details)

3. Item Vendor (99000785)
   Link via: [27:1] = [99000785:2]
   // Item → Item Vendor (vendor relationships)
   Reference Filters:
     [99000785:4] is true  // Preferred vendor

4. Vendor (23)
   Link via: [99000785:1] = [23:1]

Relationship Chain:

Sales Line (37)
  → Sales Header (36) [document]
  → Item (27) [item details]
  → Item Vendor (99000785) [vendor linkage]
  → Vendor (23) [vendor master data]

Condition:

[36:73] < [T] + [99000785:5]

// Requested receipt date less than today + vendor lead time

Where:
[36:73] = Requested Receipt Date (Sales Header)
[99000785:5]

Action - Message:

Type: Warning

Message:
DELIVERY DATE MAY NOT BE ACHIEVABLE

Item: [27:3]
Requested Receipt: [36:73]
Vendor: [23:2]
Vendor Lead Time: [99000785:5] days
Earliest Possible: [T + [99000785:5]]

Requested date is earlier than vendor lead time allows.

Options:
1. Adjust requested date to [T + [99000785:5]

Why Multi-Level: Combines sales document timing (Sales Header), item identification (Item), vendor relationship (Item Vendor), and lead time data (Vendor) to validate delivery feasibility.

Four-Table Complex Relationships

Rule 2: Customer Payment History AnalysisValidation Set: Sales Order - Payment Risk Assessment - OnInsert

Source References:

1. Customer (18)
   Link via: [36:2] = [18:1]
   // Sales Header → Customer

2. Customer Ledger Entry (21)
   Link via: [18:1] = [21:3]
   Reference Filters:
     [21:13] is false  // Open
     [21:6] is 'Invoice'

3. Detailed Customer Ledger Entry (379)
   Link via: [21:5] = [379:2]
   // CLE → Detailed CLE
   Reference Filters:
     [379:7] is 'Payment'

4. Payment Method (289)
   Link via: [18:64] = [289:1]

Condition:

COUNT(21:* WHERE [21:17] < [T]) > 0
AND [289:CustomRequiresPreapproval]

Action - Message:

Type: Error

Message:
PAYMENT PREAPPROVAL REQUIRED

Customer: [18:2]
Payment Method: [289:1] - [289:2]

Past Due Invoices: [COUNT(21:* WHERE [21:17] < [T])]
Total Past Due Amount: $[SUM(21:14 WHERE [21:17] < [T])]
Days Overdue (Average): [AVG([T] - [21:17] WHERE [21:17] < [T]

Why Four Tables: Combines customer info, ledger analysis, payment history, and payment method rules for comprehensive risk assessment.

Part 2: Filtered Relationship Subsets

Date-Filtered References

Source references can include date filters to access time-specific data subsets.

Validation Set: Customer - Recent Activity Analysis - OnModify

Rule 1: Active Customer ValidationTable: Customer (18)

Source References:

1. Sales Header (36) - Recent Orders
   Link via: [18:1] = [36:2]
   Reference Filters:
     [36:20] >= [T] - 365  // Last 365 days
     [36:1] is 'Order'
     [36:120] is not 'Open'  // Posted orders only

2. Sales Header (36) - Open Orders [Separate Reference]
   Link via: [18:1] = [36:2]
   Reference Filters:
     [36:1] is 'Order'
     [36:120] is 'Open'

3. Customer Ledger Entry (21) - Recent Payments
   Link via: [18:1] = [21:3]
   Reference Filters:
     [21:6] is 'Payment'
     [21:4] >= [T]

Condition:

[18:CustomActivityStatus] is 'Inactive'
AND (COUNT(36[Recent]:*) > 0 OR COUNT(36[Open]:*

Action - Assign:

Target: [18:CustomActivityStatus]
Formula: 'Active'

Target: [18:CustomLastActivityDate]
Formula: [T]

Why Filtered References: Multiple filtered references to same table (Sales Header) distinguish between recent posted orders, open orders, and enable independent analysis of each subset.

Status-Filtered References

Rule 2: Order Pipeline AnalysisValidation Set: Item - Availability Calculation - OnModify

Source References:

1. Sales Line (37) - Open Order Demand
   Link via: [27:1] = [37:6]
   Reference Filters:
     [37:SalesHeader:1] is 'Order'
     [37:SalesHeader:120] is 'Open'

2. Sales Line (37) - Quote Potential Demand
   Link via: [27:1] = [37:6]
   Reference Filters:
     [37:SalesHeader:1] is 'Quote'
     [37:SalesHeader:20] >= [T] - 30  // Recent quotes

3. Purchase Line (39) - On Order Supply
   Link via: [27:1] = [39:6]
   Reference Filters:
     [39:PurchaseHeader:1] is 'Order'
     [39:PurchaseHeader:120] is 'Open'

4. Purchase Line (39) - Planned Supply
   Link via: [27:1] = [39:6]
   Reference Filters:
     [39:PurchaseHeader:1]

Condition:

[27:Inventory] + SUM(39[OnOrder]:15) - SUM(37[OpenOrders]:15) < [27:15]

Action - Assign:

Target: [27:CustomAvailableInventory]
Formula: [27:Inventory] + SUM(39[OnOrder]:15) - SUM(37[OpenOrders]:15)

Target: [27:CustomReorderUrgency]
Formula: IF([27:CustomAvailableInventory] < 0, 'Critical',
           IF([27:CustomAvailableInventory] < [27:15] * 0.5, 'Urgent',
             IF([27:CustomAvailableInventory] < [27:15], 'Normal', 'None'
           )
         )

Target: [27:CustomPotentialDemand]
Formula: SUM(37[Quotes]

Why Status Filters: Different document types and statuses represent different demand/supply categories requiring separate analysis.

Part 3: Conditional Source References

References Based on Field Values

Source references can vary based on conditions in the current record.

Validation Set: Sales Order - Dynamic Approver Assignment - OnModify

Rule 1: Determine Approver Based on AmountTable: Sales Header (36)

Source References (Conditional):

IF [36:109] > 100000:
  1. User Setup (91)
     Reference Filters:
       [91:CustomApprovalLevel] >= 3  // Executive level
       [91:CustomApprovalCategory] is 'Sales'

ELSE IF [36:109] > 50000:
  1. User Setup (91)
     Reference Filters:
       [91:CustomApprovalLevel] >= 2  // Manager level
       [91:CustomApprovalCategory] is 'Sales'

ELSE IF [36:109] > 10000:
  1. User Setup (91)
     Reference Filters:
       [91:CustomApprovalLevel] >= 1  // Supervisor level
       [91:CustomApprovalCategory]

Implementation Note: While QUALIA doesn't support literal IF statements in source references, this pattern is implemented through multiple rules with different conditions, each having appropriate source references.

Rule Configuration:Rule 1A: Executive Approval AssignmentCondition:

[36:109] > 100000
AND [36:CustomApprovalRequired] is true
AND [36:CustomApproverUserID]

Source References:

1. User Setup (91)
   Reference Filters:
     [91:CustomApprovalLevel] >= 3
     [91:CustomApprovalCategory]

Action:

Assign: [36:CustomApproverUserID] = [91:1]
Assign: [36:CustomApproverName] = [91:2]

Rule 1B: Manager Approval AssignmentCondition:

[36:109] > 50000
AND [36:109] <= 100000
[... similar pattern ...]

Customer Type-Based References

Rule 2: Dynamic Pricing SourceValidation Set: Sales Line - Pricing Logic - OnValidate

Different rules for different customer types:Rule 2A: Contract CustomersCondition:

[Customer:CustomCustomerType]

Source References:

1. Customer Price Agreement (Custom)
   Link via: [37:6] = [CPA:ItemNo]
         AND [36:2] = [CPA:CustomerNo]
   Reference Filters:
     [CPA:StartDate] <= [T]
     [CPA:EndDate] >= [T]

Rule 2B: Volume CustomersCondition:

[Customer:CustomCustomerType]

Source References:

1. Volume Discount Table (Custom)
   Link via: [37:6] = [VDT:ItemNo]
   Reference Filters:
     [VDT:MinimumQuantity] <= [37:15]

Rule 2C: Standard CustomersCondition:

[Customer:CustomCustomerType]

Source References:

1. Item (27)
   Link via: [37:6] = [27:1]

Part 4: Self-Referencing Patterns

Comparing Record to Others in Same Table

Self-references enable comparing current record to other records in the same table.

Validation Set: Item - Pricing Consistency Check - OnModify

Rule 1: Validate Price Against Similar ItemsTable: Item (27)

Source References:

1. Item (27) - Similar Items [Self-Reference]
   Link via: [27:CustomItemGroup] = [27[Similar]:CustomItemGroup]
   Reference Filters:
     [27[Similar]:1] <> [27:1]  // Exclude current item
     [27[Similar]

Scenario:

Scenario 1: {27:27} is <>[27:27]

Condition:

Condition 1: [27:27] is <{AVG(27[Similar]:27) * 0.7}
// Price below 70% of similar items average

Condition 2: [27:27] is >{AVG(27[Similar]

Action - Message:

Type: Warning

Message:
PRICE OUTLIER DETECTED

Item: [27:1] - [27:3]
Item Group: [27:CustomItemGroup]
New Price: $[27:27]

Similar Items Average Price: $[AVG(27[Similar]:27)]
Your Price vs. Average: [[27:27] / AVG(27[Similar]:27) * 100]%

[IF([27:27] < AVG(27[Similar]:27), 'BELOW', 'ABOVE')] average by [[27:27] / AVG(27[Similar]:27) - 1] * 100]%

Significant price deviation from similar items detected.
Verify pricing is intentional.

Similar Items:
[FOR EACH 27[Similar] TOP 5:
  [27[Similar]:1] - [27[Similar]:3]: $[27[Similar]

Why Self-Reference: Comparing item to others in same table with similar characteristics enables relative validation.

Duplicate Detection

Rule 2: Prevent Duplicate EntryValidation Set: Customer - Duplicate Prevention - OnInsert

Source References:

1. Customer (18) - Potential Duplicates
   Reference Filters:
     [18[PD]:2] = [18:2]  // Same name
     OR [18[PD]:102] = [18:102]  // Same email
     OR [18[PD]:5] = [18:5]

Condition:

COUNT(18[PD]

Action - Message:

Type: Warning

Message:
POTENTIAL DUPLICATE CUSTOMER

You are creating:
Name: [18:2]
Email: [18:102]
VAT Reg. No.: [18:5]

Possible existing customers:
[FOR EACH 18[PD]:
  Customer No.: [18[PD]:1]
  Name: [18[PD]:2]
  Address: [18[PD]:5]
  Email: [18[PD]

Part 5: Many-to-Many Relationships

Handling Junction Tables

Many-to-many relationships use junction tables to connect entities.

Validation Set: Item - Sales Campaign Pricing - OnModify

Scenario: Items can belong to multiple campaigns, campaigns can include multiple items.

Tables:

Item (27)

Campaign (Custom5050)

Campaign Item (Custom5051) [Junction Table]

Source References:

1. Campaign Item (Custom5051)
   Link via: [27:1] = [5051:ItemNo]
   Reference Filters:
     [5051:EndDate] >= [T]  // Active campaigns

2. Campaign (5050)
   Link via: [5051:CampaignNo] = [5050:No]
   Reference Filters:
     [5050:Status]

Relationship:

Item (27)
  → Campaign Item (5051) [junction]
  → Campaign (5050) [campaign details]

Condition:

{27:27} <> [27:27]

Action - Message:

Type: Warning

Message:
ITEM IN ACTIVE CAMPAIGNS

Item: [27:3]
New Price: $[27:27]

This item is featured in active sales campaigns:

[FOR EACH 5050:
  Campaign: [5050:Description]
  End Date: [5050:EndDate]
  Campaign Price: $[5051:PromotionalPrice]

Part 6: Performance Optimization

Efficient Filtering

Optimize source references with appropriate filters:

✓ Good: Specific filters reduce record set
  Reference Filters:
    [36:20] >= [T] - 90
    [36:120] is 'Open'
    [36:109]

Use field indexes:

✓ Good: Filter on indexed fields
  [36:20] >= [T] - 90  // Date fields typically indexed

✗ Poor: Filter on non-indexed custom fields
  [36:CustomCalculatedField]

Minimize Relationship Depth

Limit chain length when possible:

✓ Good: Direct 2-table relationship
  Sales Header → Customer
  [36:2] = [18:1]

Cache calculated values:

✓ Good: Store frequently accessed calculation
  [18:CustomTotalOrderValue]

Test with Production Data Volumes

Performance acceptable in test environment (100 records) may degrade in production (100,000 records).

Testing approach:

Create representative data volumes

Enable performance monitoring

Execute validation scenarios

Measure execution time

Identify bottlenecks

Optimize source references and filters

Retest with optimizations

Part 7: Testing Complex Relationships

Verification Checklist

All source references return expected records

Filters correctly limit record sets

Multi-level chains traverse correctly

Self-references exclude current record appropriately

Aggregates calculate correctly across filtered sets

Performance acceptable with production volumes

Rules handle missing relationships gracefully

Date filters account for timezone if relevant

Many-to-many junctions traverse properly

Conditional references select appropriate paths

Common Issues

Issue: Source reference returns no records

Verify linking fields contain matching values

Check filter criteria not overly restrictive

Confirm related records exist in test data

Validate field types match (text vs. code)

Issue: Unexpected records included

Review filter logic for gaps

Check for null/empty field handling

Verify date filter boundaries

Confirm status field values

Issue: Performance degradation

Add indexes to filter fields

Narrow filter criteria

Reduce relationship chain depth

Cache frequently calculated values

Summary and Key Takeaways

This guide covered advanced table linking patterns in QUALIA Rule Engine:

Multi-level chains accessing data through 3+ table relationships

Filtered references using date, status, and field-based filters

Conditional references varying links based on record conditions

Self-referencing comparing records to others in same table

Many-to-many handling junction table relationships

Performance optimization through filtering and indexing

Practical applications:

Vendor lead time validation across order→item→vendor

Customer payment risk assessment across multiple ledger tables

Inventory availability calculating demand and supply from multiple sources

Pricing logic varying by customer type and agreements

Duplicate detection comparing to existing records

Campaign pricing coordination across junction tables

Implementation exercise: Create multi-table credit validation:

Identify tables: Customer, Ledger Entries, Sales Orders

Define relationships: Customer→CLE (balance), Customer→Orders (open orders)

Add filters: Open ledger entries, non-posted orders

Calculate exposure: Balance + open orders

Compare to credit limit

Test with various customer scenarios

Optimize filters for performance

Related topics:

Blog 024: Aggregate Calculations (SUM, COUNT across references)

Blog 030: Understanding Scenarios (data availability timing)

Blog 032: Testing and Debugging (verifying relationships)

Blog 033: Performance Optimization (efficient reference filtering)

This blog is part of the QUALIA Rule Engine series for Microsoft Dynamics 365 Business Central. Follow along as we explore business rule automation patterns.