# Autocomplete Field Fix — Projects / Accounts / Stocklists

- **Date:** 2026-07-15
- **Status:** Complete
- **Status Date:** 2026-07-15
- **Phases:** 4
- **Phases Complete:** 4
- **Notes:** Pure bug fix — views, template HTML, and shared JS only. No meta-table or admin
  changes — all metadata-driven work lives in `docs/2026-07-15-static-fields-to-meta.md`,
  which depends on this plan shipping first. Migration 008 applied and all fields verified
  working in the UI 2026-07-15. Follow-up spotted during verification: some static
  autocomplete fields (e.g. projects `company_id`) generated no audit-log entries — the
  history tables captured everything, but the audit display queries in `*_load.php` only
  covered 4 fields per module. Fixed 2026-07-16: company/account audit CTEs added, parent
  fields now display names not IDs, a leftover `where project_id = 16` debug filter removed
  from the projects parent-project CTE, and copy-paste audit labels corrected.

## Plan Phases

- 0 Verify ✅
- 1 Views ✅
- 2 Helper HTML ✅
- 3 Style/Shared JS ✅

## Problem

Several fields across the project, account and stocklist editors are autocomplete pairs: a
visible text input where the user types a name (user, company, account, project) and a hidden
input that stores the resolved ID. The hidden ID is what gets saved. On load, the hidden ID is
repopulated but the visible name input can be left blank, so the user cannot tell whether the
field is set.

The wayleave editor implements the intended behaviour end-to-end (visible label repopulated on
load, clear button, hidden ID) but is architecturally different from the other modules.
**Projects is the template to align to**, not wayleave.

### Affected fields

| Module | Field | Visible input id | Hidden input id | Lookup type |
|---|---|---|---|---|
| Projects | Account | `account_name` | `account_id` | `accountname` |
| Projects | Project Manager | `project_manager_name` | `project_manager` | `usernames` |
| Projects | Assigned Company | `company_name` | `company_id` | `companyname` |
| Projects | Parent Project | `parent_project_name` | `parent_project_id` | `projectname` |
| Accounts | Account Manager | `account_manager_name` | `account_manager` | `usernames` |
| Accounts | Assigned Company | `company_name` | `company_id` | `companyname` |
| Accounts | Parent Account | *(none — raw ID input)* | `parent_account_id` | `accountname` |
| Stocklists | Stocklist Manager | `stocklist_manager_name` | `stocklist_manager` | `usernames` |
| Stocklists | Assigned Company | `company_name` | `company_id` | `companyname` |
| Stocklists | Account | `account_name` | `account_id` | `accountname` |

**Parent Stocklist is deliberately excluded.** Its input is commented out in the v2 template
(`html_body_stocklistedit_v2.php:188-192`) so a parent stocklist cannot be set from the UI at
all today. Rather than hardcode HTML here that the meta migration would immediately delete, it
is the acceptance test for the meta plan (enable a field with zero template changes).

## Root-cause analysis

All three editors populate static fields with the same loop
(`project_edit_v2.js:2646`, `account_edit_v2.js:1386`, `stocklist_edit.js:1537`):

```js
$.each(dataStatic, function (index, row) {
    $.each(row, function (key, value) { $('#' + key).val(value); });
});
```

A visible name input is only populated if the load view returns a column whose **alias exactly
matches the input's element id**. Comparing each load view (per
`sql/netplanner_schema_20260706.sql`) against the form ids:

| Module | Load view | Label column returned | Input id expected | Match? |
|---|---|---|---|---|
| Projects | `projects.vw_projects_list` | `pm_username` | `project_manager_name` | **✗ broken** |
| Projects | `projects.vw_projects_list` | `contractor_name` | `company_name` | **✗ broken** |
| Projects | `projects.vw_projects_list` | `account_name` | `account_name` | ✓ |
| Projects | `projects.vw_projects_list` | `parent_project_name` | `parent_project_name` | ✓ |
| Accounts | `accounts.vw_accounts` | `account_manager_name` | `account_manager_name` | ✓ (verify) |
| Accounts | `accounts.vw_accounts` | `company_name` | `company_name` | ✓ (verify) |
| Accounts | `accounts.vw_accounts` | *(no parent label column)* | *(no input exists)* | **✗ missing feature** |
| Stocklists | `stocklists.vw_stocklists` | `stocklist_manager_name` | `stocklist_manager_name` | ✓ (verify) |
| Stocklists | `stocklists.vw_stocklists` | `company_name` | `company_name` | ✓ (verify) |
| Stocklists | `stocklists.vw_stocklists` | `account_name` | `account_name` | ✓ (verify) |

Findings:

1. **Projects — Project Manager and Assigned Company are definitively broken**: the view
   aliases (`pm_username`, `contractor_name`) don't match the input ids, so the labels are
   never written. The hidden IDs populate fine.
2. **Accounts / Stocklists** — per the baseline schema the aliases match, so those labels
   *should* populate. The verify step confirms each in the running app; if any are blank in
   practice, diff the live view definitions against the baseline (drift) before touching JS.
3. **Accounts — Parent Account** isn't an autocomplete at all: a bare text input showing the
   raw ID (`html_body_account_edit_v2.php:184-186`).
4. **UI parity gaps**: projects has clear buttons (`setupAutoclearButton`,
   `project_edit_v2.js:7865`); accounts and stocklists have none. The function also never sets
   the button's initial visibility (line 7886 commented out), so the button shows even when
   the field is empty.
5. **Labels are never saved — by design, and that stays.** Save endpoints whitelist the hidden
   ID columns in `$staticFields` and update the entity table; the `*_name` POST keys fall
   through to the dynamic-field lookup and are ignored. (The residual risk — an admin creating
   a dynamic field whose `field_form_id` collides with a helper id — is closed in the meta
   plan, not here.)
6. **Do not rename `pm_username` / `contractor_name` in the views** — they're consumed by the
   list pages and embedded tables (`project_list.js`, `account_list.js`, `stocklist_list.js`,
   `stocklist_edit.js`, `account_edit_v2.js`, `opportunity_edit.js`). New alias columns must
   be **appended** (also a PostgreSQL `CREATE OR REPLACE VIEW` requirement).
7. **Known limitation, out of scope**: `fn/autocomplete.php` exits for `companyname` lookups
   when the user's company is not company 1, so non-super users have a dead company
   autocomplete. After this fix the field at least displays its current value.

## The fix

### Step 0 — Verify (no code changes)

For each of the 10 fields: open an existing record with the value set, confirm whether the
visible label populates. Expected: projects PM + company blank; everything else populated.
If accounts/stocklists labels are blank in practice, dump the live view definitions and diff
against the baseline before proceeding.

### Step 1 — Fix the views (migration `db/008_autocomplete_view_labels.sql`)

- `projects.vw_projects_list`: restate the full baseline definition and **append**
  `COALESCE(upm.username, '') AS project_manager_name` and
  `COALESCE(c.company_name, '') AS company_name` (both joins already exist in the view).
  `pm_username` / `contractor_name` stay untouched for the list pages.
- `accounts.vw_accounts`: append `COALESCE(pa.account_name, '') AS parent_account_name` via a
  new `LEFT JOIN accounts.accounts pa ON p.parent_account_id = pa.account_id`.
- `stocklists.vw_stocklists`: no change expected (confirm in Step 0).

The existing populate loops pick the new columns up automatically — no JS change needed for
the projects PM/company labels.

### Step 2 — Add the correct helper HTML

- `html_body_account_edit_v2.php`: replace the bare `parent_account_id` input with the
  projects markup — visible `parent_account_name` input (`data-autocomplete='accountname'`,
  `data-autocomplete-target='parent_account_id'`), clear button, hidden `parent_account_id`.
  `parent_account_id` is already in `$staticFields` in `account_save.php`, so save works as-is.
  (Nothing stops a user selecting the account as its own parent — accepted for now; an
  `exclude_id` param on `fn/autocomplete.php` is a possible follow-up.)
- Add the projects-style clear-button markup (position-relative wrapper + `btn-link` +
  `bi bi-x-circle`) to:
  - `html_body_account_edit_v2.php`: account manager, company, parent account
  - `html_body_stocklistedit_v2.php`: stocklist manager, company, account

### Step 3 — Update the style / shared JS

- Move `setupAutoclearButton()` from `project_edit_v2.js:7865` into `main.js` (alongside
  `bindAutocompleteInputs`), fix the initial-state line so the button only shows when the
  field has a value, and re-apply initial state after load-populate completes.
- Wire `setupAutoclearButton(...)` calls in `account_edit_v2.js` and `stocklist_edit.js` for
  their pairs; delete the duplicated function/calls from `project_edit_v2.js` in favour of the
  shared one.
- Ensure visual consistency across the three editors (input padding for the clear button,
  `pe-4` class as per projects).

## Testing checklist

Per field (all 10): assign via autocomplete → save → reload: label **and** hidden ID populated;
clear button clears both → save → reload: both empty, DB column NULL; journal/history records
the ID change. Regression: project/account/stocklist list pages still render Project Manager
and Contractor columns (`pm_username` / `contractor_name` untouched); account header
("Details From: …") still renders; wayleave editor unaffected; dynamic (EAV) fields still save
and reload; migration applies cleanly via `php db/migrate.php --apply` on a fresh baseline
restore.

## Risks

- `CREATE OR REPLACE VIEW` only permits appending columns; if a live view has drifted from the
  baseline the migration errors — resolve the drift first rather than `DROP ... CASCADE`
  (dependent views/consumers).