Surrey and Borders ESR Automation

The Challenge

The Workforce Intelligence team wanted the daily Staff in Post report and other ESR extracts pulled overnight, with nobody logging in to do it by hand.

The daily flow failed often enough that the working diagnosis was “Edge is unstable”. Other flows were waiting on it.

The Constraint

The flow runs unattended on a virtual PC. Nobody is there to click retry, so every step has to recover by itself, or fail cleanly and record why.

It also drives the ESR Business Intelligence portal, which loads content in the background and is outside the Trust’s control.

The Approach

We left the browser alone and instrumented the failing actions. Every failure traced to one of six causes: selectors tied to dates or session tokens, clicks fired before the page had rendered, script-driven buttons that ignore a standard click, forced shutdowns that reopened old windows, renames that raced the download, and a pop-up that appeared a moment too late to be dismissed.

Each had a specific fix: stable selectors, readiness waits at each page transition, script-level clicks, a controlled browser launch and clean close, a wait-for-file before rename, and a bounded wait for the pop-up. Output now lands in SharePoint.

We then built a second flow, Education Staff in Post, by copying the stabilised one, and wrote the whole method up as build notes with a pre-go-live checklist. The Trust’s lead built alongside us from the first session, so the know-how stayed in the Trust.