Writing Expressions

[tvinci]

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!

[Kirk_Masden]

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

help.appsheet.com

SELECT()

Gather column values from row in table or slice

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:

tvinci:

all entries of the same “Order” key all have the “How many samples collected?” column populated?

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 · Accepted solution]

Try:

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

1. FILTER("delineatedLocationsV6", ...) gathers all rows from the delineatedLocationsV6 table that match the given criteria (...; see (2)).

2. AND(..., ...) matches only if both criteria (..., ...; see (3) and (4)) are true.

3. ([_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]).

4. ISBLANK([How many sample collected?]) matches only rows of delineatedLocationsV6 with a blank How many sample collected? column value.

5. 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:

help.appsheet.com

AND()

Are all true?

help.appsheet.com

FILTER()

Select rows of table or slice

help.appsheet.com

ISBLANK()

Is value absent?

[tvinci]

Thank you so much @Steve! I greatly appreciate it

Should I add a “Route Finished” column to my delineatedLocationsV6 table? I’m going to try that. I made tiny changes to the formula you gave me because when I copy and pasted it it gave me errors. The result was that every single Route Name was crossed out. I’m going to try to see what happens when I add a column.

[Steve]

What table is the Route Finished format rule attached to? And what column in that table ties that table’s rows to rows in delineatedLocationsV6?