Help with selection expression

[Lucinda_Mason]

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.

[WillowMobileSys]

Lucinda_Mason:

AND both should have those with FAMILY_GROUP=ALL

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.

[Lucinda_Mason]

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

[WillowMobileSys · Accepted 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”)
    )
)

[Lucinda_Mason]

WillowMobileSystems:

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.

[Lucinda_Mason]

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”)))