Forms Lookup Fields Developer Guide

Form lookup fields — Developer Guide

How a form field searches FreeITSM's own records, why the scope is the entire feature, and the two bugs that shipping it exposed.

Shipped as #962 (the field) and #963 (the scoping fix + the drift test).

The user-facing page is Forms. The sibling guide is Form sections & conditional logic, whose §2 (field identity) and §3b (one type with a mode) are load-bearing here — read those first if you have not.

---

1. 📁 The files involved

Colour key: 🗄️ schema · ⚙️ engine · 🔌 API · 🖥️ UI · 🎨 CSS · 🌍 i18n · 🧪 tests · 📄 docs

🎨	File	What it does
⚙️	includes/services/forms.php	the source of truth. LOOKUP_SOURCES (the registry), lookupSourceOf(), lookupPortalAllowed(), lookupTenantClause(), lookupSearch(), lookupValueAllowed(); lookup in FIELD_TYPES + ANSWERABLE_TYPES; the submitForm() guard
⚙️	includes/form_logic.php	unwraps the stored {"id":…,"label":…} to its label, so a condition compares against what the person saw
⚙️	assets/js/form-logic.js	the browser mirror — FormLogic.lookupLabel/lookupSource/attachLookups, lookup in TYPES, the normaliseValue() unwrap
🔌	api/forms/lookup_search.php	new. The search endpoint. Resolves the source from the field, and the company scope from who is asking
🔌	api/forms/ai_generate.php	lookup in $allowedTypes, the lookup_source sanitiser, and the system prompt that documents the type
🖥️	forms/edit/index.php	the builder — the palette button, lookupSourceOf()/setLookupSource()/setLookupPortal(), the settings panel, the preview
🖥️	forms/fill.php	the analyst renderer + its .lookup-* styles
🖥️	self-service/catalogue.php	the portal renderer + the same styles
🖥️	forms/submissions.php	shows the label, not the id
🖥️	forms/help.php	the analyst-facing explanation, including the portal warning
🌍	lang/en/forms.php + lang/pt-BR/forms.php	fieldtypes.lookup, field.lookup_*, fill.lookup_*, help.lookup_* — same commit, 480 keys each
🧪	tests/forms-lookup/run.php	27 assertions — sources, scoping, tampering, portal gates, and the type-list drift check
📄	CHANGELOG.local.md, this wiki	#962–#963

🔑 There is no schema change at all. A lookup is a form_fields row of field_type = 'lookup' with its source in the existing config JSON — so neither database/freeitsm.sql nor includes/db_verify_schema.php needed touching, and no Database Verification is required to use the feature. Adding a field type is a code change, not a migration.

---

2. 🔑 One type with a source, not one type per source

field_type = 'lookup', with config.lookup_source naming what it searches.

This is §3b's argument again, and it is worth restating because it will come up for the next field type too: field_type cannot be changed once a field exists. There is no type control in the builder, only a read-only badge, because changing a type would reinterpret every answer already stored against that field.

So lookup_asset / lookup_cmdb / lookup_user as three separate types would force an irreversible guess at add-time. Someone who picks "equipment" and realises next week they meant "infrastructure" would have to delete the field — which retires it and strands its answers under a separate column. A source is a setting; the field keeps its id.

---

3. ⚙️ The source registry

LOOKUP_SOURCES is a constant map, and it is also the whitelist:

'asset' => [
    'table'       => 'assets',
    'id_col'      => 'id',
    'label_col'   => 'hostname',
    'search_cols' => ['hostname', 'asset_tag', 'service_tag'],
    'tenant_col'  => 'tenant_id',
    'portal_safe' => true,
],

⚠️ Those column names are interpolated into SQL. They can only ever come from this constant — never from the request, and never from the field's config. The registry is what makes that safe. If you add a source, the column names you write are trusted, so verify them against the live schema first.

That is not a hypothetical: four of the five column sets assumed while building this were wrong. There is no assets.asset_name and no assets.serial_number; there is no software table at all. A wrong column name does not throw anywhere a user can see it — lookupSearch() logs and returns [], which renders as "no matches". A source can therefore be completely broken and look merely empty. §7's first test exists solely to catch that.

Sources deliberately absent

Source	Why not
contracts	the table has no company column at all, so a lookup could not be scoped and a company-restricted analyst would see every client's contract titles. Needs contracts.tenant_id first
software	software_licences has no name of its own — the product name lives on software_inventory_apps, so it needs a join and a decision about which of the two a person is actually picking

Both were in the original pitch. A half-scoped source is worse than no source.

---

4. 🔑 The answer stores BOTH halves

{"id": 11, "label": "LT-001"}

The label is what the answer meant at the time. The id is what it points at.

Keep only the id and renaming an asset silently rewrites history — a submission from March starts claiming something it never said. Keep only the label and the answer is a string again, which is the problem the field exists to solve.

This is the same split as an issue tracker's stable internal id versus its display key (see External Issue Trackers — Developer Guide), and for the same reason.

Everything that displays an answer uses the label: forms/submissions.php, the CSV export, and both condition evaluators. FormLogic.lookupLabel() and the PHP unwrap in form_logic.php are the two halves of that, and they must agree — a rule written as "Which machine is broken is LT-001" compares against the label, because that is what the person composing the rule can see.

---

5. ⚠️ The scope is the whole feature

A lookup is a search box over records we already hold, offered on a page customers type into. Getting the scope wrong turns a convenience into a disclosure.

lookupSearch() and lookupValueAllowed() both take ?array $tenantIds, and it is passed in, never derived, because the two callers know different things:

Caller	Scope
analyst	getAccessibleTenantIds($conn, $analystId)
portal user	[their own company], and only that

Three values, three meanings — get these confused and the bug is silent:

$tenantIds	Means
null	unrestricted. Only ever correct for an analyst who can see every company
[] (empty)	no companies — returns nothing. An analyst with access to nothing must not see everything
[1, 4]	those companies, plus NULL rows if one of them is Default (see below)

🔑 There is deliberately no 'all' string. A typo in a string would silently mean everything; a typo in a variable name is a PHP error.

⚠️ tenant_id IS NULL means "the Default company's", not "shared"

For assets, cmdb_objects and users — as for tickets and changes — a NULL tenant means unassigned, treat as the Default company's. Knowledge means the opposite by the same NULL (see Knowledge visibility), which is exactly why this cannot be eyeballed.

lookupTenantClause() is the single implementation, called by both the search and the submit guard:

$clause = "$col IN ($in)";
if (in_array(getDefaultTenantId($conn), $tenantIds, true)) {
    $clause = "($clause OR $col IS NULL)";
}

On a single-company install every row is NULL, so that second line is not an edge case — it is what makes the feature work at all.

---

6. 🔒 The three gates

Gate 1 — the search endpoint

api/forms/lookup_search.php takes a field id, not a source. The source is then read from that field. A request cannot name the table it wants to search; it can only point at a field someone deliberately created.

Gate 2 — the portal, twice over

lookupPortalAllowed() requires both:

1. the source is portal_safe — the staff directory never is, whatever a form builder ticks;
2. the field has config.portal_lookup — off by default, so nothing is exposed by accident.

Both are re-checked in the endpoint. Markup appearing is not permission, and the builder's tickbox is a convenience, not the enforcement. The AI generator may set the source but is never allowed to set portal_lookup: exposing records to customers is the form owner's decision.

Gate 3 — the submit-time anti-tamper check

The posted answer is an id, and nothing stops a crafted request naming another company's asset. submitForm() therefore re-checks it with lookupValueAllowed() against the submitter's own scope.

🔑 This is the generalisation of a rule the module already had — "a choice field must be answered with one of its own choices" — to a list built at answer time rather than stored on the field.

Without it, the id would be whatever the client posted, and the crafted answer would appear resolved and correctly labelled on a submission an analyst reads and reasonably believes.

---

7. ⚠️ The bug this shipped with, and how it was found

#963 was not found by reading the code. It was found because tests/forms-lookup skipped a section with "no assets belong to a company" — every one of the 566 assets on the dev database has tenant_id NULL. That is not an empty database, it is the normal single-company shape.

Both functions knew the NULL-is-Default rule. But each reached getDefaultTenantId() behind a function_exists() guard, because includes/functions.php does not pull in tenancy.php.

That guard is what made it a bug rather than a missing feature:

• api/forms/lookup_search.php does require tenancy.php, so the dropdown offered NULL-tenant assets;
• submitForm() runs wherever its caller put it, and with tenancy absent the guard evaluated false, dropped the NULL clause, and refused the record the list had just offered.

A customer picks their laptop from the dropdown, and the form tells them it is not a valid choice.

Two fixes, and the second matters more than the first:

1. forms.php requires tenancy.php outright. It is a hard dependency — two halves of one scoping rule must not resolve differently depending on which caller included what. getDefaultTenantId() self-guards on an unverified database and always returns an int, so nothing is lost by calling it directly.
2. The clause became one private helper both halves call. It was duplicated, which is precisely how they were able to drift.

🔑 The general lesson: a function_exists() guard around a security rule degrades silently, and the direction it degrades in is not always "safer". Here it degraded toward refusing legitimate answers on the commonest install shape.

---

8. 🖥️ The control itself

FormLogic.attachLookups(root, searchUrl) is shared by forms/fill.php and self-service/catalogue.php, so the two renderers behave identically without being merged (§8 of the sibling guide explains why they are not merged — different CSS, real visual risk).

🔑 The hidden answer input is written only when someone picks from the list. Typing alone never sets a value. That is what makes the field mean "a record" rather than "a string that resembles one" — and it is why a required lookup left half-typed correctly fails validation.

The results panel is position: absolute so it overlays the fields below rather than shoving the form around as the person types.

---

9. 🧪 Verifying a change here

php tests/forms-lookup/run.php     # 27 assertions
php tests/forms-logic/run.php      # 85 — the sibling suite, must stay green

⚠️ Every negative assertion is paired with a positive control. "The other company saw none of them" proves nothing on its own — it is equally true of a search broken by a bad column name. The suite always also asserts that the owning company does see them.

The five things it checks:

1. every source's real query runs — the wrong-column-name trap from §3;
2. scoping, both directions, on whichever data shape the database has;
3. the anti-tamper guard — genuine accepted, cross-company refused, nonexistent refused, id 0 refused;
4. both portal gates refusing independently, each tested with the other satisfied so neither can be carrying the other;
5. field-type drift.

On that last one

A field type must be added in five places: the service whitelist, the AI generator's whitelist, the AI generator's prompt, the shared JS evaluator, and the builder's known-types list.

One of those five is prose. When it falls behind, nothing fails — the generator just quietly stops offering a type, or keeps offering one that no longer exists.

It had already happened. The prompt's CRITICAL FORMAT RULES said "field_type must be exactly one of…" and listed eight types, silently omitting datetime and section months after both shipped. The fix was to make that line point at the list above it instead of keeping a second copy, and the test now compares all five lists against FIELD_TYPES.

🔑 If you add a field type, run tests/forms-lookup/run.php — it will tell you which of the five you forgot.

---

10. ⚠️ Upgrading

Nothing to do. No schema change, no Database Verification, no configuration. Existing forms are unaffected; the type simply appears in the builder's palette.

---

11. ⬜ Not built yet

• contracts and software sources — blocked on the schema gaps in §3, not on the mechanism.
• Multi-select lookups. One record per field today.
• Showing more than a label in the results — an asset's model or owner would help someone choose between two similarly-named machines.
• Acting on the linked record. The answer knows which asset it is, so a form submission could in principle attach itself to that asset's history. Today it is a value on a submission, nothing more.