How can I create an expression that checks whether there is at least one related row that meets multiple conditions?

[jthurston]

We have a primary table used to track student information (“Students1”). We also have a connected table that stores related assessment and outcome records (“Assessments”). Each record in the “Students1” table will have multiple related records from “Assessments” that appear in the Students1 table in a list column.

I am able to successfully create an expression that checks whether a particular value is present in any of the related assessment records for a particular student. For example:

Contains([Related Assessments][Pre or Post], “post”)

If I understand correctly, this expression will check to see whether any of the student’s Related "Assessments records contains “post” in the [Pre or Post] column. Basically, does the student have a posttest.

Now, I want to create an expression that checks whether any one of the related rows has both a value of “post” in the [Pre or Post] column, and does not contain a “+” in the [Score] column. Does anyone have any advice on how to do this? Or maybe a resource you could link to?

The best I can come up with is:

AND(
Contains([Related Assessments][Pre or Post], “post”),
NOT(Contains([Related Assessments][Score], “+”))
)

However, I don’t think this works. For a particular student record, one related row might have “post” in [Pre or Post], and a second related row might not have “+” in the [Pre or Post] column. In that case, the expression would evaluate to true, right? But I want it to only evaluate to true if both of these conditions are true for a single row/record.

Thanks for reading, and for any advice you might have.

[SkrOYC]

What do you want exactly?

• A yes or no answer?
• The assessment in which both conditions where met?
• The student where the assessment in which both conditions where met?

[jthurston]

Hello, and thank you very much for answering. I think I want a yes/no answer, since I’m trying to count the number of students that have an assessment that meets both conditions. The expression is part of a larger expression, which I will share below in case it helps clarify. The ultimate goal is to generate a count of all students who have at least one assessment that meets both conditions. The students must have also registered between 07/01/21 and 11/11/2021 OR have registered before 07/01/21 and have attendance after 07/01/21.

COUNT(
FILTER(
“Students1”,
AND((Contains([Related Assessments][Pre or Post], “post”)),
OR(
AND(

DATETIME([Related SignUpSheets][Timestamp])>=“07/01/2021”,
DATETIME([Related SignUpSheets][Timestamp])<=“11/11/2021”
),

AND(
DATETIME([Related SignUpSheets][Timestamp])<“07/01/2021”,
DATETIME([Last Attendance])>= “07/01/2021”
)
)
)))

[Aurelien]

Hi @Justin_Thurston

You may want to make it more simple:

ISNOTBLANK(
  FILTER(“Students1”,
    AND(
      Contains([Related Assessments][Pre or Post], “post”),
      OR(
         AND(
              ANY([Related SignUpSheets][Timestamp])>=“07/01/2021”,
              ANY([Related SignUpSheets][Timestamp])<=“11/11/2021”
         ),
         AND(
              ANY([Related SignUpSheets][Timestamp])<“07/01/2021”,
              [Last Attendance]>= “07/01/2021”
         )
      )
    )
  )
)

I removed Datetime() as TimeStamp are supposed to be DateTime type.
I added ANY instead, as [Related SignUpSheets][Timestamp] will give a list of TimeStamp, and you probably want just one: I guess this is by construction, but you are supposed to have only one SignUpSheet per student ?

Also:

How can I create an expression that checks whether there is at least one related row that meets multiple conditions?

For this purpose, I replaced COUNT that would require a comparison with zero, with ISNOTBLANK() which will give a Yes/No output.

For reference:

help.appsheet.com

ANY()

One arbitrary list item

help.appsheet.com

List Dereference

Dereference the items of a list in bulk to produce a list of the resulting values.

help.appsheet.com

ISNOTBLANK()

Is value present?

Small tip: for displaying indented expression, you can wrap it with this:

[jthurston]

Hi Aurelien,

Thank you very much for the feedback, and all of the helpful links.

Each student will have one SignUpSheet.

To clarify, the goal of the full expression in my last post is to produce a number as an output, namely the total number of fall students with a valid posttest. In this case, COUNT() is what is needed, isn’t it?

I am having issues specifically with this part of the expression:

Contains([Related Assessments][Pre or Post], “post”)

This part checks to see if the assessment is a post test, which seems to work fine. However, I want to expand it so it checks whether there is at least one related assessment that both has “post” in the [Pre or Post] column and that does not contain “+” in the [Score] column. The “+” indicates the test is invalid, so I don’t want to include rows with that value.

Since [Related Assessments] produces a list, I don’t think I can simply add another Contains() expression. The reason being I might have one list item that meets condition A and another that meets condition B. What I really want to check is if there is an item in the list that meets both conditions. In other words, when I pull the list of related assessments, I want to only include the rows that both are post tests and do not contain “+” in the score column.

Thank you,

[SkrOYC]

I would do it a little bit different:

COUNT(
  UNIQUE(
    SELECT(
      Assessments[RefToStudentsColumn], /*Change it with the real names*/
      AND(
        [Pre or Post]="Post",
        [Score]="+",
        OR(
          AND(
            DATE([Timestamp])>=DATE("07/01/2021"),
            DATE([Timestamp])<=DATE("11/11/2021")
          ),
          AND(
            DATE([Timestamp])<DATE("07/01/2021"),
            DATE([RefToStudentsColumn].[Last Attendance])>=DATE("07/01/2021")
          )
        )
      )
    )
  )
)

BTW
Containts() is for strings/text. Like if “Justin” contains “u”.
In() is for lists of anything. Like if 43 is in the list “21, 32, 43”

help.appsheet.com

CONTAINS()

Does text contain fragment?

help.appsheet.com

IN()

Is item in list?

[Steve · Accepted solution]

Justin_Thurston:

I want to create an expression that checks whether any one of the related rows has both a value of “post” in the [Pre or Post] column, and does not contain a “+” in the [Score] column.

Easy enough, though it’s not clear exactly what you mean by this:

Justin_Thurston:

does not contain a “+” in the [Score] column

Would the [Score] value be exactly +? Or would the value possibly contain other characters, like +75? Is [Score] a numeric type column, like Number or Decimal? Or is it a textual type, like Text?

If [Score] would be exactly +, try this:

ISNOTBLANK(
  SELECT(
    [Related Assessments][_ROWNUMBER],
    AND(
      ("post" = [Pre or Post]),
      ("+" = [Score])
    )
  )
)

If [Score] is a numeric type column, and you’re looking for positive scores, try this instead:

ISNOTBLANK(
  SELECT(
    [Related Assessments][_ROWNUMBER],
    AND(
      ("post" = [Pre or Post]),
      ([Score] > 0.0)
    )
  )
)

If [Score] is a textual column, and the + would only occur as the first character if present, try this:

ISNOTBLANK(
  SELECT(
    [Related Assessments][_ROWNUMBER],
    AND(
      ("post" = [Pre or Post]),
      STARTSWITH([Score], "+")
    )
  )
)

If [Score] is a textual column, and the + might occur amid other characters, try this:

ISNOTBLANK(
  SELECT(
    [Related Assessments][_ROWNUMBER],
    AND(
      ("post" = [Pre or Post]),
      CONTAINS([Score], "+")
    )
  )
)

[jthurston]

A huge thank you to everyone. You’ve all been very helpful.

[Score] is a textual column that might have a + amid other characters. I used the expression Steve provided for that situation, and it seemed to work for our purposes. Hurray!

I believe the formula SkrOYC would have achieved the same result. It’s interesting to see different methods for achieving the same goal, and seeing those methods helps me understand the underlying logic a bit better as well.

[SkrOYC]

Justin_Thurston:

[Score] is a textual column that might have a + amid other characters.

COUNT(
  UNIQUE(
    SELECT(
      Assessments[RefToStudentsColumn], /*Change it with the real names*/
      AND(
        [Pre or Post]="Post",
        CONTAINS([Score], "+"), /*Good example of usecase for Contains()*/
        OR(
          AND(
            DATE([Timestamp])>=DATE("07/01/2021"),
            DATE([Timestamp])<=DATE("11/11/2021")
          ),
          AND(
            DATE([Timestamp])<DATE("07/01/2021"),
            DATE([RefToStudentsColumn].[Last Attendance])>=DATE("07/01/2021")
          )
        )
      )
    )
  )
)

Justin_Thurston:

It’s interesting to see different methods for achieving the same goal

Yep!