Tracking Patient Encounters

[mtcolon]

Hello, I’m new to the AppSheet community, and I’m trying to determine whether AppSheet is the right solution for interaction with my data. I am trying to store records of physicians’ encounters with patients, and our data is unfortunately dictated by existing codes and processes used in the healthcare industry.

To describe the model, each time a physician sees a patient, he logs the patient’s identifier on a note card and writes down at least one or several procedures he performed during the encounter on that date. Each procedure is described by a static set of CPT codes published by the American Medical Association (also known as HCPCS codes published by CMS). Each encounter has only one physician and one patient, but may have multiple CPT codes. Each CPT code belonging to an encounter is unique for the set of CPT codes associated with that encounter and may be associated with zero, one, or many modifier codes.

For example, Physician A may see Patient X on Jan 1, 2022 and on Jan 2, 2022 and perform several different procedures (coded by CPT codes) on each day. It’s a complex system that unfortunately reflects the complexities of medical coding and claim submission and requires at least two many-to-many relationships.

A proposed database diagram is here:

Ideally, the UX would allow a physician to sign into the app, add a new encounter, select an existing patient or add a new patient ID for the encounter, add one or more CPT codes from a drop-down for that encounter, and add one or more modifiers from a drop-down for each CPT code.

I understand that AppSheet does not natively support many-to-many relationships but that users have found ways around this using virtual columns. I’m just not certain that AppSheet is the best way to record this data without native many-to-many support.

I’d appreciate any suggestions on whether this is a project worth building in AppSheet. Thanks!

[WillowMobileSys]

mtcolon:

and requires at least two many-to-many relationships.

Where are the many-to-many relationships? I don’t see any depicted in your diagram.

There is a lot of confusion about many-to-many relationships - what they actually are and how they are dealt with.

I have seen a lot of comments that seem to imply there is some easy way for database to handle many-to-many relationships. The fact is relational databases CANNOT directly handle these. You have to create a bridge table.

And that is exactly what you can do in AppSheet.

Based on what I have seen in your diagram…AppSheet can absolutely handle that data structure. Based on my experiences, the system you are describing can be handled in AppSheet - HIPPA and all!!

[mtcolon]

WillowMobileSystems:

Where are the many-to-many relationships?

Thanks for the quick response. The many-to-many relationships conceptual, as you indicated. In the diagram, they are broken down into one-to-many relationships using a bridge table, as you suggested. The two bridge tables are Enconters_CPTs and Encounters_CPTs_Modifiers.

In my experience, relational databases do a pretty decent job of handling these relationships with joins (SQL Fiddle), but I’ll agree that they don’t directly handle such relationships.

WillowMobileSystems:

And that is exactly what you can do in AppSheet.

Several of the posts I’ve read in the community seem to indicate that virtual columns containing enum lists or similar hacks were required to implement these relationships and performance might be reduced. With the nested relationships in my model, I was concerned that AppSheet might not be the right solution.

It’s good to hear your feedback though. I’ll dig in and give it a shot!

[WillowMobileSys]

mtcolon:

Several of the posts I’ve read in the community seem to indicate that virtual columns containing enum lists or similar hacks were required to implement these relationships and performance might be reduced. With the nested relationships in my model, I was concerned that AppSheet might not be the right solution.

Not required but can be made more user friendly with some customization using selection lists. Note that AppSheet automatically creates Virtual Columns for you for the child lists.

VC’s are not generally an issue except in two cases:

1. For unnecessary things. E.g. A label you want to copy from another table that MIGHT change rarely. It’s easy to throw in a VC to grab it. BUT, if the table you place it on gets big OR you add LOTS of these type of VC’s they can degrade performance. VC’s are calculated for each row on every sync.

2. Overly complicated VC’s either for complex calculations (sometimes cannot be avoided) or for complicated row selections. Again on large tables these can chew up performance since calc’s on each row on each sync. For these I look to break up the calculation looking for more static parts that maybe can be done with actions only when needed then leave the parts that needs to be updated on Syncs for the VC’s

---

You haven’t said the expected size of your datasource. I have read recently of developers having tables in the hundred thousand range and performance seems good. I personally have created several systems with many table in the thousands of rows (a couple tables in tens of thousands) and noticed only slight slowness compared with a new app - mostly in the Editor on Saves and manual Syncs.

There are a number of things that can be done, such as Security Filtering, that limits data to only what the end user needs. This helps keep the performance snappy for the end users.

I strongly believe an AppSheet system can serve you well. And if you construct your datasource with the idea of moving to a database (assuming you are starting on sheets), it will be super simple to migrate when the time is right.

If there is anything I can do to help your system along - I do provide development and consulting services.