The problem seems to be with the IN([TEAM line. It does NOT find the matches it should.
[TEAM_NO] from the record is always a single value.
TEAM_NO from the USERS table can have one or more values
What am I missing?
IN([TEAM_NO], LOOKUP(USEREMAIL(), Users, USER_EMAIL, TEAM_NO))
The list below SEEMS to be what I want.
ALL these statements are true:
…1: (The value of column ‘SCOPE_NO’) is equal to (10)
…2: (The value of column ‘START_DATE’) is less than or equal to (TODAY())
…3: ((The value of column ‘END_DATE’ + 7) is greater than or equal to (TODAY())
…4: This statement is false:
…(The value of column ‘TEAM_NO’) is empty
…5: (The value of column ‘ANSWERED’) is equal to (“Keep Praying”)
…6: (The value of column ‘TEAM_NO’) is one of the values in the list (One randomly chosen value from this list (
…The list of values of column ‘TEAM_NO’
…from rows of table ‘Users’
…where this condition is true: ((The value of column ‘USER_EMAIL’) is equal to (USEREMAIL()))))