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

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.

3 Likes

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

6 Likes

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

6 Likes

Yeah, Grant is little bit guilty for this :smiley:

2 Likes

Equivalent with after… if I understand your request correctly.

2 Likes

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.

2 Likes

It works.

3 Likes

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

1 Like

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])
    )
  )
)
5 Likes

What’s the point of “after” values? I’ve never understood why I would use that?

1 Like

@Steve Could we now do double dereference?


In this article we read that this is not possible. (under " Double Dereference Expressions")

Let’s say we have 3 Tables:
Parent
Child
Grandchild

In the Grandchild Table we want to get a value from the Parent Table.
s the article says, we need to create an extra VC in the Child Table and use this VC like a Bridge between the Parent and Child Table.

Could we now use something like [_thisrow-2].[Parent Name] in the Grandchild Table?
I tested a bit but with no luck.
But that would be very helpful and would save the world from too many VCs :joy:

2 Likes

Not to my knowledge. _THISROW-n is not a work-around for dereference chains.

1 Like

Back-references within nested queries ([_THISROW-n])

Consider a complex App formula with “nested” SELECT() expressions (SELECT() expressions within SELECT() expressions):

SELECT(
  table[column1],
  ...
  SELECT(
    table[column2],
    ...
    SELECT(
      table[column3],
      ...
    )
    ...
  )
  ...
)

Let’s call the row for which this entire expression is evaluated as its App formula the Origin Row.

Within this entire expression, we can refer to column values of the Origin Row by dereferencing [_THISROW]. For instance, to get the value of the Customer Name column of the Origin Row, we can use [_THISROW].[Customer Name].

Let’s also name each of the nested queries within the entire expression:

  • Query 1: SELECT(table[column1], ...) (outer-most query)
  • Query 2: SELECT(table[column2], ...)
  • Query 3: SELECT(table[column3], ...) (inner-most query)

Query 2 is nested inside Query 1. Within Query 2, column values of the row currently being examined by Query 1 can be accessed by dereferencing [_THISROW-1]. For instance, to get the value of the column1 column of the Query 1 row, we can use [_THISROW-1].[column1]. The -1 in _THISROW-1 refers to the query one step outside the current query.

Query 3 is nested inside Query 2. [_THISROW-1] refers to the query one step outside the current query, so within Query 3, [_THISROW-1] refers to the current row of Query 2. Query 2 is itself nested within Query 1, so Query 1 is two steps outside Query 3. [_THISROW-2] can be dereferenced to access the values of columns in the query two steps otside the current one: Query 1.

image

7 Likes

I’ve been trying to figure out a good use case, but I was struggling with the concept. But that chart makes it super easy. I hope you can add that image to the support docs at some point.

2 Likes

I hope to at some point. In the meantime, I’ve added a link to that reply to this FAQ’s index so it’ll have some prominence here.

2 Likes

Come to think of it. Images similar to this would probably help in several of the support docs detailing some of the more complex stuff.

6 Likes

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

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

Nope. Try:

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

Hey @Steve… You’ve expressed a complex thing with succinct elegance here… I’ve been struggling to grasp the ([_thisrow-n]) concept but I get it now… Cheers… :slight_smile:

2 Likes

Thanks a lot for this @Steve

1 Like