Delegating CountRows for large Sharepoint List

[Dan007]

Hi everyone,

I'm currently working on a booking app for our office. Each user can come 8 times per month.

I'm trying to do a CountRows and Filter my SharePoint list to see how many records have the "Approved" status for the selected date.

My issue is that since the function is not delegated and the SharePoint list already has 3k bookings within the first month of the apps release, the counter and my If statements don't take in consideration the whole database. So users can go past the 8 days limit.

Here is a snippet of my code. Any help would be greatly appreciated :).

0 < limit - CountRows(

 Filter(

  Bookings,

  date = selected_date,

  mail = user_email,

  status = "Approved"

 )

)

[StalinPonnusamy · Accepted solution]

Hi @Dan007 

Please try this

0 < limit - CountRows(
With({BookingData:'Bookings'},
Filter(BookingData,
  date = selected_date,
  mail = user_email,
  status = "Approved"
 )
)
)

Thanks,
Stalin - Learn To Illuminate

[Dan007]

Thank you for your quick response, I'm not sure if I'm doing something wrong but I'm only getting one record :

With( {BookingData: Booking}, Filter( BookingData, Name = _UserEmail, StatusOfRequest = "Approved" ) )

[StalinPonnusamy]

Hi @Dan007 

This is a valid command which should get the count based on your data.

CountRows(With( {BookingData: Booking}, Filter( BookingData, Name = _UserEmail, StatusOfRequest = "Approved" ) ))

• _UserEmail is a valid one means is not blank right?
• Are Name and StatusOfReport are Single line text data types?
• Do you have data for this condition?

I tested similar and works fine.

[Dan007]

Set( _UserEmail, User().Email ); this is set OnStart

Name column in the SharePoint list stores the email address of the person who made the booking

StatusOfReport is either "Approved" or "Cancel"

both are single line of text.

I tried the code above, making a gallery using the table returned by the With() function. I'm filtering the database with my email address and the status approved. It should return a table with 13 records, but I only have one (see screenshot)

Thank you again for your time.

[StalinPonnusamy]

Hi @Dan007 

No problem. Will do some troubleshooting things.

1. Can you filter only with Status and see the results are OK?

With({BookingData:'Bookings'},
Filter(BookingData,
  status = "Approved"
 )
)

2. Once the count is OK then add the date condition, check the count now.

3. If both above counts is correct then I suspect about the email. Need to validate the email in UI vs Datasource

Insert a label on the screen and set the Text to

Office365Users.MyProfileV2().mail

Insert another label and set the Text to

User().Email

 
Thanks,
Stalin - Learn To Illuminate

[Dan007]

Thanks again for the quick answer.

It still gives inconsistent results, if my "Data Row Limit" is set to 2000, I get 1790 records back, if I set it to 500, it gives me 299. So my understanding is that it's still loading 2000 records and doing the filtering onto those chunks, so not completely delegable. Still could work since I'm expecting at max 8 records per user.

Another piece I need to figure out how to do is a forAll loop going trough an email collection, for each email do a database query to see if all selected people for the day at the office still have days left.

[StalinPonnusamy]

Hi @Dan007 

Can you please share the outcomes of the troubleshooting items?

[Dan007]

Both return my email as Text.
Filtering only by the approved status doesn't return all the values and it varies with the limit set in the settings.

[Dan007]

Any chance I could use a Flow to do the counting ?