Problem with expression

Lucinda_Mason · 05-26-2020 01:31 PM

I can’t get the last part of this expression to work. It does not pull all the data it should.

AND(
[SCOPE_NO]=3,[START_DATE]<=Today(),[END_DATE]>=Today()+7,

OR(
AND(LOOKUP(USEREMAIL(), Users, USER_EMAIL,FAMILY_GROUP)=“GEO”,
LOOKUP(USEREMAIL(), Users, USER_EMAIL, GEO_NO)=[GEO_NO]),

   AND([PI_NO]>4, LOOKUP(USEREMAIL(), Users, USER_EMAIL, FAMILY_GROUP)="JFP"),

   AND([PI_NO]=4, 
         OR(LOOKUP(USEREMAIL(), Users, USER_EMAIL, GEO_NO)=[GEO_NO],
                LOOKUP(USEREMAIL(), Users, USER_EMAIL, FAMILY_GROUP)="JFP")

)))

AND([PI_NO]=4,
OR(LOOKUP(USEREMAIL(), Users, USER_EMAIL, GEO_NO)=[GEO_NO],
LOOKUP(USEREMAIL(), Users, USER_EMAIL, FAMILY_GROUP)=“JFP”)

Does not pull the data from the “LOOKUP(USEREMAIL(), Users, USER_EMAIL, GEO_NO)=[GEO_NO]” part.

In this case, where PI_NO=4 I want it to pull all the records with GEO_NO corresponding to the USERs.GEO_NO and all the records where the USERs.FAMILY_GROUP=JFP

What am I missing? Thanks.

hru

How about:

IN(LOOKUP(USEREMAIL(), Users, USER_EMAIL, GEO_NO), Users[GEO_NO])

Steve

Your expression reformatted for my clarity:

AND(
  ([SCOPE_NO] = 3),
  ([START_DATE] <= TODAY()),
  ([END_DATE] >= (TODAY() + 7)),
  OR(
    AND(
      (
        LOOKUP(
          USEREMAIL(),
          "Users",
          "USER_EMAIL",
          "FAMILY_GROUP"
        )
        = "GEO"
      ),
      (
        LOOKUP(
          USEREMAIL(),
          "Users",
          "USER_EMAIL",
          "GEO_NO"
        )
        = [GEO_NO]
      )
    ),
    AND(
      ([PI_NO] > 4),
      (
        LOOKUP(
          USEREMAIL(),
          "Users",
          "USER_EMAIL",
          "FAMILY_GROUP"
        )
        = "JFP"
      )
    ),
    AND(
      ([PI_NO] = 4),
      OR(
        (
          LOOKUP(
            USEREMAIL(),
            "Users",
            "USER_EMAIL",
            "GEO_NO"
          )
          = [GEO_NO]
        ),
        (
          LOOKUP(
            USEREMAIL(),
            "Users",
            "USER_EMAIL",
            "FAMILY_GROUP"
          )
          = "JFP"
        )
      )
    )
  )
)

By my interpretation, your expression could be simplified and made vastly more efficient by rewriting it as this:

AND(
  ([SCOPE_NO] = 3),
  ([START_DATE] <= TODAY()),
  ([END_DATE] >= (TODAY() + 7)),
  ISNOTBLANK(
    FILTER(
      "Users",
      AND(
        (USEREMAIL() = [USER_EMAIL]),
        OR(
          AND(
            ("GEO" = [FAMILY_GROUP]),
            ([_THISROW].[GEO_NO] = [GEO_NO])
          ),
          AND(
            ([_THISROW].[PI_NO] > 4),
            ("JFP" = [FAMILY_GROUP])
          ),
          AND(
            ([_THISROW].[PI_NO] = 4),
            OR(
              ("JFP" = [FAMILY_GROUP]),
              ([_THISROW].[GEO_NO] = [GEO_NO])
            )
          )
        )
      )
    )
  )
)

Lucinda_Mason

Thanks Steve. I really appreciate it.

Steve

Please let me know if your problem persists. I didn’t specifically address it in my rewrite, in part because I didn’t see anything obviously wrong with your expression, based on my understanding (which may be wrong!). All my expression is is a rewrite of yours to replace the multiple *LOOKUP()*s with a single FILTER() that tries to apply the same logic.