ajbcoding/extracting-filemaker-business-logic

Extracting FileMaker Business Logic

Overview

FileMaker DDR reports contain calculation fields, custom functions, and business logic embedded in scripts. This skill helps you extract, understand, and adapt that logic for PostgreSQL implementations—typically for import processes, data transformations, and maintenance scripts.

Key principle: PostgreSQL implementations will often be more efficient than direct translations. Use FileMaker logic to understand what the business rules are, then implement them idiomatically in PostgreSQL.

When to Use

Use this skill when:

• Extracting calculation logic from FileMaker DDR for import scripts
• Understanding business rules embedded in FileMaker calculations
• Adapting FileMaker custom functions to PostgreSQL functions
• Creating PostgreSQL maintenance scripts based on FileMaker script logic
• Documenting business logic that exists only in FileMaker

Do NOT use for:

• Direct 1:1 database migration (most PostgreSQL designs will differ)
• FileMaker UI/layout logic (not relevant to PostgreSQL)
• Simple field mappings (use standard ETL tools)

DDR Files and Structure

Detailed DDR: {ProjectName}_ddr/{DatabaseName}.html (5-20MB file)

• Contains complete calculation formulas
• Lists all custom functions with code
• Documents scripts step-by-step
• Shows field definitions and relationships

Example from user's project:

• File: /Users/anthonybyrnes/PycharmProjects/Python419/AugustServer_ddr/AugustServer.html
• Size: 9.7MB
• Contains: 148 custom functions, 362 scripts, calculation fields

Finding Calculations in DDR

Calculation Fields

In DDR HTML, calculation fields appear in field definitions:

Field Name: wtu_calculation
Type: Calculation
Result Type: Number
Formula:
  contacthours * units * csfactor

XPath pattern (adjust based on actual DDR structure):

from lxml import etree

tree = etree.parse("AugustServer_ddr/AugustServer.html", etree.HTMLParser())

# Find calculation fields
calc_fields = tree.xpath('//td[contains(text(), "Calculation")]/parent::tr')

for field_row in calc_fields:
    field_name = field_row.xpath('./td[1]/text()')[0]
    formula = field_row.xpath('./following-sibling::tr//text()')

Scripts (Critical Source of Business Logic)

Important: Many "calculated" values are actually set by FileMaker scripts, not calculation fields. Scripts often contain the most complex business logic.

Look for scripts that:

• SetField steps (e.g., "Set Field [ClassInstance::wtu; ...]")
• Loop through records performing calculations
• Names containing "Calculate", "Update", "Process", "Compute"
• Triggered by imports or scheduled tasks

Example: The WTU field may appear as a Number field (not Calculation), but scripts like "419F - Loop WTU Contact Hours" contain the actual calculation logic.

Script: 419F - Loop WTU Contact Hours - c courses
Steps:
  If [Units ≠ "-"]
    Set Field [ClassInstance::wtu; ACCU * Workload_Weight_Factor]
    Set Field [ClassInstance::contactHours; ACCU * contact_hours_per_unit]
  End If

Extraction tip: Search DDR for field names (e.g., "wtu") to find all scripts that reference them.

Custom Functions

Custom functions section (anchor: #valCustomFunctionsSectionAnchor_):

Function Name: GenerateUUID
Parameters: none
Formula:
  Upper(Get(UUID))

Look for patterns in custom function names:

• Prefix conventions (e.g., CF_, Calc_)
• Purpose indicators (Validate_, Format_, Calculate_)

Understanding FileMaker Calculation Syntax

Common FileMaker Functions → PostgreSQL Equivalents

| FileMaker | PostgreSQL | Notes | |-----------|------------|-------| | Get(UUID) | gen_random_uuid() or uuid_generate_v4() | FileMaker UUIDs are uppercase | | Upper(text) | UPPER(text) | Direct equivalent | | Left(text, n) | LEFT(text, n) | Direct equivalent | | Right(text, n) | RIGHT(text, n) | Direct equivalent | | Position(search, text, start, occurrence) | POSITION(search IN text) | PostgreSQL simpler, use SUBSTRING for start/occurrence | | Substitute(text, search, replace) | REPLACE(text, search, replace) | Direct equivalent | | Let([var1 = value; var2 = value]; expression) | WITH vars AS (...) or function variables | FileMaker's scoped variables | | Case(test1; result1; test2; result2; default) | CASE WHEN test1 THEN result1 WHEN test2 THEN result2 ELSE default END | Similar structure | | If(test; trueResult; falseResult) | CASE WHEN test THEN trueResult ELSE falseResult END | Or use IF in PL/pgSQL | | GetField(fieldName) | Dynamic SQL or CASE statement | FileMaker allows dynamic field references | | Count(relationship::field) | SELECT COUNT(*) FROM related_table WHERE... | Relationship counts become subqueries | | Sum(relationship::field) | SELECT SUM(field) FROM related_table WHERE... | Aggregate from related table |

FileMaker Operators

• & (concatenation) → || in PostgreSQL
• ≠ or != → <> or != in PostgreSQL
• and, or, not → AND, OR, NOT in PostgreSQL
• ¶ (paragraph return) → E'\n' in PostgreSQL

Extraction Workflow

Step 1: Identify Business Logic Locations

Scan DDR for (in priority order):

• [ ] Scripts that manipulate data (SetField, Loop, calculations) - often the PRIMARY source
• [ ] Calculation fields in tables you're importing
• [ ] Custom functions referenced by calculations/scripts
• [ ] Auto-enter calculations (default values with logic)
• [ ] Validation calculations (field constraints)

Critical: Check scripts FIRST. Many fields appear as "Number" or "Text" but are actually calculated by scripts.

Step 2: Document Calculation Purpose

For each calculation field:

**Field**: ClassInstance.wtu
**Purpose**: Calculate weighted teaching units for workload reporting
**Formula**: `contacthours * units * csfactor`
**Dependencies**: contacthours, units, csfactor fields
**Used by**: WTU reports, faculty workload calculations
**Implementation**: PostgreSQL VIEW or calculated during import

Step 3: Adapt to PostgreSQL Idioms

FileMaker approach (calculation field):

// FileMaker calculation field
Case(
  enrollment_total = 0; "Empty";
  enrollment_total < class_capacity * 0.5; "Low";
  enrollment_total >= class_capacity; "Full";
  "Adequate"
)

PostgreSQL approach (CASE expression in VIEW):

CREATE OR REPLACE VIEW class_status AS
SELECT
  id,
  class_nbr,
  CASE
    WHEN enrollment_total = 0 THEN 'Empty'
    WHEN enrollment_total < class_capacity * 0.5 THEN 'Low'
    WHEN enrollment_total >= class_capacity THEN 'Full'
    ELSE 'Adequate'
  END AS status
FROM classinstance;

Or as import script logic (Python):

def calculate_class_status(enrollment_total, class_capacity):
    """Adapted from FileMaker ClassInstance.status calculation"""
    if enrollment_total == 0:
        return 'Empty'
    elif enrollment_total < class_capacity * 0.5:
        return 'Low'
    elif enrollment_total >= class_capacity:
        return 'Full'
    else:
        return 'Adequate'

# Use during import
cursor.execute("""
    UPDATE classinstance
    SET enrollment_status = %s
    WHERE id = %s
""", (calculate_class_status(row['enrollment_total'], row['class_capacity']), row['id']))

Step 4: Handle Custom Functions

FileMaker custom function:

Function: GenerateUUID
Parameters: none
Code:
  Upper(Get(UUID))

PostgreSQL equivalent (in existing codebase pattern):

def generate_uuid():
    """Generate uppercase UUID matching FileMaker format"""
    return str(uuid.uuid4()).upper()

Reference: program_catalog_parser.py:116-120

Common Patterns

Pattern 1: Aggregates from Relationships

FileMaker:

// Count related records
Count(ClassAssign::id)

// Sum from related table
Sum(Enrollment::units)

PostgreSQL (import script):

# Calculate during import
cursor.execute("""
    SELECT COUNT(*)
    FROM classassign
    WHERE id_classinstance = %s
""", (classinstance_id,))
assign_count = cursor.fetchone()[0]

# Or as a VIEW
CREATE VIEW classinstance_summary AS
SELECT
    ci.id,
    COUNT(ca.id) as assignment_count,
    SUM(e.units) as total_enrollment_units
FROM classinstance ci
LEFT JOIN classassign ca ON ca.id_classinstance = ci.id
LEFT JOIN enrollment e ON e.id_classinstance = ci.id
GROUP BY ci.id;

Pattern 2: Conditional Logic

FileMaker:

Let([
  base = contacthours * units;
  factor = Case(
    component = "LAB"; 1.5;
    component = "LEC"; 1.0;
    1.0
  )
];
  base * factor
)

PostgreSQL function:

CREATE OR REPLACE FUNCTION calculate_wtu(
    contact_hours NUMERIC,
    units NUMERIC,
    component_type TEXT
) RETURNS NUMERIC AS $$
DECLARE
    base NUMERIC;
    factor NUMERIC;
BEGIN
    base := contact_hours * units;

    factor := CASE component_type
        WHEN 'LAB' THEN 1.5
        WHEN 'LEC' THEN 1.0
        ELSE 1.0
    END;

    RETURN base * factor;
END;
$$ LANGUAGE plpgsql IMMUTABLE;

Pattern 3: Text Parsing/Formatting

FileMaker:

// Extract course code from title
Let([
  spacePos = Position(" "; course_title; 1; 1)
];
  Left(course_title; spacePos - 1)
)

PostgreSQL (import script):

def extract_course_code(course_title):
    """Extract course code from title (FileMaker logic)"""
    space_pos = course_title.find(' ')
    if space_pos > 0:
        return course_title[:space_pos]
    return course_title

# Or SQL function
CREATE OR REPLACE FUNCTION extract_course_code(course_title TEXT)
RETURNS TEXT AS $$
BEGIN
    RETURN SPLIT_PART(course_title, ' ', 1);
END;
$$ LANGUAGE plpgsql IMMUTABLE;

Script Logic Extraction

FileMaker scripts often contain:

• Data transformation logic → PostgreSQL functions or Python import scripts
• Validation rules → CHECK constraints or application validation
• Business workflows → Application layer logic
• UI automation → Ignore for PostgreSQL

Focus on extracting:

• SetField steps (data updates)
• If/Else logic (conditional rules)
• Loop structures (batch processing)
• Calculation expressions used in scripts

Integration with Import Processes

When building import scripts:

# Reference: program_catalog_parser.py patterns

def process_catalog_entry(row, catalog_year):
    """
    Process catalog entry with business logic adapted from FileMaker.

    FileMaker calculation: catalog_year format "2024-2025"
    FileMaker custom function: ConvertToAY("2024-2025") → "24-25"
    """
    # Adapt FileMaker's year conversion logic
    ay_short = catalog_year[2:4] + '-' + catalog_year[7:9]

    # Get AY record (FileMaker relationship equivalent)
    cursor.execute("SELECT id FROM ay WHERE ay = %s", (ay_short,))
    id_ay = cursor.fetchone()[0]

    # Apply business rule (from FileMaker calculation)
    if row['total_units'] is None:
        # FileMaker: If(IsEmpty(total_units); Calculate_Default_Units; total_units)
        total_units = calculate_default_units(row)
    else:
        total_units = row['total_units']

    return {
        'id_ay': id_ay,
        'total_units': total_units,
        # ... other fields
    }

Common Mistakes

Mistake 1: Literal Translation

• Don't translate FileMaker calculations character-by-character
• Understand the business rule, then implement idiomatically in PostgreSQL

Mistake 2: Ignoring Context

• Calculation fields may reference global fields or UI state
• Determine if logic is data-based (extract) or UI-based (ignore)

Mistake 3: Missing Dependencies

• Custom functions may call other custom functions
• Extract the full dependency chain

Mistake 4: Over-Engineering

• Simple calculations don't need PostgreSQL functions
• Calculate during import if logic is only used once

Mistake 5: Skipping Documentation

• Document the business purpose, not just the formula
• Future maintainers need to understand why, not just what

Mistake 6: Only Checking Calculation Fields

• Scripts often contain the primary business logic
• Check SetField steps in scripts for complex calculations
• A "Number" field type doesn't mean it's not calculated

Common Rationalizations to Avoid

| Rationalization | Reality | |-----------------|---------| | "The field is type Number, so it's not calculated" | FileMaker scripts often calculate and set Number/Text fields. Check scripts that reference the field. | | "I'll just look at calculation fields" | Most complex logic is in scripts (SetField steps), not calculation field types. Scripts are the PRIMARY source. | | "This is too complex to extract, I'll rebuild from scratch" | You'll miss critical business rules. Extract the logic first, then refactor for PostgreSQL. | | "I can translate this literally to PostgreSQL" | FileMaker idioms differ from PostgreSQL. Understand the business rule, then implement idiomatically. | | "I don't need to document this, the code is self-explanatory" | Business context gets lost. Document WHY the calculation exists, not just WHAT it does. | | "I'll skip the custom functions for now" | Custom functions contain reusable business logic. Extract them early; they'll be referenced throughout. | | "This global field must be in PostgreSQL" | Global fields are UI/session state, not database state. Handle in application layer, not schema. | | "I can figure out relationships from field names alone" | FileMaker relationships include conditions. Check DDR relationship definitions for filtering rules. |

Checklist for Logic Extraction

When extracting FileMaker business logic:

• [ ] Locate DDR detailed HTML file
• [ ] Identify calculation fields in relevant tables
• [ ] Document purpose and dependencies for each calculation
• [ ] List custom functions used by calculations
• [ ] Extract custom function code and dependencies
• [ ] Map FileMaker functions to PostgreSQL equivalents
• [ ] Decide: PostgreSQL function, VIEW, or import script logic?
• [ ] Implement with idiomatic PostgreSQL patterns
• [ ] Test with sample data from FileMaker
• [ ] Document business rules separately from code

Real-World Example

From user's existing codebase:

# program_catalog_parser.py:41-70
def get_ay_id(conn, catalog_year: str) -> Optional[str]:
    """
    Get AY id from catalog_year string.

    Converts full format "2025-2026" to short format "25-26"
    and looks up corresponding AY record.

    This logic was adapted from FileMaker calculation that
    performed similar year format conversion in catalog imports.
    """
    # Convert "2025-2026" to "25-26" (FileMaker custom function logic)
    try:
        ay_short = catalog_year[2:4] + '-' + catalog_year[7:9]
    except IndexError:
        logger.error(f"Invalid catalog_year format: {catalog_year}")
        return None

    with conn.cursor() as cur:
        cur.execute("SELECT id FROM ay WHERE ay = %s", (ay_short,))
        result = cur.fetchone()

        if not result:
            logger.warning(f"AY not found for catalog_year: {catalog_year}")
            return None

        return result[0]

This function adapted FileMaker's year conversion logic for use in PostgreSQL import scripts.