Alerts
What are Alerts?
One of the core pillars of Care Control is that we have alert-driven information – we want to push data back out of the system.
We get the system to do some of the work in terms of analysing what is going on or providing notifications when things aren’t right.
Almost every new feature we add has some kind of alert component to it.
Where do I find them?
In CC Windows, from the Main Administrative Screen, on the Care Planning tab, click “Alerts and Notifications”.
Fig 1. the Main Administrative screen in CC Windows
Fig 2. Active Alerts and Notifications screen
Creating a new Alert
From the Active Alerts and Notifications screen, click “Create Alert”, to display the Create/ Amend Alerts screen
Fig 3. Create / Amend Alert screen
Step 1 – Select an Alert Template
Alerts are organised/ filtered by Category, Sub-Category, and Detail. For example:
Fig 4. Create / Amend Alert screen, Alert \Template categorisation
Step 2 – Fill in Alert Parameters
The Alert wording contains ‘wild card’/ fill-in-the-blanks entries that will be replaced by parameters. For example, the Resident parameter for this Alerts can be replaced by a given Service User selected from the drop-down, or [All residents].
Select the required value, and click “Update”: the list of parameters is updated to reflect your selection.
Fig 5. Alert \Template categorisation, filling in parameters
Step 3 – Who is Getting the Alert?
Finally, you say where you want to save this information.
The application uses Message Groups, which can be defined in the Build Staff Message Groups screen, access via the Message Groups button to the right of the Message Group drop-down:
Fig6. Message Groups button
Fig 7. Build Staff Message Groups screen
Here, you can opt to alert some or all members of an existing Message Group, or create your own group, populating it with individuals based on their category (e.g. Agency, Care, Management..), their role, (e.g. Cook, Domestic, Maintenance…), or simply by name.
From the Create/ Amend Alert screen, you can also opt to show the Alert on the Home Status screen; if you do so, you can specify how many staff members are required to sign off on the Home Status Alert.
Finally, it is possible to only alert the staff group’s manager for certain Alerts:
Fig 8. Alert Notification options
So, if the Alert is staff-related e.g., late check in, you can check the Alert Staff Manager box so that only the group’s manager will be notified, (although you can also select other people to be alerted, too).
Types of Alert
There are three types of Alert Template supported at time of writing:
Alert Template Type | Description |
Instance | Reactive, event-driven – e.g., with an Alert based on the content of a Note, which fires when the Notes is written to the system |
Scheduled | Polling based on a schedule time entered – the Create/ Amend Alert screen presents a Schedule Time field if the Alert Template selected supports it e.g., an Alert based on fluid intake in the past 24 hours |
Constant | Polling every five minutes and will raise the Alert as soon as the condition is matched |
Fig 9. Alert Template Types
The Template selected in the Create/ Amend Alert screen will present different options e.g., a “Care Planning>Fluid>Alert when a resident does not drink enough” selection is a “Scheduled” type, that fires when a period of time has passed (here, 24 hours). As a result, the Schedule Time field is shown, so that you can say when to start counting from (e.g., 08:00).
How are Alerts communicated?
Alerts are, or can be, shown on the Home Screen of a device running Care Control; they are also sent by e-mail to members of the specified Message Group:
Fig 10. An Example e-mail Alert message
Technical – Database schema
Note: foreign key relationships/ referential integrity is not implemented within the database, but rather managed in code. The diagram is for illustration purposes only.
Fig 11. table objects
Technical details – database objects
Note: Alert-related objects exist in the dbo schema and are prefixed with “Alert”
Note too that this is not in alphabetical order – Template-specific stored procedures are listed for information only and have been moved to the end
Object name | Type | Description |
dbo.AlertTemplates | Table | Used in the Create/ Amend Alerts screen, holds Alert Template Category, Sub-Category and Detail, along with the “fill-in-the-blanks” narrative text with parameter placeholders. Also holds the Type ID and whether an Alert form this Template supports having the Staff Manager alerted only. |
dbo.AlertTemplateType | Table | ID and description for whether Alerts created from a given Template are Instance, (reactive/event-driven), Scheduled, (geared to a point in time), or Constant, (polled for, every five minutes) |
dbo.AlertParams | Table | Contains the name, placeholder to be found/replaced within the template narrative, and the data type (pDataType) of each parameter for a Template. Note that data type can be a custom object, addressed by a case statement |
dbo.AlertTemplateParams | Table | Linkage between dbo.AlertTemplates and Alert Params – a many-to-many relationship as parameters are reused between templates. Note [pOrder] indicates the position a gaining stored procedure expects the parameter to be supplied |
dbo.AlertParamValues | Table | The actual values specified for each parameter in a given Alert (i.e., instance of a Template) |
dbo.AlertsDetail | Table | A record for every time every Alert fires. Note: Message Sent: 0=not yet processed (in the last five minutes), 1 = due, awaiting processing, 2 = sent |
dbo.AlertsDetailNotifications | Table | A log of whether a notification of the record in dbo.AlertsDetail has been sent or not, one row per recipient |
dbo.Alerts | Table | The Alert instances created from Templates. The [AlertDetail] field contains the completed narrative. Visible to the user on the Alerts and Notifications screen |
dbo.Alert_CheckSendMessages | Stored proc |
|
dbo.Alert_CreateUpdateAlert | Stored proc |
|
dbo.Alert_DeleteAlert | Stored proc |
|
dbo.Alert_FutureCareTasks | Stored proc |
|
dbo.Alert_GetAlertDetails | Stored proc |
|
dbo.Alert_GetAlertNotificationDetails | Stored proc |
|
dbo.Alert_GetAlertNotificationSignoffList | Stored proc |
|
dbo.Alert_GetTemplateParams | Stored proc |
|
|
|
|
dbo.Alert_ProcessAlert | Stored proc | In event-driven Alerts, within the procedure that might trigger them, called if dbo.IsAlertDue returns a 1. Takes a Template ID and e.g., a Note ID or / and additional text, Client ID etc; looks up the Alerts for the given Template ID and builds appropriate parameters, based on the [pOrder] in the dbo.AlertParams table |
dbo.Alert_ProcessAlert2 | Stored proc | Called by dbo.Alert_ProcessAlert, with both the parameters that proc was supplied, (e.g., the content of a Note), and those that proc has looked up from dbo.AlertParams, given a Template ID. Uses a GOTO approach to execute a Template ID-specific block of code and procedure, (such procedures listed at the bottom of this table). Note that parameter order differs between sprocs: client ID if required may not always be second, for example. |
dbo.Alert_RunScheduledAlerts | Stored proc | Called by an SQL Agent job every five minutes: builds a cursor of dbo.Alerts records that need to be addressed and calls the Template ID-specific sproc in each case |
dbo.Alert_SignOffNotification | Stored proc | Marks a Home Status screen Alert as having been signed off |
dbo.Alert_StoreDetail | Stored proc | Called by dbo.Alert_ProcessAlert2, adds a row to dbo.AlertsDetail |
dbo.Alerts_StoreAlert | Stored proc |
|
dbo.IsAlertDue | Scalar function | For event-drive Alerts, embedded withing the procedure that might trigger them (e.g. dbo.AddCarePlanNote). Takes a Template ID and returns a 1 or 0 to indicate whether the Alert is due. If 1, customarily, we then call dbo.Alert_ProcessAlert procedure |
dbo.BuildAlertTemplate | Scalar function |
|
dbo.Alert_CalculateFluidLoss | Stored proc | Specific to this type of Alert |
dbo.Alert_CalculateHomelyRemedyLevel | Stored proc | Specific to this type of Alert |
dbo.Alert_CalculateLowStockMedication | Stored proc | Specific to this type of Alert |
dbo.Alert_CalculateMissedMonitoringWindow | Stored proc | Specific to this type of Alert |
dbo.Alert_CalculatePrescriptionEnding | Stored proc | Specific to this type of Alert |
dbo.Alert_CalculatePRNLevel | Stored proc | Specific to this type of Alert |
dbo.Alert_CalculateRedCross | Stored proc | Specific to this type of Alert |
dbo.Alert_CheckAgedBalance | Stored proc | Specific to this type of Alert |
dbo.Alert_CheckCheckInOutsideRange | Stored proc | Specific to this type of Alert |
dbo.Alert_CheckCheckOutOutsideRange | Stored proc | Specific to this type of Alert |
dbo.Alert_CheckEarlyCheckOut | Stored proc | Specific to this type of Alert |
dbo.Alert_CheckEvent | Stored proc |
|
dbo.Alert_CheckLateCheckIn | Stored proc | Specific to this type of Alert |
dbo.Alert_CheckLateCheckOut | Stored proc | Specific to this type of Alert |
dbo.Alert_CheckMissedCheckIn | Stored proc | Specific to this type of Alert |
dbo.Alert_CheckPersonalAllowance | Stored proc | Specific to this type of Alert |
dbo.Alert_CheckPettyCash | Stored proc | Specific to this type of Alert |
dbo.Alert_CalculateFluidLoss | Stored proc | Specific to this type of Alert |
dbo.Alert_CalculateHomelyRemedyLevel | Stored proc | Specific to this type of Alert |
dbo.Alert_CalculateLowStockMedication | Stored proc | Specific to this type of Alert |
dbo.Alert_CalculateMissedMonitoringWindow | Stored proc | Specific to this type of Alert |
dbo.Alert_IsNoteNotThere | Stored proc | Specific to this type of Alert |
Fig 12. Alert database objects
Technical details – firing Alerts
Scheduled Alerts have separate, dedicated procedures; event-driven Alerts are integrated into the saving process of e.g., a Care Plan Note. dbo.AddCarePlanNote includes the following calls:
--New Alert Code
IF [dbo].[IsAlertDue](2) = 1 -- 2 is the template id for notes
EXEC [dbo].[Alert_ProcessAlert] 2,@NotePKIndex,@UpdatedNote,'',@ClientID,'','',''
--New Alert Code
IF [dbo].[IsAlertDue](68) = 1 -- 68 is the template id for notes plus exclusion criteria
EXEC [dbo].[Alert_ProcessAlert] 68,@NotePKIndex,@UpdatedNote,'',@ClientID,'','',''Fig 13. example of embedded Alert triggering
For Scheduled alerts (type IDs 2 and 3 – scheduled and constant), an SQL Agent job fires every five minutes to execute dbo.Alert_RunScheduledAlerts. This builds a cursor of dbo.Alerts records and will execute the Template ID-specific sproc if the NextFired date < GETDATE() (for scheduled Alerts) or anyway (for constant-type Alerts), passing in the AlertID.