Vista by Viewpoint Integrated Data
Field mapping reference for Vista by Viewpoint (On-Premise)
What this page covers: A complete reference mapping every Vista field to its Mosaic equivalent. Use this guide when configuring your integration, verifying synced data, or troubleshooting discrepancies.
| Integration type | On-Premise (Direct SQL Server access via replication) |
| Stored procedures required? | No — read-only access via replicated Vista tables |
| Setup complexity | Moderate |
| Configurable fields | 4 provision settings (companies list, fee source, budget source, hourly rate source) |
| Prerequisite | Vista On-Premise Integration Setup |
How Data Flows
Vista (SQL Server Replica) → Mosaic Sync Engine (field mapping + cross-company deduplication) → Mosaic (12 synced data types)
The Vista integration connects to a SQL Server replica of your on-premise Vista database. The agent reads directly from Vista's standard tables (no stored procedures, views, or schema changes required). Where the same project or phase exists in multiple companies (JCCo), Mosaic deduplicates and keeps the lowest-numbered company's copy.
Integrated Data Types
The integration syncs 12 data categories from Vista into Mosaic:
| Data Type | Description |
|---|---|
| Members | Employee name, email |
| Clients | Customer records from Accounts Receivable |
| Projects | Job records (number, dates, status, contract amount, client, currency) |
| Phases | Job-phase breakdowns under projects with cost aggregation |
| Time Entries | Cost detail entries — both posted and unposted (hours, project, phase, employee, hourly rate) |
| Standard Roles | Roles derived from Vista Class + Craft combinations |
| Member Roles | Default role per Member based on the employee's Class and Craft assignments |
| Rate Groups | Rate Tables grouped per company |
| Rates | Individual rate entries within a Rate Group, keyed by Class + Craft |
| Entity Rates | Links a Rate Group, Role, and Rate together |
| Member Bill Rates | Per-employee billable rates with effective date ranges (requires budEmpRates table) |
| Currency Exchange Rates | Currency conversion rates with effective date ranges (multi-currency customers) |
This list may expand as new features are added.
Not Synced
| Data Type | Description |
|---|---|
| Departments | Departmental groupings within a member |
| Offices / Regions / Locations | Member or project organizational unit assignments |
| Work Categories / Activities | Time-entry activity codes — targetActivityId is not surfaced |
| Cost Rates | Member and Role-level cost rates — only charge rates are synced |
| Work Plans | Vista does not surface a resource-planning entity that Mosaic can sync as Work Plans |
| Project Manager assignments | Not mapped from Vista |
| Consultant / Expense / Labor breakdowns | Only the lump ContractAmt is sent — the Labor / Consultant / Expense subtotals are not synced |
| Portfolios | Not mapped from Vista |
| Invoices / AR billing data | Not mapped from Vista |
| Project / Phase Archived flag | No source-side archived flag is read — Mosaic-side archive is driven by deletion detection during sync |
| Time-entry Billable flag | Not propagated — Vista charge-type → Mosaic billable mapping is not wired up |
| Time-entry Posted vs Unposted status | Both flow into Mosaic — the distinction is not preserved on the Mosaic time-entry record |
Reading the Field Mapping Tables
Symbol legend for technical tables
The Technical Reference tabs below use a shorthand notation for Vista database tables.
| Symbol | Meaning | Example |
|---|---|---|
. | Database table field reference | bPREH.FirstName = the FirstName column from the bPREH (PR Employee) table |
• | Values combined into one composite value | bJCCM.JCCo • bJCRT.RateTemplate |
or | Configurable — an integration setting selects which | bJCCM.ContractAmt or SUM(bJCCP.CurrEstCost) depending on Fee Source setting |
Vista Table Reference
| Table | Full Name | Used For |
|---|---|---|
bPREH | PR Employee Master | Members, Member Roles |
bARCM | AR Customer Master | Clients |
bJCJM | JC Job Master | Projects |
bJCCM | JC Contract Master | Projects (contract status, amount, dates, client) |
bJCJP | JC Job Phases | Phases |
bJCCP | JC Cost by Period | Phase fee/budget aggregation |
bJCCD | JC Cost Detail | Time Entries |
bJCRT | JC Rate Table | Rate Groups, Entity Rates |
bJCRD | JC Rate Detail | Rates, Entity Rates, Roles |
bHQCO | HQ Company | Currency code lookup (udCurrency custom field) |
budEmpRates | Bud Employee Rates | Member Bill Rates (and optional time-entry hourly rate) |
budFXRates | Bud FX Rates | Currency Exchange Rates |
Members
| What Syncs to Mosaic | Where It Comes From in Vista |
|---|---|
| Employee ID | The unique employee identifier from Payroll |
| First Name | The employee's first name |
| Middle Name | The employee's middle name |
| Last Name | The employee's last name |
| The employee's primary email address |
Important: Email is required
Only employees with a non-null
| Mosaic Field | Source |
|---|---|
| Employee ID | bPREH.Employee |
| First Name | bPREH.FirstName |
| Middle Name | bPREH.MidName |
| Last Name | bPREH.LastName |
bPREH.Email |
Fetch filters:
bPREH.Email IS NOT NULLbPREH.PRCo IN (Companies List)
Vista Documentation
Clients
| What Syncs to Mosaic | Where It Comes From in Vista |
|---|---|
| Client ID | The unique customer identifier |
| Client Number | The customer's business number |
| Client Name | The customer name from AR |
| Mosaic Field | Source |
|---|---|
| Client ID | bARCM.Customer |
| Client Number | bARCM.Customer |
| Client Name | bARCM.Name |
All bARCM rows are pulled — no filter applied.
Vista Documentation
Projects
| What Syncs to Mosaic | Where It Comes From in Vista |
|---|---|
| Project ID | The unique job identifier |
| Project Number | The job number |
| Project Name | The job description |
| Client | The customer linked to the contract |
| Start Date | The contract's start month |
| End Date | The contract's actual close date |
| Status | Active if the contract is Open; Complete if Closed |
| Fee | The contract amount — only when Fee Source = "Contract Amount" (default). When Fee Source = "Total Cost", project Fee is aggregated from phases. |
| Estimated Cost (Budget) | The contract amount — only when Budget Source = "Contract Amount". When Budget Source = "Total Cost" (default), budget is aggregated from phases. |
| Currency Code | The currency assigned to the company (from the Unispace custom field, if present) |
Cross-company deduplication
If the same job appears in multiple Vista companies, Mosaic keeps the version from the lowest-numbered company.
See the Visual Settings Guide for the Fee Source and Budget Source toggles.
| Mosaic Field | Source |
|---|---|
| Project ID | bJCJM.Job |
| Project Number | bJCJM.Job |
| Project Name | bJCJM.Description |
| Client | bJCCM.Customer |
| Start Date | bJCCM.StartMonth |
| End Date | bJCCM.ActualCloseDate |
| Status | bJCCM.ContractStatus — 1 (Open) = Active; 2 (Closed) = Complete |
| Fee | bJCCM.ContractAmt or aggregated from bJCCP.CurrEstCost (depends on Fee Source setting) |
| Estimated Cost | bJCCM.ContractAmt or aggregated from bJCCP.CurrEstCost (depends on Budget Source setting) |
| Currency Code | bHQCO.udCurrency (Unispace custom user-defined field — column-presence-gated) |
Fetch filters:
INNER JOIN bJCJM ↔ bJCCM ON Job = Contract AND JCCo = JCCobJCJM.JCCo IN (Companies List)andbJCCM.JCCo IN (Companies List)- Optional incremental:
bJCCM.StartMonth >= dateAfter (start of month) bHQCOis joined only if theudCurrencycolumn exists on the customer's instance
Vista Documentation
Phases
| What Syncs to Mosaic | Where It Comes From in Vista |
|---|---|
| Phase ID | A composite of the parent job and phase identifier |
| Phase Name | The phase description |
| Phase Number | The phase identifier |
| Parent / Project | The parent job |
| Status | Active if the phase is marked active; Not Started otherwise |
| Fee | Aggregated current cost across cost periods — only when Fee Source = "Total Cost" (otherwise null) |
| Estimated Cost (Budget) | Aggregated current cost — only when Budget Source = "Total Cost" (default) (otherwise null) |
Phase dates (start/end) are not synced from Vista.
Cross-company deduplication applies to phases the same way as projects — the lowest-Co version wins.
| Mosaic Field | Source |
|---|---|
| Phase ID | `Job=${bJCJP.Job}&Phase=${bJCJP.Phase}` |
| Phase Name | bJCJP.Description |
| Phase Number | bJCJP.Phase |
| Project | bJCJP.Job |
| Status | bJCJP.ActiveYN — Y = Active, N = NotStarted |
| Fee | SUM(bJCCP.CurrEstCost) GROUP BY (Job, Phase) — only when Fee Source ≠ Contract Amount |
| Estimated Cost | SUM(bJCCP.CurrEstCost) — only when Budget Source ≠ Contract Amount |
| Start / End Date | null (not synced) |
Fetch filters:
INNER JOIN bJCJP ↔ bJCCP ON Job AND PhaseJCCo IN (Companies List)on both sidesbJCJP.Description IS NOT NULL- GROUP BY phase to deduplicate across companies
Vista Documentation
Time Entries
| What Syncs to Mosaic | Where It Comes From in Vista |
|---|---|
| Time Entry ID | A composite of company, job, phase, cost type, month, and cost transaction number |
| Date | The actual date of the time entry |
| Employee | The employee who logged the time |
| Project | The job the time was logged against |
| Phase | The phase the time was logged against |
| Hours | The hours recorded |
| Hourly Rate | Calculated from actual cost / actual hours or sourced from the Custom Bill Rate Table (configurable) |
| Description | The cost detail description |
Posted and Unposted entries both sync
Vista's posted vs. unposted distinction is not filtered or preserved. Both posted and unposted cost-detail rows flow into Mosaic. Mosaic deletes entries from its store if they disappear from Vista on a subsequent sync (e.g. if a row is reposted under a different ID).
See the Visual Settings Guide for the Time Entry Hourly Rate Source setting.
| Mosaic Field | Source |
|---|---|
| Time Entry ID | composite of bJCCD.JCCo • bJCCD.Job • bJCCD.Phase • bJCCD.CostType • bJCCD.Mth • bJCCD.CostTrans |
| Date | bJCCD.ActualDate |
| Employee | bJCCD.Employee |
| Project | bJCCD.Job |
| Phase | bJCCD.Job + bJCCD.Phase |
| Hours | bJCCD.ActualHours |
| Hourly Rate | See hourly rate calculation below |
| Description | bJCCD.Description |
Hourly rate calculation
The hourly rate depends on the Time Entry Hourly Rate Source setting:
- Actual Cost / Actual Hours (default):
bJCCD.ActualCost / bJCCD.ActualHours. IfActualCostis unavailable on your Vista schema, the rate isnull. - Custom Bill Rate Table: For each cost-detail row, the agent looks up the most recent
budEmpRates.BillableRateeffective on or beforebJCCD.ActualDatefor the employee. If no bill rate is found, the time entry is skipped (logged as a warning).
Fetch filters:
bJCCD.Employee IS NOT NULLbJCCD.PRCo IS NOT NULLbJCCD.ActualHours != 0bJCCD.JCCo IN (Companies List)- Optional date range filters on
bJCCD.ActualDate - No filter on posted/unposted status —
PostedDateis intentionally not used in the WHERE clause
Historical fetches are split into weekly batches and processed newest-first, with a 10-year lookback cap.
Vista Documentation
Standard Roles
Roles are derived from the unique combinations of Class and Craft in Vista's Job Cost Rate Detail table.
| What Syncs to Mosaic | Where It Comes From in Vista |
|---|---|
| Role ID | A composite of Class and Craft |
| Role Name | Combined Craft and Class label |
| Mosaic Field | Source |
|---|---|
| Role ID | `Craft=${bJCRD.Craft}&Class=${bJCRD.Class}` |
| Role Name | `${bJCRD.Craft}, ${bJCRD.Class}` |
Fetch filters:
bJCRD.JCCo IN (Companies List)bJCRD.Class IS NOT NULL AND bJCRD.Craft IS NOT NULL- DISTINCT rows (GROUP BY Craft, Class)
- Gated on
bJCRD.ClassandbJCRD.Craftcolumns existing on the customer's Vista instance
Vista Documentation
Member Roles
| What Syncs to Mosaic | Where It Comes From in Vista |
|---|---|
| Member Role ID | A composite of the employee, Craft, and Class |
| Employee | The employee this role assignment belongs to |
| Role | The Craft + Class combination assigned to the employee |
| Override Roles | Always false — Member Roles do not override existing Mosaic role assignments |
| Mosaic Field | Source |
|---|---|
| Member Role ID | `Employee=${bPREH.Employee}&Craft=${bPREH.Craft}&Class=${bPREH.Class}` |
| Employee | bPREH.Employee |
| Role | bPREH.Craft + bPREH.Class |
| Override Employee Roles | Hard-coded false |
Fetch filters:
bPREH.PRCo IN (Companies List)bPREH.Class IS NOT NULL AND bPREH.Craft IS NOT NULL- Gated on
bPREH.Employee,Class,Craftcolumns existing
Vista Documentation
Rate Groups
| What Syncs to Mosaic | Where It Comes From in Vista |
|---|---|
| Rate Group ID | A composite of company and rate template |
| Rate Group Name | Combined company number and rate template description |
| Active Entity Type | Always Role — all Vista rates are by Craft + Class |
| Currency Code | The currency assigned to the company (if the Unispace currency field is present) |
| Is Default | Always false |
| Mosaic Field | Source |
|---|---|
| Rate Group ID | `Co=${bJCRT.JCCo}&RateTemplate=${bJCRT.RateTemplate}` |
| Rate Group Name | `[Co ${bJCRT.JCCo}]: [${bJCRT.Description}]` |
| Active Entity Type | Hard-coded Role |
| Currency Code | bHQCO.udCurrency |
| Is Default | Hard-coded false |
Fetch filters:
bJCRT.JCCo IN (Companies List)- Gated on the
bJCRDtable existing
Vista Documentation
Rates
| What Syncs to Mosaic | Where It Comes From in Vista |
|---|---|
| Rate ID | A composite of company, rate template, Class, and Craft |
| Rate Amount | The new rate value from the Rate Detail |
| Rate Description | A human-readable label including company, rate template, Class, Craft |
| Currency Code | The currency assigned to the company (if available) |
| Is Cost Rate | Always false — only charge rates are synced |
| Mosaic Field | Source |
|---|---|
| Rate ID | `Co=${JCCo}&RateTemplate=${RateTemplate}&Craft=${Craft}&Class=${Class}` |
| Rate Amount | bJCRD.NewRate |
| Rate Description | `[Co ${JCCo}] [${bJCRT.Description}]: [${Class}, ${Craft}]` |
| Currency Code | bHQCO.udCurrency |
| Is Cost Rate | Hard-coded false |
Rates are fetched together with Entity Rates from bJCRD joined to bJCRT.
Vista Documentation
Entity Rates
| What Syncs to Mosaic | Where It Comes From in Vista |
|---|---|
| Entity Rate ID | A composite of company, rate template, Class, and Craft |
| Rate Group | The parent Rate Group |
| Role | The Craft + Class combination this rate applies to |
| Rate | The linked Rate entry |
| Is Cost Rate | Always false |
| Mosaic Field | Source |
|---|---|
| Entity Rate ID | `Co=${JCCo}&RateTemplate=${RateTemplate}&Craft=${Craft}&Class=${Class}` |
| Target Rate Group | `Co=${JCCo}&RateTemplate=${RateTemplate}` |
| Target Role | `Craft=${Craft}&Class=${Class}` |
| Target Rate | same as Entity Rate ID |
| Is Cost Rate | Hard-coded false |
Fetch filters:
bJCRD.JCCo IN (Companies List)bJCRD.Class IS NOT NULL AND bJCRD.Craft IS NOT NULLINNER JOIN bJCRT ON JCCo + RateTemplate- Gated on the
bJCRDtable existing
Vista Documentation
Member Bill Rates
Member Bill Rates require the budEmpRates table to exist on your Vista instance. If the table is missing, this data type is silently skipped.
| What Syncs to Mosaic | Where It Comes From in Vista |
|---|---|
| Bill Rate ID | A composite of employee, effective date, and end date |
| Employee | The employee the bill rate applies to |
| Rate Amount | The billable rate value |
| Rate Description | The notes attached to the bill rate |
| Start Date | The effective-from date |
| End Date | The effective-to date |
Cross-company precedence: When the same employee has bill rates with identical effective dates across multiple companies, the lower-numbered company's rate wins.
| Mosaic Field | Source |
|---|---|
| Bill Rate ID | `Employee=${E}&EffectiveDate=${ED}&EndDate=${EnD}` |
| Employee | budEmpRates.Employee |
| Rate Amount | budEmpRates.BillableRate |
| Rate Description | budEmpRates.Notes |
| Start Date | budEmpRates.EffectiveDate |
| End Date | budEmpRates.EndDate |
Fetch filters:
budEmpRates.BillableRate IS NOT NULLbudEmpRates.EffectiveDate IS NOT NULLbudEmpRates.Notes IS NOT NULLbudEmpRates.EndDate IS NOT NULLbudEmpRates.Co IN (Companies List)- Gated on the
budEmpRatestable existing
budEmpRatesis a custom Unispace bill-rates table. If your Vista instance does not have it, Member Bill Rates will not sync. Member Bill Rates can be added manually in Mosaic in that case.
Vista Documentation
Currency Exchange Rates
| What Syncs to Mosaic | Where It Comes From in Vista |
|---|---|
| Source Currency | The currency being converted from |
| Destination Currency | The currency being converted to |
| Exchange Rate | The conversion rate |
| Start Date | The effective month for the rate |
| End Date | Calculated as the day before the next rate's effective date for the same currency pair |
Currency Exchange Rates are only relevant for multi-currency Vista deployments.
| Mosaic Field | Source |
|---|---|
| ID | `SourceCurr=${SourceCurr}&DestCurr=${DestCurr}&Mth=${Mth}` |
| Source Currency | budFXRates.SourceCurr |
| Destination Currency | budFXRates.DestCurr |
| Exchange Rate | budFXRates.ExRate |
| Start Date | budFXRates.Mth |
| End Date | Computed by overlap logic (day before the next rate's Mth for the same currency pair) |
Fetch filters:
SourceCurr IS NOT NULL AND DestCurr IS NOT NULL AND ExRate IS NOT NULL- Optional date range on
Mth
Vista Documentation
Configurable Fields at a Glance
These fields can be configured via integration settings. Refer to the Visual Settings Guide for the full setting list and defaults.
| Entity | Field | Available Sources |
|---|---|---|
| Project / Phase | Fee | bJCCM.ContractAmt (Project only) or SUM(bJCCP.CurrEstCost) (Phase only) |
| Project / Phase | Estimated Cost / Budget | bJCCM.ContractAmt (Project only) or SUM(bJCCP.CurrEstCost) (Phase only) |
| Time Entry | Hourly Rate | bJCCD.ActualCost / bJCCD.ActualHours or budEmpRates.BillableRate (most recent for date) |
| All data types | Companies scope | JCCo / PRCo / Co IN (Companies List) — which Vista companies the agent reads from |
Troubleshooting & FAQ
Why are some employees missing from Mosaic?
Employees are only synced when their bPREH.Email is non-null. Employees with no email address in Vista will not appear in Mosaic until an email is added.
Why don't I see projects from all my Vista companies?
The integration only reads from the companies listed in your Companies List setting (see the Visual Settings Guide). Ask your CSM or Solutions Engineer to add additional Co IDs if needed.
Why do project fees show as zero or null on phases (or vice versa)?
The Fee Source setting controls whether fees come from the contract amount (Project only) or the cost-by-period rollup (Phase only). The same project/phase will not have a fee from both sources — by design.
Where do time-entry hourly rates come from?
The Time Entry Hourly Rate Source setting selects between:
- Actual Cost / Actual Hours (default):
bJCCD.ActualCost / bJCCD.ActualHours - Custom Bill Rate Table: the most-recent
budEmpRates.BillableRateeffective on or before the entry's date
If your instance does not have an ActualCost column, the calculated rate will be null when using the default source.
Are posted and unposted time entries both synced?
Yes. The agent does not filter on PostedDate. Both flow into Mosaic, and the posted/unposted distinction is not preserved.
Why don't I see Bill Rates synced?
Member Bill Rates come from the Unispace custom budEmpRates table. If your Vista instance does not have that table, Bill Rates will be silently skipped. You can add Bill Rates manually in Mosaic in that case.
Why are some Rate Groups / Roles missing?
Rate Group, Rate, Entity Rate, and Role syncing is gated on the bJCRD table existing. Roles and Member Roles additionally require Class and Craft columns. If your Vista schema doesn't have these, those data types are silently skipped.
Why do project / phase IDs duplicate across companies?
They don't — the agent deduplicates Projects and Phases across companies and keeps the lowest-numbered company's record. Bill Rate dedup uses the same lowest-Co-wins rule.
Can I rename field labels in Mosaic to match my Vista custom labels?
No. Custom field name changes in Mosaic to match Vista labeling are not supported as a standard integration feature.
I need a data source or field that isn't listed here.
Any integration not listed in this documentation is considered a customization and is charged at an hourly rate. For more information, see Integration Requests.
Vista Documentation Links
| Topic | Link |
|---|---|
| Vista Product Site | Vista by Viewpoint |
| Vista Online Help | Vista Help |
| Trimble (formerly Viewpoint) Support | Trimble Help |
Related Documentation
Updated 2 months ago