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:
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.
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.
- 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 firstname.lastname@example.org 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.
- 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.
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…)
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.
- 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:
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: