Filter not working on collection from SharePoint.

[Anonymous]

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.

[WarrenBelz]

Hi @Anonymous ,

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.

[Anonymous]

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?

[WarrenBelz]

@Anonymous ,

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.

[Anonymous]

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

[WarrenBelz · Accepted solution]

Hi @Anonymous ,

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.

[Anonymous]

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 Number	Full_Name	FullNameOfCourse
RowNo - 1	A	efgh
RowNo - 2	A	ijkl
RowNo - 3	B	efgh
RowNo - 2005	A	mnop
RowNo - 4800	A	rstu

We want the table for A as follows:

CourseName

efgh

ijkl

mnop

rstu

Please check.

[WarrenBelz]

Hi @Anonymous ,

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.

[Anonymous]

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

[WarrenBelz]

Hi @Anonymous ,

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.