I am trying to get the list of TYPEs from a TYPE_SLICE where
ARCHIVE<>TRUE, and based on the PI_NO of the USER, either TYPEs with a FAMILY_GROUP = JFP or FAMILY_GROUP=GEO AND both should have those with FAMILY_GROUP=ALL
This expression works for GEO and JFP individually:
AND([ARCHIVE]<>TRUE,
IF(LOOKUP(USEREMAIL(), โUsersโ, โUSER_EMAILโ, "PI_NOโ)>3,
[FAMILY_GROUP]=โJFPโ,[FAMILY_GROUP]=โGEOโ))
But that does NOT include the records with ALL.
I tried several expressions but canโt get it to work. What am I missing?
AND([ARCHIVE]<>TRUE,
IF(LOOKUP(USEREMAIL(), โUsersโ, โUSER_EMAILโ, "PI_NOโ)>3,
SELECT(OR([FAMILY_GROUP]=โJFPโ,[FAMILY_GROUP]=โALLโ),
[FAMILY_GROUP]=โGEOโ,[FAMILY_GROUP]=โALLโ)))
Thanks.
Solved! Go to Solution.
Yes and you could make it a little easier to read by re-arranging it like this:
AND([ARCHIVE]<>TRUE,
OR(
[FAMILY_GROUP]=โALLโ,
IF(LOOKUP(USEREMAIL(), โUsersโ, โUSER_EMAILโ, "PI_NOโ) > 3,
[FAMILY_GROUP]=โJFPโ,
[FAMILY_GROUP]=โGEOโ)
)
)
I am not clear what โbothโ is referring to in the comment above?
However, in your second expression, the SELECT() doesnโt have the table specified it is operating against and then, based on my question above, we need to clarify how that last line:
[FAMILY_GROUP]=โGEOโ,[FAMILY_GROUP]=โALLโ)))
needs to fit into the expression.
I want the TYPE_SLICE to return records that have FAMILY_GROUP=JFP AND FAMILY_GROUP=ALL
if the USERโs PI_NO>3
and conversely FAMILY_GROUP=GEO and FAMILY_GROUP=ALL when PI_NO<=3
See attached data
Yes and you could make it a little easier to read by re-arranging it like this:
AND([ARCHIVE]<>TRUE,
OR(
[FAMILY_GROUP]=โALLโ,
IF(LOOKUP(USEREMAIL(), โUsersโ, โUSER_EMAILโ, "PI_NOโ) > 3,
[FAMILY_GROUP]=โJFPโ,
[FAMILY_GROUP]=โGEOโ)
)
)
Yes, that is better. Thanks.
Think I got it.
AND([ARCHIVE]<>TRUE,
IF(LOOKUP(USEREMAIL(), โUsersโ, โUSER_EMAILโ, "PI_NOโ)>3,
OR([FAMILY_GROUP]=โJFPโ,[FAMILY_GROUP]=โALLโ),OR([FAMILY_GROUP]=โGEOโ,[FAMILY_GROUP]=โALLโ)))
User | Count |
---|---|
40 | |
34 | |
29 | |
23 | |
17 |