ClearCollect OR SharePoint LIST NOT REFRESHING! PLEASE HELP ME

[ramyara]

Hi all

PLEASE help me with this query, as I have been facing issues with the SharePoint List volumes and with my collection and sharepoint list not refreshing on power, I'm going crazy. 

What happened?

1. My SharePoint list reached 5K records, so I moved the items to a backup sharepoint. Right now my list has less than 200 rows. 

2. Last 2 days, my list doesn't show items after 28 Sep 2023. I have 10 more records submitted and they dont show!

My App On Start Formula now:

Set(CurrUser, Office365Users.MyProfile());
ClearCollect(TeamNewColl, 'Team Workflow');
ClearCollect(ACApproversCollection, 'AC Approvers');
Set(varCurrentTab,"All Appeals")

My App on Start Formula before everything stopped at 28 Sep 2023. Here I created a Numerical ID filter column to capture IDs due to excess volumes. 

Set(CurrUser, Office365Users.MyProfile());
ClearCollect(TeamColl,Filter('Team Workflow', 'ID Filter' <= 9000), Filter('Team Workflow', 'ID Filter' >9000 And 'ID Filter'<=10000), Filter('Team Workflow', 'ID Filter' >10000 And 'ID Filter' <=12000));
ClearCollect(ACApproversCollection, 'AC Approvers');
Set(varCurrentTab,"All Appeals")

Note on ID: The latest one has an ID of 7995 assigned. Not sure if this info helps.

My Gallery View Formula where data stops at 28 Sep 2023.

SortByColumns
(Filter(TeamColl,
Switch(varCurrentTab,"Appeals Due in 7 Days",'Due Date'>=Today() And 'Due Date'<=DateAdd(Today(),7,TimeUnit.Days)
&&('Appeal Status'.Value="Review In Progress"|| 'Appeal Status'.Value="Unassigned" ||'Appeal Status'.Value="More Info Required"),
"Overdue Appeals",'Due Date'<Today()
&&('Appeal Status'.Value="Unassigned" || 'Appeal Status'.Value="Review In Progress" ||'Appeal Status'.Value="More Info Required"),"All Appeals",
1=1,"My Appeals",
'Created By'.Email=CurrUser.Mail,"Default",1=1)
&&StartsWith(MID,SearchTitle.Text)
&&('Appeal Status'.Value=RadioStatus.Selected.Value||
RadioStatus.Selected.Value=Blank())
&&('Appeal Reason'.Value=ComboBoxStatus.Selected.Value||
ComboBoxStatus.Selected.Value=Blank())
&&((ChckMe_1.Value&&'Council Reviewer'.Email=User().Email)||(ChckMe_1.Value= false &&chckother_2.Value= false )||(chckother_2.Value&&'Created By'.Email=cbUser_2.Selected.Email))),
"Created",
SortOrder.Descending)

1. I tried refreshing source data

2. I tried adding a new refresh button with Refresh( 'Team Workflow')

Nothing works.

I'll be grateful if you cvan please help mw refresh my Sharepoint List or Coll after 28 Sep 2023. This issue keeps happening every now and then.

Many thanks

[WarrenBelz]

Hi @ramyara ,

Sorry for the slight confusion, but you have 2 different collections - TeamCol where you are doing this

ClearCollect(
   TeamColl,
   Filter(
      'Team Workflow', 
      'ID Filter' <= 9000
   ), 
   Filter(
      'Team Workflow', 
      'ID Filter' > 9000 And 'ID Filter' <= 10000
   ), 
   Filter(
      'Team Workflow', 
      'ID Filter' > 10000 And 'ID Filter' <=12000
   )
);

and I assume you have less than 2,000 records with the 'ID Filter' under 9000. And now you have TeamNewCol

ClearCollect(
   TeamNewColl, 
   'Team Workflow'
);

where your list has less than 200 rows and you are missing some ? Assuming your Delegation limit is 2,000 and you can see your SharePoint list has the total rows you expect in Site Contents, have you tried re-indexing the list in SharePoint ?

[ramyara]

Hi @WarrenBelz 

My apologies for the typo. There is just 1 collection 'TeamColl'

and I did try reindexing the list, still the data doesn't show records after 28 Sep 2023. 😞

[WarrenBelz]

Hi @ramyara ,

Are you referring to SharePoint or Power Apps you cannot see the data in ? If SharePoint, try clearing your browser cache.

[ramyara]

Hi @WarrenBelz  I don't see the data flow from Sharepoint list to power app.

The data is working fine on sharepoint list. But after 28 Sep 2023, the new records haven't flowed to power apps.

I have tried making a clear collect column (as seen above) as data source

I have used Sharepoint list as direct data source, but both are not working.

I'm not able to figure what's wrong

Many thanks

Ramya

[WarrenBelz]

@ramyara ,

I mentioned that as there has been a recent issue with SharePoint not refreshing properly. Just to be totally clear: -

• You have a SharePoint list Team Workflow' with less than 200 records.
• You are running this 
  ClearCollect(TeamColl,'Team Workflow')
  and cannot see a number of recently created records in TeamColl

[ramyara]

Hi @WarrenBelz  Yes that's correct

[WarrenBelz]

Hi @ramyara ,

OK - I just needed to know exactly what I was dealing with. It is actually the opposite of the issue I was referring to earlier where recent data was visible in Power Apps but not in SharePoint.

I am clutching at straws a bit here (as this sounds like a corruption in the SharePoint index), but have you indexed (in SharePoint) any fields involved in queries ?

If you have no other alternatives, you can always make a new list, export the data over and then disconnect the old list, reconnect the new one and use the search function to replace all references.

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.

MVP (Business Applications)   Visit my blog Practical Power Apps

[ramyara]

Hi @WarrenBelz  Ill try this and come back with an update. thank you

[ramyara]

Hi @WarrenBelz 

The issue was with my CearCollect Formula. It was wrong. I had increase the filter in increments of 2000 records. It worked well after that 🙂

ClearCollect(
   TeamColl,
   Filter(
      'Team Workflow', 
      'ID Filter' <= 1999
   ), 
   Filter(
      'Team Workflow', 
      'ID Filter' > 1999 And 'ID Filter' <= 3999
   ), 
   Filter(
      'Team Workflow', 
      'ID Filter' > 3999 And 'ID Filter' <=5999
   )
);

Thank you for your immediate responses as always 🙂

Cheers