[_THISROW] in IN() Isn't Working

AppSheet AppSheet Q&A
1

Strange one, but I’m hoping opening it up might shed some light.

[Status] & " (" & COUNT(SELECT(People[Status], IN([Status], {[_THISROW].[Status]}))) & ")"

This little snippet displays the page name, with a count of the relevant entries in a separate table (the one you are linked to if you click). For example:

Happy People (5)

In isolation, [_THISROW].[Status] operates correctly, pulling the text through that I’m expecting, in our example it will display “Happy People”.

When I use it inside this IN() function, it doesn’t seem to pull anything.

[The expression is using _THISROW because it is used as a single function on multiple records, and if you click another record you may be looking for “Sad People” instead, and it seems a whole lot neater than writing one function per row.]

Any ideas what’s going on and how I might fix it?

2

If each row’s value in the [Status] column isn’t a list, then IN is not the appropriate function. Perhaps you just want [Status] = [_THISROW].[Status].

2 Likes
3

Love a quick fix, thank you.

Funny how you can’t see the obvious sometimes.

Just out of curiosity, is there a way to do this as at row level, rather than with a VC?

4

I’m not sure what you’re asking.

If you’re seeking to maintain in each row a value that aggregates information across multiple rows of the same table, then there are two options:

  • Use a data source column and ensure that when rows are revised (i.e., added, updated, deleted) there’s some trigger to recalculate all impacted rows.
  • Use a virtual column.