cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ogh_sabyasachi
Helper III
Helper III

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.

27 REPLIES 27

Hi @WarrenBelz ,

 

I am not able to understand "how to back-populate the existing records with the ID value in a numeric field."  Can you please suggest some reference to read or videos to go through to get an idea?  Thank you!

Hi @ogh_sabyasachi ,

Create a SharePoint DataSheet view and put in two columns - your ID and the new column and open in Quick Edit mode.

Copy the ID columns about 50 at a time and paste into the new column, being careful to start at the same record you started copying from. It will take a while but you will only have to do this once. You probably could do it with code, but would risk SharePoint Throttling, so this way is more reliable and probably less 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.

Hi @WarrenBelz,

 

I have not been able to crack the issue.  By any chance, can I reach you one to one and discuss the issue.  If possible, please let me know at cs.sabya@gmail.com.  Thank you.

@ogh_sabyasachi ,

Can you please explain what it is that you cannot do.

As I mentioned much earlier, this is not something you should be attempting on your first app unless you understand the concept of the process.

Hi @WarrenBelz , 

 

Firstly, I thank you for your cooperation so far.  

 

To elaborate a little, here, my SharePoint list was created from an excel file, which I do not need to update.  That excel will be updated in quarterly basis by another department and we can think of updating the SharePoint list at that time.

 

So, I am not able to understand the use of updateif or patch function here. 

 

Secondly, I tried the ClearCollect code for a button by replacing "IDRef" with "ID" (for the SharePoint) list, the button is giving me delegation error and the collection is blank.

@ogh_sabyasachi ,

I am sorry, but you have completely missed the concept of the whole exercise I posted, which is why you need to understand it.

I will try to summarise it

  • You cannot use the SharePoint ID number as a filter to collect more than 2000 items as it is not Delegable.
  • You need to have another numeric field in your list (you refer as IDRef) with the same value on every record as the ID number (as numeric fields other than ID are Delegable on queries).
  • This means for all existing records, you need to copy the ID number to this field (which is the back-populate exercise I mentioned).
  • For every new record done in Power Apps, after that you need to set this number to the ID of the records just created, hence the UpdateIf on the FormName.LastSubmit.ID. If you are updating from Excel, then the "back-populate" exercise can be done.
  • All this whole exercise does is allow you to collect more than 2000 items.

The collection code works as long as you apply the correct values to the syntax. You cannot use the ID in the code as you have - it needs to be the "shadow" numeric field that you have populated with this ID number.

Please confirm you understand this now.

 

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.

Hi  @WarrenBelz ,

 

I created another numeric field in my SharePoint list (as IDRef) with the same value on every record as the ID number.  My SharePoint list has 9220 IDs.  Then, I used the following formula for a button and it did not show any error.

 

ClearCollect(
colCourse,
SabysachiMohanty_Test
);
If(
CountRows(colCourse) = 2000,
Set(
vID,
Max(colCourse,IDRef)
);
Collect(
colCourse,
Filter(
SabysachiMohanty_Test,
IDRef>vID
)
)
);

If(
CountRows(colCourse) = 4000,
Set(
vID,
Max(colCourse,IDRef)
);
Collect(
colCourse,
Filter(
SabysachiMohanty_Test,
IDRef>vID
)
)
);

If(
CountRows(colCourse) = 6000,
Set(
vID,
Max(colCourse,IDRef)
);
Collect(
colCourse,
Filter(
SabysachiMohanty_Test,
IDRef>vID
)
)
);

If(
CountRows(colCourse) = 8000,
Set(
vID,
Max(colCourse,IDRef)
);
Collect(
colCourse,
Filter(
SabysachiMohanty_Test,
IDRef>vID
)
)
);
If(
CountRows(colCourse) = 10000,
Set(
vID,
Max(colCourse,IDRef)
);
Collect(
colCourse,
Filter(
SabysachiMohanty_Test,
IDRef>vID
)
)
)

 

Then, I used the following formula for an HTML text to view the records.

 

"<table width='100%' border='1' cellpadding='5' style='border:1px solid black; border-collapse:collapse'>" & "<tr style='background-color:#efefef' >
<td> Course Name </td>
</tr>" &
Concat(
Sort(
Distinct(
Filter(colCourse, 'OG_ ID' in ([@PowerBIIntegration]).Data.OG_ID),
CourseName),
Result),
"<tr><td>" & Result & "</td></tr>"
) & "
</table>"

 

I have a table, but I have few records missing that is available in the SharePoint list.  The ID of these records 

7404, 8708, 6621, 7856, 9124, 9123, 9122, 6460, 7001, 7053.  On the other hand, I checked the ID of the items visible in the table and they all appear to be below 2000.  I am unable to find what might have gone wrong here.

 

@ogh_sabyasachi ,

Normally ID numbers should be in the order that the list was created, so this should not be necessary, but try wrapping this around the list name in each collection.

Sort(
   SabysachiMohanty_Test,
   IDRef
)

I cannot add anything else to this code as it always works and I have posted it several times for other people who have confirmed this. Also all collections other than the last one have to contain 2000 records, otherwise the next one will not 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
PA User Group

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

V3_PVA CAmpaign Carousel.png

Community Challenge - Giveaways!

Participate in the Power Virtual Agents Community Challenge

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Top Solution Authors
Top Kudoed Authors
Users online (2,082)