SELECT() statement assistance

[sloganuga]

I am using the following statement as a security filter to allow employees to only see their profit centers data based on the field [CenterNo]. I would like to allow users with “ALL” in the CenterNo field to see all data. What would be the best way to include that in the statement below?

AND(IN([Tract_No], SELECT(Tracts[TractNo], [Archive] <> “Y”)),IN([CenterNo],
SELECT(Employees[CenterNo],
[UserEmail] = USEREMAIL())))

Thanks,
Stephen

[WillowMobileSys]

sloganuga:

AND(IN([Tract_No], SELECT(Tracts[TractNo], [Archive] <> “Y”)),IN([CenterNo],
SELECT(Employees[CenterNo],
[UserEmail] = USEREMAIL())))

I think you just need to add an OR() function with a LOOKUP() for the ALL part like so:

AND(IN([Tract_No], SELECT(Tracts[TractNo], [Archive] <> “Y”)),
    OR(
       IN([CenterNo],SELECT(Employees[CenterNo],[UserEmail] = USEREMAIL())),
       "ALL" = LOOKUP(USEREMAIL(), "Employees", "UserEmail", "CenterNo")
    )
)

and if you wanted to maintain functional consistency you could change the Employee IN() clause like so:

AND(IN([Tract_No], SELECT(Tracts[TractNo], [Archive] <> “Y”)),
    OR(
       [CenterNo] = LOOKUP(USEREMAIL(), "Employees", "UserEmail", "CenterNo"),
       "ALL" = LOOKUP(USEREMAIL(), "Employees", "UserEmail", "CenterNo")
    )
)

I hope this helps!