Formulas and Rules Syntax Guide

fields:
  field_name:
    type: formula
    expression: "field1 + field2"
    data_type: number  # Result type: number, text, date, boolean

# Simple field reference - use field name directly
calc_total:
  type: formula
  expression: "price * quantity"
  data_type: number
 
# With curly braces (alternative syntax)
calc_total_alt:
  type: formula
  expression: "{price} * {quantity}"
  data_type: number

# Addition
profit:
  type: formula
  expression: "revenue - cost"
  data_type: currency
 
# Multiplication
area:
  type: formula
  expression: "width * height"
  data_type: number
 
# Division
average_score:
  type: formula
  expression: "total_score / num_attempts"
  data_type: number
 
# Percentage
profit_margin:
  type: formula
  expression: "(revenue - cost) / revenue * 100"
  data_type: percent

# Concatenation
full_name:
  type: formula
  expression: "first_name + ' ' + last_name"
  data_type: text
 
# String methods
uppercase_name:
  type: formula
  expression: "name.toUpperCase()"
  data_type: text

# Date difference
days_open:
  type: formula
  expression: "$today - created_date"
  data_type: number
 
# Date comparison
is_overdue:
  type: formula
  expression: "due_date < $today && status != 'completed'"
  data_type: boolean

# Simple if/else
priority_label:
  type: formula
  expression: "score > 80 ? 'High' : 'Low'"
  data_type: text
 
# Multi-condition
status_category:
  type: formula
  expression: |
    if (amount > 10000) {
      return 'High Value';
    } else if (amount > 1000) {
      return 'Medium Value';
    } else {
      return 'Low Value';
    }
  data_type: text

# Access related object fields using dot notation
account_owner_name:
  type: formula
  expression: "customer.account.owner.name"
  data_type: text
 
# Nested lookups
region_manager:
  type: formula
  expression: "account.region.manager.email"
  data_type: text

# Simple profit
profit:
  type: formula
  expression: "revenue - cost"
  data_type: currency
 
# Profit margin percentage
profit_margin:
  type: formula
  expression: "(revenue - cost) / revenue * 100"
  data_type: percent
 
# Discounted price
final_price:
  type: formula
  expression: "list_price * (1 - discount_rate)"
  data_type: currency

# Age calculation
days_since_created:
  type: formula
  expression: "$today - created_date"
  data_type: number
 
# Is expired
is_expired:
  type: formula
  expression: "expiration_date < $today"
  data_type: boolean
 
# Duration
project_duration_days:
  type: formula
  expression: "end_date - start_date"
  data_type: number

# Risk level based on multiple factors
risk_level:
  type: formula
  expression: |
    if (amount > 100000 && customer.credit_score < 600) {
      return 'High Risk';
    } else if (amount > 50000 || customer.credit_score < 700) {
      return 'Medium Risk';
    } else {
      return 'Low Risk';
    }
  data_type: text
 
# Priority score
priority_score:
  type: formula
  expression: |
    (urgency_level * 0.4) + 
    (impact_level * 0.4) + 
    (customer_tier * 0.2)
  data_type: number

# In object.yml
fields:
  email:
    type: email
    required: true
    validation:
      format: email
      message: "Please enter a valid email address"
  
  age:
    type: number
    validation:
      min: 0
      max: 150
      message: "Age must be between 0 and 150"
  
  username:
    type: text
    required: true
    validation:
      min_length: 3
      max_length: 20
      pattern: "^[a-zA-Z0-9_]+$"
      message: "Username must be 3-20 alphanumeric characters"

# In project.validation.yml
rules:
  - name: valid_date_range
    type: cross_field
    rule:
      field: end_date
      operator: ">="
      compare_to: start_date
    message: "End date must be on or after start date"
    error_code: "INVALID_DATE_RANGE"

rules:
  - name: description_required_for_high_budget
    type: conditional
    description: "Description is required for high budget projects"
    condition:
      field: budget
      operator: ">"
      value: 10000
    rule:
      field: description
      operator: "not_empty"
    message: "Description is required for projects over $10,000"

rules:
  - name: status_transition
    type: state_machine
    field: status
    transitions:
      planning:
        allowed_next: [active, cancelled]
      active:
        allowed_next: [on_hold, completed, cancelled]
      on_hold:
        allowed_next: [active, cancelled]
      completed:
        allowed_next: []
        is_terminal: true
      cancelled:
        allowed_next: []
        is_terminal: true
    message: "Invalid status transition from {{old_status}} to {{new_status}}"
    error_code: "INVALID_STATE_TRANSITION"

rules:
  - name: budget_within_limits
    type: business_rule
    constraint:
      expression: "budget <= department.budget_limit OR executive_approval = true"
      relationships:
        department:
          via: department_id
          fields: [budget_limit]
    message: "Budget exceeds department limit. Executive approval required."
    error_code: "BUDGET_LIMIT_EXCEEDED"
    trigger: [create, update]
    fields: [budget, department_id, executive_approval]

rules:
  - name: credit_check
    type: custom
    message: "Customer credit limit exceeded"
    error_code: "CREDIT_LIMIT_EXCEEDED"
    trigger: [create, update]
    fields: [amount, customer_id]
    validator: |
      async function validate(record, context) {
        const customer = await context.api.findOne('customers', record.customer_id);
        const totalOrders = await context.api.sum('orders', 'amount', [
          ['customer_id', '=', record.customer_id],
          ['status', 'in', ['pending', 'processing']]
        ]);
        
        return (totalOrders + record.amount) <= customer.credit_limit;
      }

# In project.permission.yml
rules:
  - name: owner_full_access
    description: "Record owner has full access"
    priority: 10
    condition:
      field: owner_id
      operator: "="
      value: $current_user.id
    permissions:
      read: true
      create: true
      update: true
      delete: true

rules:
  - name: public_approved_read_only
    description: "Public approved records are read-only"
    condition:
      all_of:
        - field: status
          operator: "="
          value: approved
        - field: is_public
          operator: "="
          value: true
    permissions:
      read: true
      update: false
      delete: false

rules:
  - name: safe_division
    type: custom
    validator: |
      function validate(record) {
        if (record.denominator === 0) {
          return false;
        }
        return (record.numerator / record.denominator) < 100;
      }
    message: "Result must be less than 100 (division by zero not allowed)"

// Equality
field = value
field != value
 
// Numerical comparison
field > value
field >= value
field < value
field <= value
 
// Set membership
field in [value1, value2, value3]
field not_in [value1, value2]
 
// String operations
field contains "substring"
field starts_with "prefix"
field ends_with "suffix"
 
// Existence
field is_null
field is_not_null
field not_empty

# AND - All conditions must be true
condition:
  all_of:
    - field: status
      operator: "="
      value: active
    - field: budget
      operator: ">"
      value: 1000
 
# OR - At least one condition must be true
condition:
  any_of:
    - field: priority
      operator: "="
      value: high
    - field: amount
      operator: ">"
      value: 10000
 
# NOT - Condition must be false
condition:
  none_of:
    - field: status
      operator: "="
      value: deleted

// Basic filter - [field, operator, value]
["status", "=", "active"]
 
// Multiple filters with AND
[
  ["status", "=", "active"],
  "and",
  ["amount", ">", 1000]
]
 
// Multiple filters with OR
[
  ["priority", "=", "high"],
  "or",
  ["amount", ">", 50000]
]
 
// Complex nested conditions
[
  ["status", "=", "active"],
  "and",
  [
    ["priority", "=", "high"],
    "or",
    ["amount", ">", 10000]
  ]
]

# Good - Simple and clear
discount_amount:
  type: formula
  expression: "list_price * discount_rate"
  data_type: currency
 
# Good - Handles null
full_name:
  type: formula
  expression: "(first_name || '') + ' ' + (last_name || '')"
  data_type: text
 
# Avoid - Too complex for a formula
complex_score:
  type: formula
  expression: |
    var base = weight * coefficient;
    for (var i = 0; i < iterations; i++) {
      base = base * (1 + rate);
    }
    return base;
  data_type: number
  # Better: Move to a hook or action

# Good - Clear message and intent
rules:
  - name: valid_date_range
    type: cross_field
    ai_context:
      intent: "Ensure timeline is logical"
      business_rule: "Projects cannot end before they start"
    rule:
      field: end_date
      operator: ">="
      compare_to: start_date
    message: "End date must be on or after start date"
    severity: error
 
# Good - Conditional validation
  - name: approval_required_for_high_value
    type: conditional
    condition:
      field: amount
      operator: ">"
      value: 50000
    rule:
      field: approved_by
      operator: "not_empty"
    message: "Approval required for transactions over $50,000"
    severity: error

rules:
  - name: inventory_check
    type: custom
    batch_enabled: true
    batch_size: 100
    validator: |
      async function validateBatch(records, context) {
        const skus = records.map(r => r.sku);
        const inventory = await context.api.find('inventory', {
          filters: [['sku', 'in', skus]]
        });
        
        return records.map(record => {
          const inv = inventory.find(i => i.sku === record.sku);
          return inv && inv.available_quantity >= record.quantity;
        });
      }