FIND A ROW VALUE IN A FILTERED LIST WHICH IS THE DATE PREVIOUS TO _THISROW DATE

AppSheet AppSheet Q&A
1

Untitled.jpg
Untitled.jpg937×423 28.4 KB

  • I have a table of “appointments” which has column “patients weight today”

  • The patients come in every few weeks and get weighed, I need the weight they had in their previous consultation

  • So patient came in 01 Jan weighed 100, then 30 Jan weighed 95, then 30 Feb weighed 92 and so on. I have a calculated value “Current Weight Loss” ("patients previous weight - “patients weight today”)

  • I’ve tried INDEX and SORT, SELECT, but its not working as I hoped, it worked when adding a row, but not when updating, so I am trying to make an action automation that will make sure I get the correct results in the table and update every time I update/edit a form

  • In the screenshot of the formula I was imagining getting a list of weights which were taken on dates before _THISROW and then expecting the index to get the very next date in the list but its not, where am I going wrong (I’ve spent hours on this, my wife uses the app in her business and she is so mad at how long this is taking me :slightly_smiling_face:

2

I suggest adding a new normal (not virtual) column to the Appointments Register table named (e.g.) Previous appointment of type Ref to Appointments Register and an Initial value expression of:

MAXROW(
  "Appointments Register",
  "DATE & TIME",
  AND(
    ([_THISROW].[PATIENT ID] = [PATIENT ID]),
    ([_THISROW].[DATE & TIME] > [DATE & TIME])
  )
)

Then you can access any column of the previous appointment by dereferencing this new column. For instance, to get the patient’s weight at the previous appointment:

[Previous appointment].[CURRENT WEIGHT (KG)]
3 Likes
3

Thank you Steve!!

This worked 100%