← Back to all projects
Ready Created 2026-08-11 0/2 tasks

Cotton PO Template — Fixes Plan

Sheet: Cotton PO Template 16Gx-N9HK35MSLZfz5DAGJ6FhHN6u2VLdruwmiXIL4lE Apps Script: scriptId 1BV0ubVbA5xMr5-IL3oPpPxuXwWG5CVZN5M3qkY3QoYoAcCWJiboAxHy8, deploy via clasp push from ~/ai-projects-local/cotton-po-scripts/rb-cotton-po/ Related memory: [[cotton-po-clasp-and-overrides]], [[rbd-collection-ordering-tiers]]

Objective

Fix four defects in the Cotton PO generator so the Feb-2027 (081526) collection POs total correctly and price correctly, without breaking the override columns or the clasp deploy path.

Decisions locked with Cole (2026-08-11 Q&A)

  1. Dropship units → excluded from Ship-to-RBD columns only; still counted in the row-35 mill PO grand total (mill still makes them).
  2. Panels → both the yardage math AND the rate/surcharge are wrong; some of it is expected to be hand-edited by Sabrina (so leave editable, don't fully hardcode).
  3. Rates → base per-yard rate is FINE; only the D/R surcharge is wrong.
  4. D/R surcharge mechanism → driven by the PO's mill/vendor, which already lives in the Data and Collections tabs (reuse it — no new manual tag).
  5. Surcharge build → one Standard surcharge everywhere + a Taihan override (only Taihan differs today).

Root causes found (verified in-sheet + in code)

Two divergent column layouts (drives Fix 1)

  • Master Template / Template (canonical): M=# bolts · N–T=Ship-to-RBD (15 D/R, 10 D/R, ROT, Laminate, …) · U–Y=Dropship slots (row 8 = "PO #") · Z=Rate · AA=Amount · AB/AC=D/R 15/10 Surcharge · AD=Laminate Surcharge · AE=Total. Grand total = row 35.
  • Generated tabs (e.g. HOORAYUSA-081526): shifted ~2 cols left, 27 cols, no dropship region, no laminate col — Ship-to-RBD = L–P, Total = U, grand total = row 34.
  • Dropship POs.js inserts columns at hardcoded START_COL = U1 = 21 and copies the formula from col U. On a generated tab col U = Total, so the insertion lands on the pricing/total block → corrupts the row-35 grand total and mis-aligns Ship-to-RBD. Same hardcoded-index fragility class as the DATA_COL constants.

Rates.js drops the surcharge (drives Fix 3)

  • buildSimplePricingLookup_() reads surcharge (Simple Pricing col Q) into the lookup, but updateBaseRates() writes only match.rate to Data "Base Rate". The surcharge is never written. Per-line D/R 15/10 surcharge (template cols AB/AC) is computed by in-sheet formula, not by mill.

Exact formula-level diagnosis (Master Template, verified 2026-08-11)

Data cols: AJ=Item(matchkey), AL=D/R or Units(qty), AQ=Laminate, AR=CP10, AT=CP15, BC=Vendor(mill), BD=PO#, BG=Base Rate, BI=Rate. Master sections: Cotton 12–34 (TOTAL 35) · PRECUTS 39–52 · DESIGN BOARDS 56–67 · KIT 68–81 / 82–94 · casepack sub-table 70–93 · precut breakout 109–220. Panels (1P-) are excluded from the cotton QUERY — need to confirm where 1P lands + whether it gets a base-rate lookup.

Root cause A — grand total row 35 breaks on dropship insert. Every row-35 cell = =SUM(INDIRECT("AE12:AE"&ROW()-1)) — hardcoded column LETTER inside INDIRECT. INDIRECT does not follow column insertions. Dropship inserts at col U push the pricing block (Rate/Amount/Surcharge/Total, Z–AE) one col right to AA–AF, but AE35's INDIRECT still sums old col AE (now a surcharge col) → grand total wrong. → Fix: make each row-35 sum reference its OWN column: =SUM(INDIRECT("R12C"&COLUMN()&":R"&(ROW()-1)&"C"&COLUMN(),FALSE)). Robust to both row inserts (ROW()) and column inserts (COLUMN()).

Root cause B — Ship-to-RBD (N/O) + surcharges (AB/AC) miss dropship cols. N12/O12/AB12/AC12 subtract dropship via FILTER(U12:X12, LEFT(U$10:X$10,4)="CP15"…) — hardcoded 4-col range U:X, but the dropship block is U–Y (5) and GROWS when the script inserts more. Columns past X aren't subtracted → Ship-to-RBD overstated (includes dropship it should exclude) and surcharge base misses those units. → Fix: replace U:X with a range spanning the whole dropship block = U through the col left of "Rate" (anchor right edge via MATCH("Rate",$11:$11,0)), using INDIRECT so it self-extends as columns are inserted.

Root cause C — D/R-10 surcharge rate is a flat literal. AB12 = (…)*0.15 (D/R-15, correct everywhere). AC12 = (…)*0.2 (D/R-10) — hardcoded 0.2, never Taihan's 0.17. → Fix: AC12 = (…) * IF(INDEX(Data!BC:BC,MATCH($A12,Data!AJ:AJ,0))="TaiHan Textile Co., Ltd.", 0.17, 0.2). D/R-15 (AB, 0.15) stays literal — identical for all mills.

Panels (Fix 2): ensure the panel (1P-) section carries a base-rate lookup INDEX(Data!BG:BG, MATCH(sku,Data!AJ:AJ,0)); size (24/36/56) + qty stay manual for Sabrina.

Implementation plan

Fix 1 — Dropship no longer breaks grand total / Ship-to-RBD

  • Re-anchor Dropship POs.js insertion point dynamically instead of hardcoded U=21: locate the dropship region by label (row 8 "PO #" run, or a fixed marker column relative to the "Rate" header) so it works regardless of whether the tab has a Laminate column.
  • Make the row-35 grand total and Ship-to-RBD sums reference columns by structure/label so inserted dropship columns don't shift them.
  • Accounting: grand total (mill PO) SUMs cotton + precut + kit incl. dropship; Ship-to-RBD SUMs exclude the dropship slot columns. Verify both after a dropship insert.
  • Apply the same fix to the precut and kit Ship-to-RBD sub-totals (Cole flagged those specifically).

Fix 2 — Panels

  • Yardage math: correct the yds/pnls (col L) → Total-yds conversion for panel SKUs (1P- prefix). [OPEN: exact correct rule — see Open Items.]
  • Rate/surcharge: panels should not carry the per-yard cotton rate + D/R surcharge blindly. [OPEN: correct panel rate rule.] Leave the panel rate/qty cells editable so Sabrina can override.

Fix 3 — D/R surcharge by mill (Standard + Taihan override)

  • Build a small, readable Standard vs Taihan surcharge reference (D/R 15 + D/R 10 values).
  • Wire the template's D/R 15/10 surcharge (cols AB/AC) to pick Standard vs Taihan based on the PO's vendor/mill from Data/Collections (vendor already populated per line via Data col BC / Collections).
  • Leave updateBaseRates() writing base rate as-is (base rate is correct); the surcharge change is formula-side keyed on mill.

Answers (2026-08-11, round 2) + revised approach

Approach: update the corrected Master Template layout into each collection sheet, then add functionality to the supporting sheets (Simple Pricing surcharge table + mill logic). Not a from-scratch rebuild.

Surcharge values (per-yard, multiplied by the D/R yardage):

  • Standard (all mills): D/R-15 = $0.15/yd, D/R-10 = $0.20/yd
  • Taihan override: D/R-15 = $0.15/yd, D/R-10 = $0.17/yd
  • → Only the D/R-10 rate differs for Taihan (.20 → .17). D/R-15 identical everywhere. So the override is a single conditional on the D/R-10 surcharge.
  • Taihan vendor string: TaiHan Textile Co., Ltd. (match against Data/Collections vendor).

Panels: per-panel; sizes vary (24" / 36" / 56") — messy, so keep manual for Sabrina. But base rate must pull in (currently suspected NOT pulling). Confirm panel SKUs (1P-/P) get a base rate from Simple Pricing; leave size/qty editable.

Still open

  • ☐ Propagation method — edit each existing collection tab in place vs. re-push corrected master (affects whether Sabrina's in-progress qty/overrides survive). ← next question
  • ☐ Confirm which tabs are live vs. TEST/Copy scratch.

Sequencing

  1. Fix 1 (dropship/totals) — highest blast radius, unblocks correct PO totals.
  2. Fix 3 (D/R surcharge by mill) — pricing correctness.
  3. Fix 2 (panels) — partly manual; do after 1 & 3, coordinate editable cells with Sabrina.

Verification

  • clasp workflow: clasp pull → edit → node --check *.js → clasp push -f.
  • After each fix, regenerate one collection tab (e.g. HOORAYUSA) + one with dropships and one Taihan PO; confirm: row-35 grand total, Ship-to-RBD (incl. precut/kit), and D/R surcharge all tie out. Snapshot before/after.
  • Do NOT write to the live sheet while Cole/Sabrina is editing (sheet-level lock causes API timeouts).

Risks / guardrails

  • Live pricing sheet — verify each formula reproduces expected values on a sample before rolling across tabs (batik-retrofit gate pattern).
  • Hardcoded DATA_COL indices in Apps Script break on any Data column insert — re-audit against live headers if touched.
  • Never commit .claude/; clasp auth in ~/.clasprc.json.
Source: ~/ai-projects/mission-control/plans/cotton-po-fixes.md