Evaluate Decision Table

Description

The Evaluate Decision Table node allows workflows to perform conditional evaluations based on structured rule tables or legacy-style logical expressions.
It connects to a configured Microsoft SQL Server service account and safely executes conditions against SQL-compatible syntax to determine which rule applies.

This node is used to:

• Implement complex business logic (e.g., discount rules, scoring systems, routing conditions)
• Replace large nested IF statements with a structured decision table
• Evaluate numeric, boolean, string, or JSON results dynamically from database-compatible expressions

---

How to Use

When added to a workflow, this node presents a configuration form with the following fields.

Field	Type	Description	Required	Visible When
ServiceAccount - Microsoft SQL Server	Service Account Picker	Select the SQL Server connection to use for evaluating the decision table.	✅	Always
Evaluation Mode	Picklist	Determines the type of evaluation. Options: - Structured Table (JSON-based rule table) - Legacy Expression (single text formula)	✅	Always
Rules JSON	JSON Array	Defines the decision table in structured JSON format (used when Evaluation Mode = Structured Table).	✅	When Evaluation Mode = Structured Table
Legacy Equation	Text	Defines a single line or comma-separated set of var = value IF condition expressions (used when Evaluation Mode = Legacy Expression).	✅	When Evaluation Mode = Legacy Expression
Else Value	Text	Fallback value to return when no rules match.	❌	Always
Return Type	Picklist	Defines the type of result to return. Options: number, string, boolean, json	✅	Always
Stop At First Match	Boolean	When enabled, evaluation stops at the first matching condition. When disabled, all rules are evaluated and the last match is used.	❌	Always
Condition Safety	Picklist	Controls how strictly conditions are checked for safe SQL syntax. Options: - Lenient (no checks) - Safe (sanitize) (default) - Strict (allowlist)	❌	Always
Allowed Columns	Text (comma-separated)	Required only when using Strict safety mode. Lists the column names allowed in conditions.	❌	When Condition Safety = Strict
Diagnostics Level	Picklist	Controls the level of diagnostic information returned. Options: none, basic, verbose	❌	Always

---

Evaluation Modes

🧩 Structured Table (Recommended)

This mode evaluates a list of condition-value pairs defined as a JSON rule table.

Example Rules JSON

[
  { "Label": "Gold Tier", "Condition": "Spend > 10000 AND Tier = 'Gold'", "Value": 0.15 },
  { "Label": "Silver Tier", "Condition": "Spend > 5000 AND Tier = 'Silver'", "Value": 0.10 },
  { "Label": "Default", "Condition": "1=1", "Value": 0.05 }
]

Behavior:

• Each rule is checked in order.
• The first (or last, depending on Stop At First Match) condition that evaluates TRUE is returned.
• The result is coerced into the selected Return Type (e.g., number, string, boolean, or JSON).

---

⚙️ Legacy Expression Mode

This mode supports a compact, text-based syntax similar to Excel or traditional business logic scripts.

Example Legacy Equation

Discount = 0.15 IF Tier='Gold' AND Spend>10000,
Discount = 0.10 IF Tier='Silver' AND Spend>5000,
Discount = 0.05 IF Spend>0

Behavior:

• Each clause uses the format: Variable = Value IF Condition
• Clauses are separated by commas.
• The first matching condition returns the corresponding value.

---

Safety Modes

Mode	Description
Lenient	No validation. All SQL-like syntax is allowed. (Use with caution)
Safe (default)	Disallows unsafe SQL characters (;, --, /* ... */) and overly long expressions.
Strict	Additionally restricts allowed identifiers to those explicitly listed in Allowed Columns.

Example of Allowed Columns: Spend, Tier, Discount

---

Return Types

Type	Description	Example
number	Numeric result (float).	0.15
string	String result.	"Approved"
boolean	Returns true or false.	true
json	Returns a JSON fragment or object.	{"status":"ok","value":5}

---

Example Configurations

Example 1 – Tiered Discount Table

Setting	Example Value
Evaluation Mode	Structured Table
Rules JSON	(see JSON example above)
Else Value	0.0
Return Type	number
Stop At First Match	true
Diagnostics Level	basic

Result: If Spend > 10000 and Tier='Gold', the result is 0.15. Otherwise, falls back to 0.0.

---

Example 2 – Boolean Legacy Logic

Setting	Example Value
Evaluation Mode	Legacy Expression
Legacy Equation	IsHighValue = true IF Spend > 50000
Return Type	boolean
Else Value	false

Result: Returns true if Spend > 50000, otherwise false.

---

Example 3 – Safe Mode with Strict Allowlist

Setting	Example Value
Evaluation Mode	Structured Table
Rules JSON	[{"Label":"Valid","Condition":"Spend > 1000 AND Tier='Gold'","Value":"Approved"}]
Condition Safety	Strict (allowlist)
Allowed Columns	Spend, Tier

Result: Evaluates safely — any column outside of the allowlist causes the rule to be skipped or rejected.

---

Outputs

After execution, the node produces one of the following outputs:

Output Variable	Type	Description
Result	Varies	The value returned by the matched rule, coerced to the selected Return Type.
Diagnostics	Object	(When Diagnostics Level ≠ none) Includes evaluation details such as which rule matched.

---

Example Output (Basic Diagnostics)

{
  "Result": 0.15,
  "Diagnostics": {
    "EvaluatedCount": 3,
    "MatchedIndex": 0,
    "MatchedLabel": "Gold Tier"
  }
}

---

Example Workflow Usage

1. Use a Data Preparation or SQL Query node to populate variables (e.g., Spend, Tier).
2. Add the Evaluate Decision Table node to determine a result such as discount, classification, or decision.
3. Use the output (Result) in downstream workflow nodes (e.g., D365UpdateRecord or EmailNotification).
4. Optionally, enable Diagnostics Level = verbose for debugging rule behavior.

---

Status Messages

Status	Description
Result	Evaluation completed successfully and returned a value.
Diagnostics	Evaluation completed successfully and returned diagnostics data.
Fail	Evaluation failed, no rule matched, or unsafe condition detected.

---

Error Handling

If the evaluation fails, the node stops with a Fail status. Common causes include:

• No matching rule found and no Else Value defined
• Invalid JSON structure in Rules JSON
• Unsafe SQL syntax or disallowed identifiers
• Connection failure to SQL Server
• Invalid data type conversion (e.g., string provided where number expected)

---

Example JSON (for reference only)

{
  "ServiceAccount-Microsoft SQL Server": "SQLProdConnection",
  "Evaluation Mode": "Structured Table",
  "Rules JSON": [
    { "Label": "High Spend", "Condition": "Spend > 10000", "Value": 0.15 },
    { "Label": "Medium Spend", "Condition": "Spend > 5000", "Value": 0.1 },
    { "Label": "Default", "Condition": "1=1", "Value": 0.05 }
  ],
  "Else Value": "0.0",
  "Return Type": "number",
  "Stop At First Match": true,
  "Condition Safety": "Safe (sanitize)",
  "Diagnostics Level": "basic"
}

---

Notes

• SQL-style conditions are evaluated server-side against the connected SQL Server environment.
• Always ensure that Condition Safety is set appropriately when referencing dynamic data.
• Use Strict mode in production when conditions come from user input.
• The node uses CASE WHEN SQL logic to safely evaluate rule conditions.
• Diagnostics can be disabled for performance optimization.

---

Category: Evaluation & Logic Task Name: EvaluateDecisionTable