Gotchas and Tips (from Training Sessions)¶

Critical gotchas and non-obvious tips extracted from training sessions with Mark Wright and Peter Steele. These are the things most likely to waste hours if not known in advance.

Workflow variables store credentials in plaintext — exporting that category exports the passwords (added 19/08/2026)¶

Versaa workflow variables carry <IsEncrypted>false</IsEncrypted>, so the SQL logins, SMTP password and full connection strings held in the ENV_* variables are literal text in the exported .variable file — an ENV_*_ConnectionString exports with its service account and password spelled out in the <Value> element, and an ENV_*_Password exports as the bare password.

Every pull.ps1 run that includes workflow_variables therefore writes live credentials into extracted/ and Versaa_Exports_md/, both of which are git-tracked. This went unnoticed for months because the .gitignore was correct and history had been scrubbed — the leak was in exported content, not in a config file anyone had thought to ignore.

Now handled automatically. versaa-rag/redaction.py blanks sensitive values at extraction (watch_and_process._extract_package) and again at markdown conversion, keeping server and database names (documented anyway) and $VAR$ indirections, and dropping only credentials. versaa-rag/scrub_variable_secrets.py re-runs it over the existing trees and audits them (--apply to write).

Watch out for: - The redaction is one-way and local. The real values still live in Management Studio — that is where you read or change them, and where a rotation has to be applied, per environment. - Anything already pushed to a remote is exposed permanently. Rewriting history does not fix it; rotating the credential is the only fix. - The same trap applies to any new export category. When you add one, check what the platform actually puts in the file before committing it.

A form file can change without anyone editing the form — the record `id` swaps (added 14/08/2026)¶

What it looks like. A refresh produces a huge diff on a .form file (e.g. Damp+and+Mould+Inspection_0.form, −13,563/+5,470 lines) with no corresponding work in Dev, and the catalog reports the page as unchanged. Eight sibling files (Electrical+Edit+*_0.form) changed by exactly two lines.
The two lines are the id attribute. <Form id="576251bb-36df-eb11-8d6a-000d3ad48bfb" …> → <Form id="4723aa86-ee79-ec11-8df1-000d3ad48fe9" …> — same name, same version, different record. The rest of the file was byte-identical, so nothing was edited; the export simply delivered a different form record under the same filename. Where the two records genuinely differ (D&M Inspection), the whole file appears rewritten.
Why the filename can't distinguish them. extracted/forms/Forms/ is keyed on name_version, and these forms all sit at version="0". Two records with the same display name and the same version therefore collide on one path — the last export written wins. Same failure mode as the cross-environment collisions already documented, but it also bites within a refresh cycle.
The id suffix is the cheap environment tell. The final GUID node is the MAC of the server that created the record, so a lineage is recognisable at a glance: Dev records here end 000d3ad48fe9 (e.g. Void Post Inspection Dev bfc2b5bc-f079-ec11-8df1-000d3ad48fe9, Fire Door Check Dev 968bd979-f9a5-ec11-8df3-000d3ad48fe9). In the 14/08 refresh all nine swapped ids moved to that suffix, and D&M returned to the exact id held by the pre-drift 16/04 Dev export — i.e. a verified-Dev export overwriting residue left by the 30/06 Live-mislabelled pull.
Watch out for: this is not proof the repo is now clean. The 29/07 corrective Dev pull rewrote 37 form files but left these nine untouched, which a full Dev forms export should not have done — so at least one refresh has delivered a partial or mixed form set. Before trusting any version="0" form page, check the id= on line 1 against the record that is Active in MS for that environment. Diffing git log -- <file> and reading only the first line of each revision is the fastest way to see when a lineage flipped.

Nightly "Dev pull" silently exported Test/Live for 4 weeks (added 29/07/2026)¶

Root cause — a tool wrote back the config field it also read for targeting. export_from_management_studio.save_cookies() persisted the session by doing json.dump(cfg) — the entire config dict, not just cookies. Every diagnostic tool sets cfg["base_url"] = ms.base_url_for_env(args.env) in memory before building a session, so a single ms_import.py --env live / ms_workflow_errors.py --env test / compare_asbestos_test_live.py run permanently repointed versaa-rag/config/ms_export_config.json. The export script had no --env of its own and just followed that field, so the next run_dev_pull.py — including the nightly scheduled task — exported that environment and committed it as a Dev refresh.
It was invisible because nothing named the environment. The log line was Fetching items from <url> buried mid-output; the status file, commit message and catalog said nothing. Audit trail (grep "Fetching items from" versaa-rag/logs/dev_pull_*.log): Dev until 29/06 → Live 30/06, 01/07, 07/07 → Test for most runs 07/07–27/07 → Live again 27/07 20:30.
Consequences. extracted/, Versaa_Exports_md/ and wiki/catalog/ became an environment mixture; the catalog's current_version is "highest version seen from any env" (e.g. Void Survey 57 = Test while Dev was 48). A 1stTouch_PrtyLive connection string and Live DB password were committed via ENV_1T_DB_ConnectionString / ENV_1TSS_DB_Password. Never trust a KB/catalog version number for a specific environment — query MS per-env and check the record id.
Fix (29/07/2026). save_cookies() re-reads the on-disk config and updates cookies only, stored per-env under cookies_by_env (a Test/Live session can no longer be replayed against Dev); base_url on disk is never rewritten. export_from_management_studio.py gained --env {dev,test,live} (default dev, authoritative — it always wins over the config), an --i-understand-live guard, a loud ENVIRONMENT: <ENV> -> <url> banner and a warning when the env isn't Dev. run_dev_pull.py gained --env and passes it explicitly to the export step; scheduled_dev_pull.ps1 passes --env dev and records the env actually used in logs/last_run_status.json.
Pulls are now ON-DEMAND. The nightly task was disabled in the same pass; run .\versaa-rag\pull.ps1 (-Only forms,workflows,reference_data to retry just the 504-prone categories). logs/last_run_status.json now records the environment actually hit, read back from the export's own banner rather than assumed.
Watch out for: the same shape anywhere a script persists a config it also uses for targeting — the write silently outlives the run that made it. ms_workflow_info.py and ms_submit_form.py still have no --env and read cfg["base_url"]; they are safe only because that field is now pinned to Dev.

Non-ASCII characters break `.ps1` scripts (added 29/07/2026)¶

Windows PowerShell 5.1 reads a BOM-less UTF-8 .ps1 as CP1252. An em-dash (—, UTF-8 E2 80 94) decodes to â€" — and that final 0x94 is CP1252's right double quotation mark. Inside a Write-Host "..." string it silently closes the string, producing baffling errors far from the real cause: Unexpected token 'retries' in expression or statement / Missing closing ')'. Comment blocks are not safe either, and the file will look perfectly fine in VS Code.
Rule: keep .ps1 files pure ASCII — - not —, -> not →, ! not ⚠, straight quotes only. (Saving with a UTF-8 BOM also works, but the repo already has a BOM-related trap with Set-Content and the manifest parser, so ASCII is the safer habit.) Quick check: LC_ALL=C grep -n '[^ -~\t]' script.ps1 should return nothing. Bit versaa-rag/pull.ps1 on 29/07/2026.

Nightly Dev pull — transient gateway 504s (added 18/06/2026)¶

DownloadExportPackage 504s are transient Azure-gateway assembly timeouts, not "category too big." The Microsoft-Azure-Application-Gateway returns HTTP 504 when the Versaa backend takes too long to assemble a package server-side. PrepareExportPackage (cheap) always returns 200; only the download (assembly + stream) times out. Which categories get hit varies night-to-night with server load (e.g. 11/06 missed reference_data + workflows + document_templates; 10/06 20:37 missed forms + reference_data + workflows; 10/06 08:56 missed only forms; 18/06 Forms 504'd once then recovered). The same categories succeed on other nights → it is load-dependent, not deterministic.
A longer client timeout does NOT help. Our requests GET already waits 600s, but the gateway (not our client) enforces the limit and kills the request first. The cure is waiting longer between retries so a load spike can clear — implemented in versaa-rag/export_from_management_studio.py as: exponential backoff (RETRY_BASE_DELAY=30s × 2^(n-1), capped 180s, 4 attempts), a short inter-category pause (INTER_CATEGORY_PAUSE=5s), and a deferred second pass that re-tries only the failed categories after a DEFERRED_PASS_COOLDOWN=60s cooldown — by which point the server has finished assembling every other package and its load is lowest. Re-preparing before each download attempt is essential (a 504 discards the server's prepared package, so a bare download retry 404s). Tuning constants live at the top of that file.
Exit-code contract unchanged: 0 = all ok, 2 = partial (some saved — run_dev_pull.py still processes/commits what worked), 1 = nothing saved. The new waits only fire on failure, so clean runs are unchanged in duration.

Workflow / Management Studio (added 11/06/2026, ticket 21617)¶

AppendItemGroupsFromSubGroups null-crashes on an absent top-level group. A Merge … step (FirstTouch.Workflow.Steps.Toolkit.AppendItemGroupsFromSubGroups) whose sourceGroupPath is a top-level group that doesn't exist in the form throws NullReferenceException in GroupCursor and kills the whole workflow instance. A path with an existing parent (Room_Rooms/GenBP_RoomMaterials) is graceful even if the sub-group is missing; a bare absent group (External_Materials) is fatal. The Void Survey form has only schedule groups, no materials groups, yet Void Pre Inspection QL Updates carried vestigial materials-merge steps → crashed before Send Inspection Result / the QL contact / Process Order in QL. Fix = re-point the transition to bypass the dead steps and delete them. Watch out: a crash here silently blocks every downstream step, so a "missing contact/order" can actually be an earlier step dying.
Diagnose a completed run via Search Instances, not Task.Task. Completed tasks are cleared from Versaa_PrtyDev.Task.Task (it only keeps active/dispatched). MS → Workflows → Search Instances → Instance Details shows the full completed Task XML (Initial data), the Errored step + stack, the per-step History, plus Failed Creation Requests (ImportContactCreationRequest failures) and Ignored Events. This is the only reliable view of what a completed workflow actually did.
Retry re-runs the instance's OWN pinned workflow version, NOT the active one. A workflow instance is bound to the version it was created on (Instance Details shows Workflow (v6)). Retrying an errored instance (MS UI, or ms_workflow_actions.py retry) re-executes the failed step on that same version — so activating a fixed new version does not heal old errored instances, and a retry is useless for validating a new version. Proven 24/06/2026: after activating the materials-bypass v7, retrying a v6 instance crashed again at Merge Room Materials (runs=2). To validate a new workflow version you must complete a fresh task so a new instance spawns on the active version.
Versioning on import differs by artefact type:
Forms — MS auto-increments (= max(existing)+1), ignoring the manifest version (e.g. Void+Survey_32.form → MS v33).
Workflows — MS reads the target version from the .workflow FILENAME SUFFIX, not the manifest. The internal file MUST be <Name>_<nextFreeVersion>.workflow. A suffix that collides with an existing version 500s at the apply step (ImportCurrentPackage → "An unexpected error occurred") — this is the real mechanism behind the "re-import existing version" gotcha. Proof (24/06/2026): void_pre_insp_ql_updates_v7 with internal file ..._4.workflow (manifest version=7) 500'd because Dev already had v4; renaming the file to ..._7.workflow (Dev was on v6) imported cleanly as v7. Content/[Content_Types]/_rels were identical — only the suffix mattered.
Check current versions read-only before building a workflow package: export_from_management_studio.fetch_items_for_category(session,"Workflows",base_url) (endpoint api/RefData/GetWorkflowsWithVersions) → each item's Versions[] lists Version/IsActive/IsLatest.
New imports are not auto-activated — Activate the new version in MS deliberately.
Keep package manifest comments concise (~40-60 chars), for forms AND workflows — the column truncates ~100 (SQL 8152) and long comments clutter the version history.
A conditional transition needs a gateway <Editor><Layout> or the designer renders a mess. When you turn a single-target <Transition> into a conditional (add an <XPathConditionalTarget> beside the default <Target>), you MUST also add <Editor><Layout l="…" t="…" w="60" h="60" /></Editor> inside that <Transition> to position the new decision diamond — every existing conditional in a workflow has one. Omit it and MS auto-places the diamond off-page and drags the connectors across the diagram (looks broken to colleagues even though behaviour is correct). Pattern for a clean gate (skip a step when a field is empty): default <Target label="…"> → the step; <XPathConditionalTarget … expression="string-length(//Task[not(ancestor::Task)]/DataItems/DataItem[@name='X'])" value="0" sourceStep="."> → the skip target. (Used to gate Raise Property Change Contact on QL_Client No so a clientless void completes instead of erroring — 25/06/2026.)
Process Order in QL / CompleteOrderWorkflowStep "unprocessed or errored updates in the order details interface table" is usually a TRANSIENT, not a failure. After Send Inspection Result pushes the SORs to QL's order-details interface table, Process Order in QL may try to complete the order before QL's interface job has processed them → it parks as PendingRetry / AwaitingRetry (TransitoryError) and auto-retries. It clears itself once the interface processes the SORs (observed: ~11 min in Test, Complete at runs=4). Don't treat the initial PendingRetry as a bug — re-check the instance later; the SORs land on the order (Single Order Entry → order no) and the workflow Completes. Only if the interface rows are genuinely errored (not just unprocessed) is intervention needed (see Maint_QL Clear Interface Error). A manual RetryCurrentStep on an already-Complete instance returns HTTP 500 "Cannot retry … in status Complete" — harmless.
A QL contact always needs a client_no; a void has none → ImportContactCreationRequest errors "Unable to create contact as there is no client no provided". For a void/property contact, set the provider's <prefix>_Use Property Void Client No = Yes (e.g. QLMV_Use Property Void Client No) and make sure the workflow step receives the propertyId — QL then resolves the property's void client (a real number, e.g. 3000384, not 0). Verified end-to-end Void Survey 23/06/2026. Full detail in sql/cheat-sheet.md.
A form can behave differently per environment via the Active version alone. Test showed different Create Jobs behaviour than Dev with nothing imported — Dev was on a working v4, Test on an old v2. Before assuming a regression, compare the Active version (the one showing "Deactivate") in Dev vs Test in MS → Forms.

Form Building Gotchas¶

Condition values default to "true" — always verify¶

When adding a condition in the formula builder, the system auto-populates the comparison value with "true". This is almost never correct. If your list contains "Yes", "No", "Other", etc., the default "true" will not match anything and the condition will always evaluate as false.

Always click into the value field and manually confirm it matches an actual value from your list. This is the single most common cause of "my formula isn't working" debugging sessions.

Cloning does not copy formulas¶

When you clone a form item, only the base properties are cloned — formulas are not copied. You must manually recreate any ReadOnly, Visible, Value, or Caption formulas after cloning.

Copying vs cloning a form item¶

Copy = duplicates the reference to the same underlying FormItem — changes to one affect both.
Clone = creates a new independent FormItem instance.

Use Clone when you want a truly independent field. Use Copy only when you want the same data item shown in multiple places.

Multi-line scripts cause errors¶

Versaa formula fields require single-line JavaScript expressions. Multi-line code (e.g., from a GPT response with const x = ... declarations) will fail silently or throw an error.

When using GPT to generate formula code, always prompt: "as a one-liner, minimal abstraction, no const declarations". The output should be a single expression, not a multi-line script.

If a previously working multi-line formula was saved to the database via the XML trick, editing it in the front-end designer will attempt to convert it back to single-line — potentially breaking it. If this happens, go back into the database to restore the single-line-compatible version.

Don't click before the form fully loads¶

When opening a form in Management Studio, the middle panel is blank initially. Wait for it to fully load before clicking. If you click too early, the editor won't let you interact with the form properly until you refresh.

Spaces in field names break Versaa¶

Form item names (not captions) must use underscores, never spaces. Spaces cause JavaScript errors inside formulas that reference the field.

Case sensitivity traps¶

Front end (forms, formulas): case-sensitive — Other_Component and other_component are different.
Workflows: case-sensitive.
Database (SQL): NOT case-sensitive — this difference causes subtle bugs when debugging cross-layer issues.

If it's working, don't tinker¶

Form elements are often interconnected via formulas. One small change can cascade and break other elements. Follow the light touch principle: only change what's strictly necessary, and test after every change.

QL Integration Gotchas¶

There is no "Dev" environment in QL — Dev maps to Acceptance¶

Versaa Dev (PrtyDev) connects to QL Acceptance (a_qlfdat), not a QL "Dev" environment. QL only has: Acceptance, Test, User Training, Config, and Live. When Peter says "Dev" in QL context, he means Acceptance.

QL database name mapping: | Versaa environment | QL database | |---|---| | Dev (PrtyDev) | a_qlfdat (Acceptance) — confirm via workflow variable | | Test (PrtyTest) | t_qlfdat | | Live (PrtyLive) | qlfdat (no prefix) |

Dev Versaa could also point to u_qlfdat (User Training) depending on config. Always check the QL DB connection string in Versaa workflow variables (Management Studio → Workflow Variables → QL → DB connection) to confirm which QL database Versaa Dev is talking to.

Changes to QL CRM Classifications must be made in all 3 environments¶

QL CRM Classification config is not automatically synced between environments. If you add/change a rule in Acceptance, you must manually replicate it to Test and Live. Forgetting one environment is a common cause of "works in dev but not live" (or vice versa) issues.

CRM Classification row exists but has no action code — silent failure¶

The HGM CRM Classifications screen can have a row with the correct class levels but no action code assigned to it. When a contact comes in matching that row, nothing happens — no action fires, no error is thrown. This is completely silent.

Ticket 14128 (May 2026) example: RENTS / BALANCE / STATEMENT / SELFSERVE row existed in all environments but had no action code. Contacts from the MyLHP portal were being created correctly but RT0016 never fired. Fix: add the action code to the existing row in each QL environment.

Diagnostic approach: run the HGMCNTCT query to confirm contacts are arriving, then check HGM CRM Classifications to verify the action code field is not blank.

QL field name lookup: Ctrl+Shift+F1¶

In QL, click on any field in the UI and press Ctrl+Shift+F1 to see the underlying database column name. This is the fastest way to map a QL screen label to its SQL column — useful when building queries or mapping form items to QL data.

Note: this doesn't work for every field, but works for most standard QL Housing screens.

QL asterisk convention in field labels¶

When Peter's team renamed a QL field label (changing the display name but not the database column), they prefix the new label with an asterisk *. If you see *Income Officer on a QL screen, the display label has been changed but the underlying DB field name is something different — use Ctrl+Shift+F1 to find the actual column name.

QL property responsible persons¶

Each property in QL has several "responsible persons" that actions can be routed to. These are set in QL Property Maintenance (stored in HGMPRT1/2/3/4):

QL label	Notes
Housing Officer	`HSG_OFF`
Housing Manager	`HSG_MGR`
Income Officer	`OTH_PER1`
Lettings Officer	`OTH_PER2` (approx)
Repairs Team Leader	`RT_REP_CD`
Surveyor	—
ASP	—

Each label links to a "responsible person" record, which links to a QL user ID. Action codes (like RT0016) are configured to route to one of these roles — e.g., RT0016 routes the rent statement request to the Income Officer for that property.

Accessing QL non-live environments via Citrix¶

Do not use Remote Desktop to access QL environments. Use Citrix: - Citrix → Devices → scroll to Apps → QL non-live environments - This gives a popup to select: Acceptance, Test, User Training, or Config - Never use Config — no reason to go there - For Live QL: use the standard QL Live app (separate Citrix item)

Formula Builder Tips¶

Drag form items instead of typing¶

In the formula builder, drag form items from the left panel into the expression field rather than typing their names. This avoids typos and ensures you're referencing the exact internal name, not the caption.

Insert Value vs Insert Name¶

When building a formula condition that checks a field's value: - Use Insert Value to reference the field's current value (what you want for most conditions). - Use Insert Name inserts the field name as a string literal (rarely what you want).

Integer vs String fields look identical — use captions¶

Integer and String fields are visually identical in the form UI. A practical trick: give them a caption that includes the type, e.g., "Age [int]" or "Name [txt]". This makes it instantly obvious what type each field is during development.

Calculated fields should be read-only¶

If a field is populated by a Value formula, mark it as ReadOnly. Otherwise users can overwrite the calculated value, which is almost never intended.

SQL / Management Studio Tips¶

Comment out FormDefinition when querying vw_Forms¶

The FormDefinition column in the VW_Forms view contains the entire form XML. This column is enormous and makes queries extremely slow. Always comment it out:

-- Fast: excludes the large XML column
SELECT FormId, FormName, FormVersion, IsActive
-- , FormDefinition  -- exclude this column — it's huge
FROM [1stTouch_PrtyDev].[dbo].[VW_Forms]
WHERE FormName LIKE '%Component%'

Colon-separated reference data lists¶

Reference data lists can be stored as a single name/value pair with colon-separated values (e.g., "Option1:Option2:Option3") to reduce the number of rows. This is a space/performance optimisation. When reading this in SQL, use STRING_SPLIT or pattern matching.

Deployment Gotchas¶

NEVER hand-author `.ftpackage` metadata — clone a package that imported (added 29/07/2026, ticket 22387)¶

Take a package that has actually imported for that form, copy Manifest/manifest.xml, _rels/.rels and [Content_Types].xml verbatim — preserving each entry's compression type and byte-order mark — and replace only the artefact bytes. Change nothing else except the manifest comment.

Reference implementation: versaa-rag/build_void_post_insp_package.py, which clones all_versaa_forms/VPI-v3.ftpackage.

Why. Hand-authored metadata for a Void Post Inspection package failed import after import with only An unexpected error occurred, please try again later. There is no useful detail anywhere: the response body is just {StatusCode, Message, Warning}, and /api/Logging/GetSearchInformation 504s. Testing one theory at a time (BOM, compression, version, description) cost many round-trips and left four corrupt draft versions on the Dev record. A byte-diff against one known-good package settled it in minutes.

Format facts from that diff — these correct earlier assumptions:

	Value
`Manifest/manifest.xml`	UTF-8 BOM, DEFLATED
`_rels/.rels`	UTF-8 BOM, STORED
`[Content_Types].xml`	UTF-8 BOM, STORED
`.form`	no BOM, DEFLATED
`version=`	`"3"` imported fine even though v3–v8 already existed — it does not have to be the next free number
Relationship `Id`s	come from the source package; don't invent them

The June asbestos_site_audit_v77 package has no BOM and everything deflated, and still imported at the time — so the format varies, and no single hand-written recipe is safe. That is exactly why cloning beats a shared recipe.

Corollaries - Keep a known-good template package per form, and ask for one before hand-building. - A package entry filename matching a real file in extracted/forms/Forms/ gets extracted over it by the watcher on all_versaa_forms/. Back the baseline up first. - When an import fails, run the control: repackage the unmodified source through the same builder. If that fails too, the payload is irrelevant and the fault is packaging.

The Forms Repository does not validate your XML (added 29/07/2026)¶

It throws An unexpected exception has occurred in the Forms Repository service: Object reference not set to an instance of an object, naming neither the element nor the property. Well-formed XML is not enough — generated content must match the shapes of real elements/items in the same form:

Property sets per element type. ImageFormElement takes resolution, thumbnailSize, selectPhoto, captureSingleImage, allowAnnotation — and has no caption/bold/colour/font. Decimal items need maxDecimalPlaces/minDecimalPlaces/ restrictDecimalPlaces; String items need minLength; LabelElement needs captionWidth/style.
Container child shapes. <PreventNavigationBack> wraps a <Value>, it is not raw text; every <Branch> carries a trailing <Branches /> after </Blocks>.
Ordering. The top-level <FormItems> block is in strict case-sensitive alphabetical order. Appending new items at the end breaks an invariant that XML validation cannot see.

versaa-rag/transform_void_post_insp_lettable_standard.py has a validate_against_base() that refuses to write on any mismatch, and a --stage control|items|branch|button|full switch for bisecting a failing package.

Version number conflicts between environments¶

Each time you save a form, the version number increments. If two developers work simultaneously in different environments, you can end up with: - Dev at version 65 - Test at version 70 (because a colleague saved more there)

Importing the Dev package to Test at this point will downgrade the Test version, potentially losing work. Before any cross-environment import, check version numbers in both environments and confirm with the team.

Activating locks the version comment¶

Once a form is activated (moved from Draft to Active), the version comment is locked and cannot be changed. Make sure your comment is accurate before activating. If you made an error, you must deactivate, correct, and re-activate (not recommended in Live).

Draft forms are invisible on the mobile app¶

A form in Draft status is not visible to field staff on the Aareon Mobile app. It must be Activated to appear. When testing, always activate to a non-Live environment first.

Always check version comments match across environments¶

Before importing to Test or Live, compare the version comments between the package you're importing and what's already there. Mismatches indicate something different was deployed.

Word Connector / Document Template Gotchas¶

Date fields return JS Date objects — `String()` gives the day of week¶

Date/DateTime FormItems and GetSelectedItemValue on date group columns return a JavaScript Date object. Calling String(dateObj) or dateObj.toString() produces something like "Mon Dec 14 2037 00:00:00 GMT+0100 (BST)". Calling .split(" ")[0] on that gives "Mon" — the day of week, not the date.

The correct pattern:

$formItem.InstallDate?$formItem.InstallDate.getDate()+"/"+($formItem.InstallDate.getMonth()+1)+"/"+$formItem.InstallDate.getFullYear():""

getDate() = day number (1–31)
getMonth()+1 = month number (months are zero-based!)
getFullYear() = 4-digit year

This is the same technique the PercentthroughLife formula uses ($formItem.InstallDate.getFullYear()) — look there as a working reference.

`availableForDocumentRendering=False` causes silent email failure¶

When a form field has availableForDocumentRendering=False and is used as a merge field in a Word Connector template, the Word Connector add-in will show "N invalid display items" in the Server Connection dialog. If you save the template to the server anyway, emails stop arriving with no error message. The failure is completely silent — no workflow errors, no logs.

Fix: set availableForDocumentRendering=True in the form XML for all fields used in the template, rebuild the package, re-import, then re-open in Word → Server Connection → confirm 0 invalid items → Save to Server again.

Changing a field binding requires re-doing Server Connection in Word¶

Patching word/document.xml to rename a field (changing the name portion of FieldName:GUID in descr attributes) is not sufficient. The server-side Word Connector binding cache also needs updating. To update it, open the patched .docx in Word → Server Connection → re-select the field → Save to Server. Without this step, the template renders the old field values or empty values.

Python-built .docx breaks the server-side renderer¶

Never build a Word Connector template using Python's python-docx library as the base. Python-origin documents contain structural differences (extra settings.xml entries, extra drawings, different XML namespaces) that cause the Versaa server-side renderer to fail. Always clone byte-for-byte from the known-working original using PowerShell ZipFile.

Formula Expression text has a ~178 char SQL column limit¶

The Expression field of a formula is stored in a SQL column that truncates at approximately 178 characters. Exceeding this causes import error 8152: String or binary data would be truncated. Keep formulas, especially date-formatting expressions, as short as possible.

Manifest `comment` field for forms has a short column limit (~100 chars)¶

When building an .ftpackage manually, the comment attribute on the <Form> element in manifest.xml is stored in a short SQL column. Long comments (>~100 characters) cause 8152: String or binary data would be truncated on import — the same error as the formula expression limit.

Rule: Keep form manifest comments brief, e.g. "v41: sendAllItems=True on Get Asbestos Data request". Workflow manifest comments appear to tolerate longer strings (different table/column). This only affects forms.

Recurred 10/06/2026 — Void Survey v31. A 180-char manifest comment failed import with 8152; rebuilding with an 85-char comment fixed it (form XML unchanged). build_void_survey_v31.py now hard-asserts len(comment) < 100. Build scripts should assert this, since the only signal is a generic 8152 at import time. (The form's own long Calculation expressions — 462/454 chars — import fine and are unrelated; the ~178 limit is a different, narrower column than these multiline calc expressions.)

PowerShell ftpackage build: unquoted XML attributes in manifest → "Could not read file as an Import Package"¶

When building a .ftpackage via PowerShell and the manifest string is defined in a PowerShell variable, shell quoting can silently strip the double quotes from the XML declaration, producing <?xml version=1.0 encoding=utf-8?> instead of <?xml version="1.0" encoding="utf-8"?>. The Versaa importer is strict and rejects this with "Error: Could not read file as an Import Package."

Root cause: Subagent-generated PowerShell scripts may mangle inner double quotes inside single-quoted strings when writing to disk.

Fix: Always build packages using a Python script (zipfile module) rather than PowerShell. Python string literals handle quoting unambiguously. See versaa-rag/build_void_survey_v12.py as a reference.

Watch out for: This error looks like a structural/ZIP problem but is actually an XML malformation in the manifest only. The form XML itself is fine. Verify by reading the manifest bytes from the ZIP directly and checking the first ~60 bytes.

Reference Data / Lists¶

`.list` files are gzipped XML — read them, they are the ground truth (added 18/08/2026)¶

Every Aareon.QL-x.*.list inside a reference-data .ftpackage is gzip-compressed XML, so it looks like binary noise and grep finds nothing. Nothing in the pipeline ever decompressed them — which is why every page under Versaa_Exports_md/reference_lists/ renders as *(parse error)*, and why a whole class of "the list is wrong" questions had never been checked against the actual artefact.

import gzip, re
t = gzip.decompress(open(path, "rb").read()).decode("utf-8", "replace")
items = re.findall(r"<Name>(.*?)</Name>\s*<Value>(.*?)</Value>", t, re.S)   # (Name, Code)

Use versaa-rag/fetch_reference_list.py --env <dev|test|live> --name <substring> to pull and decode one list from any environment. Fetching a single item matters: Live has ~6,255 reference-data items and a whole-category export 504s every time (8/8 attempts on 18/08/2026, ~11 min), while a single-item payload is 357 bytes and returns instantly.

Two techniques this unlocks: - Diff a list across git revisions to see what a deployment actually contained — git show <rev>:extracted/reference_data/ReferenceData/<name>.list piped through the snippet above. This is how the Ticket 17337 dedup mechanism was disproved. - Compare the same list across environments before believing a user's "it's still broken" — the deployed list is exactly what the device downloads, so if it is correct in that env, the fault is device-side cache, not config.

Watch out: an export from Test or Live lands in all_versaa_forms/, which is the watcher's scan path — the next watch_and_process.py --once will extract it straight over the Dev-derived extracted/ tree. -SkipProcess only protects that run. Move non-Dev packages out immediately.

A datalist built from cat/gen codes keeps ONE row per duplicate description — the lowest code wins (added 18/08/2026)¶

CreateDataListFromDatabaseWorkflowStep writes one row per unique Name. AsbestosLocationsSQL (and its twin in QL Property Survey Base Data) is order by desn with no DISTINCT and no tie-break, so when two codes share a description the tie falls to physical/index order — code_id ascending. If the dead code sorts first it takes the slot and the in-use code renders blank. Only pairs in that configuration break; do not assume every duplicate pair is affected. See ../asbestos/overview.md (Ticket 17337: LAUNDR beat LAUNDRY; Bin Store / Common Room / Passage were fine throughout).

Corollary — check which workflow actually maintains the list. Two can write the same datalist. For Aareon.QL-x.AsbestosLocations: QL Asbestos Base Data is manual-only (bare <Start>, allowRunFromPortal="false") while QL Property Survey Base Data rewrites it nightly at 20:20. Last writer wins, so running the manual one is a temporary override that the nightly undoes — fix the source data, not the list. Schedules are per-environment.

"Value Check" vs "Form Item" condition types¶

In the formula builder conditions panel: - Value Check: tests whether the field has any value (is not empty). - Form Item: tests the field's actual value against a constant.

These are not interchangeable. Using Value Check when you meant Form Item (or vice versa) is a common mistake.

Debugging¶

Silent workflow failures¶

Workflows can fail silently — they appear as "Completed" in the workflow search but have not actually done the intended work. Always verify the downstream outcome (e.g., check if the repair was raised, the email was sent, etc.) not just that the workflow status shows Completed.

vw_Workflow_Errors for actual errors¶

SELECT * FROM [1stTouch_PrtyLive].[LHP].[vw_Workflow_Errors]
ORDER BY ErrorDate DESC

This catches workflows that errored out. Workflows that "fail silently" (appear complete but did nothing) may not show here — those require manual outcome verification.

First thing when something isn't working: check workflow instances¶

Management Studio → Workflows → Search Instances is the first place to look whenever a form isn't behaving or data isn't flowing as expected. Filter by form name, date, or status (Errored / Running / Completed). This will usually show you exactly what broke and where.

Peter Steele: "The first thing we do is we look at what's errored."

Retrying a workflow step only retries FROM that step¶

When you click Retry on an errored workflow instance, it retries from the step that failed — it does NOT replay the workflow from the beginning. If the failing step's error is caused by missing data that should have been populated by an earlier step, retrying will not fix it.

In that case, you need to go back into the workflow definition and add an "Append Data Item" step that explicitly copies the missing data from a prior step in the chain — then start a new workflow instance.

One change can break seemingly unrelated things¶

Versaa's workflows are deeply interconnected. Changing one workflow step or form item can cause errors in sub-workflows or downstream workflows that you wouldn't expect. When a new bug appears: 1. Check when the last change was made 2. Think about what calls what in the workflow chain 3. Test the exact path the bug follows end-to-end — don't assume the fix location is obvious

SQL Server Tips¶

Multi-cursor column editing (Ctrl+Alt+click)¶

In SQL Server Management Studio, you can place multiple cursors for column-mode editing: - Ctrl+Alt+click → places an additional cursor at click position - Drag with Ctrl+Alt held → selects a vertical column block

This is extremely useful when formatting a list (e.g., adding quotes around every item in a column of values). Combine with a Notepad++ find-and-replace pass for fast list preparation.

Automation / Script Gotchas¶

Non-ASCII console characters can break export scripts on Windows terminals¶

In export_from_management_studio.py, non-ASCII arrow characters in print() output caused UnicodeEncodeError on some Windows terminal code pages during VS Code runs.

Example failure:

UnicodeEncodeError: 'charmap' codec can't encode character '\u2190'

Fix: Use ASCII-only console output strings in automation scripts (<-, -> instead of Unicode arrows). This avoids code-page-specific failures and makes command-based runs reliable.

Watch out: A script may work in one shell and fail in another if terminal encoding/code page differs.

The package watcher only extracts `all_versaa_forms/` — other categories drift stale¶

versaa-rag/watch_and_process.py (and run_dev_pull.py, which calls it) scans only WATCH_DIR = all_versaa_forms/ for new .ftpackage files. Its _extract_package() is generic (routes any ZIP entry via ENTRY_FOLDER_MAP), but the scanner never looks at all_workflows/, all_providers/, all_versaa_reference_data/, etc.

Historically this was hidden because a Dev "export all" produced a single combined package dropped into all_versaa_forms/, which carried every category's entries. When a Dev export is instead delivered as 9 separate per-category packages (one in each all_* folder), the watcher extracts only the forms package and the workflow/refdata/provider/variable/template content in extracted/ silently goes stale.

Symptom: months later a "full re-export" shows a workflow markdown appearing at a version that was already live in Dev (e.g. Asbestos Get Property Data v5 was in the 270526 package but its markdown only generated when all categories were finally extracted on 04/06/2026).

Fix: for split per-category exports, run versaa-rag/extract_all_categories.py (extracts the top-level .ftpackage from every all_* folder using the same _extract_package logic), then convert_exports.py and generate_catalog.py. Don't rely on the forms-only watcher for a true full sync.

Distinguishing real changes from export noise: after extraction, most extracted/workflows/*.schedule and *.workflow.rels diffs are scheduler/relationship-GUID re-serialisation, and many reference-data / XSLT diffs are entity-encoding only (' ↔ '). Use the manifest version numbers (Forms/Workflows) as the signal for genuine logic changes; treat the rest as serialisation churn.

The automated export was silently Forms-only until 08/06/2026 (now fixed)¶

export_from_management_studio.py fetched 10 categories via GetAll* endpoints, but 8 of the 10 endpoint names were wrong. An unmatched API route in Management Studio returns the SPA's index HTML with HTTP 200 (not a 404), and the script's error handling treated any non-list response as "0 items" — so workflows, reference data, variables, providers, etc. were silently exported as empty. Only GetAllFormsWithVersions was correct. Every "full export" before this date was Forms-only.

The real endpoints (harvested from the app's main.js ajax service) are all under /api/RefData/ except UserProperties: GetAllFormsWithVersions, GetReferenceDataInfoList, GetWorkflowsWithVersions, GetWorkflowVariables, GetAllDocumentTemplates, GetDiaryConfigurations/All, GetReportConfigurations/All, GetProviders, GetBaseTableNamesWithMappings, and /api/Users/GetConfigureProperties.

Other findings baked into the fix: - Payload shape matters per type. Versioned items (Forms, Workflows) must be sent as {Info, Version} (a single resolved version object) — the raw browser shape {Info, Versions, ChosenVersion} makes DownloadExportPackage return HTTP 500. Flat categories are sent as-is (the SPA's qo = i => i.Item). - A single combined "select-all" package (~60 MB) 504s during server-side assembly. The fix exports one package per category (each small enough to assemble), all dropped into all_versaa_forms/. Because they all land in the watched folder, watch_and_process.py --once extracts every type in one pass — so the extract_all_categories.py route above is not needed for this flow. - DownloadExportPackage can return the SPA HTML page on error. The download now validates the body starts with a ZIP magic (PK…) before saving — never write HTML as a .ftpackage. - Reference Data is 1,794 files, ~40 MB, mostly plumbing (images, CSS themes, .htmlform form-definition resources, .db3 SQLite caches). Default export keeps only ResourceType List/DataTree (~1,073 business items); --all-refdata includes everything. - The .ftpackage binaries are no longer git-tracked (timestamped/reproducible — would add tens of MB to the repo every run). Only extracted/, Versaa_Exports_md/, wiki/catalog/ are versioned.

A download retry MUST re-prepare; one failed category must not sink the run (fixed 10/06/2026)¶

Two resilience bugs surfaced when Forms hit an HTTP 504 (server assembly timeout): (1) download_package retried only the GET, but a 504 discards the server's prepared package so the retry got HTTP 404 and bailed; and (2) export…py exited non-zero on any failure, and run_dev_pull.py aborted the whole pipeline — so 8 successfully-downloaded categories were never processed/committed (the morning's wiki didn't update at all).

Fixes: - Retry the prepare+download PAIR per category (export_one_category, attempts=3): each attempt re-runs prepare_export() before download_package(), so a 504/404 recovers via a fresh prepare. download_package is now single-attempt and raises on any non-200/non-ZIP. (Verified: this recovered ReferenceData after a 504 on a real run.) - Partial success is non-fatal. Export exit codes: 0 = all ok, 2 = partial (some saved, some failed), 1 = total failure. run_dev_pull.py proceeds to processing on 2 (commits what succeeded) and only aborts on 1. The scheduled wrapper maps 2 → result:"partial" + a "PARTIAL" toast. A failed category keeps its last-good extracted/ content (its prior package is still in archive/) and recovers on a later run. - Note: a consistent 504 on one category (e.g. Forms assembling slowly under server load) is a server-side condition the client can't force past — the value of the fix is that the run still updates everything else and flags itself partial, rather than doing nothing.

Nightly auto-commit must not `git add -A`¶

watch_and_process.py's _git_commit() previously staged git add -A then pushed. For an unattended scheduled run that sweeps any work-in-progress in the tree into a pushed commit. It now stages only COMMIT_PATHS (extracted/, Versaa_Exports_md/, wiki/catalog/, versaa-rag/data/). If you add a new generated output location, add it to COMMIT_PATHS or it won't be committed.

Scheduled evening Dev pull (Windows Task Scheduler)¶

The full refresh now runs automatically each evening — see CLAUDE.md §15. install_scheduled_task.ps1 registers task "Versaa Dev Pull (nightly)" at 20:30 with WakeToRun + StartWhenAvailable (run-if-missed) + AllowStartIfOnBatteries, LogonType Interactive (so the toast shows and git/.venv work without storing the Windows password). Evening over overnight because the laptop is asleep-but-charged at 20:30 vs flat by morning. Check it ran via versaa-rag/logs/last_run_status.json. A cloud/remote Claude routine cannot do this — it can't reach testmobile.lincolnshirehp.com behind LHP's network; it must run locally.

Package storage management — archive retention + no binary git-tracking¶

Each run archives the previous set of packages into all_versaa_forms/archive/, so without a cap the archive grows ~27 MB/night forever. export_from_management_studio.prune_archive(keep=3) runs at the end of every export and keeps only the newest 3 of this job's own packages per category — it matches only the strict <category>_YYYYMMDD_HHMMSS.ftpackage pattern, so hand-built/legacy packages are structurally never deleted. Tune with --archive-keep N.

The .ftpackage binaries are not git-tracked — all_versaa_forms/ and the now-vestigial sibling folders (all_workflows/, all_versaa_reference_data/, all_document_templates/, …) are all git-ignored. The per-category export routes everything into all_versaa_forms/, so the other all_* folders are unused going forward. A one-time cleanup_legacy_packages.py (dry-run by default; --confirm; writes a manifest to logs/) removed 125 legacy packages / 442 MB on 08/06/2026. Note: binaries that were previously committed remain in git history — reclaiming remote size would need a history rewrite (out of scope). Run-logs (dev_pull_*.log) are pruned to the newest 60 by run_dev_pull.py.

A sub-task only gets its own workflow if the parent extracts it¶

A sub-task lives as a nested <Task> inside the parent's TaskXml. It does not become its own root Task — and therefore does not get its own completion workflow run — unless the parent's completion workflow lifts it out with FirstTouch.Workflow.Steps.Toolkit.ExtractSubTaskCreateWorkflow.

This matters because Generic Task Completion is anchored to //Task[not(ancestor::Task)] on every parameter (~60 of them) — the outermost Task only, nested Tasks explicitly excluded. So a still-nested sub-task's data items are invisible to it: the XPaths silently resolve against the parent. Anything gated on a sub-task data item (email recipient, document template, contact request) just doesn't happen, with no error.

Watch for the near-miss step class Toolkit.Workflow.CreateWorkflowInstanceForSubtasks (params subtaskNames / parentTaskIdDataItemName). It starts a workflow instance but does not extract, so the nested task stays nested and the instance runs against the parent. It looks correct in the diagram and completes successfully — it just does nothing. Repo-wide it is used twice; the correct ExtractSubTaskCreateWorkflow is used 128 times. This was the cause of the CRR completion email never firing from Repair Works Order — see forms/component-replacement-request.md.

Correct parameter set (copy from a working sibling step in the same workflow):

<ConstParameter name="sourceStep" value="Unpack Data" />
<ConstParameter name="formItemNameList" value="ComponentReplacementRequest" />
<ConstParameter name="formItemNameStatusOverride" value="" />
<ConstParameter name="workflowName" value="Generic Task Completion" />
<ConstParameter name="parentGuidDataItemName" value="" />

formItemNameList takes the parent form's FormItem name, not the sub-form's display name. There is also an Aareon.Toolkit.ExtractSubTaskCreateWorkflow namespace variant (5 uses) — prefer plain Toolkit. (128 uses).

`extracted/` workflow filenames COLLIDE across environments — never trust the highest `_N`¶

extracted/workflows/Workflows/ names files <Name>_<version>.workflow. The name is not unique across workflow records: several distinct workflow ids share a display name, and each has its own independent version sequence. They all write into the same folder, so the highest-numbered file is not necessarily the latest — or even present in the environment you're targeting.

Real example (verified 21/07/2026): three files named Repair+Works+Order+Comp_*, three different ids, and only one of them is in Dev:

File	Workflow id	In Dev?
`_4`, `_7`	`4e8a0aa4-…`	yes — the Dev record, active v7
`_8`	`a93c9261-…`	no (another env)
`_15`	`1deb4b13-…`	no (another env)

Basing a fix on _15 because it was the highest number would build on a foreign environment's lineage. (Here _7 and _15 happened to be byte-identical apart from the id, so the shipped content was still correct — but that was luck, not design.)

Always confirm the base before editing: compare the id= attribute on line 1 against the live record, e.g.

& "versaa-rag\.venv\Scripts\python.exe" "versaa-rag\ms_workflow_info.py" --structure-only "<Workflow Name>"

which prints the active version, and assert the expected id in the transform script (see transform_rwo_comp_crr_extract.py). Related: on import MS matches the workflow by NAME, ignores the id in the XML, and assigns the next sequential version itself — so a package labelled version="16" landed in Dev as v8. Track the MS version, never the on-disk _N.