Redundant-Concept Detection Sweep

01Executive Summary

Twelve concept groups were identified across customer, vendor, item, and transaction records. The population evidence sorts them into a spectrum: 2 active duplicates (same value stored twice on the same records), 3 parallel silos (same concept split across disjoint record populations — worse than duplication for reporting), 5 latent/dormant groups (multiple homes exist, at most one is used), 1 complementary pair (looks redundant, isn't), and 1 unverifiable. No group shows the worst-case pattern — conflicting values populated in parallel at scale.

02Verdict Taxonomy

Every group receives one verdict, earned by population evidence — not by field names alone.

03Concept Groups & Evidence

Population counts are from live SuiteQL. For multiselect fields, counts come from MAP_item_* join tables — the only truthful source (see Methodology: the sentinel trap). Base populations: 274 customers, 94 vendors, 338 items.

04Key Findings

1. F1 — The Color/Size concept is siloed, not duplicated (OPERATIONAL). Local matrix fields (custitemcustitem_color: 80 items, custitemcustitem_size: 27) and WS-demo fields (custitem_ws_matrix_color/_size: 16 each) hold the same concepts on completely disjoint item sets — co-population is exactly 0 for both pairs. Matrix child items (mattress family, ids 228–239) populate size-local only; the WS-16 are a separate demo population. Any "sales by color" report reads one field and silently drops the other population.
2. F2 — EBP's DD file format is a true active duplicate (COSMETIC, bundle-owned). custentity_9572_dd_file_format = custentity_9997_dd_file_format = 37 on the same two customers (Blockster Inc. id 280, Design Excellence Ltd. id 257), both alongside custentity_2663_direct_debit = T. This is the EBP bundle family writing one concept into two generations of its own schema — harmless today, but it doubles as proof the account carries multiple overlapping EBP bundle generations (also visible in the prefix census: 2663 / 9572 / 9997 / 15529).
3. F3 — "Which email do we use?" has three homes on customer, none reconciled (LATENT). Standard email (271/274) does all the work; custentity_atlas_customer_invoice_email is empty (0) and custentity_2663_email_address_notif has 1 value. On vendor, the one populated EBP notification email exactly equals the standard email — a duplicate write. The risk is forward-looking: an AP/AR automation picking the wrong field will behave correctly in testing (fields agree or are empty) and wrongly the day someone populates an override.
4. F4 — Risk-rating concept: the local field lost (LATENT). custentity_renewal_risk (local, with its own custom list) is populated on 0 customers; Atlas's custentity_atlas_customer_probability carries 9. The local field is dead schema — it was superseded before adoption.
5. F5 — Item lifecycle flags: three homes, none used (LATENT). custitem_atlas_approved is set on 272 items but every value is F; custitem_atlas_item_stage and custitem_publish_item are 0. An always-false flag on 272 records is indistinguishable from noise — nothing reads it, or everything that reads it sees "no".
6. F6 — Item imagery: three naming schemes, one winner (LATENT). custitem_atlas_item_image (158) is the working field; custitem_supplier_image_url (7, PromoStandards) and custitemcustitem_image_code (0) are fringe; the Atlas WOA duplicate set (_woa_item_image, _woa_att1-3, _woa_item_planner — near-verbatim copies of other Atlas fields) is entirely empty.
7. F7 — Packing instructions exist at four levels; only one is used (LATENT). Customer entity (0), SO body (0), SO line (0) — all Ship Central plumbing — while item-level custitem_packship_item_packinstruction (6) is the only populated home. The four-level design is the bundle's, not the account's; the account has effectively chosen item-level.
8. F8 — In-Hand vs Promise Date are complementary, not redundant (CLEARED). custbody_inhand_date (2 headers, sales-side "customer needs it by") vs custcol_atlas_promise_date (67 lines, purchase-side vendor commitment). Different grain, different direction — documented here so nobody consolidates them.
9. F9 — External-identifier schemes on vendor are parallel but disjoint (SILO, minor). externalid (19 vendors) vs custentity_promostandards_id (4), overlap 0. Defensible (different integrations), but worth a naming/ownership note before a third scheme appears.
10. F10 — Methodology finding: multiselect population counts lie. Aggregate CASE WHEN <multiselect> IS NOT NULL returned 338/338 because multiselects surface a 'RELATIONSHIP FIELD' sentinel string instead of NULL. True counts require the MAP_<table>_<fieldid> join tables (80/16/27/16). Any prior analysis in this account that counted multiselect population via NULL checks is suspect.

05Recommendations

06Methodology

1. Candidate generation. The verified field inventory from the Native-Record Footprint Map (same account, same day — 258 fields with provider attribution) was scanned for label/scriptid semantic collisions on the same record type: same noun ("color", "email", "file format", "packing instructions"), same concept family (risk/churn/probability), or near-verbatim field sets (Atlas WOA duplicates). Twelve groups emerged.
2. Population profiling. For each group, one aggregate query counted per-field population, co-population (both fields non-null on the same record), and — where formats allowed — value equality (LOWER(TRIM(a)) = LOWER(TRIM(b))).
3. The multiselect sentinel trap (discovered mid-sweep). Item color/size fields first reported 338/338 populated via CASE WHEN ... IS NOT NULL, while row-level SELECTs showed blanks and a raw probe returned the literal string 'RELATIONSHIP FIELD'. These are multiselect fields: SuiteQL exposes a sentinel, not the values. Authoritative counts came from the MAP_item_<fieldid> join tables (COUNT(DISTINCT mapone)), and co-population from joining two MAP tables on mapone. Also observed: GROUP BY on a multiselect column throws "Invalid or unsupported search".
4. Record sampling. Groups with non-trivial population were spot-checked at row level: the 2 DD-format customers (ids 280, 257 — values equal), 12 matrix-child items (ids 228–239 — size-local populated, WS fields blank), the 1 vendor with both email fields (values equal).
5. Verdict assignment. Per the taxonomy in §02 — evidence-based, never name-based. One pair (In-Hand vs Promise Date) was cleared by evidence of different grain and direction.
6. Scope guards. custentity_atlas_avail_credit is not SQL-exposed on customer (query error: unknown identifier), so the credit-concept group (vs standard creditlimit, 254 populated) is verdict-unverifiable. Transaction-line probes for dormant columns (custcol_scm_customerpartnumber, reason codes, SO packing fields) ran account-wide with zero rows found — dormancy is global, not window-limited.

07Source Queries

The evidence set, reproducible as-is. Q5–Q7 document the multiselect trap and its resolution.

SELECT COUNT(*) AS n,
 SUM(CASE WHEN email IS NOT NULL THEN 1 ELSE 0 END) AS e_std,
 SUM(CASE WHEN custentity_atlas_customer_invoice_email IS NOT NULL THEN 1 ELSE 0 END) AS e_atlas,
 SUM(CASE WHEN custentity_2663_email_address_notif IS NOT NULL THEN 1 ELSE 0 END) AS e_ebp,
 SUM(CASE WHEN LOWER(TRIM(email)) = LOWER(TRIM(custentity_atlas_customer_invoice_email)) THEN 1 ELSE 0 END) AS e_eq,
 SUM(CASE WHEN custentity_9572_dd_file_format IS NOT NULL AND custentity_9997_dd_file_format IS NOT NULL THEN 1 ELSE 0 END) AS dd_both,
 SUM(CASE WHEN custentity_renewal_risk IS NOT NULL THEN 1 ELSE 0 END) AS risk_renewal,
 SUM(CASE WHEN custentity_atlas_customer_probability IS NOT NULL THEN 1 ELSE 0 END) AS risk_prob
FROM customer
-- → n=274, e_std=271, e_atlas=0, e_ebp=1, dd_both=2, risk_renewal=0, risk_prob=9

SELECT COUNT(*) AS n,
 SUM(CASE WHEN email IS NOT NULL THEN 1 ELSE 0 END) AS e_std,
 SUM(CASE WHEN custentity_2663_email_address_notif IS NOT NULL THEN 1 ELSE 0 END) AS e_ebp,
 SUM(CASE WHEN email IS NOT NULL AND custentity_2663_email_address_notif IS NOT NULL THEN 1 ELSE 0 END) AS e_both,
 SUM(CASE WHEN LOWER(TRIM(email)) = LOWER(TRIM(custentity_2663_email_address_notif)) THEN 1 ELSE 0 END) AS e_eq
FROM vendor
-- → n=94, e_std=53, e_ebp=1, e_both=1, e_eq=1 (the one override duplicates the standard email)

SELECT id, entityid, custentity_9572_dd_file_format AS dd_9572,
  custentity_9997_dd_file_format AS dd_9997, custentity_2663_direct_debit AS dd_flag
FROM customer
WHERE custentity_9572_dd_file_format IS NOT NULL OR custentity_9997_dd_file_format IS NOT NULL
-- → 2 rows: ids 280, 257 — both fields = 37 on both rows, dd_flag = T

-- LIES for multiselect fields (returns a sentinel, not NULL):
SELECT SUM(CASE WHEN custitemcustitem_color IS NOT NULL THEN 1 ELSE 0 END) FROM item  -- → 338 (wrong)
-- The tell — raw value is a literal sentinel string:
SELECT TO_CHAR(custitemcustitem_color) FROM item WHERE id = 228  -- → 'RELATIONSHIP FIELD'
-- Also: GROUP BY on a multiselect column → "Invalid or unsupported search"

SELECT
 (SELECT COUNT(DISTINCT mapone) FROM MAP_item_custitemcustitem_color)   AS items_color_local, -- 80
 (SELECT COUNT(DISTINCT mapone) FROM MAP_item_custitem_ws_matrix_color) AS items_color_ws,    -- 16
 (SELECT COUNT(DISTINCT mapone) FROM MAP_item_custitemcustitem_size)    AS items_size_local,  -- 27
 (SELECT COUNT(DISTINCT mapone) FROM MAP_item_custitem_ws_matrix_size)  AS items_size_ws     -- 16
FROM dual

SELECT
 (SELECT COUNT(*) FROM (SELECT DISTINCT a.mapone FROM MAP_item_custitemcustitem_color a
   JOIN MAP_item_custitem_ws_matrix_color b ON b.mapone = a.mapone)) AS color_both,  -- 0
 (SELECT COUNT(*) FROM (SELECT DISTINCT a.mapone FROM MAP_item_custitemcustitem_size a
   JOIN MAP_item_custitem_ws_matrix_size b ON b.mapone = a.mapone)) AS size_both     -- 0
FROM dual
-- Zero overlap in both pairs = parallel silos, not duplication

SELECT
 (SELECT COUNT(*) FROM item WHERE custitem_atlas_item_image IS NOT NULL)     -- 158,
 (SELECT COUNT(*) FROM item WHERE custitem_supplier_image_url IS NOT NULL)  -- 7 (∩ atlas = 0),
 (SELECT COUNT(*) FROM item WHERE custitem_atlas_woa_item_image IS NOT NULL) -- 0 (whole WOA set = 0),
 (SELECT COUNT(*) FROM item WHERE custitem_atlas_approved = 'T')             -- 0 of 272 set (all F)
FROM dual

SELECT
 (SELECT COUNT(*) FROM transaction WHERE type='SalesOrd' AND custbody_packship_so_packinstructions IS NOT NULL) -- 0,
 (SELECT COUNT(*) FROM transactionline tl JOIN transaction t ON t.id=tl.transaction
   WHERE t.type='SalesOrd' AND tl.custcol_packship_so_line_packinstruct IS NOT NULL) -- 0,
 (SELECT COUNT(*) FROM transaction WHERE custbody_inhand_date IS NOT NULL)      -- 2,
 (SELECT COUNT(*) FROM transactionline WHERE custcol_atlas_promise_date IS NOT NULL) -- 67,
 (SELECT COUNT(*) FROM transactionline WHERE custcol_scm_customerpartnumber IS NOT NULL) -- 0
FROM dual

SELECT
 (SELECT COUNT(*) FROM vendor WHERE externalid IS NOT NULL) AS v_externalid,  -- 19
 (SELECT COUNT(*) FROM vendor WHERE custentity_promostandards_id IS NOT NULL AND externalid IS NOT NULL) AS v_both -- 0
FROM dual

08Assumptions, Caveats & Limitations

1. Concept grouping is semantic judgment. Groups were formed from label/scriptid meaning; a field pair could share a name yet be intentionally distinct (one pair — In-Hand vs Promise Date — was cleared on exactly those grounds). Groupings are falsifiable against the evidence shown.
2. Candidate universe = the footprint map's verified inventory (customer, vendor, item, SO/Invoice/Vendor Bill fields, same account, 2026-08-22). Concepts split across other record types (e.g., contact vs customer) were out of scope.
3. Population ≠ usage. A populated field might be written by a dead script; an empty field might be read by live logic expecting future data. Retirement decisions still need the zero-reference check that the retirement process performs.
4. Value-equality checks ran only where formats allowed (emails, DD formats). Select-field equality compares internal ids, which is exactly right for same-list fields (DD formats share id 37) but meaningless across different source lists (renewal risk vs probability were not value-compared).
5. Multiselect counts count items with ≥1 value via MAP tables; multi-valued combinations were not decomposed. Item base = 338 (grew from 374-catalog snapshot date; count is live).
6. custentity_atlas_avail_credit is not SQL-exposed on customer in this account — the credit group's verdict is honestly "unverifiable" rather than assumed.
7. Dormancy checks were account-wide, not windowed — zero rows means zero ever, not zero recently.
8. Point-in-time snapshot (2026-08-22). Bundle updates and new writers change population patterns; re-run Q1–Q9 to refresh. The verdicts most likely to move: latent groups gaining a second populated field.