Family Stays in HIFIS 4

It's almost universally agreed that family stays in HIFIS 4 are a little problematic, especially if you compare family stays in HIFIS 4 to HIFIS 3.

There are three challenges are for day-to-day use when it comes to front line staff at family shelters, and then even bigger problems that are hiding behind the curtain when you're trying to pull data out of the database.

Occupancy

First, all of the occupancy in HIFIS 4 is bed-based as opposed to room-based. Let's imagine we have a family shelter with 20 beds. If 16 of those beds are occupied, HIFIS thinks that the shelter currently has an 80% occupancy rate. But consider two possible layouts for this shelter:

Shelter 1

Shelter 2

Both shelters are at 80% occupancy, according to HIFIS, but Shelter 1 can't accommodate any additional families, while Shelter 2 has room for another family of four. So it would be more accurate to state that Shelter 1 is at 100% occupancy, while Shelter 2 is only at 80% occupancy, even though technically they both have the same number of total and occupied beds. It's the rooms that matter.

Mobile Beds

The second challenge for front-line staff at family shelters is the concept of mobile beds. It's very common for family shelters to rearrange their furniture when there's a new intake. While Shelter 1 (above) may have been listed as having 5 beds per room, they could accommodate a family of four in room A and a family of six in room B by simply moving one of the beds from room A to room B. Cribs in particular are often moved from room to room.

In HIFIS 4, there is a way that you can indicate that a particular Bed is "Mobile" which is to say, can be moved around. Unfortunately, there is currently no way to actually move Beds around in HIFIS 4. There's a way to say that a Bed is movable, but you can't actually move it. Hopefully, in a future software update this will get addressed, but I have no idea when that could be expected. (As an aside, if this feature is important to you, you should contact the HIFIS Support Desk and request it. More people asking for the same fix means it will happen faster.)

What this means, practically, is if there's a possibility for Beds to get moved around, you may need to create some "phantom" Beds - Beds that exist in HIFIS but don't exist in the real world. You would add the maximum number of possible Beds to each Room. For example, a room that could hold up to 5 Beds would actually have 5 Beds in HIFIS, even if it currently only held 3 Beds. You would then temporarily deactivate the excess Beds, or you could mark some of them as Overflow Beds, so that the occupancy rates appear normal.

Book-Outs

The third challenge for front-line staff occurs on book-out. Family members' stays aren't actually linked together in any way (see Data Storage, below), so when a family is being booked out of the shelter, they need to be booked out individually, or lose a lot of data.

You have two options to book out a Client:

• Click the "Book Out" button, which books out just that Client, or

• Use the "Block Book Out" feature, which books out any number of Clients

When you click the Book Out button, you book out just one single Client, but can specify information like the Date and Time they Booked Out, the Reason for Discharge, and whether they were Referred To another agency. You can repeat this process for every Client, making it time consuming to book out an entire family.

When you use the Block Book Out function, you are given a checklist and can select which Clients, or which Rooms, you want to Book Out. But for all of these Clients, they have the Book Out Date and Time of "now," the Reason for Discharge of "Block Book-Out," and no Referred To agency. If you're using the Reason for Discharge field to track housing outcomes, then this isn't really a valid option. (Edit: Reason for Bookout has newly been added to HIFIS as of version 4.0.60.1, so there’s slight improvement.)

So, staff at family shelters are forced to tediously book out each individual Client one at a time, whereas they could have booked them all in at the same time.

Data Storage

Be warned: this is a bit of a technical discussion, so if you're not interested in a dive into data storage and linking, save yourself and run away now!

As I mentioned at the beginning of this article, there is an even bigger problem behind the scenes. As a report writer, I can attest that one of the most common, basic requests from communities is a report that will let them know something about the families that stayed in their shelter. Sometimes it's as basic as wanting to know how many families were served, compared to the number of singles, but often it goes deeper than that. I get requests for household composition, how many single-parent households there were, average household size, even things like average length of stay per household.

Those all sound like reasonable requests, right? I thought so too, until I really looked into the data.

First, let me explain how the data is stored. We have a Stays table, which contains information for each book-in. Let's pretend that it looks like this (this is a simplified representation):

There's more data, like reason for service and reason for discharge, but I promise there isn't a hiding field that indicates whether a stay is a "family stay" versus a "single stay." There's no field that links some of the stays together. 

So. From looking at the sample data above, can you tell me who is part of a family here? Who isn't?

Of course not. 

There's not enough data. We need to look somewhere else in the database.

There's another table that governs family membership (the People_Groups table, if you want to go hunting). Let's imagine it looks like this (by the way, NULL means the field is empty. In this case, it means the client is still in the family and there is no end date):

What this table is telling us is that Clients 30, 33, and 44 are all, or were all, part of Group 1 at some time. We have the date that they joined the family, and the date they left (if applicable).

Does this lead us closer to determining whether a particular stay is a "family stay" or not? Yes... and also no. There are so many different ways that we can define a family stay.

• We can test when someone books in to see what their family status is. This would mean that Stays 2 and 3 are Family stays but not Stay 4.

• We can say if a person is part of a family at any point during the stay, they must have that family status.  So client 44 became part of a family halfway through their stay, so they would therefore be considered part of a family, not a single.  Therefore, Stays 2, 3, and also 4 are considered Family Stays.

But there's a problem: what about Client 22? They're staying at the shelter and according to the data, they were part of a family at the time of the book-in and also throughout the stay. This could be a case where a user never bothered to update the family after there was a separation. In both of the above definitions, Stay 1 counts as a family stay, as does Stay 5.

So does that mean that we need to not only look at each person and determine whether they're part of a family at the time of the stay, but also look at their other family members and see if they're also staying at the same shelter at the same time?

I'm going to show you what the code might look like using these definitions. You don't necessarily have to follow along, but just take a note of how long the code is. For the first definition (when someone books in, see if they're in a family) [12 lines of code]:

CASE WHEN (SELECT TOP 1 HIFIS_Groups.GroupHeadClientID
FROM HIFIS_Groups
    INNER JOIN HIFIS_People_Groups ON HIFIS_Groups.GroupID = HIFIS_People_Groups.GroupID
    INNER JOIN HIFIS_People ON HIFIS_People_Groups.PersonID = HIFIS_People.PersonID
    INNER JOIN HIFIS_Clients ON HIFIS_People.PersonID = HIFIS_Clients.PersonID
    INNER JOIN vw_StaysSummary.ClientID ON HIFIS_Clients.ClientID = vw_StaysSummary.ClientID
WHERE HIFIS_Groups.GroupTypeID = 1
    AND vw_StaysSummary.StayDateStart BETWEEN HIFIS_People_Groups.DateStart AND ISNULL(HIFIS_People_Groups.DateEnd,CURRENT_TIMESTAMP)
ORDER BY HIFIS_Groups.GroupID DESC) IS NULL THEN 'Single'
ELSE 'Family'
END

For the second (when someone books in, see if they're in a family, and whether the family is also booked in) [18 lines of code]:

CASE WHEN (SELECT TOP 1 HIFIS_Groups.GroupHeadClientID 
FROM vw_StaysSummary 
    INNER JOIN HIFIS_Clients ON vw_StaysSummary.ClientID=HIFIS_Clients.ClientID 
    INNER JOIN HIFIS_People ON HIFIS_Clients.PersonID=HIFIS_People.PersonID 
    INNER JOIN HIFIS_People_Groups ON HIFIS_People.PersonID=HIFIS_People_Groups.PersonID 
    INNER JOIN HIFIS_Groups ON HIFIS_People_Groups.GroupID=HIFIS_Groups.GroupID 
    INNER JOIN HIFIS_People_Groups AS "HIFIS_People_Groups_Family" ON HIFIS_Groups.GroupID=HIFIS_People_Groups_Family.GroupID 
    INNER JOIN HIFIS_People AS "HIFIS_People_Family" ON HIFIS_People_Groups_Family.PersonID=HIFIS_People_Family.PersonID 
    INNER JOIN HIFIS_Clients AS "HIFIS_Clients_Family" ON HIFIS_People_Family.PersonID=HIFIS_Clients_Family.PersonID 
    INNER JOIN vw_StaysSummary AS "vw_StaysSummary_Family" ON HIFIS_Clients_Family.ClientID=vw_StaysSummary_Family.ClientID
WHERE  HIFIS_Groups.GroupTypeID=1
    AND vw_StaysSummary.StayDateStart BETWEEN HIFIS_People_Groups.DateStart AND ISNULL(HIFIS_People_Groups.DateEnd,CURRENT_TIMESTAMP)
    AND vw_StaysSummary.StayDateStart BETWEEN vw_StaysSummary_Family.StayDateStart AND ISNULL(vw_StaysSummary_Family.StayDateEnd,CURRENT_TIMESTAMP)
    AND vw_StaysSummary.OrganizationID=vw_StaysSummary_Family.OrganizationID
ORDER BY HIFIS_Groups.GroupID DESC) 
IS NULL THEN 'Single'
ELSE 'Family'
END

Don't worry if you didn't follow that. The point is that it's significantly more effort to check if someone is in a family and also if other family members are also booked in to the same shelter at the same time. And it's not very efficient! Crystal Reports would need to run that code for every single stay, so if you have thousands of stays you're dealing with, the query's being run thousands of times. (If anyone's having trouble with reports that take forever to run, it's because of complicated formulas like this.)

I have another potential solution for this family stay problem. To be honest, all of the above solutions are less than ideal. You're likely to get false positives and false negatives all over the place. For example, let's say a client was part of a family (they stayed as a child), then returned some time later as an unaccompanied youth to a youth shelter. They get booked in, and perhaps a few days or weeks later, or never, someone updates that client's family membership. They were booked in at the same time that HIFIS thought they were a family. Or what about the opposite: a couple book in together, and the staff doing the book-in hadn't been trained on the Family module in HIFIS, so they just do a book-in. They're a couple, but HIFIS thinks they're two singles.

The point is, the way the data is stored in HIFIS 4, any report about families is going to be imperfect, we just need to decide what's the best way for it to be imperfect. (By the way, you could contact the HIFIS Support Desk and tell them what you think.)

What about this: what if we don't even need to look up the family module at all? When a staff books a client into a shelter in HIFIS, if they're part of a family, staff see a "Book In Family" button. What that does is it just adds all the family members to the same book-in. It does not actually link the stays in any way, but what if it doesn't need to? All family members would have the same Stay information, like the exact date, time, reason for service, referral source. What if we just defined a family as "a group of people that booked in together"?

This approach allows us to avoid a lot of the messiness of comparing book-in information to the families module. The query would look something like this [6 lines]:

CASE WHEN (SELECT DISTINCT COUNT (StayID)
FROM vw_StaysSummary s
WHERE s.StayDateStart = vw_StaysSummary.StayDateStart
    AND s.OrganizationID = vw_StaysSummary.OrganizationID) = 1 THEN 'Single'
ELSE 'Family'
END

The main advantage of this is it would be so much faster and less complicated. Would it have limitations? Of course! But it may be the best of the available bad options. Now, I am genuinely curious: is this a good idea? Or am I barking up the wrong tree here?