Skip to main content

Lookups Page

Cross-system data validation tools for debugging sync issues and verifying data consistency between Business Central, middleware database, and e-commerce platforms.

Overview​

The Lookups page provides four specialized tools to compare data across all three systems in your sync architecture:

  1. Business Central (BC) - Source of truth for inventory, orders, and tracking
  2. Middleware Database - Intermediate sync database (rm_* tables)
  3. CMS Platform - BigCommerce or WooCommerce storefront

Each lookup tool queries all relevant systems and highlights discrepancies, making it easy to identify where sync issues occur.

Access: GSM Middleware → Tools → Lookups

Permission Required: manage_gsm_middleware

Lookup Tools​

1. Order Lookup​

Purpose: Compare a single order across all systems to identify data mismatches

Use when:

  • Customer reports incorrect order details
  • Order stuck in sync queue
  • BC shows different data than storefront
  • Debugging order import issues

Interface​

Inputs:

  • Site: Select site from dropdown (all active sites)
  • Order Number: Enter full order number (e.g., SITE-12345)

Button: "Lookup Order"

Results Display​

Status Badge:

  • ✅ "No differences found" — Data matches across systems
  • ⚠️ "X Differences Found" — Mismatches detected
Differences Table​

Shows when mismatches detected:

ColumnDescriptionExample
FieldWhat data differstotal_price
MiddlewareValue in MW database$149.99
CMSValue on platform$139.99
SeverityIssue severity level🔴 Critical

Severity levels:

  • 🔴 Critical — Data mismatch affecting fulfillment (e.g., address, items)
  • 🟡 Warning — Minor difference (e.g., date format, rounding)
  • ℹ️ Info — Informational only (e.g., timestamps)
System Panels​

Expandable JSON views:

Middleware Database Panel:

  • Shows complete rm_order record
  • Includes line items (rm_lineitems)
  • Shows address (rm_address)
  • Click to expand/collapse JSON

CMS Platform Panel:

  • Shows API response from BigCommerce or WooCommerce
  • Full order object including products, customer, totals
  • Click to expand/collapse JSON

Line Items Table:

  • Displays all products in order with SKU, quantity, price
  • Data from middleware database

Example Use Case​

Scenario: Customer says they ordered 2 units but only received invoice for 1

Steps:

  1. Select customer's site
  2. Enter order number
  3. Click "Lookup Order"
  4. Check Differences Table:
    • Field: line_item_qty
    • Middleware: 2
    • CMS: 1
    • Severity: Critical
  5. Expand CMS Panel to see API response
  6. Verify: Storefront has wrong quantity, MW is correct
  7. Fix: Manually update storefront order via admin panel

2. Order Status Lookup​

Purpose: Quick order status check without email requirement (mirrors customer widget)

Use when:

  • Quick status check for customer service
  • Verifying tracking was pushed to BC
  • Checking shipment dates
  • Debugging order status widget issues

Interface​

Inputs:

  • Order Number: Enter order number (no email required)

Button: "Lookup Order Status"

Results Display​

Header:

  • Order number
  • Status badge (color-coded):
    • 🟢 SHIPPED — Order fully shipped
    • 🟢 COMPLETED — Order completed
    • 🟠 PROCESSING — Order in progress
    • 🔴 NOT FOUND — Order doesn't exist

Customer Card:

  • Customer name
  • Email address (clickable mailto link)
  • City, State, ZIP

Shipments Table (from Middleware):

ColumnDescriptionExample
Tracking #Tracking number1Z999AA10123456789
CarrierShipping providerUPS
SKUsProducts in shipmentSKU1, SKU2
DateShipment date2024-12-15 10:30

Shows all shipments from shipments table (middleware).

Business Central Tracking Table:

ColumnDescriptionExample
Tracking #Tracking number1Z999AA10123456789
CarrierCarrier codeFEDEX
ItemProduct SKUWIDGET-001
QtyQuantity shipped2

Shows BC tracking data (retrieved via BC API integration).

Example Use Case​

Scenario: Customer asking "Where's my order?"

Steps:

  1. Enter order number in tool
  2. Click "Lookup Order Status"
  3. See status: SHIPPED 🟢
  4. View Shipments table:
    • Tracking: 1Z999AA10123456789
    • Carrier: UPS
    • Date: 2024-12-10
  5. Copy tracking number and email customer

3. Inventory Lookup​

Purpose: Compare inventory quantities across BC, middleware, and CMS for multiple SKUs

Use when:

  • Debugging "out of stock" issues
  • Verifying inventory sync working
  • Checking why product shows wrong quantity
  • Auditing inventory accuracy

Interface​

Inputs:

  • Site: Select site from dropdown
  • SKUs: Comma-separated SKU list (e.g., SKU1, SKU2, SKU3)

Button: "Lookup Inventory"

Results Display​

Summary Stats:

StatDescriptionExample
CheckedHow many SKUs queried10
In SyncSKUs matching across all 3 systems8
Out of SyncSKUs with mismatches2

Filter Buttons:

  • All — Show all checked SKUs
  • Out of Sync (X) — Show only mismatches
  • In Sync (X) — Show only matching SKUs

Useful for focusing on problems when checking large SKU lists.

Inventory Comparison Table:

ColumnDescriptionExample
SKUProduct SKUWIDGET-001
BC QtyBusiness Central quantity500
BC StatusBC product statusActive
MW QtyMiddleware database quantity450
MW Last SyncLast sync timestamp2024-12-15 10:30
CMS QtyStorefront quantity450
BC↔MWDifference between BC and MW+50
MW↔CMSDifference between MW and CMS0
StatusSync status✅ In Sync / ❌ Mismatch

Row highlighting:

  • ⚠️ Yellow background — Out of sync (mismatch detected)
  • No highlighting — In sync (all systems match)

Difference cells:

  • +50 — CMS has 50 more than MW
  • -20 — CMS has 20 less than MW
  • 0 — Perfect match
  • — — Data unavailable in one system

Example Use Case​

Scenario: Product showing "Out of Stock" on website but BC shows 100 units available

Steps:

  1. Select site
  2. Enter SKU: WIDGET-001
  3. Click "Lookup Inventory"
  4. Read results table:
    • BC Qty: 100
    • MW Qty: 0
    • CMS Qty: 0
    • BC↔MW: +100 (BC has 100 more than MW)
    • Status: ❌ Mismatch
  5. Diagnosis: Inventory sync from BC → MW failed
  6. Fix: Check inventory sync health check, run manual sync, verify cron job

4. Tracking Lookup​

Purpose: Compare tracking/shipment data across BC, middleware, and CMS for an order

Use when:

  • Tracking not showing on storefront
  • BC shows shipment but customer doesn't see it
  • Debugging tracking sync issues
  • Verifying which system has tracking data

Interface​

Inputs:

  • Site: Select site from dropdown
  • Order Number: Enter order number

Button: "Lookup Tracking"

Results Display​

Summary Stats:

StatDescriptionExample
Total Tracking NumbersUnique tracking numbers found2
Fully SyncedTracking in all 3 systems1
Partial/MissingTracking missing from one+ system1

Tracking Comparison Table:

ColumnDescriptionWhat It Shows
Tracking NumberShipment tracking ID1Z999AA10123456789
In BCFound in Business Central✅ (expandable JSON)
In MiddlewareFound in shipments table✅ (expandable JSON)
In CMSFound on storefront❌ Not found
StatusSync status⚠️ Partial

Status badges:

  • ✅ Fully Synced — Tracking in all 3 systems
  • ⚠️ Partial — Missing from one or more systems

System cells:

  • ✅ Green checkmark — Data found (click to expand JSON)
  • ❌ Red X — Data not found

Tracking Detail Sections:

Expandable panels showing full JSON from each system:

  • Business Central — BC API response
  • Middleware — shipments table row
  • CMS — Platform API response

Click "▸ Full Details" to expand.

Example Use Case​

Scenario: Customer says tracking link not working on order confirmation page

Steps:

  1. Select site
  2. Enter order number
  3. Click "Lookup Tracking"
  4. View table results:
    • Tracking: 1Z999AA10123456789
    • In BC: ✅
    • In Middleware: ✅
    • In CMS: ❌
    • Status: ⚠️ Partial
  5. Diagnosis: Tracking sync from MW → CMS failed
  6. Fix: Check tracking sync toggle in site settings, verify API credentials, run manual sync

Common Workflows​

Debugging "Order Not Syncing"​

Goal: Find where order sync is failing

Steps:

  1. Go to Order Lookup tab
  2. Enter site and order number
  3. Check results:
    • Found in MW but not CMS? → Order sync MW → CMS failed
    • Not found in MW? → Order sync CMS → MW failed
    • Found in both but differences? → Data transformation issue
  4. Review Differences Table for specific fields
  5. Check action log in Orders page for error messages
  6. Verify API credentials in site settings

Verifying Inventory Sync Health​

Goal: Confirm inventory sync is working correctly

Steps:

  1. Go to Inventory Lookup tab
  2. Select site
  3. Enter 10-20 popular SKUs (comma-separated)
  4. Click "Lookup Inventory"
  5. Review summary stats:
    • In Sync > 95%? → Sync healthy
    • Out of Sync > 5%? → Investigate issues
  6. Click "Out of Sync" filter button
  7. Review MW Last Sync column:
    • Recent timestamps? → Sync running but data mismatching
    • Old timestamps? → Sync may be stopped
  8. Check Control Panel → Inventory health check

Pre-Launch Site Validation​

Goal: Ensure new site sync working before going live

Steps:

  1. Create test order on new site
  2. Use Order Lookup to verify:
    • Order appears in MW database
    • Data matches between CMS and MW
  3. Use Inventory Lookup with 5-10 SKUs:
    • Verify all show "In Sync"
  4. Use Tracking Lookup with test order (after creating test shipment in BC):
    • Verify tracking appears in all 3 systems
  5. Use Order Status Lookup to verify customer widget works
  6. If all lookups pass → Site ready for launch

Monthly Data Audit​

Goal: Proactive check for sync drift

Steps:

  1. For each site:
    • Run Inventory Lookup on 20+ SKUs
    • Record "Out of Sync" percentage
  2. If any site > 5% out of sync:
    • Check inventory sync health
    • Review recent errors in debug.log
    • Run manual sync if needed
  3. Verify 5-10 recent orders with Order Lookup
  4. Document any persistent issues for investigation

Technical Details​

API Endpoints​

Base: /wp-json/gsm-middleware/v1/

EndpointMethodParametersDescription
/lookups/orderGETsite_id, order_numberCompare order data
/lookups/inventoryGETsite_id, skus (comma-separated)Compare inventory
/lookups/trackingGETsite_id, order_numberCompare tracking
/lookups/order-statusGETorder_numberGet order status

Authentication: WordPress REST API nonce (auto-handled by @wordpress/api-fetch)

Controller: src/API/Lookups_REST_Controller.php

Data Sources​

LookupMiddleware DBBusiness Central APIPlatform API
Orderrm_order, rm_lineitems, rm_addressOrder APIOrders endpoint
Order Statusshipments, rm_orderTracking APIShipments endpoint
Inventoryrm_itemsInventory APIProducts endpoint
TrackingshipmentsTracking APIShipments endpoint

React Components​

Main Component: assets/js/pages/LookupsPage.jsx (tab container)

Sub-components:

  • OrderLookup.jsx — Order comparison tool
  • OrderStatusLookup.jsx — Status widget mirror
  • InventoryLookup.jsx — Inventory comparison tool
  • TrackingLookup.jsx — Tracking comparison tool

State Management: React hooks (useState, useCallback)

API Client: assets/js/api/lookups.js (wrapper functions)

Troubleshooting​

"Request failed" Error​

Cause: Network error, API timeout, or permission issue

Solution:

  1. Check browser console for detailed error
  2. Verify user has manage_gsm_middleware capability
  3. Check wp-content/debug.log for PHP errors
  4. Test REST API directly: 
    curl -X GET "https://site.com/wp-json/gsm-middleware/v1/lookups/order?site_id=1&order_number=SITE-123" \
      -H "X-WP-Nonce: YOUR_NONCE"

Order/SKU "Not Found" in All Systems​

Cause: Order/product doesn't exist or wrong site selected

Solution:

  1. Verify order number is correct (check case-sensitivity)
  2. Confirm correct site selected in dropdown
  3. Check Orders/Items page to verify order/SKU exists in MW
  4. Query database directly: 
    SELECT * FROM rm_order WHERE number = 'SITE-12345';
    SELECT * FROM rm_items WHERE sku = 'WIDGET-001';

Inventory Shows "-" (Dash) for BC or CMS​

Cause: API connection failed or SKU not found in that system

Solution:

  1. Check API credentials in site settings
  2. Verify SKU exists in BC/platform:
    • BC: Check Business Central directly
    • CMS: Check product admin page
  3. Review API error logs: 
    tail -100 wp-content/debug.log | grep -i api
  4. Test API connection manually from Testing Tools

Tracking Shows "Partial" Status​

Cause: Tracking sync incomplete (normal during sync delay)

Solution:

  1. Wait 5-10 minutes and re-check (cron may be running)
  2. Check tracking sync toggle enabled in site settings
  3. Check tracking sync health check in Control Panel
  4. Review tracking sync logs: 
    tail -100 wp-content/debug.log | grep tracking
  5. Manually trigger sync if needed (Testing Tools page)

Large SKU List Causes Timeout​

Cause: Too many SKUs queried at once (API rate limits)

Solution:

  1. Break SKU list into smaller batches (10-20 SKUs max)
  2. Run sequential lookups instead of one large query
  3. Increase PHP max_execution_time for admin users (in wp-config.php): 
    if ( is_admin() && current_user_can( 'manage_gsm_middleware' ) ) {
        set_time_limit( 120 );
    }

Best Practices​

  1. Start with Order Lookup when debugging order issues (most comprehensive)
  2. Use Inventory Lookup with "Out of Sync" filter for large SKU audits
  3. Bookmark Lookups page for quick customer service access
  4. Document patterns - If you see same difference repeatedly, may indicate systemic issue
  5. Combine with Control Panel - Check health status before running lookups
  6. Use Order Status for customers - Quick, non-technical status view
  7. Export lookup results - Take screenshots or copy JSON for support tickets
  8. Regular audits — Run monthly inventory lookups to catch drift early