Solved: Pull a Virtual Column Calculation from One Table i...

Tony_Insua · 06-27-2020 02:15 PM

I have a Virtual Column with an app formula of that finds the MAX value of a column by the user. I want to pull this value into another table. How?

My current approach (which has not been working) was creating a reference column to the referenced table (with the Virtual Column Calc). Then I had a Column with the app formula [Reference Table Key].[Virtual Column Name] and the value comes back as nothing.

Help please.
Thanks!

Steve

Try:

INDEX(
  SORT(
    SELECT(
      ACTIVITY DATA[DISTANCE],
      AND(
        [EMAIL]=USEREMAIL(),
        [ACTIVITY]=“RIDE”
      ),
      TRUE
    ),
    TRUE
  ),
  2
)

View solution in original post

Steve

Try using LOOKUP() instead.

Tony_Insua

@Steve Thanks!
On a related note, is there an equivalent AppSheet expression to google’s LARGE((some list), 2) function that can find the second largest value in a list?

Not sure if it’s MAX( LIST(1, 2, 3) )

Cheers

Steve

Try:

INDEX(SORT(LIST(1, 2, 3), TRUE), 2)

See also:

Koichi_Tsuji

In case there are duplicate value in the list, this may help as well.

max({1,2,3}-list(max({1,2,3})))

Tony_Insua

Thanks for the suggestions @tsuji_koichi
I’ve read the help doc on MAX(LIST(1, 2, 3)); however, I’m not understanding where the list string goes.

Here’s my formula which gives me the MAX value of my distance which totally works! However, how can I modify this to find the SECOND LARGEST distance using this MAX(LIST(1,2,3) formula?

MAX(
SELECT(ACTIVITY DATA[DISTANCE] , AND([EMAIL]=USEREMAIL(), [ACTIVITY]=“RIDE” ) )
)

Koichi_Tsuji

Untested

max(SELECT(ACTIVITY DATA[DISTANCE] , AND([EMAIL]=USEREMAIL(), [ACTIVITY]=“RIDE” ) )-list(max(SELECT(ACTIVITY DATA[DISTANCE] , AND([EMAIL]=USEREMAIL(), [ACTIVITY]=“RIDE” ) ))))

Steve

Try:

INDEX(
  SORT(
    SELECT(
      ACTIVITY DATA[DISTANCE],
      AND(
        [EMAIL]=USEREMAIL(),
        [ACTIVITY]=“RIDE”
      ),
      TRUE
    ),
    TRUE
  ),
  2
)

Tony_Insua

Thanks @Steve this worked! And thanks for linking the help docs. I’ll learn this index and sort function more. Cheers.

Pull a Virtual Column Calculation from One Table into Another Table