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.
