Re: FAQ: FILTER(), LOOKUP(), MAXROW(), MINROW(), R... - Page 2

Steve · 05-11-2020 10:20 AM

Core concepts & functions

Adding & updating rows

Excel alternatives

Last row of the spreadsheet

Rows of this thing only

Miscellaneous

Steve

MAX(
  SELECT(
    table[column],
    match-expr
  )
)

FASHUR

Hi Steve, I was wondering... does this work on a per row level? Or should I do some sort of dereferencing? Im having a really hard time trying to implement a sheets formula in Appsheet and would appreciate your help

Steve

It can be done per row, but that could quickly cause performance problems. I suggest you start a new topic with a description of what you're trying to accomplish and we can work it out there.

Jonathan_S

Your a Beast

Steve

How are FILTER(), LOOKUP(), MAXROW(), MINROW(), REF_ROWS(), and SELECT() related?

FILTER(), LOOKUP(), MAXROW(), and MINROW() are all built upon SELECT(), so they all share similar capabilities and limitations.

FILTER("table", select-expression) is equivalent to:

SELECT(
  table[key-column],
  (select-expression)
)

LOOKUP(match-expression, "table", "match-column", "result-column") is equivalent to:
```
ANY(
  SELECT(
    table[result-column],
    ([match-column] = (match-expression))
  )
)
```
Note that if multiple rows match, the result value from only one row, chosen at random, will be returned.

MAXROW("table", "column", select-expression) is equivalent to:

ANY(
  SELECT(
    table[key-column],
    AND(
      (
        [column] = MAX(
          SELECT(
            table[column],
            (select-expression)
          )
        )
      ),
      (select-expression)
    )
  )
)

Note that if multiple rows contain the maximum value, only one row, chosen at random, will be returned.

MINROW("table", "column", select-expression) is equivalent to:

ANY(
  SELECT(
    table[key-column],
    AND(
      (
        [column] = MIN(
          SELECT(
            table[column],
            (select-expression)
          )
        )
      ),
      (select-expression)
    )
  )
)

Note that if multiple rows contain the minimum value, only one row, chosen at random, will be returned.

REF_ROWS("table", "ref-column") is equivalent to:

SELECT(
  table[key-column],
  ([ref-column] = [_THISROW])
)

J_jvs

Hi Steve,

but the output view it's not exactly as a Related view of ref_rows.

Lucinda_Mason

@Steve, This is excellent. Thanks for doing all this.

Steve

How do I get the last row of this thing only?

MAXROW(
  "My Table",
  "_ROWNUMBER",
  ([_THISROW].[Thing] = [Thing])
)

Replace My Table with the name of the table whose row you want; and Thing with the name of the column containing a value that identifies the thing you want (e.g., Order ID).

tvinci

So to get the NEXT TO LAST or PENULTIMATE row would this work?

MAXROW(
  "My Table",
  "_ROWNUMBER-1",
  ([_THISROW]-1.[Thing] = [Thing])
)

Steve

Nope. Try:

INDEX(
  ORDERBY(
    FILTER(
      "My Table",
      ([_THISROW].[Thing] = [Thing])
    ),
    [_ROWNUMBER],
      TRUE
  ),
  2
)

Edisson_Camilo_

@Steve there is some way to get only the next row to set data with an action?
Thanks

Steve

What do you mean by “the next row”?

Edisson_Camilo_

I mean, when update task number 1 “to completed”, in the next row 2 set estatus task to “To do” with an action.
thanks

Steve

The following expression will produce a list containing the row with the next higher row number:

TOP(
  ORDERBY(
    FILTER(
      "table",
      ([_THISROW].[ROWNUMBER] < [ROWNUMBER])
    ),
    [_ROWNUMBER],
      FALSE
  ),
  1
)

Replace table with the name of the table containing the rows.

Edisson_Camilo_

@Steve it works! thanks a lot i´m so glad.

Austin

Bookmarked and appreciated

Suvrutt_Gurjar

All very useful references @Steve. Thank you.

Steve

Is this a new row?/Does this row already exist?

Is this a new row?

NOT(IN([_ROWNUMBER], My Table[_ROWNUMBER]))

or:

ISBLANK(
  FILTER(
    "My Table",
    ([_ROWNUMBER] = [_THISROW].[_ROWNUMBER])
  )
)

Does this row already exist?

IN([_ROWNUMBER], My Table[_ROWNUMBER])

or:

ISNOTBLANK(
  FILTER(
    "My Table",
    ([_ROWNUMBER] = [_THISROW].[_ROWNUMBER])
  )
)

In the above, replace My Table with the name of the table about which you’re inquiring.

When modifying rows, such as in forms or with actions, expressions act on a temporary copy of the row. Only after the form is saved or action completed is the new row data saved to the table. Until saved, the data in the table remains as it was when the form was opened or the action started. Expressions that explicitly reference tables, such as table-column references and SELECT() and its derivatives, can be used to access the table directly, bypassing any pending changes in the temporary copy of the row.

See also: FILTER(), IN(), ISBLANK(), ISNOTBLANK(), List from Table-Column Reference, NOT()

Steve

What is the previous value of this column?

The simplest way is to dereference [_THISROW_BEFORE]:

[_THISROW_BEFORE].[Wanted Column]

Replace Wanted Column with the name of the column whose value you want.

LOOKUP() could also be used:

LOOKUP(
  [_THISROW].[_ROWNUMBER],
  "My Table",
  "_ROWNUMBER",
  "Wanted Column"
)

Replace My Table with the name of the table from which you want the column value; and Wanted Column with the name of the column whose value you want.

When modifying rows, such as in forms or with actions, expressions act on a temporary copy of the row. Only after the form is saved or action completed is the new row data saved to the table. Until saved, the data in the table remains as it was when the form was opened or the action started. Expressions that explicitly reference tables, such as table-column references and SELECT() and its derivatives, can be used to access the table directly, bypassing any pending changes in the temporary copy of the row.

See also: List from Table-Column Reference, LOOKUP()

Grant_Stead

You can also use the THISROWBEFORE /AFTER functions… They’re supposed to be for reports and such, but they seem to work fine in other expressions.

Jonathon

Uhh… thats news to me

Bahbus

Do you have an example where you used it successfully in other expressions?

Cando_Mayorga

¿y el valor anterior en una tabla dividida en paginas? es decir el valor ultimo de cada pagina?
https://docs.google.com/document/d/1ueakiQj0vVD142arkpW5scLnW4YP_4Bpwq-smFS-XtI/edit?usp=sharing
Y otra pregunta porque al generar el pdf queda tan distinto como esto:
https://drive.google.com/file/d/1o_MSln4WbeK56sBAfDnsJsPMIuxmq1m7/view?usp=sharing

Grant_Stead

@Bahbus and @Jonathon Here is an example in which I have an app formula looking toward a status column to pin an actual start date…

IFS(
OR(
	AND(
  [_THISROW_BEFORE].[status_code]<>"Not Started",
  [status_code]<>"Not Started"
  ),
  AND(
  [_THISROW_BEFORE].[status_code]="Not Started",
  [status_code]="Not Started"
  )
  ),
[_THISROW_BEFORE].[act_start_date],
AND(
[_THISROW_BEFORE].[status_code]="Not Started",
[status_code]<>"Not Started"
),
NOW()-
((TIME(
CONCATENATE(
  MOD(FLOOR([target_drtn_hr_cnt]), 24),
  ":",
  (MOD(((60 * 60) * [target_drtn_hr_cnt]), (60 * 60)) / 60),
  ":",
  MOD(((60 * 60) * [target_drtn_hr_cnt]), 60)
	)
)
- "00:00:00"
	) + (FLOOR([target_drtn_hr_cnt] / 24) * 24))
)

Grant_Stead

Grant_Stead

I might as well post the finish date time expression as well…
IFS(
AND(
[_THISROW_BEFORE].[status_code]<>“Completed”,
[status_code]=“Completed”
),
NOW(),
AND(
[_THISROW_BEFORE].[status_code]=“Completed”,
[status_code]<>“Completed”
),
“”,
AND(
[_THISROW_BEFORE].[status_code]=“Completed”,
[status_code]=“Completed”
),
[_THISROW_BEFORE].[act_end_date]
)

Bahbus

Hmm, I could have sworn that I remember @Aleksi or one of the other devs stating this wasn’t possible… But maybe they’ve been working on it. Or maybe it’s always been theoretically possible but has some sort of other limitation which is why they don’t emphasis it.

LeventK

[_THISROW_BEFORE] and [_THISROW_AFTER] works only when you actually SAVE the form and therefore both mainly works very well with the workflow triggering. Provided you want to compare a column’s value on a live form, then @Steve’s LOOKUP expression is the only way to achieve the desired target.

Aleksi

@Bahbus @LeventK Since May devs have been working with this one and that’s why you can use it with a normal app formula as well. Though I’m not sure if it’s fully rolled out for all accounts.

LeventK

@Aleksi
Thanks for tip Master Alien

Steve

Is [_THISROW] equivalent to before, or after?

Koichi_Tsuji

I m afraid i m not fully catching up with the topic diccuseed here.

I just now tested a app formula in physical column with expression like [_thisrow_before].[OtherColumnInTheSameTable] and saved the existing form with a change in other column. This column captured the “previous value”. I did not know about this is possible. I have been using [_thisrow_before] [_thisrow_after] expression on the place where I trigger the workflow, but I may use the expression in the normal physical column app formula to keep the tracking record of the previous value if it is required.

Aleksi

Equivalent with after… if I understand your request correctly.

Grant_Stead

Not gonna lie, I feel like I broke the internet… LoL
I’ve had this expression up and running for about 90 days.

Koichi_Tsuji

Hi Bro, I believe you just simply and nicely refresh the brain and mind of us! Thanks for giving a head up This is one of the interesting aspect of Appsheet. We believe it is impossible, but actually it BECAME possible wihtout noticing. haha.

Aleksi

Yeah, Grant is little bit guilty for this

Grant_Stead

Sorry for hijacking this thread, just thought I should point out that you don’t have to do the lookups…

I’ve also always wanted to try out the parent / grandparent / [_THISROW-n] expressions in app formula, but haven’t ran into the need yet… If any of y’all could confirm if those work, that would be a good piece of mind.

When it comes to Appsheet, I tend to just roll with, “if it works, it works.” I try not to implement too many multi layered hacky type things… but expressions, etc… They can’t easily take it away from us after so many people may be leveraging it in production applications.

Steve

It works.

Koichi_Tsuji

Hi Steve I could find a way to do it. Any quick sample and syntax?

Steve

List of the rows with the first occurrence of each distinct name:

filter(
  "example",
  (
    [_rownumber]
    = min(
      select(
        example[_rownumber],
        ([_thisrow-1].[name] = [name])
      )
    )
  )
)

[_thisrow-1] in the SELECT() expression refers to the row being examined by the enclosing FILTER() expression.

[_thisrow] (without the -1), were it present, would still refer to whatever row the FILTER() expression was being evaluated for, as normal.

The slice row filter equivalent to the above would be:

(
  [_rownumber]
  = min(
    select(
      example[_rownumber],
      ([_thisrow].[name] = [name])
    )
  )
)

FAQ: FILTER(), LOOKUP(), MAXROW(), MINROW(), REF_ROWS(), and SELECT()