MAXROW for a DERFERENCE

AppSheet AppSheet Q&A
1

Hi All,

I have a “google form” to collect “patient information”, the patient fills out the google form and that info goes to a sheet in my app table, in the google form there is an automated reference number which is the same as the PATIENTS (UNIQUE ID) in the app table. So I created the reference column [GOOGLE FORM REF] in the PATIENTS table, and it has an app formula = [UNIQUE ID]

I then have a DEREFERENCE to the google form response sheet in my PATIENTS table.
[GOOGLE FORM REF].[PATIENT TELEPHONE NUMBER]. This retrieves the patients telephone number, and then I have several other columns all using the similar formula to retrieve the data.

But sometimes a patient send a second response at a later date with updated info, how can I apply a MAXROW to the DEREFERENCE?

PS. I have tried adding another column that checks for the MAX Timestamp where [_THISROW].[UNIQUE ID]=[PATIENT CODE], but I am getting stuck trying to bring this result into the DEREFERNCE so that I get the latest information.

Before I had the info from the google form getting pulled in using spreadsheet formula to the google form responses sheet, but this kept giving problems and formulas jumped around, so now I have made all of it app formula with dereference.

2

I haven’t tried it, but I asume MAXROW([GOOGLE FORM REF].[PATIENT TELEPHONE NUMBER], “Date”) doesen’t work.

So you could maybe try: SELECT(PATIENTS[PhoneNo], [ID] = MAXROW(“GOOGLE FORM REF”, “Date”))

3

Hi Khuslid,

I tried like this:

SELECT(Form responses 6[STREET ADDRESS],[_THISROW].[UNIQUE ID]=MAX(“GOOGLE FORM REF”,“TIMESTAMP”))

Form responses 6 = Table Name (where the google form data comes to)
[STREET ADDRESS] = a column in Form responses 6
[UNIQUE ID] = the key value of the table I am searching from “PATIENT LIST”
“GOOGLE FORM REF” = The column ref to Form responses 6 in the PATIENT LIST table
“TIMESTAMP” = The column in Form responses 6 which contains a timestamp, I assume MAX can use this value?

I am getting an error "MAX function is used incorrectly "

4

I don’t know if a timestamp is the most accurate way. I would go with a boolean y/n column. Every time the form is opened that column is true, you collect what you need based on the true value, then you set that value back to false with an action.

5

There could be 3 or 4 rows of google form responses from the same person, I need the info from the latest form, I know the correct info could be in the date before, but I can throw the ball back at the patient to say their last form submitted was used for the billing.

The deref is working great in the PATIENTS LIST table, its just I cant get the latest form data.

Not sure if I am making sense

6

I made another column in the PATIENT LIST

[MAX TIMESTAMP](COLUMN TYPE = REF and Ref table = Form response 6)

Initial value (reset on edit)
MAXROW(“Form responses 6”,“Timestamp”,[_THISROW].[UNIQUE ID]=[PATIENT CODE (DO NOT EDIT)])

Then I dereferenced all the column info I needed from the Form response 6 table (google form replies) to [MAX TIMESTAMP], this seemed to return the result I was looking for, is this what you meant @Gustavo_Eduardo ?

Steve showed me this before but I was using within a table, but it works for any other table you might be searching in.

7

Have you tried creating a slice that returns the record you’re looking for?

Suppose you have a table you want to access. You can create a slice on that table, and the expression should be like this:

[id] = MAXROW(
               "TABLE",
               "changeTimeStamp",
               AND(
                    [Patient] = [_THISROW].[Patient]
               )
           )

This will return the IDs that meet the condition, meaning it will bring the most recent row ID from the table for this patient.

8

I need the data to be used for statements which I use a bot to send, so the data needs to be up to date, the slice will just show me in the app the latest data but could I then run the bot with the slice? (If so I never knew this), otherwise it working fine, when the patient appointment row gets edited it refreshes the patient info, then I put small time delay on the statement email so that it gives time for the patient update to reflect before looking for the patient info.

9

With bots you can execute actions and those actions can target rows that belong to slices. I don’t see why it wouldn’t work if you structure it like that.

1 Like
10

Thank you I will try this aswell.

1 Like
11

Ok Adam, otherwise we will have to resort to Steve’s magic.

1 Like
12

The PATIENTS table should have a virtual column named (e.g.) GOOGLE FORM ROWS that gathers all of the form responses for each patient with an App formula expression of:

REF_ROWS("Form responses 6", "PATIENT CODE (DO NOT EDIT)")

The GOOGLE FORM REF in the PATIENTS table should be a virtual column with an App formula expression like this:

ANY(
  ORDERBY(
    [GOOGLE FORM ROWS][key column],
    [Timestamp],
      TRUE,
    [_RowNumber],
      TRUE
  )
)

In the above, replace key column with the name of the key column of the Form responses 6 table.

The GOOGLE FORM REF column will then always point to the latest form response.

3 Likes
13

Thank you Steve, I managed to get it working using previous logic you taught me some time back, I mentioned it above in the thread, I believe its close to your final (probably optimised) solution here.

I called the column “MAX TIMESTAMP”

MAXROW("Form responses 6","Timestamp",[_THISROW].[UNIQUE ID]=[PATIENT CODE (DO NOT EDIT)])

But I made it an actual column, would it be better to make it a virtual?

1 Like
14

A normal column will only catch the latest form present at the time the patient row is updated. If that’s good enough then it’s good enough!

2 Likes