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 Time

Table: 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 Analysis

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

Table: 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 Analysis

Validation 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 Amount

Table: 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 Assignment

Condition:

[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 Assignment

Condition:

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

Customer Type-Based References

Rule 2: Dynamic Pricing Source

Validation Set: Sales Line - Pricing Logic - OnValidate

Different rules for different customer types:

Rule 2A: Contract Customers

Condition:

[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 Customers

Condition:

[Customer:CustomCustomerType]

Source References:

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

Rule 2C: Standard Customers

Condition:

[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 Items

Table: 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 Entry

Validation 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:

1. Create representative data volumes

2. Enable performance monitoring

3. Execute validation scenarios

4. Measure execution time

5. Identify bottlenecks

6. Optimize source references and filters

7. 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:

1. Identify tables: Customer, Ledger Entries, Sales Orders

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

3. Add filters: Open ledger entries, non-posted orders

4. Calculate exposure: Balance + open orders

5. Compare to credit limit

6. Test with various customer scenarios

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