ETL Integration

Forms integrate seamlessly with ETL (Extract, Transform, Load) chains to validate, transform, and process form data. This guide explains how to use ETL with forms.

Overview

ETL chains can be used with forms for:

  • Validation - Validate form data before submission
  • Data Transformation - Transform form data before storage
  • Business Logic - Apply complex business rules
  • External Integration - Update external systems
  • Notifications - Send emails or messages
  • Data Enrichment - Add calculated or looked-up data

How It Works

User submits form → Client validation → Server receives data
                                        ↓
                                   ETL validation
                                        ↓
                                   ETL processing
                                        ↓
                                   Store results
                                        ↓
                                   Return response

Validation with ETL

Validation Endpoint

To use an ETL chainset for form validation, it must include a Validation Endpoint step.

Purpose: Marks a chainset as a validator so it appears in form configuration.

Steps:

  1. Create an ETL chainset
  2. Add validation logic steps
  3. Add Validation Endpoint step (from Result category)
  4. The chainset now appears in form’s Validator dropdown

Example Chainset:

Validate Employee Form
├── Step 1: Check email format
├── Step 2: Check employee ID exists
├── Step 3: Check hire date is valid
├── Step 4: Validation Endpoint (marker)
└── Returns: validationIssues array

Validation Issues Format

Validation chainsets should return a validationIssues array:

{
  "validationIssues": [
    {
      "field": "email",
      "message": "Email address is already in use"
    },
    {
      "field": "hireDate",
      "message": "Hire date cannot be in the future"
    }
  ]
}

Field Mapping: - field - Component ID in the form - message - Error message to display

Form-Level Validation

Configure validation for the entire form:

  1. Select root in form structure
  2. In Component Properties, find Validator
  3. Select your validation chainset
  4. Validation runs on form submission

Page-Level Validation

Configure validation for a specific page:

  1. Select Page component
  2. In Component Properties, find Validator
  3. Select your validation chainset
  4. Validation runs when leaving the page

Validation Flow

  1. User clicks Submit
  2. Client-side validation runs (mandatory fields, formats)
  3. If client validation passes, data sent to server
  4. Server-side validation ETL runs
  5. If validation fails, errors returned to form
  6. If validation passes, submission continues

Form Submission Processing

Submit Button ETL

Button Bar components can trigger ETL chains on submission.

Configuration:

  1. Add Button Bar to form
  2. In properties, configure Submit button
  3. Select ETL chainset to run on submit
  4. Chain processes form data

Example:

Process Leave Request
├── Step 1: Validate dates
├── Step 2: Check leave balance
├── Step 3: Create workflow instance
├── Step 4: Send notification
└── Step 5: Set redirect URL

Accessing Form Data in ETL

Form data is available in the ETL record:

{
  "employeeName": "John Smith",
  "leaveType": "Annual",
  "startDate": "2026-03-10",
  "endDate": "2026-03-14",
  "reason": "Family vacation"
}

ETL Steps can: - Read field values - Transform data - Add new fields - Remove fields - Validate values

Setting Redirect URL

Control where user goes after submission:

Using ETL Step:

Add a step that sets elxRedirect:

{
  "elxRedirect": "/dashboard/my-requests"
}

Using form.SetElxRedirect Step:

Chain: Process Form
├── Step 1: Process data
├── Step 2: form.SetElxRedirect
│   └── url: "/dashboard/success"
└── User redirected to URL

Form-Specific ETL Steps

The Form module provides 14 specialized ETL steps:

form.NewFormInstance

Purpose: Create a form with workflow instance

Parameters: - formId - Form to open - workflowId - Workflow to create

Returns: - formId - Form ID - workflowId - Workflow ID - instanceId - Workflow instance ID - location - URL to redirect to - statusCode - HTTP redirect code

Use Case: Button that creates a new leave request

Example:

Button Click Chain
├── form.NewFormInstance
│   ├── formId: "leave-request"
│   └── workflowId: "leave-approval"
└── Redirects to form with new workflow instance

form.NewFormInstanceField

Purpose: Create form instance with specific field data

Parameters: - formId - Form to open - workflowId - Workflow to create - fieldName - Field to populate - fieldValue - Value to set

Use Case: Pre-populate form with data

Example:

Create Pre-filled Form
├── form.NewFormInstanceField
│   ├── formId: "employee-form"
│   ├── workflowId: "onboarding"
│   ├── fieldName: "employeeId"
│   └── fieldValue: "EMP-12345"
└── Form opens with employeeId pre-filled

form.NewChecklistInstance

Purpose: Create a checklist-style form instance

Parameters: - formId - Checklist form - workflowId - Workflow to create

Use Case: Task checklists, inspection forms

form.NewChecklistInstanceField

Purpose: Create checklist instance with field data

Parameters: - formId - Checklist form - workflowId - Workflow to create - fieldName - Field to populate - fieldValue - Value to set

Use Case: Pre-filled inspection checklists

form.ReadFormResults

Purpose: Query submitted form data

Parameters: - formId - Form to query - filter - MongoDB filter (optional) - projection - Fields to return (optional) - sort - Sort order (optional) - limit - Max results (optional)

Returns: Array of form submissions

Use Case: Generate reports from form data

Example:

Get Leave Requests
├── form.ReadFormResults
│   ├── formId: "leave-request"
│   ├── filter: {"status": "Approved"}
│   ├── sort: {"submittedDate": -1}
│   └── limit: 100
└── Returns array of approved leave requests

form.DeleteFormResult

Purpose: Delete form submission data

Parameters: - formId - Form ID - resultId - Submission ID to delete

Use Case: Data cleanup, GDPR compliance

Example:

Delete Old Submissions
├── form.ReadFormResults (get old records)
├── For each record
│   └── form.DeleteFormResult
│       └── resultId: record._id
└── Old data removed

form.AddAlert

Purpose: Add alert message to form

Parameters: - alertType - Type: info, warning, error, success - message - Alert message - fieldId - Associated field (optional)

Use Case: Show warnings or info messages in forms

Example:

Validation Chain
├── Check budget
├── If over budget
│   └── form.AddAlert
│       ├── alertType: "warning"
│       └── message: "Request exceeds budget"
└── Alert displays in form

form.AddSubmissionInfo

Purpose: Add metadata to form submission

Parameters: - key - Metadata key - value - Metadata value

Use Case: Track submission context

Example:

Process Submission
├── form.AddSubmissionInfo
│   ├── key: "submittedFrom"
│   └── value: "mobile-app"
├── form.AddSubmissionInfo
│   ├── key: "ipAddress"
│   └── value: "${requestIp}"
└── Metadata stored with submission

form.SetElxRedirect

Purpose: Set redirect URL after submission

Parameters: - url - URL to redirect to

Use Case: Navigate to specific page after submit

Example:

Approval Chain
├── Process approval
├── form.SetElxRedirect
│   └── url: "/dashboard/approved-requests"
└── User redirected to dashboard

form.UpdateScriptField

Purpose: Update form field via script

Parameters: - fieldId - Field to update - value - New value - script - JavaScript to execute

Use Case: Dynamic field updates

form.ExtractFormLabels

Purpose: Extract field labels from form definition

Parameters: - formId - Form to extract from

Returns: Map of field IDs to labels

Use Case: Generate reports with human-readable labels

Example:

Generate Report
├── form.ExtractFormLabels
│   └── formId: "employee-form"
├── form.ReadFormResults
│   └── formId: "employee-form"
├── Map field IDs to labels
└── Generate readable report

form.Download

Purpose: Download form definition as JSON

Parameters: - formId - Form to download

Returns: Form definition JSON

Use Case: Backup, migration, version control

form.Upload

Purpose: Upload form definition from JSON

Parameters: - formJson - Form definition - formId - Target form ID - overwrite - Replace existing (optional)

Use Case: Restore, migration, deployment

form.FormCategory

Purpose: Categorize forms for organization

Parameters: - formId - Form to categorize - category - Category name

Use Case: Organize forms by department, type, etc.

Common ETL Patterns

Pattern 1: Pre-Submission Validation

Validate data before allowing submission.

Validate Purchase Order
├── Step 1: Check required fields
├── Step 2: Validate budget code exists
├── Step 3: Check amount within limit
├── Step 4: Verify approver is valid
├── Step 5: Validation Endpoint
└── Returns validation issues or success

Pattern 2: Data Enrichment

Add calculated or looked-up data.

Enrich Employee Form
├── Step 1: Get form data
├── Step 2: Lookup department from code
├── Step 3: Calculate years of service
├── Step 4: Get manager from org chart
├── Step 5: Add to elxPublic
└── Enriched data available in form

Pattern 3: External System Integration

Update external systems on submission.

Submit Expense Claim
├── Step 1: Validate claim
├── Step 2: Create record in accounting system
│   └── HTTP POST to API
├── Step 3: Store reference ID
├── Step 4: Send notification
└── Step 5: Set redirect URL

Pattern 4: Conditional Processing

Different processing based on data.

Process Leave Request
├── Step 1: Get leave type and days
├── Step 2: If days > 5
│   └── Create workflow with manager approval
├── Step 3: Else
│   └── Auto-approve
└── Step 4: Send notification

Pattern 5: Multi-Step Workflow

Complex processing with multiple stages.

Onboard Employee
├── Step 1: Create employee record
├── Step 2: Create email account
├── Step 3: Assign equipment
├── Step 4: Enroll in benefits
├── Step 5: Schedule orientation
├── Step 6: Send welcome email
└── Step 7: Create workflow for tracking

Pattern 6: Notification on Submission

Send emails or messages when form submitted.

Leave Request Submitted
├── Step 1: Get form data
├── Step 2: Lookup manager email
├── Step 3: Compose email
│   ├── Subject: "Leave Request from ${employeeName}"
│   └── Body: Include request details
├── Step 4: Send email
└── Step 5: Log notification

Pattern 7: Data Validation with Lookup

Validate against database records.

Validate Employee ID
├── Step 1: Get employee ID from form
├── Step 2: Query employee database
├── Step 3: If not found
│   └── Add validation error
├── Step 4: If found
│   └── Add employee details to form
└── Step 5: Validation Endpoint

Pattern 8: Calculated Fields

Calculate values based on form input.

Calculate Leave Days
├── Step 1: Get start and end dates
├── Step 2: Calculate business days
├── Step 3: Subtract holidays
├── Step 4: Check against leave balance
├── Step 5: Store calculated days
└── Step 6: If insufficient balance, add error

Validation Examples

Example 1: Email Uniqueness Check

Validate Email
├── Step 1: Extract email from form
├── Step 2: Query user database
│   └── Filter: email == form.email
├── Step 3: If exists
│   └── Add validation issue
│       ├── field: "email"
│       └── message: "Email already registered"
├── Step 4: Validation Endpoint
└── Returns validation result

Example 2: Date Range Validation

Validate Date Range
├── Step 1: Get start and end dates
├── Step 2: Check end > start
│   └── If not, add error
├── Step 3: Check dates not in past
│   └── If past, add error
├── Step 4: Check dates within 1 year
│   └── If too far, add error
├── Step 5: Validation Endpoint
└── Returns validation issues

Example 3: Budget Validation

Validate Budget
├── Step 1: Get amount and budget code
├── Step 2: Query budget database
├── Step 3: Get remaining budget
├── Step 4: If amount > remaining
│   └── Add validation issue
│       ├── field: "amount"
│       └── message: "Exceeds available budget"
├── Step 5: Validation Endpoint
└── Returns validation result

Example 4: Cross-Field Validation

Validate Dependent Fields
├── Step 1: Get leave type
├── Step 2: If type == "Sick"
│   └── Check medical certificate attached
│       └── If not, add error
├── Step 3: If type == "Annual"
│   └── Check sufficient balance
│       └── If not, add error
├── Step 4: Validation Endpoint
└── Returns validation issues

Best Practices

1. Use Validation Endpoint

Always include Validation Endpoint step in validation chainsets.

2. Return Structured Errors

Use validationIssues array with field and message.

3. Validate Early

Use client-side validation for simple checks, ETL for complex validation.

4. Keep Chains Focused

One chain per purpose (validate, process, notify).

5. Handle Errors Gracefully

Always return meaningful error messages.

6. Log Important Actions

Log ETL processing for audit trail.

7. Set Redirect URLs

Always set elxRedirect to guide users.

8. Use Transactions

For multi-step processing, use transactions to ensure consistency.

9. Optimize Queries

Use projections and indexes for form result queries.

10. Test Thoroughly

Test ETL chains with various form data scenarios.

Troubleshooting

Validation Not Running

Check: - Chainset has Validation Endpoint step - Chainset is selected in form’s Validator property - Form submission is triggering validation

Validation Errors Not Displaying

Check: - Errors returned in validationIssues array - Field IDs match form component IDs - Error format is correct

ETL Chain Not Executing

Check: - Chain is configured in button properties - User has permission to execute chain - Chain is enabled

Form Data Not Available in ETL

Check: - Form submission is sending data - Field IDs are correct - Data is in expected format

Redirect Not Working

Check: - elxRedirect is set in ETL chain - URL is valid - No errors preventing redirect

Next Steps

Next: Form Validation