How to isolate a set of rows based on a dates

Hi,
I need a quick help.
I am trying to create a Slice with a row filter expression. The Slice’s name is “Chart Slice” and it is based on a Table called “Full DB” which records a lot of patient details. This “Full DB” has a column called [OT Date] which records operation theatre dates for each patient. I want to isolate a set of rows based on this [OT Date] column’s dates. The dates will be determined by the user who fills out a form in a different table called “Analyser”. In this Analyser table, there are 2 columns called [OT Start Date] and [OT End Date]. When the user fills out this start and end dates and save the form, it creates a new row in this Analyser table. I just want the Slice (“Chart Slice”) to grab the values of [OT Start Date] and [OT End Date] from the Analyser table’s last filled row, and isolate the rows in the Full DB based on the [OT Date] column that has recorded dates lying between these values determined by the [OT Start Date] and [OT End Date].

Can anyone please help me?

Many thanks to you!

Hi @djmalaka,

Could you please mention if you are using the app in mulituser environment? Also are two or more users likely to use the app and create the above mentioned slice during the same time period?

Hi @Suvrutt_Gurjar
Thank you for the reply.
Yes, it is used in a multiuser environment where 2 or more users are likely to use the app and create the Slice during the same time period. I’ve placed a UniqueID() for a key column inside the Analyser table to prevent creating the same combination.

Hi @djmalaka,

Thank you. So if the two users have two different date selections, I believe you are identifying those selections with USEREMAIL() ?

Could you mention if there is any specific reason you are considering incremental rows for each new slice conditions?Is it possible that you could have a user table and each user has a single row to have that user’s variable settings such as start and end date.or any other such variable parameters in your app?

I believe with user table , the variable parameters selection expressions into other places ( as in your mentioned slice in DB table) could be easier.

Hi @Suvrutt_Gurjar,

Although it’s an app where multiple users will be using, it’s mainly to maintain a single database of patients. I’ve created this orthopaedic theatre list app for my hospital’s orthopaedic team. The ortho team has multiple wards, each ward has a doctor to take care of the patients and he/she will input the patient data into a single table called Full DB. All the doctors in the unit have access to the same app.
So let’s say one person wants to look at some statistics from the Full DB table. But he/she wants to look at the statistics pertaining to a particular time period and not of the entire database. For that, I have created a set of charts and these sets of charts get the details not from the Full DB, but from the Slice that isolates the relevant set of rows the user defines by the Start and End Dates of Operation theatre dates. So I created this separate form using a separate table that records the Start and End Dates the user defines. I just want the Slice to grab these Dates so that the Slice can isolate the OT dates in the Slice falling between the Start and End Date that the user selected. Since the Chart view will be discarded after viewing it (as it is a ref view and not a permanent view in the app that gets generated only when the user fills out the Analyser table’s Start and End Dates), I want the Chart view’s charts to grab the rows isolated from the Full DB based on Analyser table’s last row’s Start and End Dates. So if another user wants to look at a different set of data pertaining to a different time period defined by his Start/End Dates, that user’s app will grab the last row he created on the Analyser table. Since only a handful of people will be using the app, this two or more users creating the same Slice won’t be a big issue.
I hope you understood what I’m trying to say here.
Thank you for your reply. Hope you’ll be able to help me with this as all the LOOKUP, SELECT, ANY and IN expressions I tried didn’t work for me.

Hi @djmalaka,

As per my understanding creatinng an expression based on moving rows( I mean additional rows for each selection) will need a bit more complex expressions. I believe the app logic will need to first grab the last row and then select the dates from that row. We can of course work on that,

  1. So first find the last row in the Analyser table in a VC column called say [LASTROW] in DB Table with an expression like

MAXROW(“Analyser”,"_ROWNUMBER", ([UserMail]=USEREMAIL()))

Here [UserMail] is the user’s email column in the Analyser table.

Then in the slice in DB table an expression can be something like

AND( [OT Date]>=[LASTROW].[OT Start Date], [OT Date]<=[LASTROW].[OT End Date])

2 Likes

That worked @Suvrutt_Gurjar!
Thank you so much.

There’s a small lag until the data gets saved and charts view grabs the new last row from the VC. So the user has to wait a bit until the charts change according to the last row the user saved, but that’s a small price to pay for the great analysis I can achieve! :smiley:

1 Like

Hi @djmalaka,

Thank you for the update. Good to know it works.

Yes, as I mentioned grabbing the last row from another table and then getting it for the further expressions in host table in VCs will introduce those lags.

Typically, expressions that work on multiple rows are time expensive. In this case MAXROW() as well as slice filter are in that category.

As I requested earlier you may evaluate concept of the user table or even user settings ( if you need this analysis less frequently). I believe they are likely to slightly improve processing time.

1 Like

@Suvrutt_Gurjar
I haven’t fiddled around with user table and user settings still. I’ll have to learn about them first. In the meantime, I’m trying to figure out another way to do this other than using a virtual column, like creating a LINKTOFILTEREDVIEW action that will be triggered at the end of filling out the Analyser table’s form view.

LINKTOFILTEREDVIEW(“Full DB”,AND([OT Date]<=Analyser[_THISROW].[OT End Date], [OT Date]>=Analyser[_THISROW].[OT Start Date]))

This actually takes me to a table view with the relevant rows isolated very fast as it doesn’t depend on saving data to the Google sheet. The only issue is trying to feed these rows to the Charts from thereon. As it’s only a virtual table (for lack of a better word) made out of the physical “Full DB” table, it doesn’t have a foothold for me to feed it to the Charts. Or is there a way that I don’t know yet? :thinking:

1 Like

Hi @djmalaka,

Got it.

You indeed have a very interesting idea. Even though at this point , I am unable to think how to further feed it into a chart as an input. It is like slice and chart view combined.

I am sure someone in the community may have ideas to achieve this or some other way.

I will also revert back in case I come across some concept

2 Likes

Thank you @Suvrutt_Gurjar
Really appreciate it! Great stuff you all do!

Hi @djmalaka,

Thank you but I must say that you have been very modest in your statement " Great stuff you all do!". I believe you must include yourself and many amazing app creators like you whose main professional specialization is different- you are a sergeon.

Some people like me at least have IT background, so no wonder we understand a bit of app logic. Competent app creators like you prove that human capability to learn is immense.and AppSheet is a great no code platform.

5 Likes

Yes @Suvrutt_Gurjar, I am a surgeon, but doing this is my passion.

I found out about AppSheet just about a year and a half ago when I was looking for a solution to maintain an efficient surgical database through a mobile phone. I spent days and nights trying to figure out the expression language you all use in AppSheet. I wouldn’t have been able to learn this platform if you all don’t constantly do an amazing job like solving issues when ever we come across them. I have learned so much from you, @Steve, @MultiTech_Visions, @LeventK, @Aleksi and many others and for that, I’m eternally grateful.

Sri Lanka although it has a really good healthcare system in the government sector, still lacks proper patient database management systems in government hospitals. Doctors maintain patient data on paper that is always predisposed to illegibility, loss or irretrievability. So we are unable to maintain and retrieve patient data for research and audit purposes most of the time, especially at ward and unit levels.

I have up to now created 5 good apps that work as really good solutions to pertaining issues in my hospital.

  1. An HPB app to manage the huge number of hepatopancreaticobiliary (my specialty) cases we receive at our hospital from all around the country as ours is an HPB unit in a tertiary care hospital.
  2. A Urology cancer app that records data and does analysis on all the kidney, ureter and bladder cancers in our country which is a multistation app.
  3. A colorectal app that records and upkeeps colorectal cancer patients in my hospital.
  4. A surgical logbook maintenance app for surgical trainees to keep tabs on the surgeries they do, obtain printable CSVs from the app itself and analyze their data based on different surgeries and their complications etc. (I wrote a 150 page manual stating how to create the app for anyone who is interested and the manual contains all the expressions I used to create that app, and so far a lot of surgical trainees have managed to create that app by themselves sticking to the steps in the manual)
  5. The orthopedic app I was recently making which solves a major crisis in operation theatre list management, and postponed cases.

The College of Surgeons in Sri Lanka recognizing the work I did, invited me to do a guest speech at the Annual Surgical Congress last year, and I talked about AppSheet and how it empowered me to find solutions for the issues we come across to a gathering of so many surgeons in the country including surgeons from the Royal College of Surgeons in England who were present at the audience when I did this. None of that wouldn’t have been possible if it weren’t for all of you! My gratitude to you therefore is absolutely sincere! :slight_smile:

6 Likes

Hi @djmalaka,

Absolutely excellent achievemnt and very inspirational! People like you who travel that extra mile make such feats possible. Very impressive to know that you already have built 5 functioning apps to improve critical aspects of your profession that is so critical for human society.

I am sure entire community will admire what feats you have achieved.

Congratulations and all the very best in your endeavors.I am sure you can count on community support in this endavor.

3 Likes

Thank you so much @Suvrutt_Gurjar

Congratulations on everything you have built to make the healthcare in your entire country better. Very impressive.

3 Likes

Thank you very much!