Row filter condition, show only useremail()-related data

[Ratatosk]

OR(CONTAINS([Ansvar],USEREMAIL()),CONTAINS([Eposter],USEREMAIL()))

The above is working ok, but I’d like to add another condition described below to the formula, but writing out it’s logic is beyond my skills…:

CONTAINS([Eposter], GroupAddress[GroupID]) if USEREMAIL() is the same as GroupAddress[UserEmail]

(GroupAddress is a table that contains the two columns UserEmail and GroupID generated from Google groups.)

Sorry in advance if my explanation isn’t good enough.

[Steve]

Please write out the question you need the expression to answer, in plain language (not pseudo-code).

[Ratatosk]

My apologies.

I have a Sliced table with row filter condition setup that filters a column called Eposter. In addition the the topmost formula on my previous post I’d also like to add this:

If the “appsheet username” is the same as the value in a row in the table GroupAddress’s column “UserEmail”, then I’d like to filter the column “Eposter” with the GroupAddress column “GroupID”.

(All values are e-mails.)

I hope this makes more sense

[Ratatosk]

Ok, I’ll try again. Hope this is more understandable.

I have a Slice where I filter out all work tasks by an email column from a Huge Table by appsheet’s Useremail(). In this column there are also google group email adresses on tasks that many share.

I also have another table where I import Google group email adresses and their members useremail adresses.

UserName____| Groups
Bob@work.com | Group1@work.com
Bob@work.com | Group2@work.com
Seb@work.com | Group1@work.com
God@work.com | Group2@work.com

What I would like is that if for example Group1@work.com is listed in the huge table’s email column I would like Bob and Seb to only see the rows in the huge table which their group is in. (and of course where their “appsheet useremail” is listed as well.)

[Suvrutt_Gurjar]

Based on your latest description , I believe your filter expression can be something like

OR([Email]=USEREMAIL() , IN([Email], SELECT( User Table[Groups], [UserName]=USEREMAIL())))

Here [Email] is the email column in the Huge table of tasks where the slice filter is applied. It is presumed that this column is email type column and contains any one email address (either group address such as Group1@work.com or individual email eddress such as Seb@work.com at a time)

User Table is the other table where you have Groups and UserName columns.

[Ratatosk]

Here’s the code so far. I get the error message.

Cannot compare List with Email in (GroupAddress[UserEmail] = USEREMAIL())

OR(
    CONTAINS([Ansvar],USEREMAIL()),
    CONTAINS([Eposter],USEREMAIL()), 
    IN([Eposter], 
      SELECT(GroupAddress[GroupID],GroupAddress[UserEmail]=USEREMAIL() ))

    )

The Column Eposter is a Enumlist, so I can type multiple e-mail addresses if I need. Maybe that is the problem?

[Suvrutt_Gurjar]

Ratatosk:

The Column Eposter is a Enumlist, so I can type multiple e-mail addresses if I need. Maybe that is the problem?

Yes, you are correct. That is the new input and I believe issue in the expression.

Also it sounds that you are using [GroupID] and [UserEmail] columns in the expression but they do not appear to be in the earlier shared details of UserName and Groups.

Could you please mention the exact structure and type of concerned columns in both the task table and Group Address tables with a a couple of rows of purely sample data as you mentioned in earlier post ? I believe that will help us to construct a proper expression.

[Ratatosk]

Yes, I’m sorry for confusing about the names.

Here are the columns in that is used by the slice.

Google sheets example:

Here are the GroupAddress table columns:

Google sheets example:

[Suvrutt_Gurjar · Accepted solution]

Thank you very much. Could you please try the following

OR(
[Ansvar] =USEREMAIL(),
IN([USEREMAIL(), [Eposter]),
COUNT( INTERSECT([Eposter],
SELECT(GroupAddress[GroupID],[UserEmail]=USEREMAIL() ) ))>0
)

Please test well for your requirements.

[Ratatosk]

OR(
CONTAINS([Ansvar],USEREMAIL()),
CONTAINS([Eposter],USEREMAIL()),
IN(USEREMAIL(), [Eposter]),
COUNT( INTERSECT([Eposter],
SELECT(GroupAddress[GroupID],[UserEmail]=USEREMAIL() ) ))>0
)

It works now! Woho!

Thank you so much Suvrutt for your expert guidance!

[Suvrutt_Gurjar]

Great ! Nice to know it works. You are welcome @Ratatosk.

I may mention here that we thank @Steve because he initially asked the correct questions that enabled us to know the background of your requirement.

[Ratatosk]

Yes, @Steve. Thank you for nudging me in the right direction. It’s hard to explain sometimes.

This forum and the people here are just great!

[Ratatosk]

Slight update:

OR(
STARTSWITH([Ansvar],USEREMAIL()),
STARTSWITH([Eposter],USEREMAIL()),
IN(USEREMAIL(), [Eposter]),
COUNT( INTERSECT([Eposter],
SELECT(GroupAddress[GroupID],[UserEmail]=USEREMAIL() ) ))>0
)

There was a bug in my app that made two people with almost the same name “rene@mycompany.com” and “irene@mycompany.com” to show up together. “STARTSWITH” fixed this.

[Suvrutt_Gurjar]

Thank you for update. You have created a good practical example use case for STARTSWITH()