cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper II
Helper II

Filter not working on collection from SharePoint.

Hi community,

 

I am getting the result for a html while filtering a table that has been imported from an local excel file, but this is not working for the collection that I imported from a SharePoint list.   Please check the attachments.

 

Note:  Table5 is the excel table and MyCol is the collection from SharePoint list.

 

I am using Collect(Mycol, SabysachiMohanty_Test) for the collection.

 

Please help with this issue.

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted

Hi @ogh_sabyasachi ,

I also use Office365 SharePoint OnLine and regularly collect the last 2000 records of large lists with a collection sorted by ID descending (I notice you have yours sorted by name, which will (if it works) have the end of the alphabet, not the newest records.

I did one just now on a list of over 10,000 records and received the newest 2000 in descending ID order).

There are many other things in Microsoft documents that imply things cannot be done, but there are often workarounds like this one that may not suit everyone, but may work for you.

You simply need to sort by ID descending as per my post

UpdateContext({vPBI:PowerBIIntegration.Data.Full_Name});
ClearCollect(
   colTemp,
   Sort(
      Table5,
      ID,
      Descending
   )
);
ClearCollect(
   colCourse,
   Filter(
      colTemp, 
      vPBI in Full_Name
   )
);
Clear(colTemp)

Also how may items are in your list?

 

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

View solution in original post

27 REPLIES 27
Highlighted
Super User III
Super User III

Hi @ogh_sabyasachi ,

Two things to test. I have not looked at your HTML code too closely, but it seems to be valid.

Try this approach - firstly do a Collection

ClearCollect(
   colCourse,
   Filter(
      Table5, 
	  Full_Name in [@PowerBIIntegration].Data.Full_Name
   )
)

Then have a look and make sure you have the data you expect. If so, then do this with the HTML

"
   <table width='100V border='l' cellpadding='5' style='border:lpx solid black; border-collapse:collapse'>
      <tr style=’background-color:#efefef'>
         <td> Course Name </td>
      </tr>
   " & 
      Concat(
         colCourse,
         "<trxtd>" & FullNameOfCourse & " </td>
      </tr>"
      ) & "
   </table>
"

 

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

Highlighted

Hi @WarrenBelz,

 

I would like to elaborate a little more about the issue.  

 

1.  I am getting [@PowerBIIntegration].Data.Full_Name from PowerBI dashboard (via powerapp visual) and depending on the selection in the dashboard, it keeps on changing.

 

2.  I have another excel table where there are two columns Name and CourseName.  Suppose, this Name column has multiple rows of a person's name  and the CourseName column right to it shows multiple courses he has completed.  My goal is to get the name from the [@PowerBIIntegration].Data.Full_Name and filter it here and get the name of the courses in a table in an html text.

 

3.  This is working fine with the above "Excel screenshot" where Table 5 is the excel table.  Now, comes the challenge.

 

4.  Instead of excel, I want to use the SharePoint list, but the "in" operator in the "Filter" function is giving us a delegation error as the SharePoint list contains more than 2000 rows.  So, I tried to get theSharePoint list into a collection and filter the collection with  [@PowerBIIntegration].Data.Full_Name, but this code is not giving me any result as shown in above "Collection screenshot", neither any error.  Can you help to find out the reason?

 

5.  When I am trying to check the collection, it is only showing us 5 rows with a message "Here's a preview of first 5 items in this collection. "  Is there any option where we can expand the collection and see all the data?

 

Highlighted

@ogh_sabyasachi ,

That is a rather expanded issue to the one you posted - I assume now the filter is the issue rather than anything to do with the HTML?

Firstly if your PowerBIIntegration.Data.Full_Name is always constant for a particular collection, make a Variable

You mentioned the delegation issue - I assume you are looking for all records in Table5 where the Full_Name equals the PBI value?

Also are the target records going to always be in the newest 2000 records, if so try this

UpdateContext({vPBI:PowerBIIntegration.Data.Full_Name});
ClearCollect(
   colTemp,
   Sort(
      Table5,
      ID,
      Descending
   )
);
ClearCollect(
   colCourse,
   Filter(
      colTemp, 
      vPBI in Full_Name
   )
);
Clear(colTemp)

 

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

 

Highlighted

Hi @WarrenBelz, To answer your question regarding delegation issue, yes, I was looking for all records in a SharePoint list where the Full_Name equals the PBI value where I got the delegation error. I tried the above formula for a button, and it gave me the error as in the attached screen shot.   Then, I followed this link. It seems we are using Office 365 SharePoint Online.  The blog mentions "NOTE (VERY IMPORTANT) for Office 365 SharePoint Online Users: If you are getting this error from a SharePoint list that is hosted on Office 365 Cloud (eg. SharePoint Online), there is NO workaround (as of the writing of this KB article)." Please suggest if we have any alternative solution to this issue. Thanks

Highlighted

Hi @ogh_sabyasachi ,

I also use Office365 SharePoint OnLine and regularly collect the last 2000 records of large lists with a collection sorted by ID descending (I notice you have yours sorted by name, which will (if it works) have the end of the alphabet, not the newest records.

I did one just now on a list of over 10,000 records and received the newest 2000 in descending ID order).

There are many other things in Microsoft documents that imply things cannot be done, but there are often workarounds like this one that may not suit everyone, but may work for you.

You simply need to sort by ID descending as per my post

UpdateContext({vPBI:PowerBIIntegration.Data.Full_Name});
ClearCollect(
   colTemp,
   Sort(
      Table5,
      ID,
      Descending
   )
);
ClearCollect(
   colCourse,
   Filter(
      colTemp, 
      vPBI in Full_Name
   )
);
Clear(colTemp)

Also how may items are in your list?

 

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

View solution in original post

Highlighted

Hi @WarrenBelz,

 

Thank you for your reply.  I tried the code with ID descending and it worked (also working in html), but I want to filter on the whole sharepoint list instead of last 2000 records.  My sharepoint list currently has 5000 items.  Suppose we have the list as follows:

 

Row NumberFull_NameFullNameOfCourse
RowNo - 1Aefgh
RowNo - 2Aijkl
RowNo - 3Befgh
RowNo - 2005Amnop
RowNo - 4800Arstu

 

 

We want the table for A as follows:

CourseName
efgh
ijkl

mnop

rstu

 

 

Please check.

 

Highlighted

Hi @ogh_sabyasachi ,

Firstly the in (or Search) filter is not Delegable no matter how you code it, so you are simply limited to 2000 records maximum if you are querying your data source. The only way to possibly do what you need is to collect more than 2000 records as described below. This needs an additional numeric "shadow" field in your data set to the ID number (ID queries other than equals = are not Delegable). You will also have to back-populate your existing data with this field and then on the OnSuccess of new record Forms (you will need to use SubmitForm) do this. I have used IDRef below as the name of this field.

Patch(
    YouristName,
    {ID:YourNewFormName.LastSubmit.ID},
    {IDRef: YourNewFormName.LastSubmit.ID}
)

or

UpdateIf(
    MyListName,
    ID = YourNewFormName.LastSubmit.ID,
    {IDRef: YourNewFormName.LastSubmit.ID}
)

You can then Collect as many records as you want with 

ClearCollect(
   colCourse,
   MySPList
);
If(
   CountRows(colCourse) = 2000,
   Set(
      vID,
      Max(colCourse,IDRef)
   );
   Collect(
      colCourse,
      Filter(
         YourSPList,
         IDRef>vID
      )
   )
);
If(
   CountRows(colCourse) = 4000,
   Set(
      vID,
      Max(colCourse,IDRef)
   );
   Collect(
      colCourse,
      Filter(
         YourSPList,
         IDRef>vID
      )
   )
);
Then keep going in batches of 2000

 

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

 

Highlighted

Hi @WarrenBelz , can you please briefly elaborate a little about "shadow field" and "back populate".  I am not able to understand what are these, "YourNewFormName" and "LastSubmitID" here. 

 

Thanks and Regards

Highlighted

Hi @ogh_sabyasachi ,

Firstly, this is not a small exercise which is why I had not mentioned it before and you really need to understand what is being done and why.

YourNewFormName is the name I used (as I did not know your form name) for the form you are submitting a new record on - replace this with your form name. As I mentioned, you would have to use SubmitForm (not Patch) to create new records. There are other possibilities to update this field, but they are either more complex or possibly not as reliable.
LastSubmit is a Power Apps function that returns the value of the last record submitted by a form using SubmitForm, so YourNewFormName.LastSubmit.ID returns the ID of the new record you just saved.

Because ID is not a Delegable item when used in a Filter other than equals = (greater than > or less than < are not Delegable), you need to create a "shadow" numeric field with the same value as the ID in your list, which is then Delegable and allows the workaround collection of all the records to succeed. This field needs to be a numeric field in your list. By "back populate", I meant you would have to copy the ID into this field on all of your existing records as the code supplied only does new records from that point forward.

This is a lot  of work and effort unless you really need to query all records (not the newest 2000) and as another question, is there any other way (other than newest) that you can filter your list to under 2000 records with an existing text or numeric value? As an example, I have a field in several of my lists called "Status" and auto-populate this. Generally I am looking for things  like "Pending" or "Active" records, which are always less than 2000 and I Filter on this into a collection, then all other filters work.

 

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

News & Announcements

Community Blog

Stay up tp date on the latest blogs and activities in the community News & Announcements.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors
Users online (8,194)