If statement to return true if any item in a table(?) is in a multi-choice Lists record

[Dracwyrm]

Hi all,

I'm starting out in Power Apps, so not sure I will have the terminology correct.

I wanted to create what I thought would be a simple employee absence reporting app, as there's a lot of people to notify, and want just one place they can go to send it to everyone that requires the information. In the organization, there are different departments and each department has their own contact information and lead. An employee can work a different department, sometimes more than one in a day, so a lot of information to keep track of for someone feeling ill.

The set up:
I have three SharePoint Lists: Absence Log, Department Information, and Permissions.

Department Information contains a list of the department names, the group it belongs to (similar departments are grouped under a group lead), and email address.

Absence Log contains the Date and time an employee sent in the notification, their email, name, reason for absence, if it's been adjusted in payroll, and a lookup to Department Information using multi-choice feature and pulls in the group and emails columns as well.

Permissions contains a Leads name and all the departments they are in charge of using a multi-choice selection.

There's one app for all leads, but they only need to see the people in their department.

Through lots of trial and error, I managed to make the part of an employee submitting multiple departments work and creating the entry in the Absence Log, which wasn't easy for a beginner. The record has all selected departments with the groupings and emails. The groupings and emails record, according to the errors I'm getting in Power Apps, are in table formats in those cells?

I'm looking to build an if statement for matching the current user to what they are allowed to see, which I use the user email function to get the current user to compart to. Any of the departments listed in the ViewDepart column of the Permissions list needs to match any of the departments in the Department column from the Absence Log list. I've searched, but maybe I don't know enough to know that I'm looking at the answer or just nit finding it. I would be grateful if you could let me know the terminology as well. 🙂 I did manage to find how to do an if statement with trying to match a single item in a multi-choice, which involves a CountIf statement being greater than 0, but not a multi-choice with a multi-choice.

Thank you for the help in advance. Have a good day everyone.

[v-mengmli-msft]

Hi @Dracwyrm ,

Do you want to limit the permissions of leaders and employees? I found that only Leader seems to be collected in your Permissions list. Could you tell me what type your Leader column is? There will be some differences when taking the value.

Maybe you can try this If you want to control the display content of different users through the If function:

I set a text label and set the Visible of it like this: The Email is a Person type column in my table.

If(IsBlank(LookUp(TableName,User().Email=Email.Email)),false,true)

It means that this control is only visible to the current user who is the Leader.

If the Email is a text value, you should change it:

If(IsBlank(LookUp(TableName,User().Email=Email)),false,true)

Could you provide me with more information if this is not what you expected? You can also attach some pictures of your App and lists at your convenience.

Note: Please try to clearly describe one problem in one case, if you have other problems, you can initiate another case.

Best regards,

Rimmon Li

[Dracwyrm]

Thank you for taking the time to reply. 🙂

First, I didn't know yo could store an email in a person type, which is really cool, so I've been using single line of text.

I'll describe it the only way I can think of it and that is Excel terms.

I'm trying to compare one column from one list to another column of another. The reason text box can only be visible to a senior leader for private and confidential reasons, but the whole leader team needs to see who will be absent to make plans. This is why I need an if statement on the reason text box to hide it or not.

In Absence Log list, there is a column that is of type lookup with multi-choice selection enabled, called Departments. Departments column will contain 1 or more departments.

In Permissions list, the column is a multi-choice column with the departments that a leader needs to access, called ViewDepartments. This column will contain 0 or more departments (Junior leaders need to see who's missing, but not the reason).

When accessing a "cell", one record?, of column Departments in Absence Log, since there are multiple departments listed in that "cell", power apps called it a table when I tried working with it (a table in a cell of a record), same for ViewDepartments.

The if statement needs to be true if at least one department name in the ViewDepartments "cell" of Permissions for the current user is in the Departments "cell" for an employee, and false if none match or 0 items. The trouble is that each "Cell" has a table, so I'm comparing a table to a table from cells. Is this more clear?