How Perform Google Sheet equivalent Expressions/Functions

[Tony_Insua]

Hi @Steve
Maybe you could help answer this or point me to the right direction. I have a fictitious data set (table) in google sheets that is being populated by AppSheets shown below.

How would you get specific values that you’re looking for based on this table? For example, if I wanted to find the MAX distance for the AppSheet USER (filtering out users) for ACTIVITY = “RIDE”. Or another example, find out the highest elevation (also a MAX function) for the AppSheet USER (filtering out other users) for ACTIVITY = “RIDE”, etc.

In google sheets, I’ll typically use a QUERY function of the dataset and select the columns and set the parameters of what I’m looking for but I want to explore to AppSheets making these calculations for me instead of taxing google sheets to make the calculations.

Anyways, interested to hear your thoughts.
Thanks!

[Suvrutt_Gurjar]

Please try with an expression in a VC for distance

MAX(SELECT([DISTANCE], AND([ACTIVITY]=“RIDE”, [TEAMMEMBERID]=[_THISROW].[TEAMMEMBERID]))
Please replace [ELEVATION] for maximum elevation and so on.

MAX() needs the list to be of numeric type. So columns [DISTANCE] and [ELEVATION] need to be numeric type( Number, decimal, date etc)

In general too many VCs with expressions that span over multiple rows ( SELECT() for example) could impact sync performance. So please be aware of that aspect.

help.appsheet.com

MAX()

Highest item in list

[Tony_Insua]

Suvrutt_Gurjar:

MAX(SELECT([DISTANCE], AND([ACTIVITY]=“RIDE”, [TEAMMEMBERID]=[_THISROW].[TEAMMEMBERID]))

HI Suvrutt,
Thanks for pointing me in the right direction with this, particularly with VC’s being the the path to finding values in the data set.

When using your suggested expression, an error of
“Expression ‘MAX(SELECT([DISTANCE], AND([ACTIVITY]=“RIDE”, [TEAMMEMBERID]=[_THISROW].[TEAMMEMBERID]))’ could not be parsed due to exception: Number of opened and closed parentheses does not match.”

I’ve read the MAX() and the SELECT() help docs which are great to try and fix the error myself. I think what’s throwing me off is the AND() nested within the SELECT() function so I’m unable to figure out what’s wrong with it but perhaps you do?

One thought I had the could be an alternative to [TEAMMEMBERID]=[_THISROW].[TEAMMEMBERID] is perhaps using the [EMAIL]=USEREMAIL() which is also included in the data set.

Anyways, these expressions are opening up a new chapter for me in this AppSheet journey.
Thanks!

[Suvrutt_Gurjar]

Hi @Tony_Insua,

Sorry to have missed a closing parentthesis in the expression. Please try with

MAX(SELECT([DISTANCE], AND([ACTIVITY]=“RIDE”, [TEAMMEMBERID]=[_THISROW].[TEAMMEMBERID])))

Well, the AND() combines the filtering conditions so that the SELECT() comes out with a list that filters only those records haveing [TEAMMEMBERID] belonging to the [TEAMMEMBERID] of the record being evaluated and where [ACTIVITY] is only “RIDE”

[Tony_Insua]

HI @Suvrutt_Gurjar,
Thanks! I seem to have solved the issue with your help.
I’ve added the name of the table at the FRONT of [DISTANCE]. This has come back as TRUE.

MAX(SELECT(ACTIVITY DATA[DISTANCE], AND([ACTIVITY]=“RIDE”, [TEAMMEMBERID]=[_THISROW].[TEAMMEMBERID])))

Interesting to see that once the table name is “ACTIVITY DATA” is included, you don’t have to include it again with [ACTIVITY] or [TEAMMEMBERID]

[Suvrutt_Gurjar]

Yes, a table name was also required before distance

[Thomas_Varghese]

Tony_Insua:

ets to make the calculations.

hi
try out function select ,this is like the filter function on google sheet
also look for lookup function in appsheet help, there is no exact query like function in appsheet.

[Tony_Insua]

Just to add, the end game to these expressions will be to get all of these values I’m looking for and reference them on another table where I can create a dashboard view of the key metrics. The journey continues.

[Tony_Insua]

Hi
Hope you can entertain this question .
Now that I have the result of the LONGEST DISTANCE, how can I pull this specific value into another table?

What I’m seeing is that the expression on one table is not translating back to the new table.

So what I had done was created a new (read only) table with a column called LONGEST DISTANCE and included another column called RECORDID.

RECORID is a reference to the ACTIVITY DATA table.

In the LONGEST DISTANCE column of this new table, the app formulation I used was [RECORDID].[LONGEST DISTANCE] so that it can peers into the activity data table in search for this column.

I’m getting a blank result.

When I check back to the LONGEST DISTANCE (Virtual Column) in the ACTIVITY DATA table the result is a number but it’s just not translating back to the new table.

What are your thoughts on how to get this specifc value into another table?
Thanks in advance!