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

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

1 Like

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

2 Likes

Bookmarked and appreciated

1 Like

All very useful references @Steve. Thank you.

1 Like

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

Is this a new row?

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

Does this row already exist?

ISNOTBLANK(
  FILTER(
    "My Table",
    ([_THISROW].[_ROWNUMBER] = [_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. 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(), ISBLANK(), ISNOTBLANK()

5 Likes

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. SELECT() and its derivatives can be used to access the table directly, bypassing any pending changes in the temporary copy of the row.

6 Likes

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.

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
1 Like

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.

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

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