Relational Data Loop Node in Pathways

The Relational Data Loop node allows workflows in Pathways to iterate through rows of data returned from a relational database query. This node enables automation of tasks for each record, making it ideal for operations like processing multiple entries, sending individualized notifications, or generating reports.

---

Node Properties

1. Name

• Description: A unique identifier for the node within the workflow.
• Usage: Use a clear and descriptive name that reflects the purpose of the loop (e.g., "Process Claims" or "Iterate Over Customers").
• Example: "Send Invoice Notifications"

---

2. Iterator Variable

• Description: Defines the variable name that will hold the current row of data during each iteration.
• Usage: Use a meaningful name to represent the current data row (e.g., current_record or claim_entry).
• Example: current_claim

---

3. Query

• Description: Specifies the SQL-like query used to retrieve the relational data for iteration.
• Usage: Provide a valid query to fetch rows from the desired relational table.
• Example:
  • Query: SELECT * FROM claims WHERE status = 'Pending'
  • Purpose: Fetch all pending claims for processing.

---

Query Parameters

The Query Parameters section allows for additional customization and dynamic generation of the query.

3.1 Name

• Description: Assigns a name to each parameter used within the query.
• Usage: Use meaningful names to describe the parameter (e.g., Status, DateRange).
• Example: ClaimStatus

3.2 Expression

• Type: Handlebar Template or Script
• Description: Defines the value of the query parameter dynamically.
• Options:
  • Handlebar Template: Use placeholders to inject dynamic values into the query.

Example:

{{status}}

• Where status is a variable from the workflow.

• Script: Write custom logic to generate dynamic values using a scripting language.

Example (Script):

if user_role == "Admin":

   return "Approved"

else:

   return "Pending"

• Expression Field:
  • Status: Required.
  • Usage: Define the logic or value for each parameter to be used in the query.

---

How It Works

1. Query Execution:
   • The query is executed to fetch data from the relational database.
   • Query parameters, if defined, are dynamically substituted before execution.
2. Row Iteration:
   • For each row of data returned by the query, the workflow iterates and assigns the row to the Iterator Variable.
   • Each iteration processes the current row before moving to the next.
3. Downstream Node Execution:
   • Subsequent nodes in the workflow can use the data in the iterator variable to perform tasks like sending notifications, updating records, or generating documents.

---

Use Case Examples

1. Process Pending Claims

• Name: Process Pending Claims
• Iterator Variable: current_claim

Query:

SELECT * FROM claims WHERE status = 'Pending'

• Query Parameter:
  • Name: Status
  • Expression (Handlebars): {{claim_status}}
• Outcome: Processes each pending claim in the database.

2. Notify Customers of Payment Due

• Name: Notify Overdue Payments
• Iterator Variable: customer_record

Query:

SELECT * FROM invoices WHERE due_date < NOW() AND status = 'Unpaid'

• Query Parameter:
  • Name: DueDate

Expression (Script):

return f"{today() - timedelta(days=30)}"

• Outcome: Sends notifications to customers with overdue invoices.

---

Best Practices

1. Validate Queries:
   • Ensure your query retrieves the correct data before linking it to the workflow.
2. Use Descriptive Variables:
   • Use clear names for the iterator variable to avoid confusion in downstream tasks.
3. Leverage Query Parameters:
   • Dynamically define query parameters using scripts or templates to make workflows adaptable.
4. Efficient Queries:
   • Limit the number of rows retrieved to optimize workflow performance.

---

The Relational Data Loop node is an essential tool for automating tasks that involve iterating through database records. Its flexibility with handlebar templates and scripts allows for dynamic query generation and advanced data handling in workflows.