Calculated or Conditional Field Help Please

Discussion of the version of HanDBase that runs on the iPhone and iPod touch devices. This includes the synchronization conduits as well.

Calculated or Conditional Field Help Please

Postby galenmd » Tue Sep 01, 2009 8:38 am

My colleagues and I are successfully using a robust DB (created with Dave's help) for managing my patients in the hospital.

I usually sort my main view by patient room number. Unfortunately, our hospital contains several buildings. While the room numbers are unique, when sorted this way, they essentially organize by the floor, but not the tower. A simple solution would be to create a pop-up field for the different towers. However, I would like to avoid choosing a tower each time, essentially adding another step. Additionally, patients frequently move from room to room/tower to tower.

I would like to have the Tower be assigned automatically based on the room number.
Our hospital works as follows: The first digit assigns the floor. The second digit assigns the tower: 1 or 2 = North, 8 or 9 = South, s = ICU

Rooms 5100 or 5200 = 5th floor, North Tower
Rooms 5800-5900 = 5th floor, South Tower
Rooms 5s00-5s99 = 5th floor, ICU Tower

So, is there a way to use a calculated or conditional field that can see that second digit and assign a specified tower? If it can't see the second digit by itself, can it be done such as "5100 to 5299 = North" "6100 to 6299 = North" "7100 to 7299 = North", etc? If done this way, I would have to make about 15 of these ranges to encompass all room numbers.

Posts: 8
Joined: Tue Sep 01, 2009 8:06 am

Re: Calculated or Conditional Field Help Please

Postby dhaupert » Tue Sep 01, 2009 1:35 pm

Hi Steve,

I found your question to be a great puzzle today and have created a little test database demonstrating a solution! I will email it to you separately so you can see it in action and add to your database as needed. But for posterity:

Using calculations we can isolate the second digit of the room number. To do so, we take the room number and do a MOD 1000. That means divide by 1000 and give us just the remainder. So now the room goes from, for example 5209 to 209. To get just the 2 in there, we first divide by 100 and then take the floor of that amount. The floor ignores the decimal part, so we divide by 100 to get 209 to 2.09 and then floor gives us 2. Now we have the tower isolated.

Now we can string a few conditionals to get the tower name. However, the room number having an s in it is something that threw this all off, since the above assumes a numeric value. So the conditionals worked out to be something like this:

Field: IsSouth:
If Tower# is greater than 7, output is South, else output is [blank] (by [blank] I mean an empty value)
Next field: IsNorth:
If Tower # is less than 3, output is North, else output is IsSouth
last Field: TowerName:
If Room # is less than 10, output is ICU, else output is IsNorth

To explain: The first conditional says if the tower number we calculated above is 8 or higher the output of that field will be set to South, otherwise the output will be blank.
Then the next field gets evaluated. If the tower number is 2 or less, it's output is North, otherwise, the output is whatever the previous conditional was evaluated to.
Lastly, we check the Room #, not the Tower # to be more than 10. Reason being, room 5209 is greater than 10, but 5s09 is actually evaluated to be 5 (since converting 5s09 to numeric takes up to the first non-numeric value). So if the room # is less than ten, then there was likely an s in the second digit! If it's not less than 10, we take the output of the chained conditionals above it.

All seems to work as expected. Please see the email I'm sending with the database example- hope this helps!
Posts: 4113
Joined: Tue May 26, 2009 11:51 am

Return to HanDBase for iPhone and iPod touch

Who is online

Users browsing this forum: No registered users and 1 guest