Answered

Combo box to filter SharePoint list and a collection?

(0) Share

Report

Posted on by CrimsonCrayon

I am trying to filter a SharePoint data source column of items using both another column in the SharePoint list of email addresses and a collection (derived from a different SharePoint list) of only the email addresses from that list.

This works:

Filter('SPList1', StartsWith('Title',DataCardEmail.Text))

I would also like to include a collection, lets call it colSPList2Email as part of this filter. Can this be done?

For clarity:
SPList1 has 2 columns. Title (items) + Email (addresses)

SPList2 has 1 column of Email address and colSPList2Email collects this when the app loads.
DataCardEmail contains the current users email address that is using the form.

Categories:

Building Power Apps

All responses (8)

Answers (1)

Sort by

Drrickryp Super User 2024 Season 1 on at

Like
a
(0)

Report
Copy link

Link copied!

@CrimsonCrayon

Is this an Or condition for both lists or does the email need to be in both lists.
CrimsonCrayon 68 on at

Like
a
(0)

Report
Copy link

Link copied!

It would in an Or condition. If the user exists in either of the 2 lists, I want them to see the item.
Drrickryp Super User 2024 Season 1 on at

Like
a
(0)

Report
Copy link

Link copied!
@CrimsonCrayon

!IsBlank(LookUp('SPList1', StartsWith('Title',DataCardEmail.Text)) || !IsBlank(LookUp(colSPList2Email,emailcolumnname=User.Email))

for the visible property of the item. Replace emailcolumnname with the actual name of the email column in the collection.
CrimsonCrayon 68 on at

Like
a
(0)

Report
Copy link

Link copied!
Is your solution here to make the combo box invisible if their email address is not in either list?

I currently I have a condition in the Visible property based on a previous input item. I think your code above may have been missing a ) at the end of the first statement before the ||

I could not get the collection working so I decided to just try pulling directly from SPList2 and this is what I have come up with:

If(DataCardValue2.Selected.Value = "Request Type 2",true,false) && !IsBlank(LookUp('SPList1', StartsWith('Title',DataCardEmail.Text))) || !IsBlank(LookUp('SPList2', StartsWith('Title',DataCardEmail.Text)))

Is this what you are suggesting?
Drrickryp Super User 2024 Season 1 on at

Like
a
(0)

Report
Copy link

Link copied!
@CrimsonCrayon

Please try to get your head around the concept that you don't need to use an If(.. function in a visible property when the elements of the formula are already either true or false. It just complicates it.

DataCardValue2.Selected.Value = "Request Type 2"&& !IsBlank(LookUp('SPList1', StartsWith('Title',DataCardEmail.Text)) || !IsBlank(LookUp('SPList2', StartsWith('Title',DataCardEmail.Text))
Verified answer

CrimsonCrayon 68 on at

Like
a
(0)

Report
Copy link

Link copied!
In the end this worked for me:

If( CountIf(colGlobalWL, "Title" = varUserEmail)=1, Filter('SPList1', StartsWith(LocalWL, varUserEmail),"Title", Ascending), SortByColumns('SPList1', "Title", Ascending) )

colGlobalWL is a collection of email addresses pulled from SPList2. It represents people that should be able to see the entire SPList1 items. If they are not in this list then SPList1 gets filtered using the LocalWL column in SPList1. Unfortunately this column is a multiline text column so it results in a delegation warning still.
CrimsonCrayon 68 on at

Like
a
(0)

Report
Copy link

Link copied!

Thank you for helping me realize this concept and for taking the time to try to help me out here...
Drrickryp Super User 2024 Season 1 on at

Like
a
(0)

Report
Copy link

Link copied!

@CrimsonCrayon

Unfortunately, "in" is not delegated in SharePoint.