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

Uhh… thats news to me :hushed:

2 Likes

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

2 Likes

@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))
)
4 Likes
2 Likes

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

3 Likes

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.

1 Like

[_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.

3 Likes

@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.

8 Likes

@Aleksi
Thanks for tip Master Alien :hugs:

Is [_THISROW] equivalent to before, or after?

1 Like

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.

4 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