Company & Place Data Population

Assets display company (customer name) and place (city) in the card view. These are populated from the MSFS Dynamics 365 extraction DB.

How It Works

The migration script scripts/populate_asset_company_place.py maps each asset's connect_id:

Primary: connect_id → msdyn_customerasset.hsl_connectid → account table
- account.name → company + customer_name
- account.address1_city → place
- account.address1_line1 (+ line2) → address (if empty)
Fallback: When the account name is empty but the connect_id prefix (first 8 chars) maps to a branch/territory name, use the territory name as company
- e.g., VM-05064-... → branch cache → "Canteen Corp/Orlando, FL"

Coverage

Source	Assets	%
Account name from extraction DB	5,743	76.7%
Branch/territory fallback	1,606	21.4%
Still empty (no branch mapping)	139	1.9%
Total	7,488	100%

Place (city): 5,647 / 7,488 assets (75.4%)

Connect ID Format

The connect_id encodes:

VM-05912-0000068454 → branch ID 5912, machine ID 68454
First 8 chars (VM-05912) = prefix used for branch/territory mapping

Re-running

The script is idempotent — only updates empty fields:

cd ~/projects/canteen-asset-tracker
python3 scripts/populate_asset_company_place.py

SQL Query Fix

The GET /api/assets endpoint uses COALESCE(c.name, a.customer_name) instead of c.name AS customer_name to prevent the LEFT JOIN from overriding stored customer_name with NULL when customer_id is not set.