How to isolate a set of rows in a table referenced to another table?

Hi,
In my patient database app, I have a table called “Personal Details” that has a key column that generates unique IDs for each row. This table has all the personal details of patients of the entire database. Then I have 3 different tables called “Liver”, “Pancreas”, “Biliary Tract”. All patients in “Personal Details” table have been divided among these 3 tables based on the pathology they have (Liver diseases in “Liver” table, Pancreatic diseases in “Pancreas” table and so on). Each of these 3 tables (Liver/Pancreas/Biliary Tract), has a separate ‘Key’ column that records exactly the unique ID of the patients extracted from the “Personal Details” table as these 3 tables have been referenced as ‘Child’ tables for the “Personal Details” table which is the ‘Parent’ table.

What I want to do is to isolate the Unique IDs in the ‘Key’ column of the “Liver” table from the entire set of Unique IDs in the ‘Key’ column of “Personal Details” table using a SLICE, so that I can create various charts from the “Personal Details” table for just “Liver” patients (A row filter condition in the Slice).
Can I do this? Appreciate your help!
Thank you very much.

Hey Malaka.

What I’ve done in the past is to create slices for each individual sub-group I wanted to create; so for your example you’d create 3 slices (each of the Patients table):

Liver_Patients, Pancreas_Patients, and Biliary_Tract_Patients

To accomplish the slice filtering, you need a way to tell (on the patient level) whether or not someone has the presence of a “Liver” record, for example.

You said you’ve got things split into separate tables, yes? Using a reference between them and the patient table?

Then we can use the presence of a record in the [Related Liver] column; for your slices, try something like this:

isnotblank([Related Liver])

One slice for each of the Related tables you’ve got - so when someone creates a liver record, that patient’s [Related Liver] column is no longer blank, and it will then show up in the Liver_Patients slice.

Hope that made sense. :nerd_face:

2 Likes

Thank you so much for the reply.
I tried something else, and it kind of worked for me.
I had an intermediate table called “Diagnosis” in which the user could select whether patient’s diagnosis is Liver related/pancreas related/biliary tract related using an Enum type column called the [Involved organ]. So this Diagnosis table was recording all the UniqueIDs from the parent table “Personal Details”, as it too was a child table of the “Personal Details” parent table. And when the user selects “Liver” from the [Involved organ] Enum in the “Diagnosis” table, the app then opens up the “Liver” table (as I have referenced the “Liver” table to that “Diagnosis” table) so that the user can enter the liver pathology data at that point.
So what I did was, I created a virtual column in the parent table “Personal Details” called [Involved organ_VC] to grab the [Involved organ] column’s data in the “Diagnosis” table. For this purpose, I wrote the following app formula.
INDEX(SELECT([Diagnosis][Involved organ],TRUE),1)

This made [Involved organ_VC] in the “Personal Details” table to exactly act as the [Involved organ] column of the “Diagnosis” table that recorded either of the following three words “Liver”, “Pancreas”, or “Biliary Tract” (the values I’ve included in the Enum column properties of the [Involved organ] column in “Diagnosis” table). So afterward, I simply isolated the “Liver” word in this [Involved organ_VC] that is now in the “Personal Details” parent table using a row filter expression called [Involved organ_VC]=“Liver”, and that isolated all the liver patients and from there on I could simply chart the gender distribution/age distribution and other parameters that were collected in the “Personal Details” table pertaining to “Liver” patients.

I don’t know what I did was prudent or not, but somehow it worked. I’m wondering if what you’ve said could get the same results. Should check that out.

Thank you again for the reply. Appreciate it!

2 Likes

The way you went about it is exactly another way to do it! :smile: That’s one of the best things about the AppSheet platform, there’s X number of ways to do anything.

When I was typing out my explanation, this was actually the first method that came to mind: create a flag on the patient level - then you can slice based on that.

But trying to explain the whole SELECT([Diagnosis][Involved organ],TRUE) thing can be complicated. :wink:

I would create [Liver_Flag] or something like that, and use:

in(“Liver”, [Diagnosis][Involved organ])

Should give the same results - TRUE whenever there’s a Liver in the Involved Organs.

Happy it worked out!

2 Likes

Thank you so much!

1 Like