Combobox Display Fields Lookup if user email is in a sharepoint column

[misc]

Hi all, struggling to get my head around this one so hope someone might have a solution for me. 

I've put a combobox in my app, the data source being my SharePoint List. The DisplayFields is currently set to ["Title"] which lists all the reference numbers. Users will select a record and it will make a ViewForm visible of that data. 

I want to restrict access so that users can only select records in which they are named in one of 4 People Picker columns in that record (PI, RDM, BP, SubmittedBy), so I figure I need to filter the list so it only shows the relevant records, but I'm struggling to write any sort of LookUp/Filter on the Items or DisplayFields of the combobox which works. I have the current user (lowercase) email saved as varUserEmail.

Any suggestions? and many thanks in advance!

[cdwhite · Accepted solution]

Hi @misc ,

You can filter a gallery/table/collection/combo box items to only show records where the logged on users email address appears in one of the Person or Group columns in your SharePoint list, like so:

Filter(
    NameOfSharePointList,
    Person1Column.Email = varUserEmail || 
    Person2Column.Email = varUserEmail ||
    Person3Column.Email = varUserEmail ||
    Person4Column.Email = varUserEmail
)

------------------------------------------------------------------------------------------------------------------------------
If I've answered your question, please mark your post as Solved. You can accept more than one post as a solution.

If my response was a good one, please give it a Thumbs Up!

Visit my blog: https://platformsofpower.net

[misc]

Unfortunately that doesn't seem to be working. I tried with a simplified version to start, just 

but it is still showing me the whole list in the combobox

The PI column is currently empty for every record, so it shouldn't be returning anything.

[v-qiaqi-msft]

Hi @misc,

I strongly suggest you delete your current ComboBox for DisplayFields of Title and add a new one because I am not sure if you have other settings for this ComboBox, which may affect the formula we set for Items property.

Furthermore, please show me the error message of the red wave  from your formula?

[misc]

Hi Qi,

I do not get a red error line, it is as the screenshot above. The only error it sends is a delegation warning because I used Lower().

This combobox appears to be behaving differently than ones I've used in the past (I've never linked a data source directly to a combobox before, like you do a Form, and the displayfields property seems new. Has there been an update introduced which has changed how this input interacts perhaps?

In any case, I tried what you suggested and removing/replacing the combobox with no luck, but when I tried replacing with a Dropdown instead your formula did work so I'll stick with the dropdown instead.

Thanks for your help.

[misc]

Actually coming back to this again as the dropdown perhaps wasn't the solution I was looking for. 

If I use a dropdown instead of a combobox the Items property of my Form then breaks and is unable to pull through the correct record, so I'm leaning back towards using the Combobox and figuring out another solution for the filtered list unfortunately. Unless you know how to set the Item property of a form based on a DropDown rather than a combobox?

Thanks