Working with Linked Tables

Linked Tables (also called Source References) are one of the most powerful features in QUALIA Rule Engine because they enable your business rules to access data from tables beyond the primary trigger table. Without Linked Tables, your validation formulas can only reference fields in the trigger table itself. With Linked Tables, you can validate data across related tables, check customer credit limits, verify inventory availability, and implement complex cross-table business logic. This section explains when Linked Tables are necessary, how to configure them correctly, and common patterns for multi-table validation.

18.1 Understanding When You Need Linked Tables

Your business rules always have a trigger table—the table where the triggering database operation occurs. When users create or modify records in the trigger table, your rules execute. However, many business requirements need to validate data based on information in related tables.

Scenarios Requiring Linked Tables

Credit Limit Validation on Sales Orders: When validating a sales order (trigger table = Sales Header, Table 36), you need to check the customer's credit limit and current balance, which are stored in the Customer table (Table 18). Without a Linked Table to Customer, your rule cannot access credit limit information.

Item Availability Validation on Sales Lines: When validating a sales line (trigger table = Sales Line, Table 37), you may need to check current inventory levels, which are stored in the Item table (Table 27). A Linked Table to Item allows your rule to reference inventory quantity.

Vendor Payment Terms on Purchase Orders: When validating a purchase order (trigger table = Purchase Header, Table 38), you may need to verify the vendor's payment terms or blocked status, which are stored in the Vendor table (Table 23).

Document Header Information from Lines: When validating a sales line (trigger table = Sales Line, Table 37), you often need information from the sales order header, such as customer number, order date, or document status, which are stored in the Sales Header table (Table 36).

Chained Relationships: When validating a sales line, you might need customer information. This requires two linked tables: Sales Line → Sales Header (to get the customer number), then Sales Header → Customer (to get customer credit data).

When Linked Tables Are NOT Needed

If all the data you need to validate exists in the trigger table itself, you do not need Linked Tables. For example, validating that Quantity > 0 on a sales line only requires the Quantity field from the Sales Line table, so no Linked Table is necessary.

18.2 Step-by-Step: Configuring Your First Linked Table

This tutorial walks through configuring a Linked Table to access customer credit information when validating sales orders. The goal is to create a rule that prevents orders from exceeding customer credit limits.

Scenario Setup

Trigger Table: Sales Header (Table 36) - where sales orders are created
Need Access To: Customer table (Table 18) - to read credit limit and balance
Relationship: Sales Header.Sell-to Customer No. = Customer.No.

Step 1: Open the Business Rule Set

1. Navigate to the Business Rules page and open your Sales Header validation rule set

2. From the Business Rule Card page, select the Linked Tables tab

3. This tab displays all Linked Tables currently configured for this Business Rule Set

Step 2: Add a New Linked Table Entry

1. In the Linked Tables section, create a new line (click New or press Enter)

2. A new blank line appears ready for configuration

Step 3: Specify the Related Table

1. Locate the Reference Table field in the new line

2. Enter 18 or use the lookup to search for "Customer"

3. Select 18 - Customer from the lookup

4. This tells QUALIA Rule Engine that you want to access the Customer table from your Sales Header rules

Step 4: Assign a Reference Identifier

1. Locate the Identifier field in the same line

2. Enter a short, unique identifier for this linked table relationship

3. For this example, enter CUST

4. This identifier will be used in your formulas to reference customer fields

💡 TIP: Choose identifiers that are short but meaningful. Common conventions include CUST for Customer, VEND for Vendor, ITEM for Item, HEAD for document headers when triggering on lines.

Step 5: Configure the Join Condition

Now you need to specify how records in the trigger table (Sales Header) relate to records in the linked table (Customer). This is similar to writing a SQL JOIN condition.

1. Locate the Reference Filters subsection within the Linked Tables tab

2. This subsection allows you to define filter conditions that determine which Customer record to retrieve

3. Create a new reference filter line

Step 6: Specify the Customer Field to Filter On

1. In the Filter Field column of the reference filter, specify which field in the Customer table should be filtered

2. Enter 2 or use the lookup to select field 2 (Customer.No.)

3. This specifies that you want to filter Customer records based on their No. field

Step 7: Specify the Filter Type

1. In the Filter Type column, select Field

2. This indicates that the filter value will come from a field in the trigger table (Sales Header)

Step 8: Specify the Sales Header Field to Match

1. In the Filter Value column, specify which field from the Sales Header should match the Customer No.

2. Enter 2 (Sales Header.Sell-to Customer No.)

3. This completes the join condition: Customer.No. = Sales Header.Sell-to Customer No.

Step 9: Configure "If Not Found" Behavior (Optional)

1. Back in the main Linked Table line (not the filter subsection), locate the If Not Found field

2. This field determines what happens if no matching Customer record is found

3. Options typically include: Ignore (continue without error), Error (display error message), or Return Blank (placeholders return blank values)

4. For credit validation, select Ignore or Return Blank (most rules will check field values, and blank credit limits can be handled in conditions)

Step 10: Save and Test the Linked Table

1. Save the Business Rule Set configuration

2. Create a test rule that uses the linked table to verify it works correctly

Step 11: Use the Linked Table in Formulas

Now that the linked table is configured, you can reference Customer fields in your validation formulas using the identifier:

Using Standard Placeholder Syntax:

[18:20]  (Customer.Credit Limit)
[18:59]

QUALIA Rule Engine automatically uses your CUST linked table relationship to retrieve the correct customer record based on the Sales Header's customer number.

Example Validation Formula Using Linked Table:

Condition: {[36:61] + [18:59]} is >[18:20]

This formula reads: "Sales Header Amount + Customer Balance > Customer Credit Limit"

QUALIA Rule Engine:

1. Reads the Sales Header.Amount [36:61] from the current sales order

2. Uses the CUST linked table to find the related Customer record

3. Reads Customer.Balance [18:59] and Customer.Credit Limit [18:20] from that Customer

4. Performs the calculation and comparison

18.3 Configuring Multiple Linked Tables

Many business rules require data from several related tables. You can configure multiple Linked Tables within a single Business Rule Set, and your formulas can reference fields from all linked tables.

Example: Sales Line Validation with Multiple Linked Tables

Trigger Table: Sales Line (Table 37)
Linked Tables Needed:

• Sales Header (Table 36) - to get customer number and order information

• Customer (Table 18) - to get customer credit limit and status

• Item (Table 27) - to get item inventory and pricing information

Configuring the Sales Header Link:

1. Add new Linked Table entry

2. Reference Table: 36 (Sales Header)

3. Identifier: HEAD

4. Reference Filter: Filter Field = 3 (Sales Header.No.), Filter Type = Field, Filter Value = 1 (Sales Line.Document No.)

This creates the relationship: Sales Header.No. = Sales Line.Document No.

Configuring the Customer Link (Chained Through Sales Header):

1. Add new Linked Table entry

2. Reference Table: 18 (Customer)

3. Identifier: CUST

4. Reference Filter: Filter Field = 2 (Customer.No.), Filter Type = Field, Filter Value = 36:2 (Sales Header.Sell-to Customer No.)

This creates a chained relationship: Sales Line → Sales Header → Customer

⚠️ CRITICAL: When chaining relationships, ensure the intermediate table (Sales Header) is configured as a Linked Table before configuring the table that depends on it (Customer). QUALIA Rule Engine processes Linked Tables in the order they are defined.

Configuring the Item Link:

1. Add new Linked Table entry

2. Reference Table: 27 (Item)

3. Identifier: ITEM

4. Reference Filter: Filter Field = 1 (Item.No.), Filter Type = Field, Filter Value = 6 (Sales Line.No.)

This creates the relationship: Item.No. = Sales Line.No.

Using Multiple Linked Tables in Formulas:

Once all three linked tables are configured, your formulas can reference fields from any of them:

Condition 1: [27:30] is <[37:15]

Checks if Item.Inventory < Sales Line.Quantity (insufficient inventory)

Condition 2: {[36:61] + [18:59]} is >[18:20]

Checks if Sales Header.Amount + Customer.Balance > Customer.Credit Limit (exceeds credit)

Condition 3: [18:39]

Checks if Customer.Blocked is not blank (customer is blocked)

18.4 Advanced Reference Filter Types

The Reference Filter Type determines how QUALIA Rule Engine determines the filter value when searching for related records. Understanding the different filter types enables sophisticated relationship configurations.

Filter Type: Field

The most common filter type. The filter value comes from a field in the trigger table or a previously configured linked table.

Syntax: Filter Value = FieldID or TableID:FieldID

Example:

• Filter Field: 2 (Customer.No.)

• Filter Type: Field

• Filter Value: 2 (get value from Sales Header.Sell-to Customer No.)

Filter Type: Const

The filter value is a constant (literal value) that you specify directly.

Syntax: Filter Value = literal value

Example - Only link to customers in a specific region:

• Filter Field: 35 (Customer.Country/Region Code)

• Filter Type: Const

• Filter Value: 'US'

This retrieves only Customer records where Country/Region Code = 'US'.

Filter Type: FlowFilter

The filter value comes from a FlowFilter field, which is a special Business Central field type used for calculations.

When to Use: FlowFilter types are rarely needed in typical business rules. Use Field type for normal field references.

Combining Multiple Reference Filters

You can define multiple Reference Filter lines for a single Linked Table. All filters must be satisfied (AND logic).

Example - Link to customers in US region only:

Reference Filters for CUST linked table:

1. Filter Field = 2 (Customer.No.), Filter Type = Field, Filter Value = 2 (Sales Header customer)

2. Filter Field = 35 (Country/Region Code), Filter Type = Const, Filter Value = 'US'

Only Customer records matching both conditions will be retrieved.

18.5 Common Linked Table Scenarios and Configuration Patterns

This section provides ready-to-use configuration patterns for common business scenarios.

Pattern 1: Sales Order to Customer

Use Case: Access customer data when validating sales orders.

Configuration:

• Trigger Table: 36 (Sales Header)

• Linked Table: 18 (Customer), Identifier: CUST

• Filter: Customer.No. (Field 2) = Sales Header.Sell-to Customer No. (Field 2)

Available Fields: Credit Limit [18:20], Balance [18:59], Blocked [18:39], Payment Terms [18:21], Customer Name [18:2]

Pattern 2: Sales Line to Sales Header

Use Case: Access order header information when validating individual line items.

Configuration:

• Trigger Table: 37 (Sales Line)

• Linked Table: 36 (Sales Header), Identifier: HEAD

• Filter: Sales Header.No. (Field 3) = Sales Line.Document No. (Field 1)

Available Fields: Sell-to Customer No. [36:2], Order Date [36:20], Status [36:120], Amount [36:61]

Pattern 3: Sales Line to Item

Use Case: Access item inventory and attributes when validating sales line quantities.

Configuration:

• Trigger Table: 37 (Sales Line)

• Linked Table: 27 (Item), Identifier: ITEM

• Filter: Item.No. (Field 1) = Sales Line.No. (Field 6)

Available Fields: Description [27:3], Inventory [27:30], Unit Price [27:18], Item Category [27:5]

Pattern 4: Chained Sales Line → Header → Customer

Use Case: Access customer data when validating sales lines (requires two-step relationship).

Configuration:

First Linked Table:

• Trigger Table: 37 (Sales Line)

• Linked Table: 36 (Sales Header), Identifier: HEAD

• Filter: Sales Header.No. (Field 3) = Sales Line.Document No. (Field 1)

Second Linked Table:

• Trigger Table: 37 (Sales Line)

• Linked Table: 18 (Customer), Identifier: CUST

• Filter: Customer.No. (Field 2) = Sales Header.Sell-to Customer No. (Field 36:2)

Available Fields: All Customer fields accessible via Customer table placeholders

Pattern 5: Purchase Order to Vendor

Use Case: Access vendor data when validating purchase orders.

Configuration:

• Trigger Table: 38 (Purchase Header)

• Linked Table: 23 (Vendor), Identifier: VEND

• Filter: Vendor.No. (Field 2) = Purchase Header.Buy-from Vendor No. (Field 2)

Available Fields: Payment Terms [23:21], Blocked [23:39], Balance [23:59], Vendor Name [23:2]

18.6 Troubleshooting Linked Tables

When business rules using Linked Tables do not work as expected, systematic troubleshooting can quickly identify the problem.

Problem: Formula References Linked Table Field But Returns Blank

Symptoms: Your formula references a field from a linked table (for example, [18:20] for Customer Credit Limit), but the validation log or behavior suggests the value is blank or zero.

Diagnosis Steps:

1. Verify the Linked Table is configured correctly with the right Reference Table number

2. Verify the join condition (Reference Filters) specifies the correct fields

3. Check whether a related record actually exists (for example, does the customer number on the sales order exist in the Customer table?)

4. Verify field IDs are correct (use TableFieldMapping.csv to confirm)

5. Check "If Not Found" setting—if set to "Return Blank," missing relationships return blank values without errors

Common Causes:

• Join condition uses wrong fields (for example, matching on Description instead of No.)

• Related record does not exist in the linked table

• Field ID typo in the Reference Filter configuration

Problem: Rule Executes Slowly or Times Out

Symptoms: Saving records takes significantly longer when the rule is active. Users report system slowness.

Diagnosis Steps:

1. Check how many Linked Tables are configured—each requires a database lookup

2. Verify you are using Scenarios to filter out records that do not need validation

3. Check whether Reference Filters use indexed fields (No., Code fields are usually indexed; Description fields usually are not)

4. Review formula complexity—multiple linked table references in calculations increase processing time

Solutions:

• Add Scenarios to filter out 90%+ of records before Linked Table lookups occur

• Minimize the number of Linked Tables to only those actually needed

• Use indexed fields in join conditions when possible

• Consider breaking one complex rule into multiple simpler rules

Problem: Chained Relationships Do Not Work

Symptoms: You configured Sales Line → Sales Header → Customer, but formulas cannot access Customer fields.

Diagnosis Steps:

1. Verify Linked Tables are defined in the correct order (Sales Header must be defined before Customer)

2. Check that the second link's Reference Filter correctly references the intermediate table using TableID:FieldID syntax (for example, 36:2 for Sales Header.Customer No.)

3. Test each link individually to verify they work separately before combining them

Common Mistake: Defining the Customer link before the Sales Header link. QUALIA Rule Engine processes Linked Tables sequentially, so dependencies must be defined in order.

Problem: Formula Works in Test But Not Production

Symptoms: Your rule with Linked Tables works correctly in sandbox testing but fails in production.

Possible Causes:

• Test data has related records that production data lacks (for example, all test customers have valid numbers, but production has orphaned orders)

• Permission differences between test and production users

• Data quality issues in production (blank or invalid foreign key values)

Solution: Review production data quality and add error handling (use "If Not Found" settings appropriately).