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.
Solved! Go to Solution.
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.
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.
!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?
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))
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...