Solved: Writing Expressions

tvinci · 04-30-2020 08:02 AM

Hi AppSheet,

I know how to articulate what I’m thinking in English, but writing the expression is the tricky part.

So as you see the entries are grouped by “Order” because multiple entries can have the same “Order” key.

So how do I specify when all entries of the same “Order” key all have the “How many samples collected?” column populated?

Thanks!

Steve

Try:

ISBLANK(
  FILTER(
    "delineatedLocationsV6",
    AND(
      ([_THISROW].[Order] = [Order]),
      ISBLANK([How many sample collected?])
    )
  )
)

FILTER("delineatedLocationsV6", ...) gathers all rows from the delineatedLocationsV6 table that match the given criteria (...; see (2)).
AND(..., ...) matches only if both criteria (..., ...; see (3) and (4)) are true.
([_THISROW].[Order] = [Order]) matches only rows of delineatedLocationsV6 with an Orders column value ([Orders]) that matches that of the current row of Route Finished ([_THISROW].[Order]).
ISBLANK([How many sample collected?]) matches only rows of delineatedLocationsV6 with a blank How many sample collected? column value.
ISBLANK(...) is true only if no rows of delineatedLocationsV6 have the same Order value as this Route Finished row and have a blank How many sample collected? column value.

See also:

View solution in original post

Kirk_Masden

If you haven’t done so already, please read this article about how to use SELECT():

I think the proper use of SELECT() will solve your problem.

tvinci

Thanks, so I retrieved the list. How do I check whether every single value in the list evaluates to true?

tvinci

So I’m trying to make it so that if 0 items are in the list the expression will evaluate to true. If 0 items are in the list then 0 items have the “How many samples collected?” column blank, so 0 items are unvisited. This still isn’t working though.

Kirk_Masden

Your use of COUNT() is good. I think, though, they you might need to adjust your strategy.

In your first post you wrote that you wanted to do the following:

So, I think you should count the number of records with the order key (that will be one SELECT() expression that is encased in a COUNT() expression) and count the number of records with the order key AND NOT(ISBLANK[How many samples collected?])). The second one would be another SELECT and COUNT combination. Then, if the two are equal to each other, I think your condition would be satisfied.

tvinci

Thank you @Kirk_Masden!

Is this what you had in mind?

Kirk_Masden

That’s what I was thinking. I’m glad Steve came up with a good solution.

Steve

Try:

ISBLANK(
  FILTER(
    "delineatedLocationsV6",
    AND(
      ([_THISROW].[Order] = [Order]),
      ISBLANK([How many sample collected?])
    )
  )
)

FILTER("delineatedLocationsV6", ...) gathers all rows from the delineatedLocationsV6 table that match the given criteria (...; see (2)).
AND(..., ...) matches only if both criteria (..., ...; see (3) and (4)) are true.
([_THISROW].[Order] = [Order]) matches only rows of delineatedLocationsV6 with an Orders column value ([Orders]) that matches that of the current row of Route Finished ([_THISROW].[Order]).
ISBLANK([How many sample collected?]) matches only rows of delineatedLocationsV6 with a blank How many sample collected? column value.
ISBLANK(...) is true only if no rows of delineatedLocationsV6 have the same Order value as this Route Finished row and have a blank How many sample collected? column value.