Self-reporting asset application for networks of hospitals, counties or states

UPDATE: I have created a new v2 of this app based on numerous feedback items from the community as well as appsheet staff. Please see my comment below regarding “RM v2 CE” which is "Resource Manager v2 Community Edition"

Hello AppSheet community!

I have been so happy to see the work in the COVID19 community topic. Please reach out to me directly if you need assistance on something you are trying to build. (we’ve all been a bit busy over here and I am remiss in my typical response times on the forum)

In this same vein, we have created a few new apps specific to emergency response situations. These are templates that can be copied by any organization, they then work out of the box and can be modified as the org sees fit. You’re seeing evidence of that in this very community topic.

One of these apps - the purpose of this post - is a “resource self-reporting tool” for hospitals or hospital networks, as well as for county and state level orgs.

TLDR: The app link for a template you can copy is here:

https://www.appsheet.com/samples/Asset-selfreporting-for-county-or-statewide-systems?appGuidString=77ef54b6-3fcb-4c17-bb99-d861572a2b59

Please note that in the portfolio gallery, this app will appear to be completely empty due to its use of security filters. You will want to “copy and customize” the app to get started.

And in addition to this very community post, a google doc describing this app is here:

This doc is a work in progress and may update. In fact, we have erred on the side of expediency and there may be errors and omissions in this app and its documentation.

At the very bottom of this post are some screenshots from the application with a summary description of each.

So what’s in the app?

(note, when you see the word “org” below this could refer to a hospital, a network of hospitals, or a county or state level entity)

Devices and Web

  • This app, like all appsheet apps, will run in any browser as well as on iOS and android devices.

Overview

We have created a user/hospital/reporting data model that allows an organization to self-report on an ongoing basis the following types of info:

  • Available Beds
  • Available PPE devices
  • Ability to add requests for the above.

Caveats

  • We readily admit that a) the app is probably not “finished” and b) there are other apps and other ways to do this.
  • We decided to err on the side of urgency and release this template knowing that it will probably get modified in the field.
    Also note that this app does not solve for Pii or HIPAA compliance, and nor should it. It is meant to track facility-level information.
  • This app is released to the community. As such we kindly ask that you not email support@appsheet.com with technical issues, and instead use this post and comment thread to do the same. Thank you for understanding!

Features - There’s quite a bit of work that went into this app, which includes the following features

  • User roles - admins can add users, hospital or facility users can only edit their own “resource availability”.
  • Hospital data is loaded from 5,000+ public records. We used medicare open data as our starting point, but the app allows you to add new locations.
  • County and State Centric - the app is for state-level usage and includes county level detail, as well as individual facility level detail.
  • Audits/Logging - each time a facility point of contact updates their “available resources”, this info is logged to a separate table. This data will prove very useful over time for a data analyst.
  • Email “current status” - the app allows you to send yourself a simple formatted email of your county or your entire state. These are driven by appsheet workflows and the output can be formatted however you want. An example might be to send a html table of “current resources” so that you can easily update a website or portal.
  • Invite new users - when an admin creates a new end user, that user will receive an invite to the app.

Usage

  • The app will appear “blank” until you add yourself to the user list. Go to the menu and choose users. Whatever state you assign yourself will become the “state of record” for you and everyone else. This app starts off in Washington, USA.
  • We created security roles “Admin”, “State Admin” and “Hospital POC” - you are welcome to change these but note that you will also need to change numerous show-if calcs throughout the app as well.

Design

Some basic design decisions, if any of the following are not the case, please let us know (read: errors and omissions)

  • The hospital data came from medicare.gov - perhaps not a complete list but a great start. This data has a county and state column. State codes are two digits.
  • As soon as the “first user” selects a state, the rest of the app starts keying off of that fact. So if you start with “CA” then from there on out, when you add new users, select hospitals, etc, the whole experience will be filtered to “CA”.
  • The fields and calculations are an attempt to provide meaningful statistics for health care orgs, but we also readily recognize that each org may have specific requirements.
  • All the field names that are not virtual have zero spaces. This is by design in case folks want to report on the data later on (many pieces of BI and DB software still have an issue with spaces in field names even after all these years…)

Data sources

Even more technical info

  • There are a few unique keys in the data model that are important.
  • The first is the users table - the unique key here is the email address. Without this, the security of the app would fail.
  • The second is the HospitalName, which is an appsheet Key that is a reference (REF) to the hospitals table. This key is simply the ID that medicare assigned to the 5000+ hospitals in their dataset. You can add additional facilities and this app will use UniqueID() to generate a correct new key.

Public Sharing

  • An interesting idea that we didn’t implement is to have two different apps connecting to the same data source.
  • This app would be used for internal edits and management
  • And a second “read-only” app could be used to present this information on a web portal, government website, or similar.
  • This could be a very powerful real-time dissemination tool for county or state governments.

One last comment about automation

AppSheet has a Rest API - in theory an org could use the rest API to post their current info to the table in this app called “currentstatus”. They would need to work with us to understand in detail the requirements around this. More info on the Rest API is here:
https://help.appsheet.com/en/articles/1979979-invoking-the-api

Screenshots

The front door:

Map of facility color coded by bed availability:

Additional commands in the upper left menu:

Request map showing “needs” across the data set:

Detail page showing statistics about each facility:

2 Likes

Wow, that’s really amazing!

This is in alignment with what I put on the post about that hackathon. Maybe to think about this as a larger system for emergency response. Since this is capturing hospital capacity, there could be another end for citizens that helps them find the nearest hospital with capacity. Maybe when capacity goes over 90% the app then takes the hospital off the list so people start moving to the next best hospital. It’s like traffic control.
I was in the army a long time ago and during emergency situations that’s one of the hardest things to control is resource management because people flock to know locations. With what you put together, if adopted by multiple facilities, the system could know how to move people around.
Awesome work!

That’s all spot on. At the tail end of the write up we call out the idea of a second read only app that uses the same data. Maybe even marked as a AppSheet public sample so that iframe embedding can occur. :slight_smile:

Do you know if anyone is using this yet?
Or if this is a need by hospitals right now?
I wonder if they already have systems they use to track capacity? If they do, could that just be a regular data dump?

@School_Bus Working on it! it’s an ongoing process.

BTW, I just tested the Rest API (on a copy of the template app I posted above), and it works awesome.

Using an external system (read: epic software, cerner software, partnership software, something else), someone could post to appsheet - that way, they end up with manual data entry, as well as automated system entry.

The JSON body looks like the following - it’s a valid working example. For “HospitalName” this is the ID that medicare dot gov assigned in their data set, we are using it as a ref column. For Rest API settings:
we want to EDIT (see json below)
it’s of verb “POST”
auth set to none
but a header applicationAccessKey whose value is the app value once you enable rest api for your app.

json body example:

{
   "Action": "Edit",
   "Properties": {
      "Locale": "en-US",
      "Location": "47.623098, -122.330184",
      "Timezone": "Pacific Standard Time",
      "RunAsUserEmail": "ty@appsheet.com"
   },
   "Rows": [
      {
		 "UniqueID": "66bfc442",
         "HospitalName": "50038",
         "ICUBedsOccupied": "25",
         "ICUBedCapacity": "100",
         "COVIDICUBedsOccupied": "26",
         "COVIDICUBedCapacity": "100",
         "FloorBedsOccupied": "27",
         "FloorBedCapacity": "100",
         "COVIDFloorBedsOccupied": "28",
         "COVIDFloorBedsCapacity": "100",
         "VentilatorsInUse": "29",
         "VentilatorCapacity": "50",
         "GlovesAvailable": "501",
         "ApronsAvailable": "502",
         "GownsAvailable": "503",
         "FRSMAvailable": "504",
         "VisorsAvailable": "505",
         "RespiratorMasksAvailable": "506",
         "SystemPointofContact": "Ty Alevizos",
         "POCEmail": "ty@example.com",
         "SystemPOCPhone": "206-555-8888",
         "HospitalPointofContact": "Someone Else at the hospital",
         "HospitalPOCPhone": "(408) 885-5000",
         "FullAddress": "751 SOUTH BASCOM AVENUE, SAN JOSE, CA 95128",
         "COUNTY": "SANTA CLARA",
         "STATE": "CA",
         "Last Update Timestamp": "3/27/2020 0:26:39",
         "TextField100": "220"
      }
   ]
}

v2 of this app is located below. I have left the original one for both posterity as well as on the off chance someone needed it still. @Jonathon, @School_Bus

Best, -Ty

https://www.appsheet.com/samples/Resource-selfreporting-for-county-or-statewide-systems?appGuidString=574ff617-94d6-451d-9f47-2dd69dfda03d

1 Like

v3 and probably final version of this app is here:

Resources App:

https://www.appsheet.com/samples/Resource-selfreporting-for-county-or-statewide-systems?appGuidString=78a5e4d4-2740-44bb-b5ab-476c4e2dc2d4

Google doc for above:

https://docs.google.com/document/d/1AWnYmhwAVd_MMz9EzgSRuPYBoANRn7jV8zSVh8cS5Sg

1 Like

:clap::clap::clap: This is dope.

When you tap “Offer Staff” how were you able to read my email into the form?

@Sean_Lim not sure if you meant, 1) in the context of this sample, or 2) a copy of it, that you made and are looking at locally in your account.

General answer is the useremail() function: It picks up the currently logged in user’s email address.

Just looking at your app from the sample app repository, I’m able to see that my useremail was added to the form, but traditionally Appsheets does not permit public apps to read useremail. Is this still true with this app?