How to Build a Self-Correcting Daily Census in Salesforce

A multi-facility behavioral-health center asked us to fix one number: today's census. Admissions corrected dates all the time (an admit date entered wrong, a discharge recorded late, a stay moved to another facility), and each correction left the counts for days already reported wrong. Someone rebuilt the spreadsheet every morning. We replaced it with the flow on this page, built in Flow Builder with no Apex, and handed the client's team the documentation to own it. What follows is that design with the client's name and data removed.

What data does a Salesforce daily census need?

Two objects. The stay lives on the Opportunity, because admissions already works it there. The census lives on its own object with one row per facility per calendar day, so a report for any date range is a sum of rows nobody has to recalculate.

Which changes does the census have to correct?

Six. Each one touches a different set of days, and each adds or subtracts exactly one patient on each day it touches. The examples use the counting rule in the flows as built: a patient counts from the admit day through the day before discharge.

How does the flow know what changed?

The master flow, Opportunity: Recalculate Daily Census, is record-triggered and runs after an Opportunity is saved. It starts when the admit date, the discharge date or the facility changes on a stay that already had a value there, or when the outcome changes to Excluded. The old values come from $Record__Prior, which Salesforce fills with the record's values from immediately before the save (Global Variables Resource, Salesforce Help, read 16 October 2026).

Two details in that start condition decide what the census can see. The prior value has to exist, so the flow corrects a date that moves and ignores a date entered for the first time. And Salesforce documents that when a record is created and updated in the same transaction, $Record__Prior holds the same values as $Record. An import that inserts a stay and fixes its dates in one transaction changes nothing in the census, so load corrections as a separate update.

A decision then sends each change to its subflow. Salesforce evaluates a Decision element's outcomes in the order they are listed and takes one path (Decision Element, Salesforce Help). A single decision with an outcome per case would correct only the first change in a save that moves both dates. Chain one decision per field so every change reaches its subflow, and test a two-field save; it is on our test list for that reason.

What does each census subflow do?

Every subflow has the same five parts, which is what lets an admin read one and understand all of them:

1. Inputs: the facility and the two dates that bound the change, old and new, passed from $Record and $Record__Prior.
2. Get Records: the Daily_Census__c rows for that facility from the first day affected up to the last.
3. Loop with an Assignment: add or subtract 1 on each row and add the row to a collection.
4. One Update Records after the loop: write the whole collection at once.
5. A fault path: if the update fails, notify an admin with the stay and the error.

The update sits after the loop for a reason. Salesforce allows 150 DML statements and 10,000 changed records per transaction, and when an element exceeds a limit it rolls back the whole transaction, even if that element has a fault path (Per-Transaction Flow Limits, read 16 October 2026). An Update Records element inside the loop spends one statement per day, so correcting a 30-day stay uses 30 of the 150 on one patient. After the loop it uses one.

Each subflow also stops when it finds no rows. That keeps the corrections safe to run, and it means a day with no census row for that facility is never corrected. Whatever creates each day's rows has to run before a correction can land on that day.

How is a facility transfer counted?

The transfer subflow makes two passes over the same dates. It subtracts 1 from the old facility for every day of the stay, then adds 1 to the new facility for the same days. Both passes use the stay's dates, so the stay counts as if the patient had been at the new facility the whole time. That is right for a stay entered at the wrong facility. If your patients move mid-stay and the days before the move belong to the first facility, add a transfer date to the stay and bound each pass by it.

What happens when an admission is excluded from census?

The exclusion subflow is designed and documented. When the outcome changes to Excluded, it subtracts 1 for every day of the stay at that facility; when an exclusion is reversed, it adds those days back; any other outcome change does nothing. Our handover diagram for this build marks it as not yet implemented, so in that build an exclusion does not correct the census on its own. It is the next subflow to build, and it follows the same five parts as the others.

Which days count: the admit day, the discharge day, or both?

Decide the counting rule before building and use it in every subflow. The flows as built count a patient from the admit day through the day before discharge, which is why moving a discharge from Jan 20 to Jan 15 removes Jan 15 to 19. Counting the discharge day as well shifts every range by one day, and a subflow written to the other rule quietly drifts the count. Two more rules belong in the same decision: what an open stay counts through (our test plan counts a stay with no discharge date through today), and whether a same-day admit and discharge counts at all, which under this rule it does not.

What should you test before trusting the census?

1. Each correction on a stay with known dates, checking the count on every day of the range and one day either side.
2. A facility change: the old facility drops by one and the new one gains one on every day of the stay.
3. An open stay with no discharge date.
4. A same-day admit and discharge.
5. A save that changes two fields at once: both dates, or a date and the facility.
6. A bulk update of 20 or more stays in one save, to confirm the transaction stays inside its limits.
7. A forced failure, to confirm the admin notification arrives.

Where does the census sit next to the EHR?

In this design the census is Salesforce work. The EHR keeps the clinical chart; admissions, census and follow-up run in Salesforce, where leadership reads them, and the census reads the same stay record admissions uses. For the EHR side, see what syncs between AdvancedMD and Salesforce and the Opus admission pipeline. The other systems we run for treatment centers are on our behavioral health page.

Frequently asked questions