cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
CrimsonCrayon
Helper I
Helper I

Combo box to filter SharePoint list and a collection?

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. 

1 ACCEPTED SOLUTION

Accepted Solutions
CrimsonCrayon
Helper I
Helper I

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.

View solution in original post

8 REPLIES 8
Drrickryp
Super User
Super User

@CrimsonCrayon 

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

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
Super User

@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.   

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
Super User

@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))

 

CrimsonCrayon
Helper I
Helper I

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.

Thank you for helping me realize this concept and for taking the time to try to help me out here...

Drrickryp
Super User
Super User

@CrimsonCrayon 

Unfortunately, "in" is not delegated in SharePoint.

Helpful resources

Announcements
Power Apps News & Annoucements carousel

Power Apps News & Announcements

Keep up to date with current events and community announcements in the Power Apps community.

Power Apps Community Blog Carousel

Power Apps Community Blog

Check out the latest Community Blog from the community!

Top Solution Authors
Top Kudoed Authors
Users online (4,119)