Database-Level Integration: Why It Matters | CLUE Learning

Most fleet software connects to Viewpoint Vista through an API layer or file-based exports. These approaches expose a fixed set of standard fields and operate within the boundaries of what the API was designed to share. Custom fields, company-specific data structures, and multi-entity configurations are often outside what a standard API can handle.

Database-Level Integration is Clue's approach to connecting with Vista. Rather than going through an API, Clue connects directly to your Vista SQL Server database through a secure SSH tunnel. This is a deliberate architectural decision that gives organizations capabilities no API integration can match. Learn more about the full Clue and Vista integration.

‍

Who Is This For?

• IT and Integration Administrators who manage the technical configuration of the Vista connection and need to understand how the integration accesses data, what security protocols are in place, and how custom fields are handled.
• System Owners and Clue Administrators who need to understand why certain Vista data is available in Clue that other integrations cannot access, and how to extend the integration as organizational needs evolve.

‍

What Direct Database Access Provides?

User Defined Fields (UDFs)

Vista UDFs are custom fields your organization added to standard Vista tables to capture data specific to your operation. API integrations typically cannot see these fields because APIs expose only the standard fields Vista publishes. Clue reads your Vista database schema directly, which means it picks up every UDF on every table without any additional configuration needed on the Vista side.

Real examples of UDFs that Clue reads and uses:

• SyncClueYN on the EMEM table controls which assets sync to Clue on a per-asset basis. Your team sets this flag in Vista and Clue respects it during every sync cycle.
• udCleanEquipNo stores a sanitized equipment alias used when the standard Equipment field has formatting differences that cause matching failures between systems.
• Custom equipment properties on EMEM can be mapped to Clue equipment property fields through the OrgEquipmentPropertyMapping table, making custom Vista data visible directly on asset records in Clue.
• Vendor UDFs such as POCountry on the APVM table are checked for existence before querying, so the integration handles organizations that have them and those that do not without requiring separate configurations.

Multi-Company Support

Many construction contractors operate multiple legal entities within a single Vista database, each with its own EMCo number. Standard integrations typically handle one company at a time. Clue manages multi-company environments through a configurable company priority list that controls which EMCo numbers are included in the sync and in what order.

When Clue looks up equipment or an employee for a work order, it searches each listed company in priority order until a match is found. This means equipment from multiple Vista companies appears in one unified Clue fleet view, and work orders route to the correct Vista entity automatically. Timecards default to the mechanic's payroll company (PRCo) with a fallback to the equipment's EMCo when needed.

Equipment ID Mismatch Handling

In real fleet environments, the same piece of equipment often has different identifiers in different systems. Vista might record it as EQ-0042, the GPS tracker sends it as 42, and a prior manual entry used a different format entirely. Clue handles this with a two-step matching process during each sync cycle: it first tries the standard EMEM Equipment field, then falls back to the udCleanEquipNo UDF alias. This process runs across every EMCo in the priority list, catching the most common mismatch scenarios including leading zeros, special characters, spaces, and company-specific prefixes.

Customer-Specific Configuration

Because Clue reads the actual database, the integration can be configured differently per customer without requiring changes to the Vista installation. Global parameters control which data entities sync, which EMCo numbers are included and their priority order, which UDFs are read and how they are mapped, default cost codes per work order type, how Vista departments map to Clue's organizational hierarchy, and whether equipment missing from Vista is automatically deactivated in Clue.

‍

How the Connection Works?

• Protocol: Clue uses a direct SQL Server connection via pymssql to read and write Vista data.
• Security: The connection runs through an SSH tunnel with RSA key authentication. No credentials are stored in application code.
• Credential storage: Database credentials are stored in AWS Parameter Store and encrypted at rest.
• Testing environment: A separate test database connection is available using the vista_use_testing_environment flag, allowing full integration testing against a non-production Vista database before going live.

‍

Key Behaviors and Limitations

• The connection is read and write. Clue both reads data from Vista and writes data back, including work orders, timecards, and purchase orders. The direction of each data type is controlled by individual sync parameters.
• UDF availability is checked at sync time. Clue checks for the existence of each UDF before querying it. Organizations that have the UDF get the full functionality. Those that do not are handled gracefully without errors.
• Schema changes in Vista require parameter review. If your team adds or modifies UDFs in Vista, notify your Clue admin. New UDFs that Clue already references become available automatically. New UDFs that need custom mapping require a configuration update in OrgEquipmentPropertyMapping.

‍

Tips

• Notify your Clue admin when you add a new UDF in Vista. If the UDF matches one that Clue already references, it becomes available automatically at the next sync. For new custom mappings, a configuration update is required before the data flows through.
• Update the EMCo priority list when you add a new Vista company. Equipment and employees in a new company will not sync until that company's EMCo number is added to the vista_company_priority parameter. This is a common oversight when companies expand or restructure.‍
• Use testing mode before enabling live data exports. The vista_testing_mode parameter routes payroll exports to PRCo=99, keeping your production Vista payroll data clean while you verify that timecards, work orders, and purchase orders are formatting and routing correctly during initial setup.