WAY Companies Production Tracking System

McCorvey Companies — Mechanical / Plumbing Fabrication
Author: Alex Argumedo  |  Last Updated: April 2026  |  AI-Assisted: GitHub Copilot (Claude Sonnet 4.6)

1 — System Overview

The WAY Companies Production Tracking System is an automated pipeline that captures the full lifecycle of every fabrication batch produced for WAY Companies projects. Data is sourced from two places:

• Smartsheet — a filtered report showing all in-progress batches, maintained by the shop team. Synced every 15 minutes by the WAY Production Log Sync script.
• Revit (via A_script.py) — a PyRevit export that runs inside Revit and outputs pipe / fitting / hanger element data to a network folder. Processed by the Piping Processor script.

All data lands in MCDATA1 on MSM-DYNSQLTEST\mcdata and is surfaced in PowerBI through a set of purpose-built views.

What the system tracks

• Every fabrication batch ever seen in the WAY in-progress report — including historically removed batches
• The full status lifecycle of each batch: Detailing → Fabrication → QC → Shipped
• How long each batch spent in each status (duration in minutes, persisted in the history table)
• Pipe element counts, centerline footage, and hanger counts per batch (once Revit data is loaded)
• Project metadata from the Dynamics / Smartsheet project tables

2 — Architecture Diagram

Smartsheet Report (ID 5642706326867844)
  "WAY In-Progress Batches"
    └──► way_production_log_sync.py  [Task Scheduler · every 15 min]
              ├── INSERT  dbo.WAY_ProductionBatches    (new batches)
              ├── UPDATE  dbo.WAY_ProductionBatches    (field changes)
              ├── INSERT  dbo.WAY_ProductionStatusHistory (status transitions)
              ├── UPDATE  dbo.WAY_ProductionStatusHistory (close open records)
              └── INSERT  dbo.CAM_RunStats              (run audit)

Revit Model (WAY projects)
    └── A_script.py  [PyRevit — run manually by detailer]
              └── exports CSVs → \\mccorvey.com\DFS\WAY\Detailing\08 - REPORTS\<ProjectNumber>\
                        └──► piping_processor.py  [Task Scheduler — on new file]
                                  ├── INSERT/UPDATE  dbo.Pip_Submission
                                  ├── UPSERT         dbo.Pip_Element
                                  ├── UPSERT         dbo.Pip_Hanger
                                  └── REPLACE        dbo.Pip_HangerAncillary

dbo.WAY_ProductionBatches
dbo.WAY_ProductionStatusHistory
dbo.Pip_Element / Pip_Hanger
dbo.Project / dbo.Project_Details
    └──► dbo.vw_WAY_SpoolingAgg       (batch-level element + hanger aggregates)
    └──► dbo.vw_WAY_ProductionReport  (batch grain — primary PowerBI source)
    └──► dbo.vw_WAY_SpoolDetail       (VicSequence grain)
    └──► dbo.vw_WAY_MetalReport       (element grain — mirrors vw_CAM_MetalReport)

3 — Data Flow — End to End

Step 1 — Shop team updates Smartsheet

The shop team maintains individual project production log sheets in Smartsheet. A single Smartsheet Report (ID 5642706326867844) aggregates all in-progress batches from all project sheets into one filtered view. Only batches that are actively in production appear in this report — once a batch ships, it is filtered out automatically by the report criteria.

Step 2 — WAY Production Log Sync runs (every 15 min)

The sync script fetches all rows from the report via the Smartsheet REST API. For each row:

• New batch: Inserted into WAY_ProductionBatches; first status history record opened.
• Known batch: Fields updated (change-guard prevents no-op writes); status history record opened/closed if status changed.
• Batch absent from report: Flagged IsInReport = 0, RemovedFromReportDT stamped, open history record closed with ExitReason = 'RemovedFromReport'.

A backfill pass runs at the start of every sync to fix any rows with NULL ProjectNumber or BatchNumber (handles rows inserted before the current regex logic, or removed batches the normal UPDATE path never touches).

Step 3 — Detailer runs A_script.py in Revit (as needed)

When a detailer finishes spooling a WAY project, they run the PyRevit export script (A_script.py) inside Revit. This exports CSV files to the project folder on the network. The Piping Processor picks them up and upserts element and hanger data into the database.

Step 4 — PowerBI refreshes

PowerBI connects to the views. vw_WAY_ProductionReport is always populated (batch data only requires the Smartsheet sync). The spooling columns (BatchPipeElementCount, BatchTotalLinearFt, BatchHangerCount) return 0 until Revit data is loaded — they auto-populate once Pip_Element is populated.

4 — System Components

4.1 — way_production_log_sync.py

Smartsheet API Notes

The Smartsheet Report endpoint behaves differently from the Sheet endpoint:

• Columns use virtualId (not id)
• Cells use virtualColumnId (not columnId)
• Pagination: max 500 rows per page; script iterates all pages automatically

Batch ID Parsing Logic

The script parses ProjectNumber and BatchNumber from the Batch ID column using the following priority:

1. Regex match against BATCH_ID_RE (see Section 5)
2. If no regex match: extract digits from SheetName (e.g. "71000012 - Production Log" → 71000012)
3. If still no project number: stored as NULL (should not occur with current data)

Primary Field Parsing

The Primary column encodes building level, service, and area in the format:

L02 - DWV Hangers - Area J

The script parses this into three separate columns: Level, Service, Area. Rows that don't match the pattern store NULL in all three and the raw value in PrimaryField.

Status History Logic

Every status transition writes to WAY_ProductionStatusHistory:

• On first insert: open record with StatusEnteredDT = now
• On status change: close old record (ExitReason = 'StatusChange'), open new record
• On removal from report: close open record (ExitReason = 'RemovedFromReport')
• On re-appearance: open new record (previous closed record is preserved)

Backfill Function

Runs at startup each sync. Selects all rows in WAY_ProductionBatches where ProjectNumber IS NULL, BatchNumber IS NULL, or BatchNumber = BatchName (incorrect old fallback value), and re-parses them using the current regex. Safe to run repeatedly — uses COALESCE on ProjectNumber to never overwrite good data.

Run Statistics

Each run writes one row to dbo.CAM_RunStats:

ScriptName = 'way_production_log_sync'
RunStartDT, RunEndDT, DurationSec, SheetsProcessed=1, RowsUpdated, RowsInserted

4.2 — piping_processor.py

Processing Logic

• PInfo.csv → inserts a row into dbo.Pip_Submission (one per script run)
• Elements.csv → upserts dbo.Pip_Element on (ProjectNumber, RevitElementId); child dimensions in Pip_ElementDim are fully replaced (DELETE + INSERT)
• Hangers.csv → upserts dbo.Pip_Hanger on (ProjectNumber, RevitElementId); ancillaries in Pip_HangerAncillary are fully replaced

4.3 — Database Tables

WAY_ProductionBatches

Primary tracking table. One row per batch ever seen in the WAY in-progress Smartsheet report.

WAY_ProductionStatusHistory

Full audit timeline of status changes. One row per status period per batch.

Pip_Submission

One row per Revit export run (from PInfo.csv). Provides project context for every element.

Pip_Element

One row per pipe / fitting element. Upsert key: (ProjectNumber, RevitElementId). Key field: VicSequence — must match WAY_ProductionBatches.BatchName.

Pip_Hanger

One row per fabrication hanger. Same upsert key pattern. Also carries VicSequence.

Pip_ElementDim / Pip_HangerAncillary

Child dimension rows. Fully replaced (DELETE + INSERT) on every parent upsert.

4.4 — PowerBI Views

Key Join: VicSequence = BatchName

The join between Revit data and Smartsheet batch data uses:

Pip_Element.ProjectNumber = WAY_ProductionBatches.ProjectNumber
Pip_Element.VicSequence   = WAY_ProductionBatches.BatchName

This is the confirmed match between Revit's Vic_Sequence parameter and the WAY Batch ID in Smartsheet.

Recommended PowerBI Measures

Time Slicer Columns

FirstSeenYear, FirstSeenMonth, FirstSeenMonthLabel (e.g. Apr 2026), FirstSeenMonthSort (YYYYMM integer, use as sort-by for month label).

5 — Batch ID Format Reference

6 — Smartsheet Report Reference

Column Mapping

7 — Environment Variables

Verifying on the server (run as Administrator)

[System.Environment]::GetEnvironmentVariable('SMARTSHEET_ACCESS_TOKEN', 'Machine')
[System.Environment]::GetEnvironmentVariable('mcd_usr', 'Machine')
[System.Environment]::GetEnvironmentVariable('sqlro_McData', 'Machine')

8 — File & Directory Paths

9 — Task Scheduler Configuration

WAY Production Log Sync Task

10 — Troubleshooting

Script fails at startup — HTTP 401 from Smartsheet

SMARTSHEET_ACCESS_TOKEN is empty or expired. Verify the machine-level env var is set (see Section 7). If the token is expired, generate a new one in Smartsheet (Account → Apps & Integrations → API Access) and update the system env var.

Script fails — Cannot connect to SQL Server

Check that mcd_usr and sqlro_McData are set at machine level. Verify the server can reach MSM-DYNSQLTEST\mcdata — run ping MSM-DYNSQLTEST from the server. Confirm ODBC Driver 17 is installed: Get-OdbcDriver -Name "ODBC Driver 17*"

Rows still have NULL ProjectNumber after sync

The backfill runs automatically on every sync. If rows remain NULL after a run, check the log for Backfill: fixed X row(s). If the BatchName format is new and unrecognized, update BATCH_ID_RE or the SHEET_NAME_PROJECT_RE fallback in the script and re-sync.

vw_WAY_ProductionReport returns no rows

The view requires WAY_ProductionBatches to be populated by at least one successful sync run. Check the log file for errors. Confirm the view was created successfully by running:

SELECT name, create_date FROM sys.views WHERE name LIKE 'vw_WAY%'

Spooling columns (BatchPipeElementCount etc.) are all 0

Expected — Pip_Element is empty until a detailer runs A_script.py inside Revit and the piping processor processes the exported CSVs. No action required.

Log files not appearing on P:\

The script falls back to .\logs\ (next to the script) when the P: drive is not accessible. On the server, logs will be at C:\Scripting\Python\Scripts\Production Log Sync\logs\.

Deploying an updated script to the server

Edit the file in the workspace (D:\Scripting\Python\Scripts\Metal_Report\way_production_log_sync.py), then copy to the server using one of:

# Via admin share (if accessible):
Copy-Item "D:\Scripting\Python\Scripts\Metal_Report\way_production_log_sync.py" `
          "\\<ServerName>\C$\Scripting\Python\Scripts\Production Log Sync\" -Force

# Via PSRemoting:
$s = New-PSSession -ComputerName <ServerName>
Copy-Item "D:\...\way_production_log_sync.py" `
          -Destination "C:\Scripting\Python\Scripts\Production Log Sync\" -ToSession $s

11 — Related Systems (MSM / CAM)

This system is part of a broader fabrication tracking platform. The table below shows how components relate:

All systems share: dbo.Project, dbo.Project_Details, dbo.CAM_RunStats, and the environment variables mcd_usr / sqlro_McData.

McCorvey Companies © 2026 — Internal use only. For support contact the IT / BI team. Alex Argumedo