I’m trying to find a way to extract and calculate multiple values for a single row. Below is a screen shot to help give you an idea of the data I’m working with:
In this table, you’ll see that there are multiple outcome measures and test scores for the same patient in Column 1 (e.g. Danilo D. in rows 7 & 8).
What I would like to do is calculate averages test scores for different outcome measures for each individual patient. For instance, if Patient A has 12 different TUG test scores, I would like to calculate Patient A’s average for all of their TUG scores.
I’m assuming this will involve the use of relations, but I’m struggling to figure out the exact configuration necessary to get the data manipulated how I’d like. If anyone can help out, I’d be super grateful! Thanks in advance for your time!
Create a multiple relation that joins the patient name to itself. Then you can do rollups through that relation.
I would recommend using a PatientID (RowID) instead of patient names. This will avoid issues if you ever have duplicate names, or different spelling of the same name.
Actually, If I’m understanding correctly, you want to first create a Template column that joins ‘Patient’ and ‘Outcome Measure’. Then follow @Darren_Murphy’s instructions using the template column to create the relation.
@Darren_Murphy@Jeff_Hager Thanks so much for the quick responses! I’m still getting used to Glide, so please bear with me as I try to implement your suggestions.
I have already set up multiple relation that joins the patient name to itself (or at least I think that’s what I’ve done below):
Thanks for the help Thinh! I think you’re suggestions is similar to what @Darren_Murphy and @Jeff_Hager also had in mind, so that’s a good sign that this approach is correct.
I will try my hand at implementing it this way and see if that works. Thanks again!
The option that Thinh gave you is good if you only have small number of fixed test types. But if the list is more than a handful, or is likely to grow, then you may need to consider a different approach.
Yeah, okay. I suspected that might be the case.
One question - when viewing this data in the user interface, will you generally just be looking at one patient at a time, or would you need to present the aggregated data for multiple patients at the same time (on the same screen)?
I am anticipating both of those scenarios being used quite a bit. Clinicians using this tool will want to see average test scores for individual patients, but also average test scores for an entire patient population as well.
Okay, let’s deal with the individual patient case first.
I’ll assume the following:
You have a Patients table, that has one row per patient
You have a Test Results table, with all test results for all patients
You have a Tests table, with one row per test type (TUG, 10MWT, etc)
You have a Users table, with User Profiles configured, and your Clinicians will be signed in users
Somewhere in your App, you will present a screen with a list of Patients, and clicking/tapping a patient will take you to that Patients details screen.
Given all the above, proceed as follows:
In your Users table, create a User Specific text column (this will be used as a temporary storage for a Patient Name when viewing an individual patient record)
In your Test Results table, create a template column that joins the Patient Name and Test Name (you probably already have this).
In your Tests table, create the following columns:
– A template column. This will join the Test name with the Patient Name that is stored in the Users table.
– A multiple relation column that matches the template column with the template column in your Test Results table.
– Now you can add rollup columns as required
On the screen where you list your Patients, you will need to create a custom action on the list as follows:
– Set Column Values → User Profile → User Specific Column, set to Patient Name
– Show Details Screen → This Item
The effect of all of the above is that every time you select a patient from the list, their name is set in the User Specific Column in your User Profile row, and this in turn is used to dynamically aggregate the test results for that patient in the Tests table. So you can present the data from the Tests table on the Patient details screen, and it will always show the correct data for the patient being viewed.
For the second case where you want aggregated data for all patients, I think the simplest way to deal with that is to create an additional multiple relation column in your Tests table that matches the test name with the Test name in your Test Results table, and then add rollups on top of that.
I hope all the above makes sense, let me know if any of it is unclear.
One thing I would (strongly) recommend is to add a RowID column to your Patients Table and use that as a PatientID. And store this in your Test Results table instead of the Patient Name. This will be a much more robust approach. If you needed any patient data in the Test Results table, all you’d need is a Single Relation that matches the PatientID with Patients->RowID, and then you can add Lookups on top of that relation to fetch the patient name and/or any other patient details.
Thank you so much for taking the time to put together this information for me - it is GREATLY appreciated!
It may take me some time to try and implement your suggested structure, but I will be sure to circle back and let you know if I have additional questions (or, more hopefully, if the implementation was successful).
Thanks again Darren! You and the other experts in the Glide community have been unbelievably helpful!
