Software > Wellness ERP System > 102.1 - Patient DB
Technologists will tell you you the ultimate goal is to not have duplicate data. If you are a master programmer, that is a fun goal to shoot for. I've tried it a few times. I've been successful a few times, but more often than not, I end up with multiple anyway. It's a great goal to dream about -- but now is the time to toss it out the window.
2 Databases at a Minimum - 3 or 4 is more likely...
The average small business person may have as many as 4 different customer database records and that is NOT a bad thing.
=== Customer Databases ===
We are going to cover our Google Sheets Database for this System below. That includes a module for Email List management as well. For information on Google Contacts Database, see Soul Proprietor Guide website (paid access). We don't have any additional information on 3rd Party Booking software at this time.
=== Customer Databases ===
- Gmail -- Store names and email addresses in Gmail for easing emailing. Store phone numbers only if you use a cell phone for your business phone. You can store them there otherwise if really needed, but a spreadsheet will be a much better place for those.
- Google Sheet -- Create a Master Customer Database in Google Sheets. You will see an example of that below. This will be needed for integration with your other Google Sheet Business System modules.
- Email Mailing List -- Either create an email mailing list in Google sheets ( an example is below) or you may want to consider a cloud based outbound-email management software (free or paid). While we prefer to try to do everthing in Google sheets, those offer some nice benefits and may be worth the financial expenditure if you feel you will truly use them.
- 3rd Party Booking Software -- If you are going to use some kind of booking software instead of Google Calendar or a Google Sheet structured like a Calendar you will have a contact record there too.
We are going to cover our Google Sheets Database for this System below. That includes a module for Email List management as well. For information on Google Contacts Database, see Soul Proprietor Guide website (paid access). We don't have any additional information on 3rd Party Booking software at this time.
Patient DB in Google Sheets
Our Patient Database in Google Sheets consits of a patient datatable along with several other utilities. The working tabs include:
- Patients
- Patient Comments (extended)
- Patient Lookup
- Patient Flags
- Ticklers
- Email List
- Appointment Reminder Tool
1 - Patients Table
We create a Primary Key with the patients first and last name. This will be used in numerous places through the Wellness ERP application. With spreadsheets, it seems it will be much easier if you always use a primary key that has readable significance or you will be really adding to your relational database workload.
2 - Patient Comments (extended)
As database developer, often times I kept extended notes that might only exist for a few records in a separate data table so that data and null fields didn't get pulled with each list call (be that really relevant or not these days). That habit has carried over here. Technically these could be kept in the primary table and the extended comments field could just be hidden for a condensed view of the data.
3 - Patient Lookup
Web based developers and users alike should really like this... This makes using spreadsheets not nearly so "spread sheet like". You will see a lot of this in other modules too. We've created a database lookup.
- The patient flags (yellow area) are linked to a patient flag table presented later, and clicking on the add/view "link" will take you to that flag (it will shift tabs and highlight that record in the patient flags table).
- The + - signs on the right below the logo will expand and collapse all 4 sections on this workbook in an accordian style.
- The Up/Down arrows to the right of each section will expand and collapse those sections for showing/hiding, primarily for printing purposes.
- The add, edit or add/edit links will take you to another tab and highlight a relevant record. If it is "add", it will take you to that relevant tab and fill in the name Abe Amor for primary key creation for the new record.
The Functionality for this page is contained in two sections...
The first is what we call a "Hidden Header" -- Do you see how the worksheet above seems to start at row 51? All of the lookup work and data manipulation is done in that "hidden header" section and then it is just mirrored (pulled) from the view seen by the user. This way if the user does happen to unlock and mess up a portion of the visible page, he/she is only removing the reference to the data in the hidden header. Obviously, this requires the users be educated... For what it's worth, this could in fact be put on a separate tab with tighter restrictions.
The second part of the hidden functionality is contained in the script modules attached to this Google Sheet. Tools > Script Editor will expose a powerful java script programming console that is inherently tied to this file. This programming environment utilizes native java script and there are a world of Google Objects that can be used for for accessing Google World. This is a fairly cool programmers paradise once you get the hang of it. There are some really easy things here that are very powerful, but as with all things, some simple things are cumbersome. For what its' worth, this system support code librarys for distribution (big eye balls here...). We are choosing NOT to distribute code via code libraries as our goal is to have fully independent users. Our scripts will be made avail to programmers and it will be up to them to include relevant and desirable scripts where needed. Truth be told this makes for much neater and cleaner programming at the scope we are targeting as typically only a few scripts are really needed with each individual workbook.
4 - Ticklers
Your patient is going on vacation for 2 weeks. You want to remeber to call them after vacation to see how it was and to get them back on their treatment routine, where do you store that task? You would put it in your ToDo's but how about a simple list for timed call back or follow up items?
5 - Patient Flags
A patient left their umbrella during their last visit and they indicated they'd get it at their next visit? Where do you note that so you and they don't forget it again? You couldn't read the insurance card you made a copy of last time and the patient is coming in tomorrow again, where do you note the need to get that info again? A message that a flag exists shows up on the Patient Lookup tab. If you lookup each patient as they arrive to confirm personal information you will also be able to catch patient flags.
6 - Appointment Reminder Tool
Everyone wants an "appointment reminder tool". Problem - your calendar dictates your appointment reminder tool options. There is one commercial tool that works with Google Calendar and it is very cumbersome and filled with issues that aren't apparent until you get into it. We have built a very slick semi automatic reminder tool. It will pull your appointments from Google, it will try to match them with your contacts. you can modify the matches if needed, and then when you click send email, it will notify each individually. Our tool does not have an integrated feedback mechanism at this time, but you would know if an email bounced. Technically, you could set this up on a timer to run ont it's own as well, but your name matching would have to be perfect which is unlikely. Outside of this you would need to consider another calendar tool outside of Google products for your calendar. Another option would be to build a Calendar inside of a google sheet, and that would enable perfect name matching, but it would likely be a less attractive calendar, but it may be fine for many users.
There is Hidden Header functionality associated with this worksheet that is not shown, and there is a small amount of Javascript that goes along with worksheet that is not shown.
7 - Email List (for newsletters, etc)
Opt in style email lists sound great. If you have more than one mailer the subscription and unsubscription process gets more complex. We've created a simple worksheet to manage this. One might think you can just use a field in our patients table for this, but what about folks who sign up for your newsletter who aren't patients. You could track them as separate types of contacts, or you can just keep a separate database, which is what we'd suggest. People don't change their email addressess that often. The initial setup is duplicative, but after that, you will seldom need to make lots of changes, and keeping them separate enables an easier build out of functionality.