Filter Sharepoint list by Collection

(0) Share

Report

Posted on by jackwu

Hi ,

I'm new to Powerapps.

I'm designing a view table in powerapps to show all the IDs which submitted by users.

I have an 'idcollection' which contain all IDs submitted by users. Now I want to filter the Sharepoint list to only show these submitted items by IDs.

I write the code as , filter(SPlist, ID in idcollection). But no items showed up. May i ask how I can use filter function to filter out the items by the IDs in collection?

Thanks!

Categories:

Governance & administering

I have the same question (0)

All responses (8)

Answers (0)

Sort by

v-qiaqi@microsoft.com Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!
Hi@jackwu,
Based on the issue that you mentioned, do you want to filter the SP list based on the submitted ID within the 'idcollection'?
Could you please share a bit more about your scenario?
I assume that you have a Collect() function within the Submit Button and collect every ID once submitting it.
I have a test on my side, please take a try as below.
Set the OnSelect property of the Submit Button as below:

SubmitForm(EditForm1); Collect( idcollection, EditForm1.LastSubmit.ID )

Note: Every time you submit the form, a corresponding ID will be collected to 'idcollection'.
Add a Gallery to filter the SP list based on the 'idcollection', you can set the Items property of the Gallery as below:

Filter(APAC1,ID in idcollection)

Note: APAC1 is my SP list name.
Set the Text property of the Label within the Gallery as below:

ThisItem.ID

Best Regards,
Qi

Was this reply helpful? Yes No
jackwu 25 on at

Like
a
(0)

Report
Copy link

Link copied!

Hi @v-qiaqi-msft ,

I did exactly the same like you mentioned.

firstly , I have a button with the formula below.
SubmitForm(Form2);
Set(varlastsubmit,Form2.LastSubmit.ID);
Collect(IDlistCollection,
{
ID_Collect: Form2.LastSubmit.ID
})

And then I have a data table to collect the items.
Filter('SPList Name', ID in IDlistCollection)

The ID is 'Thisitem.ID'.

But there is a warning like the screenshot. After I add several items into SP, IDs was collected in the collection. But no item displayed in the data table.

Was this reply helpful? Yes No
jackwu 25 on at

Like
a
(0)

Report
Copy link

Link copied!

Oh, I got the solution , I create the ID collection as a table. So I need to filter by the ID_collect of the collection table.

Filter('Homologation Test', ID in IDlistCollection.ID_Collect)

Now the data table can list the items by ID filter.
But there is still a delegation warning. may i ask how I can fix this warning?

Was this reply helpful? Yes No
v-qiaqi@microsoft.com Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!
Hi@jackwu,
How is your data set, is it over 500 records or more than that?
Actually, if you want to collect every submitted ID to a collection and then filter the Gallery based on this collection, it will appear this delegation warning. The IDlistCollection is a Table, ID is a single value, this kind of filter result will do result in a delegation warning.
Here are some suggestions you can follow:
Move to Advanced settings within App Settings, change the 500 by default to 2000 in "Data row limit for non-delegable queries".
Since you want to filter the Gallery based on the LastSubmit.ID, you don't need to store it into a collection, you can directly quote the LastSubmit.ID within the Filter() function.
In response to the second solution I just mentioned, I have a test and it works on my side, you can take a try as below:
Set the Items property of the Gallery as below:

Filter('Homologation Test',ID=EditForm1.LastSubmit.ID)

Note: You can replace the corresponding control name with yours.
Just in case LastSubmit.ID isn't particularly stable sometimes, you can quote the 'Modified' which is a default column within the SharePoint list.
First(Sort(APAC1,Modified,Descending).ID)
Best Regards,
Qi

Was this reply helpful? Yes No
jackwu 25 on at

Like
a
(0)

Report
Copy link

Link copied!
Hi @v-qiaqi-msft ,

First, my SP list have only several items , so it does not exceed the limit of 500.

Second, my last submit ID was collected into IDlistcollection. The propose is to show all the item IDs in the data table but not only one. So we can't use '=' in below filter.
Filter('Homologation Test',ID=EditForm1.LastSubmit.ID)
Now the filter is working well with
Filter('Homologation Test', ID in IDlistCollection.ID_Collect)

Does that mean the delegation warning will always there and prevent you to filter a list which is over limited quantity (500) if we want to filter out the specific IDs from a collection? Will it fail to load after exceeding 500 items in SP?

Was this reply helpful? Yes No
v-qiaqi@microsoft.com Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!
Hi@jackwu,
@jackwu wrote:
Hi @v-qiaqi-msft ,
First, my SP list have only several items , so it does not exceed the limit of 500.
If it does, you can just ignore the delegation warning which could not affect your data processing.
@jackwu wrote:
Hi @v-qiaqi-msft ,
Does that mean the delegation warning will always there and prevent you to filter a list which is over limited quantity (500) if we want to filter out the specific IDs from a collection? Will it fail to load after exceeding 500 items in SP?
Yes, if your table is over 500 records, and the specific ID you want to figure out is well over 500 away, it will fail to load the record behind the 500 items. As I said before, you can go ahead to Advanced settings within App Settings, change the 500 by default to 2000 in "Data row limit for non-delegable queries". You can normally filter the specific within 2000 items within your SP list.
Sorry for the rash reply earlier, however, I've come up with another actionable solution that could solve the delegation issue.
You want to filter the Gallery with every submitted ID, we can absolutely create a collection to store these IDs, however, we can change that in a different way. I think that populating the Gallery directly with the collection could work.
Set the OnSelect property of the Submit Button as below:

SubmitForm(EditForm1); Collect( idcollection, EditForm1.LastSubmit.ID )

Set the Items property of the Gallery as below:

idcollection

Best Regards,
Qi

Was this reply helpful? Yes No
jackwu 25 on at

Like
a
(0)

Report
Copy link

Link copied!
Hi @v-qiaqi-msft ,

For this solution , the data table will only display the IDs in the table if only set items property like below (I'm using the data table but not gallery). Where I can define the data is from my SP list?

Set the Items property of the Gallery as below:
idcollection

Was this reply helpful? Yes No
v-qiaqi@microsoft.com Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

Hi@jackwu,
If you insist on using a DataTable, yes, there is no way currently to avoid this kind of delegation warning.
As I said before, you can go ahead to Advanced settings within App Settings, change the 500 by default to 2000 in the "Data row limit for non-delegable queries" in case you have more records in the future.
However, if you have less than 500 records now, you can ignore that delegation warning which won't affect your data.
Best Regards,
Qi

Was this reply helpful? Yes No